Steampipe renders SQL as the main query language
for more than purely databases, Cloud infrastructure included. Let's find out how.

The notion behind Steampipe is that SQL has been the data access standard for decades. It levels the playing field, easily integrates with other systems and accelerates delivery. So why not leverage it for things other than the database, like querying APIs and Cloud services?

Steampipe does just that; it enables a uniform data access method for querying:

• Cloud providers like AWS, Azure, GCP, Cloudflare, Alibaba Cloud, IBM Cloud, and Oracle Cloud
• Cloud-based services likeGitHub, Zoom, Okta, Slack, Salesforce, and ServiceNow
• Structured files like CSV, YML and Terraform
• Ad hoc investigation of network services like DNS & HTTP
• Kubernetes configurations, manifest files, and helm charts
• Use SQL to execute commands on local or remote hosts

The question is how does it pull this feat off?
Under the hood Steampipe leverages PostgreSQL and the Foreign Data Wrappers to present the various data sources as database tables. The Steampipe FDW does not directly interface with external systems, but instead relies on plugins to implement the API/provider specific code and return it in a standard format via gRPC. Thus all the Postgres-specific logic is encapsulated in the FDW, and API and service specific code resides only in the plugin which translate the APIs to SQL-queryable tables.

As such there's a myriad of plugins for any case imaginable, like:

OpenAPI
Use SQL to instantly query resources from OpenAPI.

steampipe plugin install openapi

Query all the endpoints available for an API:

select
api_path,
operation_id,
summary,
deprecated,
tags
from
openapi_path;

AWS
Use SQL to query infrastructure including servers, networks, identity and more from AWS.

select
title,
create_date,
mfa_enabled
from
aws_iam_user

Hacker News
Steampipe marshals the HN API data into queryable tables letting you interactively explore it via the command line interface or your favorite SQL client.

steampipe plugin install hackernews

select
score,
descendants as comments,
title
from
hackernews_top
where
type = 'story'
and lower(title) like '%sql%'
order by
score desc;

CSV
Query data from the users.csv file:

select
first_name,
last_name
from
users;

There's the plugin repository where you'll find plugins for the following categories:

• AI
• Asset Management
• IaC
• Internet
• Media
• Network
• PaaS
• Public Cloud
• SaaS
• Security
• Software Development
• Virtualization
• Website Monitoring

There's 146 plugins in total at the time of writing but the list keeps growing.

Installation wise:

# MacOS
brew install turbot/tap/steampipe

# Linux or Windows (WSL2)
sudo /bin/sh -c "$(curl -fsSL https://steampipe.io/install/steampipe.sh)"

Then install a plugin for your favorite service (e.g. AWS, Azure, GCP, GitHub, Kubernetes, Hacker News, etc).

What's more, Steampipe latest architecture decouples Steampipe from its batteries-included Postgres database, so you could use plugins with your own database. That effort resulted in three new plugin flavors: for Postgres, for SQLite, and for standalone export.

A great tool with a great ecosystem. SQL for the win!

More Information

Steampipe

Steampipe plugins

Related Articles

You Can Now Code Websites With SQL

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info