It's Just SQL!

Steampipe leverages PostgreSQL Foreign Data Wrappers to provide a SQL interface to external services and systems. Steampipe uses an embedded PostgreSQL database (currently, version 14.2.0), and you can use standard Postgres syntax to query Steampipe.

Basic SQL

Like most popular relational databases, Postgres complies with the ANSI SQL standard - If you know SQL, you already know how to query Steampipe!

You can query all the columns in a table:

This is inefficient though -- you should only query the columns that you need. This will save Steampipe from making API calls to gather data that you don't want anyway:

You can filter rows where columns only have a specific value:

or a range of values:

or match a pattern:

You can filter on multiple columns, joined by and or or:

You can sort your results:

You can sort on multiple columns, ascending or descending:

You can group and use standard aggregate functions. You can count results:

or sum them:

or find min, max, and average:

You can exclude duplicate rows:

or exclude all but one matching row:

Of course the real power of SQL is in combining data from multiple tables!

You can join tables together on a key field. When doing so, you may need to alias the tables (with as) to disambiguate them:

You can use outer joins (left, right, or full) when you want to find non-matching rows as well. For example to see all your volumes and the number snapshots from them:

or to find snapshots from volumes that no longer exist:

You can use union queries to combine datasets. Note that union all is much more efficient if you don't need to eliminate duplicate rows.