nathanwallace

"steampipe query" has auto-complete for SQL queries in the interactive terminal - https://steampipe.io/docs/query/query-shell#autocomplete

"steampipe service" is a standard Postgres endpoint, so works with any BI tool or SQL client and their autocompletion capabilities - https://steampipe.io/docs/query/third-party

"steampipe completion" provides terminal completion for the Steampipe CLI - https://steampipe.io/docs/reference/cli/completion

Steampipe mostly exposes underlying APIs or data through SQL. So, "select * from aws_s3_bucket" will return bucket names, tags, policies, etc all together. But, bucket size is not a direct API so doesn't have a direct table.

In some high value cases we've added tables to simplify / abstract / normalize data - for example AWS IAM policies - https://steampipe.io/blog/normalizing-aws-iam-policies-for-a...

This example query returns the number of instances attached to an autoscaling group - https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ec2_a...

BTW, we recently published a Metabase integration guide - https://steampipe.io/docs/cloud/integrations/metabase

Steampipe installs Postgres as an embedded database under the hood. We found that the initdb step kept failing on WSL1 and couldn't find a way to make it work (yet). Similar reports on StackOverflow below [1] [2]. Suggestions or PRs would be very welcome!

1 - https://stackoverflow.com/questions/72358475/postgresql-wsl-... 2 - https://stackoverflow.com/questions/69808468/postgres-is-stu...

You can run Steampipe CLI in "service mode", getting access to a Postgres endpoint and a dashboard web server [1]. Other users leverage docker / kubernetes to run it [2]. We also have a cloud hosted option with dashboard hosting, multi-user support, etc [3].

1 - https://steampipe.io/docs/managing/service 2 - https://steampipe.io/docs/managing/containers 3 - https://steampipe.io/docs/cloud/overview

You kid, but we found that useful for scripts etc! So, in addition to a secure public Postgres endpoint [1], Steampipe Cloud workspaces have a rest API to run SQL queries and get results in JSON or other formats [2].

1 - https://steampipe.io/docs/cloud/integrations/overview 2 - https://steampipe.io/docs/cloud/develop/query-api

Completely separate projects, with different goals. IaSQL is focused on infrastructure as code (like Terraform) in SQL. Steampipe is an "ops as code" platform to query, visualize and report on all your DevOps data (infra, SaaS, IaC, logs, etc). Steampipe's first version was released in Jan 2021.

Yes - our users run queries across hundreds of AWS accounts and regions. TBH, we never expected it to scale that far when we started, but it has. Steampipe parallelizes the API calls out and performs appropriate backoff in throttling to gather the results.

Steampipe does the API calls needed for the column data requested [1]. So "select instance_id from aws_ec2_instance" is much faster than "select id, tags from aws_ec2_instance" (which does a tags API call per row). But, it will still work better than you expect, and Steampipe caches results in memory to make subsequent queries instant.

1 - https://steampipe.io/blog/selective-select

Yes, we've focused on SELECT for read only - which provides a lot of value as a "universal API" to live query these many sources. Steampipe is built on Postgres Foreign Data Wrappers [1] under the hood, so could be extended to full CRUD in time. Our current belief is that it's better to follow CQRS [2] in this case though - using SQL for the queries and more native tools for the commands.

1 - https://www.postgresql.org/docs/current/ddl-foreign-data.htm... 2 - https://martinfowler.com/bliki/CQRS.html

Excited to see Steampipe shared here - thanks kiyanwang! I'm a lead on the project, so sharing some quick info below and happy to answer any questions.

Steampipe is open source [1] and uses Postgres foreign data wrappers under the hood [2]. We have 84+ plugins to SQL query AWS, GitHub, Slack, HN, etc [3]. Mods (written in HCL) provide dashboards as code and automated security & compliance benchmarks [3]. We'd love your help & feedback!

1 - https://github.com/turbot/steampipe 2 - https://steampipe.io/docs/develop/overview 3 - https://hub.steampipe.io/

Readers may also enjoy Steampipe [1], an open source CLI to live query Stripe [2] and 80+ other services with SQL (e.g. AWS, GitHub, CSV, AirTable, etc). It uses Postgres Foreign Data Wrappers under the hood and supports joins etc across the services. (Disclaimer - I'm a lead on the project.)

1 - https://steampipe.io 2 - https://hub.steampipe.io/plugins/turbot/stripe

Readers may also enjoy Steampipe [1]. It's an open source "ops as code" CLI to query 83+ services with SQL (AWS, GitHub, Terraform, etc) that comes with hundreds of ready to use benchmarks (CIS, NIST, Cost) and dashboards built in HCL. The AWS Compliance mod [2] and Trivy plugin [3] are specific examples. (Disclaimer - I'm a lead on the project.)

1 - https://steampipe.io 2 - https://hub.steampipe.io/mods/turbot/aws_compliance 3 - https://hub.steampipe.io/plugins/turbot/trivy

The Steampipe open source project and community are managed by Turbot [1]. We also have Steampipe Cloud [2] in preview if you'd prefer a hosted version focused on teams.

1 - https://turbot.com 2 - https://cloud.steampipe.io

Readers may also enjoy Steampipe[1], an open source [2] tool that uses Postgres FDW's for live SQL queries against Jira, Confluence, GitHub, GitLab, AWS, Slack and many more tools [3].

1 - https://steampipe.io 2 - https://github.com/turbot/steampipe 3 - https://hub.steampipe.io

Steampipe [1] is an open source Postgres Foreign Data Wrapper with 73 plugins [2] including Google Sheets support [3]. A lot of our users use it to connect custom metadata with other systems - e.g. join ownership info in Google Sheets with tags on AWS resources. Notes: I'm a lead on the project, it's focused on read only.

