Hacker News Reader

Comment by nathanwallace | Hacker News Reader

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/

View on HN

Hey Nathan. Can you comment on some of the known performance pitfalls of steampipe? I'm not super familiar with the Postgres foreign data wrappers API. I assume steampipe inherits many of its technical limitations from this API.

Having done some work in this space, I'm aware that it's no small thing to compile a high-level SQL statement that describes some analytics task to be executed on a dataset into a low-level program that will efficiently perform that task. This is especially true in the context of big data and distributed analytics. Also true if you're trying to blend different data sources that themselves might not have efficient query engines.

Would love to use this tool, but just curious about some of the details of its implementation.

The premise of virtual tables (Postgres FDW) is to not store the data but instead query it from the original source. So, the primary performance challenge is API throttling. Steampipe optimizes these API calls with smart caching, massive parallelization through goroutines and calling the minimum set of APIs to hydrate the exact columns requested. For example, "select instance_id from aws_ec2_instance" will do 2 API calls get 100 instances in 2 pages, while "select instance_id, tags from aws_ec2_instance" would do 2 calls (instance paging) + 100 tag API calls (one per instance). We've also implemented support for qualifiers (i.e. where clauses) so API calls can be reduced even further - e.g. get 1 EC2 instance without pagination etc.

The Postgres planner is not really optimized for foreign tables, but we can give it hints to indicate optimal paths. We've gradually ironed out many cases here in our FDW implementation particularly for joins etc.

If you can tolerate my Aussie accent, I explain many of the details in this Yow Data talk - https://www.youtube.com/watch?v=2BNzIU5SFaw

Awesome, thanks so much for that! I guess then it sounds like, even if bandwidth were not an issue for a datasource, there would still be the physical limitations of the machine on which steampipe would do any later processing (such as joins between datasources). In other words, there's no sense in which steampipe distributes this work across multiple processes or machine. Is that correct?

Sorry if a dumb question. I could be thinking entirely in terms of the wrong paradigm here since my work in this space was primarily concerned with distributed computing and big data.

Steampipe computes the SQL part in a single Postgres instance (not distributed) - we've not found this to be a significant limit (so far). A difference in this case is that the query is really combining and organizing results that may been processed at the source. In 2022 most services are API first and offer great searching and filtering, so we can use that to do much of the query processing in many cases.

Great, thanks again! Yeah, a lot of the things steampipe is doing are right up my alley. Fun to learn about! Watching your talk now and enjoying it.

To add somewhat of a counterpoint to the other response, I've tried the Steampipe CSV plugin and got 50x slower performance vs OctoSQL[0], which is itself 5x slower than something like DataFusion[1]. The CSV plugin doesn't contact any external API's so it should be a good benchmark of the plugin architecture, though it might just not be optimized yet.

That said, I don't imagine this ever being a bottleneck for the main use case of Steampipe - in that case I think the APIs themselves will always be the limiting part. But it does - potentially - speak to what you can expect if you'd like to extend your usage of Steampipe to more than just DevOps data.

I've used the benchmark available in the OctoSQL README.

[0]: https://github.com/cube2222/octosql

[1]: https://github.com/apache/arrow-datafusion

Disclaimer: author of OctoSQL

Yep, I'm aware of DataFusion and I'd expect nothing less than top notch performance from it. Interesting comparison.

Love to see Postgres FDW used. It’s a really powerful and imo not utilized as much as it could be.

Hey! Steampipe looks great and I think the architecture you chose is very smart. Some feedback from myself:

I've tried setting up steampipe with metabase to do some dashboarding. However, I’ve found that it mostly exposes "configuration" parameters, so to say. I couldn't find dynamic info, like S3 bucket size or autoscaling group instance count.

Have I done something backwards or not noticed a table, or is that a design decision of some sort? That was half a year ago, so things might've changed since then, too.

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

It's a very cool project!

