Learn Steampipe | Documentation | Steampipe (2024)

Steampipe provides zero-ETL tools for fetching data directly from APIs and services. Steampipe is offered in several distributions:

  • The Steampipe CLI exposes APIs and services as a high-performance relational database, enabling you to write SQL-based queries to explore dynamic data. The Steampipe CLI is a turnkey solution that includes its own PostgreSQL database including plugin management.
  • Steampipe Postgres FDWs are native Postgres Foreign Data Wrappers that translate APIs to foreign tables. Unlike Steampipe CLI, which ships with its own Postgres server instance, the Steampipe Postgres FDWs can be installed in any supported Postgres database version.
  • Steampipe SQLite Extensions provide SQLite virtual tables that translate your queries into API calls, transparently fetching information from your API or service as you request it.
  • Steampipe Export CLIs provide a flexible mechanism for exporting information from cloud services and APIs. Each exporter is a stand-alone binary that allows you to extract data using Steampipe plugins without a database.
  • Turbot Pipes is the only intelligence, automation & security platform built specifically for DevOps. Pipes provides hosted Steampipe database instances, shared dashboards, snapshots, and more!

This tutorial uses the Steampipe CLI. Let's dive in...

Install the AWS plugin

This tutorial uses the AWS plugin. To get started, download and install Steampipe, and then install the plugin:

steampipe plugin install aws

Steampipe will download and install additional components the first time you run steampipe query so it may take a few seconds to load initially.

Out of the box, Steampipe will use your default AWS credentials from your credential file and/or environment variables, so you'll need to make sure those are set up as well. If you can run aws ec2 describe-vpcs, you're good to go. (The AWS plugin provides additional examples to configure your credentials, and even configure steampipe to query multiple accounts and multiple regions.)


Steampipe provides commands that allow you to discover and explore the tables and data without leaving the query shell. (Of course, this information is all available in the hub if online docs are more your speed...)

Let's fire up Steampipe! Run steampipe query to open an interactive query session:

$ steampipe query

Welcome to Steampipe v0.5.0

For more information, type .help


Now run the .tables meta-command to list the available tables:

> .tables

==> aws


| table | description |


| aws_accessanalyzer_analyzer | AWS Access Analyzer |

| aws_account | AWS Account |

| aws_acm_certificate | AWS ACM Certificate |

| aws_api_gateway_api_key | AWS API Gateway API Key |



As you can see, there are quite a few tables available in the AWS plugin!

It looks like there's an aws_iam_role table - let's run .inspect to see what's in that table:

> .inspect aws_iam_role


| column | type | description |


| account_id | text | The AWS Account ID in which the resource is located. |

| akas | jsonb | Array of globally unique identifier strings (also known as) for the resource. |

| arn | text | The Amazon Resource Name (ARN) specifying the role. |

| assume_role_policy | jsonb | The policy that grants an entity permission to assume the role. |

| assume_role_policy_std | jsonb | Contains the assume role policy in a canonical form for easier searching. |

| attached_policy_arns | jsonb | A list of managed policies attached to the role. |

| create_date | timestamp without time zone | The date and time when the role was created. |

| description | text | A user-provided description of the role. |

| inline_policies | jsonb | A list of policy documents that are embedded as inline policies for the role.. |

| inline_policies_std | jsonb | Inline policies in canonical form for the role. |

| instance_profile_arns | jsonb | A list of instance profiles associated with the role. |

| max_session_duration | bigint | The maximum session duration (in seconds) for the specified role. Anyone who uses the AWS CLI, or |

| | | API to assume the role can specify the duration using the optional DurationSeconds API parameter |

| | | or duration-seconds CLI parameter. |

| name | text | The friendly name that identifies the role. |

| partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). |

| path | text | The path to the role. |

| permissions_boundary_arn | text | The ARN of the policy used to set the permissions boundary for the role. |

| permissions_boundary_type | text | The permissions boundary usage type that indicates what type of IAM resource is used as the permi |

| | | ssions boundary for an entity. This data type can only have a value of Policy. |

| region | text | The AWS Region in which the resource is located. |

| role_id | text | The stable and unique string identifying the role. |

| role_last_used_date | timestamp without time zone | Contains information about the last time that an IAM role was used. Activity is only reported for |

| | | the trailing 400 days. This period can be shorter if your Region began supporting these features |

| | | within the last year. The role might have been used more than 400 days ago. |

| role_last_used_region | text | Contains the region in which the IAM role was used. |

| tags | jsonb | A map of tags for the resource. |

| tags_src | jsonb | A list of tags that are attached to the role. |

| title | text | Title of the resource. |



Now that we know what columns are available in the aws_iam_role table, let's run a simple query to list the roles:

select name from aws_iam_role


| name |


| AWSServiceRoleForOrganizations |

| aws-elasticbeanstalk-service-role |

| admin |

| AWSServiceRoleForAmazonElasticsearchService |

| user |

| AWSServiceRoleForAccessAnalyzer |

| CLoudtrailRoleForCloudwatchLogs |

| aws-elasticbeanstalk-ec2-role |

| rds_metadata |

| metadata |

| AWSServiceRoleForAutoScaling |

| operator |

| s3crr_role_for_vanedaly-replicated-bucket-01_to_test-repl-dest-f |

| iam_owner |

| ec2_owner |

| ec2_operator |

| AWSServiceRoleForSSO |


Now let's ask a more interesting question. Let's find roles that have no boundary policy applied:






permissions_boundary_arn is null;


| name |


| AWSServiceRoleForOrganizations |

| aws-elasticbeanstalk-service-role |

| AWSServiceRoleForAmazonElasticsearchService |