1 - https://steampipe.io 2 - https://hub.steampipe.io/plugins 3 - https://hub.steampipe.io/plugins/turbot/googlesheets

Turbot [1] is a bootstrapped company since 2014. Our namesake product is a cloud governance platform with a real-time CMDB, identity suite, policy engine and thousands of automated operations for tagging, security, deployment, etc.

Steampipe Cloud [2] is in private preview providing a hosted version of Steampipe (and more). We're iterating fast and would love your feedback :-)

1 - https://turbot.com 2 - https://cloud.steampipe.io

Yes, the CLI, FDW, plugins and mods are all open source. Please let us know how you go - we thrive on feedback :-)

Steampipe [1] is an open source [2] project that uses Postgres FDWs to query 67+ cloud services (e.g. AWS, GitHub, Prometheus). The plugins [3] are written in Go similar to Terraform. We've found this approach very effective for DevOps data and pushdown works well for most (simple) cases. (Disclaimer: I'm a lead on the project.)

1 - https://steampipe.io 2 - https://github.com/turbot/steampipe 3 - https://hub.steampipe.io/plugins

Steampipe [1] is an open source CLI to query cloud APIs (e.g. AWS, GitHub, Kubernetes) with SQL. It uses an embedded Postgres instance internally and is built with Foreign Data Wrappers.

We install and leverage the Zonky Embedded Postgres Binaries [2] which are normally used for test suites. They have been great for us.

It's amazing how fast and light Postgres runs for these use cases!

1 - https://steampipe.io 2 - https://github.com/zonkyio/embedded-postgres-binaries

AWS is trying to do this with Cloud Control [1]. It's a common interface for resource inventory and provisioning and seems to come out of internal APIs for the CloudFormation service. It currently seems better suited to provisioning use cases and is still fairly limited as an inventory interface [2].

1 - https://aws.amazon.com/cloudcontrolapi/ 2 - https://steampipe.io/blog/aws-cloud-control

Hi - I'm a lead on the Steampipe (https://steampipe.io) project. Rate limits are definitely an interesting challenge, but actually less of an issue for AWS than many of our other plugins.

Because rate limits are separate across accounts, regions and services, the parallel queries work really well in those cases.

We do sub-API queries for tables, but only when the column is requested. So, "select name from aws_s3_bucket" just does a list call while "select * from aws_s3_bucket" does multiple API calls per row. These sub-API calls are the main potential source of rate limits since they hit the same API [1]. BTW, Cloud Control from AWS is actually much more subsceptible to this problem! [2].

We also use a custom backoff algorithm that is fast then slow to give good speed in the usual cases and ensure results if throttling ramps up [3].

Finally, we automatically cache results in memory between requests and can always save results into materialized views or similar to avoid repeated calls in larger cases.

TBH, live querying of APIs has proven so much more effective than we even hoped when we started!

1 - https://steampipe.io/blog/selective-select 2 - https://steampipe.io/blog/aws-cloud-control 3 - https://github.com/turbot/steampipe-plugin-aws/blob/4cbd8813...

Use `steampipe service start` to run Steampipe in the background [1]. It will print out a standard Postgres connection string running on port 9193 of your local machine. You can then simply point Metabase (or any other DB visualization tool) at it using the standard Postgres setup.

1 - https://steampipe.io/docs/using-steampipe/service

I mentioned Steampipe [1] above, it has an open source OCI plugin [1] to query resources (no support for write operations). It's a Postgres FDW under the hood, and uses subset of Postgres types (bool, bigint, double, text, inet, cidr, timestamptz and jsonb) along with their standard operators. This approach has proven very simple and effective for us.

1 - https://steampipe.io 2 - https://github.com/turbot/steampipe-plugin-oci

Both seem to have reasonable Go SDK libraries, so it should be easy to create new plugins for them. I've added them to our internal backlog, but all our plugins are open source if you'd like to try building it yourself! The Linode plugin is a simple, similar example to use as a starting point [1]. We also have docs for writing plugins [2].

1 - https://github.com/turbot/steampipe-plugin-linode 2 - https://steampipe.io/docs/develop/writing-plugins

Awesome! Would love to hear more about your use case and any suggestions for improvements if you want to DM me.

Great to hear :-) Please check out our open source mods (e.g. AWS Compliance, GitHub Sherlock, DigitalOcean Thrifty) - we'd love your feedback & help! https://hub.steampipe.io/mods

It talks directly to the cloud provider APIs. Plugins are written in go-lang (similar to Terraform providers).

OSS plugins - https://github.com/topics/steampipe-plugin

Docs - https://hub.steampipe.io/plugins

Steampipe (https://steampipe.io) is an open source CLI to query cloud infrastructure using SQL (e.g. AWS, GitHub, Slack, k8s, etc). It also has a HCL language to define security benchmarks and controls (e.g. AWS CIS, etc).

We are a Postgres FDW under the hood with Go-based plugins, so write would be possible, but we've chosen to focus on read only so far. Definitely interested to see how you approach create, update and delete in the SQL model!

Notes: Not related to iasql. I'm a lead on the Steampipe project.

It's a pure plugin model, none built in. On the home page we considered data source ~= API ~= table, sorry if that was not clear.

I expanded more on this at https://news.ycombinator.com/item?id=28229700.

Having said that, we find plugins are quick and fun to write so we'd love contributions or suggestions for any that would bring you value!

I really appreciate that, we're working hard to make it as developer friendly as possible. If you (or anyone else here) can spare a few mins to discuss use cases for Steampipe in data pipelines please drop me a note (email in bio)!

Agreed. Unfortunately requirements for long term management / large scale deployment aren't a priority until a tool is widely adopted, which is best achieved by keeping it simple. An interesting trade off...