It might just be a coincidence, but an hour before this HN post, I discovered it way back in our queue of things to review for https://golangweekly.com/ and featured it in today's issue. Hopefully kiyanwang is one of our readers :-D

Awesome - thanks for the shout out in golangweekly!

No worries, it's my job :-) I'm just so intrigued to see (well, guess, in this case) how the path of word of mouth works because most of the time it's a complete mystery!

Congratulations! This looks incredibly powerful and I'm excited to check it out.

Although this is pitched primarily as a "live" query tool, it feels like we could get the most value out of combining this with our existing warehouse, ELT, and BI toolchain.

Do you see people trying to do this, and any advice on approaches? For example, do folks perform joins in the BI layer? (Notably, Looker doesn't do that well.) Or do people just do bulk queries to basically treat your plugins as a Fivetran/Stitch competitor?

While it's extensible, the primary use case for Steampipe so far is to query DevOps data (e.g. AWS, GitHub, Kubernetes, Terraform files, etc) - so often they are working within the Steampipe DB itself and combining with CSV data etc.

But, because it's just Postgres, it can be integrated into many different data infrastructure strategies. Many users query Steampipe from standard BI tools, others use it to extract data into S3, it has also been connected with BigQuery - https://briansuk.medium.com/connecting-steampipe-with-google...

As opposed to a lake or a warehouse, we think of it as a "Data Rainbow" - structured, ephemeral queries on live API data. Because it doesn't have to import the data it works uniquely well for small, wide data and joining large data sets (e.g. search, querying logs). I spoke about this in detail at the Yow Data conference - https://www.youtube.com/watch?v=2BNzIU5SFaw

Over the last week I’ve been wanting to use Steampipe for DevSecOps by layering Sliderule.io on top. Do you have any customers doing this or something like it already? I am going to give it a try tomorrow and take it up with our Sliderule contacts on Monday.

Sounds interesting - let us know how you go!

This is awesome. Makes me want to write a plugin for my SaaS, just because it looks fun.

I'm biased, but writing plugins is really fun - and then you can create mods / dashboards for your users as well! Please give it a go - we have a guide [1], 84+ open source plugins to copy [2], many authors in our Slack community [3] and we're happy to get your plugin added to the Steampipe Hub [4].

1 - https://steampipe.io/docs/develop/writing-plugins 2 - https://github.com/topics/steampipe-plugin 3 - https://steampipe.io/community/join 4 - https://hub.steampipe.io/plugins

This is amazing. Can't believe I hadn't seen it before. Nice job

Are people usually setting up centralized shared instances of Steampipe or is it more of a "run on everyone's laptop" deployment preferred?

My guess is, that it makes sense to get regular reports, e.g. weekly. But you also want to experiment and develop queries. So probably both. Not sure if there is something like a notebook for steampipe.

Because it's just Postgres, connecting Jupyter notebooks to Steampipe via SQL works really well :-)

We see users running Steampipe on their desktop, in pipelines (e.g. to scan Terraform files) and also as a central service. See "service mode" in the CLI for a Postgres endpoint and a dashboard web server [1] or try docker / kubernetes [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

What about joins, are they supported? Can't see them in the examples.

The real power of SQL is locked until you can join different data sources.

Yes - joins work across tables and schemas! This (toy) example connects IAM user records with Slack user data:

    select u.name, s.id, s.display_name
    from aws_iam_user as u, slack_user as s
    where u.name = s.email

Here is one of our favorite examples: https://steampipe.io/blog/use-shodan-to-test-aws-public-ip

Steampipe is pretty much "just" PostgreSQL with a foreign data wrapper and some fancy extras on top. The data is in tables from the database's perspective, so pretty much everything you can do with PostgreSQL, you can do with steampipe, including creating your own tables, views, functions and whatnot.

FYI hiring page requires login to notion

hmmm ... I just tested in Incognito mode and it's working for me. Could you please try again? We are hiring for multiple roles!

Reply on news.ycombinator.com