Steampipe: Query your cloud infrastructure with SQL for faster insights

Episode #40: SQL as a universal gateway to your cloud infrastructure and beyond.

Dec 14, 2024

A steam-powered computer. Image generated with Flux1 Pro

In computer science, it is common to see programming languages emerge out of the blue every 5-10 years.

Every new language promises to improve your life for the better.

Keeping up to date as a software engineer is a challenging life.

I'm sure that engineers are still making a living by still programming in COBOL. For the rest of us, if you still want to be relevant and change jobs every 4-6 years, you need to learn the latest flavour of programming language.

However, if there is a single language that has yet to go out of fashion for over 50 years, that is SQL.

Over the years, many different flavours of databases and various SQL dialects have emerged, but at its core, SQL has stayed the same since it was created.

So, in my opinion, learning SQL is an excellent long-term investment for your career.

What if I tell you that you could use SQL to query the status of your cloud infrastructure?

Today, I would like to introduce you to Steampipe, a tool I recently added to my arsenal of DevOps power tools.

Steampipe lets you query your cloud infrastructure and many other APIs (Twitter, GitHub, Kubernetes, to name a few) via a simple SQL interface.

This article has the following sections:

What is Steampipe?
SQL to query everything: OSQuery and Cloudquery
How to query Hackernews via SQL

Want to connect?

👉 Follow me on LinkedIn and Twitter.

If you need 1-1 mentoring sessions, please check my Mentorcruise profile.

What is Steampipe?

Steampipe is an innovative open-source project that allows users to query cloud services and APIs using SQL.

Star history the Steampipe project — Star history of the Steampipe project

Between the main features of Steampipe, we can find:

Wide Range of Plugins: Steampipe has an extensive library of plugins that support popular cloud services and data sources. Currently, you can count more than 140 plugins.
Zero-ETL Data Access: Steampipe provides Zero-ETL data access, allowing you to fetch data directly from cloud APIs without complex integrations.
Open-Source: Steampipe is an open-source tool that ensures data security and integrity and is free for developers.

Notable Steampipe integrations are the following:

all major cloud providers: AWS, GCP, Azure
Kubernetes
OpenAI
GitHub

You can use Steampipe in different ways:

Default CLI: a solution with its own PostgreSQL database and plugin management.
Postgres FDWs: native Postgres Foreign Data Wrappers that translate APIs to foreign tables.
SQLite extensions: virtual tables that translate your queries into API calls.
Export CLIs: each exporter is a stand-alone binary that extracts data from Steampipe without a database.

OSQuery: SQL to query your OS

Steampipe is not the only project that provides an SQL interface for APIs.

Osquery provides a SQL interface to query OS resources like processes, open network connections and much more.

Its website describes the project as:

osquery exposes an operating system as a high-performance relational database. With osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events or file hashes.

An example query with Osquery is as follows:

SQL query to list the ports open on the local machine via Osquery

With the above query, you can list the open ports on your machine at localhost.

How to query Hackernews via SQL

Among the many integrations provided by Steampipe, Hackernews is an interesting one.

I chose this integration for my demo since Hackernews' API doesn't require an API token, which makes it easy to query.

You only need to install the Hackernews plugin via the command steampipe plugin install hackernews and then use Steampipe binary to query that API via steampipe query.

Each plugin provides multiple tables. You can find the full list of tables at Tables in Hacker news.

In this example, you can use the table hackernews_top to query the top stories for today sorted by their score.