Goglides Dev 🌱

Balkrishna Pandey
Balkrishna Pandey

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode
ls -la /home/core/etcd-backup/
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode
  • Calculate storage usages based on namespaces
octosql "SELECT namespace, SUM(valueSize) AS S from etcd.snapshot GROUP BY namespace ORDER BY S DESC"
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

References:

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

Top comments (0)