| AWSServiceRoleForAccessAnalyzer |

| CLoudtrailRoleForCloudwatchLogs |

| aws-elasticbeanstalk-ec2-role |

| AWSServiceRoleForAutoScaling |

| s3crr_role_for_vanedaly-replicated-bucket-01_to_test-repl-dest-f |

| AWSServiceRoleForSSO |


Like any database, we can join tables together as well. For instance, we can find all the roles that have AWS-managed policies attached:






aws_iam_role as r,

jsonb_array_elements_text(attached_policy_arns) as policy_arn,

aws_iam_policy as p


p.arn = policy_arn

and p.is_aws_managed;


| name | policy_arn | is_aws_managed |


| aws-elasticbeanstalk-ec2-role | arn:aws:iam::aws:policy/AWSElasticBeanstalkWorkerTier | true |

| aws-elasticbeanstalk-ec2-role | arn:aws:iam::aws:policy/AWSElasticBeanstalkMulticontainerDocker | true |

| admin | arn:aws:iam::aws:policy/ReadOnlyAccess | true |

| AWSServiceRoleForSSO | arn:aws:iam::aws:policy/aws-service-role/AWSSSOServiceRolePolicy | true |

| AWSServiceRoleForAccessAnalyzer | arn:aws:iam::aws:policy/aws-service-role/AccessAnalyzerServiceRolePolicy | true |

| aws-elasticbeanstalk-service-role | arn:aws:iam::aws:policy/service-role/AWSElasticBeanstalkEnhancedHealth | true |

| AWSServiceRoleForElasticLoadBalancing | arn:aws:iam::aws:policy/aws-service-role/AWSElasticLoadBalancingServiceRolePolicy | true |

| aws-elasticbeanstalk-service-role | arn:aws:iam::aws:policy/service-role/AWSElasticBeanstalkService | true |

| AWSServiceRoleForOrganizations | arn:aws:iam::aws:policy/aws-service-role/AWSOrganizationsServiceTrustPolicy | true |


What's Next?

We've merely scratched the surface of what you can do with Steampipe!

Learn Steampipe | Documentation | Steampipe (2024)


What language is steampipe? ›

We chose SQL as the language for Steampipe as much for its ubiquity as its power - It was invented in 1970, and became an ANSI standard in 1986.

What is the use of steampipe? ›

Steampipe provides zero-ETL tools for fetching data directly from APIs and services. Steampipe is offered in several distributions: The Steampipe CLI exposes APIs and services as a high-performance relational database, enabling you to write SQL-based queries to explore dynamic data.

What is the alternative to steampipe? ›

Resoto can be used as an alternative to Steampipe .

Steampipe is developed and maintained by Turbot , a cloud governance platform.

How to exit steampipe query? ›

You can exit the query shell by pressing Ctrl+d on a blank line, or using the . exit command.

What is the difference between steampipe and turbot? ›

Turbot Pipes is an intelligence, automation & security platform built specifically for DevOps. Steampipe is an open-source zero-ETL engine to instantly query cloud APIs using SQL.

What is the purpose of the steam pipe? ›

Steam pipe may refer to: A pipe designed to carry pressurized steam from a boiler to the working components, i.e. the steam engine(s) or turbine(s). Such piping usually includes valves to control the routing of the steam, or to stop the flow altogether.

What is the alternative to steam pipe? ›

The best alternatives to Steampipe are PopSQL, Slack SQL, and ClaySQL.

How to install Steampipe plugin? ›

To install it:
  1. Create a subdirectory .steampipe/plugins/local/myplugin.
  2. Name your plugin binary myplugin.plugin , and copy it to .steampipe/plugins/local/myplugin/myplugin.plugin.
  3. Create a ~/.steampipe/config/myplugin.spc config file containing a connection definition that points to your plugin: connection "myplugin" {

What is Steampipe beta? ›

The Portal steampipe beta is a separate download that allows VR integration so you can play on the occulous rift,etc.

How do I disable steampipe cache? ›

When running an interactive steampipe query session, you can use the . cache meta-command command to enable, disable, or clear the cache for the session. This command affects the caching behavior for this session only - it does not change the server caching options, and changes will not persist after the session ends.

What is the output format of steampipe query? ›

By default, the output format is table which provides a tabular, human-readable view. You can use the . output command to choose a different format. Valid values for this command are json , csv , line , and table .

What language is steam built in? ›

The Steam client and SDK are written in C++, so our team members all possess a strong understanding of that language as well as prior experience with game platform SDKs and game engines. Our team has excellent communication skills and presents technical issues to both lay and specialist audiences.

What material is used for steam pipes? ›

What is the typical pipe material for steam systems? Pipes for steam systems are commonly manufactured from carbon steel to ASME B 16.9 A106. The same material may be used for condensate lines, although copper tubing is preferred in some industries.

What are the biggest languages on steam? ›

The Most Popular Languages on Steam in 2024
2Simplified Chinese29.49%
4Spanish - Spain5.07%
26 more rows


Top Articles
Latest Posts
Article information

Author: Manual Maggio

Last Updated:

Views: 5283

Rating: 4.9 / 5 (69 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Manual Maggio

Birthday: 1998-01-20

Address: 359 Kelvin Stream, Lake Eldonview, MT 33517-1242

Phone: +577037762465

Job: Product Hospitality Supervisor

Hobby: Gardening, Web surfing, Video gaming, Amateur radio, Flag Football, Reading, Table tennis

Introduction: My name is Manual Maggio, I am a thankful, tender, adventurous, delightful, fantastic, proud, graceful person who loves writing and wants to share my knowledge and understanding with you.