# New: AWS Multi-Region Queries and Query Caching

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

By Steampipe Team
Published: 2021-02-23


<div className="row mb-3"> 
  <div className="col col-12 col-lg-6">
    <h2>What is Steampipe?</h2>
    <p>SQL is an expressive and powerful language for asking questions of structured data. Steampipe, an open-source project from <a href="https://turbot.com">Turbot</a>, enables cloud pros (e.g. software developers, operations engineers and security teams) to query their favorite cloud services with SQL.</p>
    <p>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.</p>
  </div>
  <div className="col col-12 col-lg-1"></div>
  <div className="col col-12 col-lg-5 mt-5">
    <Terminal title="steampipe cli">
      <TerminalCommand enableCopyToClipboard={false}>
        {`
         
select 
  region, 
  instance_state as state, 
  instance_type as type
from 
  aws_ec2_instance;
        `}
      </TerminalCommand>
        <TerminalResult>
    {`
+-----------+---------+-----------+
| region    | state   | type      |
+-----------+---------+-----------+
| eu-west-1 | running | t3.medium |
| eu-west-2 | running | m5a.large |
| us-east-1 | running | t3.large  |
+-----------+---------+-----------+
    `}
      </TerminalResult>
    </Terminal>
  </div>
</div>

# 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).

<br />

<Terminal mode="light" title="Multi-Region Query">
  <TerminalCommand>
    {`
.timing on
select
  region,
  instance_id,
  instance_state,
  instance_type,
  title
from 
  aws_ec2_instance;
    `}
  </TerminalCommand>
  <TerminalResult>
    {`
+-----------+---------------------+----------------+---------------+-----------------+
| 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
    `}
  </TerminalResult>
</Terminal>

### 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.


   <Terminal mode="light" title="">
      <TerminalCommand  withPrompt={false} enableCopyToClipboard={false}>
    {`
> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;
    `}
  </TerminalCommand>
  <TerminalResult>
    {`
+-----------+---------------------+----------------+---------------+-----------------+
| 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
    `}
      </TerminalResult>
    </Terminal>

### Real Fast (¼ Second)

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

   <Terminal mode="light" title="">
      <TerminalCommand withPrompt={false}  enableCopyToClipboard={false}>
    {`
> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;
    `}
  </TerminalCommand>
  <TerminalResult>
    {`
+-----------+---------------------+----------------+---------------+-----------------+
| 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
    `}
      </TerminalResult>
    </Terminal>

### Blazing Fast ( < 1 Microsecond)

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

   <Terminal mode="light" title="">
      <TerminalCommand withPrompt={false} enableCopyToClipboard={false}>
    {`
$ export STEAMPIPE_CACHE=true
$ steampipe query
> .timing on
> select region, instance_id, instance_state, instance_type, title from aws_ec2_instance;
    `}
  </TerminalCommand>
  <TerminalResult>
    {`
+-----------+---------------------+----------------+---------------+-----------------+
| 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
    `}
      </TerminalResult>
    </Terminal>

## 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:

<div className="row mb-5 mt-5"> 
  <div className="col col-0 col-md-1"></div>
  <div className="col col-12 col-md-8 col-xl-6">

<Terminal mode="light" title="~/.steampipe/config/aws.spc">
      <TerminalCommand withPrompt={false} language="json" enableCopyToClipboard={false} >
    {`
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"
  ]
}
 
  
    `}
  </TerminalCommand>
    </Terminal>

</div></div>

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:

<div className="row mb-5 mt-5"> 
  <div className="col col-0 col-md-1"></div>
  <div className="col col-12 col-md-8 col-xl-6">
<Terminal mode="light" title="Steampipe CLI">
      <TerminalCommand enableCopyToClipboard={false} >
    {`
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    |
+-------+
    `}
  </TerminalCommand>
    </Terminal>
</div></div>

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

<div className="row mb-5 mt-5"> 
  <div className="col col-0 col-md-1"></div>
  <div className="col col-12 col-md-8 col-xl-6">
<Terminal mode="light" title="Steampipe CLI">
      <TerminalCommand withPrompt={false} enableCopyToClipboard={false}>
    {`
> 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;
    `}
  </TerminalCommand>
   <TerminalResult>
    {`
   +--------------+-------+
   | account_id   | count |
   +--------------+-------+
   | 111222333444 | 2     |
   | 444555666777 | 15    |
   | 888899990000 | 34    |
   +--------------+-------+
    `}
      </TerminalResult>
    </Terminal>

</div></div>


## 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).