octosql to evaluate etcd snapshot on MAC

Why this blog?

This blog post is a reminder for my future self about a tool that I recently learned about. My colleague William Caban recommended it to me as an excellent tool for gathering data based on from etcd using SQL query. Thanks, William, for the recommendation! While busy with other tasks, I wanted to ensure I remembered this helpful tool, so I wrote a quick note here for future reference.

What is OctoSQL

OctoSQL is an open-source tool allowing you to write SQL queries that work simultaneously with multiple data sources. It is designed to handle modern data sources like CSV, JSON, Parquet, and Kafka, as well as traditional databases like MySQL, PostgreSQL, and SQLite.

With OctoSQL, you can join data from multiple sources, filter and aggregate data, and perform other SQL operations on data that would typically require various tools or scripts to handle. OctoSQL is written in Go and can be used as a standalone CLI tool or as a library in other Go programs.

OctoSQL provides a unified SQL interface for working with data stored in different sources, making it a powerful tool for data integration, analytics, and reporting.

Install OctoSQL and etcdsnapshot on MAC

Here is a quick list of commands I followed to install this tool on my local machine.

brew install cube2222/octosql/octosql
mkdir -p ~/.octosql/ && echo "{\"snapshot\": \"etcdsnapshot\"}" > ~/.octosql/file_extension_handlers.json
octosql plugin repository add https://raw.githubusercontent.com/tjungblu/octosql-plugin-etcdsnapshot/main/plugin_repository.json
octosql plugin install etcdsnapshot/etcdsnapshot

Create etcd snapshots of etcd database

ETCD_NODE="master1"
ssh core@$ETCD_NODE
sudo su
mkdir /home/core/etcd-backup
/usr/local/bin/cluster-backup.sh /home/core/etcd-backup
chown -R core.core /home/core/etcd-backup/

ls -la /home/core/etcd-backup/

Output:

total 1415260
drwxr-xr-x. 2 core core         96 Apr 25 15:39 .
drwx------. 6 core core        142 Apr 25 15:39 ..
-rw-------. 1 core core 1449136160 Apr 25 15:39 snapshot_2023-04-25_153941.db
-rw-------. 1 core core      84881 Apr 25 15:39 static_kuberesources_2023-04-25_153941.tar.gz

• In your machine where you have installed octosql try following

scp -r core@$ETCD_NODE:/home/core/etcd-backup/ . 
mv snapshot_2023-04-25_153941.db etcd.snapshot

OctoSQL Examples

Here are some cool command list now you can run against your etcd.snapshot.

• Select * from etcd snapshot

octosql "select * from etcd.snapshot limit 1"

• Calculate storage usages based on namespaces

octosql "SELECT namespace, SUM(valueSize) AS S from etcd.snapshot GROUP BY namespace ORDER BY S DESC"

You can imaging all cool other sql operations

octosql "select count(*) from etcd.snapshot"
octosql "select namespace,count(*) from etcd.snapshot group by namespace"
octosql "select namespace,count(*) as count from etcd.snapshot group by namespace  order by count desc"
octosql "select resourceType, count(resourceType) as s from etcd.snapshot GROUP BY resourceType order by s"

References:

https://github.com/cube2222/octosql
https://github.com/tjungblu/octosql-plugin-etcdsnapshot