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.

{` 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](#blazing-fast-query-response-with-new-query-caching), and enables you to do more work in each query with our new [multi-region](#aws-multi-region-queries-with-steampipe) and [connection configuration](#connection-configuration-management-in-steampipe) 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](https://hub.steampipe.io/plugins/turbot/aws) you can perform multi-region queries that execute in parallel against all AWS regions within your account (see below for multi-account too).
{` .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:

{` 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:

{` 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:

{` > 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](https://github.com/turbot/steampipe/blob/main/CHANGELOG.md).

New: AWS Multi-Region Queries and Query Caching