What is Steampipe?

SQL is an expressive and powerful language for asking questions of structured data. Steampipe, an open-source project from Turbot, enables cloud pros (e.g. software developers, operations engineers and security teams) to query their favorite cloud services with SQL.

The heart of Steampipe is an intuitive command line interface (CLI) that solves the challenges encountered when asking questions of cloud resources and services. Traditional tools and custom scripts that provide visibility into these services are cumbersome, inconsistent across providers and painful to maintain. Steampipe provides a consistent, explorable and interactive approach across IaaS, PaaS and SaaS services.

steampipe cli


         
select 
  region, 
  instance_state as state, 
  instance_type as type
from 
  aws_ec2_instance;


+-----------+---------+-----------+
| region    | state   | type      |
+-----------+---------+-----------+
| eu-west-1 | running | t3.medium |
| eu-west-2 | running | m5a.large |
| us-east-1 | running | t3.large  |
+-----------+---------+-----------+

New in Release v0.2.0

From the moment you have a question about your cloud, Steampipe is already at work giving you structured tables to formulate that question as SQL and execute it against your live cloud APIs. Steampipe v0.2.0 delivers an even faster response to those questions with our preview of query caching, and enables you to do more work in each query with our new multi-region and connection configuration features.

AWS multi-region queries with Steampipe

Starting with version 0.2.0 of the Steampipe CLI and version 0.5.0 of the Steampipe AWS Plugin you can perform multi-region queries that execute in parallel against all AWS regions within your account (see below for multi-account too).

Multi-Region Query


.timing on
select
  region,
  instance_id,
  instance_state,
  instance_type,
  title
from 
  aws_ec2_instance;


+-----------+---------------------+----------------+---------------+-----------------+
| region    | instance_id         | instance_state | instance_type | title           |
+-----------+---------------------+----------------+---------------+-----------------+
| eu-west-1 | i-003d889c8dc91f939 | running        | t3.medium     | Dev Bastion     |
| eu-west-2 | i-072ee9d889c80c59a | running        | m5a.large     | Squid           |
| us-east-1 | i-0667842133f5baeb7 | stopped        | t3.large      | WinBastion      |
| us-east-2 | i-059b0d1eaa04232f8 | running        | t3.large      | ECS Host        |
| us-east-2 | i-0e6f804203eb894eb | running        | t2.micro      | Linux Bastion   |
+-----------+---------------------+----------------+---------------+-----------------+
Time: 244.685827ms

How can it be so fast?

Steampipe is smart! When you execute a query it fans out concurrent connections to the configured regions, aggregates the results and then presents them to you as one result set. The speed of the query is just limited to the speed of the slowest regional response.

Blazing fast query response with new query caching

Believe it or not, we can go even faster. Once you enable the new query caching feature, subsequent queries to the same data source will operate out of the in-memory cache, and return result sets in the blink of an eye.

Fast (1 Second)

The first time you query a connection, Steampipe needs to create and authenticate API connections, in this case it took a little over 1 second to establish connections across 16 AWS regions and return results.


> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;


+-----------+---------------------+----------------+---------------+-----------------+
| region    | instance_id         | instance_state | instance_type | title           |
+-----------+---------------------+----------------+---------------+-----------------+
| eu-west-1 | i-003d889c8dc91f939 | running        | t3.medium     | Dev Bastion     |
| eu-west-2 | i-072ee9d889c80c59a | running        | m5a.large     | Squid           |
| us-east-1 | i-0667842133f5baeb7 | stopped        | t3.large      | WinBastion      |
| us-east-2 | i-059b0d1eaa04232f8 | running        | t3.large      | ECS Host        |
| us-east-2 | i-0e6f804203eb894eb | running        | t2.micro      | Linux Bastion   |
+-----------+---------------------+----------------+---------------+-----------------+
Time: 1.045864439s

Real Fast (¼ Second)

With the connections now cached, the same query returns in less than ¼ second.


> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;


+-----------+---------------------+----------------+---------------+-----------------+
| region    | instance_id         | instance_state | instance_type | title           |
+-----------+---------------------+----------------+---------------+-----------------+
| eu-west-1 | i-003d889c8dc91f939 | running        | t3.medium     | Dev Bastion     |
| eu-west-2 | i-072ee9d889c80c59a | running        | m5a.large     | Squid           |
| us-east-1 | i-0667842133f5baeb7 | stopped        | t3.large      | WinBastion      |
| us-east-2 | i-059b0d1eaa04232f8 | running        | t3.large      | ECS Host        |
| us-east-2 | i-0e6f804203eb894eb | running        | t2.micro      | Linux Bastion   |
+-----------+---------------------+----------------+---------------+-----------------+
Time: 243.693268ms

Blazing Fast ( < 1 Microsecond)

With query caching enabled, subsequent queries to the same table are more than 1000x faster!


$ export STEAMPIPE_CACHE=true
$ steampipe query
> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;


+-----------+---------------------+----------------+---------------+-----------------+
| region    | instance_id         | instance_state | instance_type | title           |
+-----------+---------------------+----------------+---------------+-----------------+
| eu-west-1 | i-003d889c8dc91f939 | running        | t3.medium     | Dev Bastion     |
| eu-west-2 | i-072ee9d889c80c59a | running        | m5a.large     | Squid           |
| us-east-1 | i-0667842133f5baeb7 | stopped        | t3.large      | WinBastion      |
| us-east-2 | i-059b0d1eaa04232f8 | running        | t3.large      | ECS Host        |
| us-east-2 | i-0e6f804203eb894eb | running        | t2.micro      | Linux Bastion   |
+-----------+---------------------+----------------+---------------+-----------------+
Time: 653.39µs

Connection configuration management in Steampipe

Regardless of what cloud you are working with, you are likely to need connections to more than one environment. Everyone has multiple Slack channels and Github repositories that we work with and most cloud pros have multiple AWS accounts that they work with on a daily basis.

The latest Steampipe release now makes it even easier to work with (and across multiple api connections). This example shows how to configure multiple AWS accounts in a single Steampipe configuration:

~/.steampipe/config/aws.spc


connection "dmi_scranton" {
  plugin      = "aws" 
  profile     = "scranton"
  regions     = ["us-east-2"]
}
 
connection "dmi_albany" {
  plugin      = "aws" 
  profile     = "albany"
  regions     = ["us-east-1"]
} 
 
connection "dmi_global" {
  plugin      = "aws" 
  profile     = "dmi_corp"
  regions     = [
    "eu-west-1", 
    "eu-west-2", 
    "us-east-1", 
    "us-east-2"
  ]
}

In the example above, I chose different [profiles] from my ~/.aws/config configuration for the connection credentials. You can optionally configure Steampipe to use access key/secret key pairs instead of your AWS profile if desired. After changing any .spc configuration, restart Steampipe.

Each account configuration creates a separate namespace in the Steampipe embedded Postgres DB; this allows us to query different accounts using standard schema.table_name syntax:

Steampipe CLI


select 
  count(*) 
from  
  dmi_albany.aws_s3_bucket;
 
+-------+
| count |
+-------+
| 2     |
+-------+
 
select 
  count(*) 
from  
  dmi_scranton.aws_s3_bucket;
 
+-------+
| count |
+-------+
| 34    |
+-------+
 
select 
  count(*) 
from  
  dmi_global.aws_s3_bucket;
   
+-------+
| count |
+-------+
| 15    |
+-------+

Aggregating results across accounts can be as simple as an SQL union statement: