New! Filter and export controls, plus lots of new mods and plugins. →
Announcement

New: AWS Multi-Region Queries and Query Caching

Learn more about the v0.2.0 release of the opensource Steampipe CLI.

Steampipe Team
7 min. read - February 23, 2021

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:

Steampipe CLI
> select account_id, count(*) as buckets
from dmi_scranton.aws_s3_bucket
group by account_id
union
select account_id, count(*) as buckets
from dmi_albany.aws_s3_bucket
group by account_id
union
select account_id, count(*) as buckets
from dmi_global.aws_s3_bucket
group by account_id;

   +--------------+-------+
   | account_id   | count |
   +--------------+-------+
   | 111222333444 | 2     |
   | 444555666777 | 15    |
   | 888899990000 | 34    |
   +--------------+-------+
    

Yes, we think that is super cool too! Get started today.

Seeing Steampipe’s multi-region and multi-account queries definitely put a smile on our product team’s face, we hope it is both delightful and a huge time saver for you in your day-to-day cloud work. For even more good stuff, checkout the full release notes on Steampipe v0.2.0.