7 ms·

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

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 https://github.com/turbot/steampipe 2 - https://steampipe.io/docs/develop/overview https://steampipe.io/docs/develop/overview 3 - https://hub.steampipe.io/ https://hub.steampipe.io/

davesque 4y ago
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.
- nathanwallace 4y ago
  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 https://www.youtube.com/watch?v=2BNzIU5SFaw
  - davesque 4y ago
    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.
    - nathanwallace 4y ago
      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.
      - davesque 4y ago
        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.
- cube2222 4y ago
  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 https://github.com/cube2222/octosql [1]: https://github.com/apache/arrow-datafusion https://github.com/apache/arrow-datafusion Disclaimer: author of OctoSQL
  - davesque 4y ago
    Yep, I'm aware of DataFusion and I'd expect nothing less than top notch performance from it. Interesting comparison.
aaronharnly 4y ago
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?
- nathanwallace 4y ago
  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-bigquery-ae37f258090f 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 https://www.youtube.com/watch?v=2BNzIU5SFaw
  - doublerebel 4y ago
    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.
    - nathanwallace 4y ago
      Sounds interesting - let us know how you go!
petercooper 4y ago
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/ https://golangweekly.com/ and featured it in today's issue. Hopefully kiyanwang is one of our readers :-D
- nathanwallace 4y ago
  Awesome - thanks for the shout out in golangweekly!
  - petercooper 4y ago
    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!
Lorin 4y ago
FYI hiring page requires login to notion
- nathanwallace 4y ago
  hmmm ... I just tested in Incognito mode and it's working for me. Could you please try again? We are hiring for multiple roles!
klysm 4y ago
Love to see Postgres FDW used. It’s a really powerful and imo not utilized as much as it could be.
cube2222 4y ago
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.
- nathanwallace 4y ago
  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-automated-analysis 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_autoscaling_group#instances-information-attached-to-the-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 https://steampipe.io/docs/cloud/integrations/metabase
breck 4y ago
This is amazing. Can't believe I hadn't seen it before. Nice job
VectorLock 4y ago
Are people usually setting up centralized shared instances of Steampipe or is it more of a "run on everyone's laptop" deployment preferred?
- rmetzler 4y ago
  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.
  - nathanwallace 4y ago
    Because it's just Postgres, connecting Jupyter notebooks to Steampipe via SQL works really well :-)
- nathanwallace 4y ago
  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 https://steampipe.io/docs/managing/service 2 - https://steampipe.io/docs/managing/containers https://steampipe.io/docs/managing/containers 3 - https://steampipe.io/docs/cloud/overview https://steampipe.io/docs/cloud/overview
ezekg 4y ago
This is awesome. Makes me want to write a plugin for my SaaS, just because it looks fun.
- nathanwallace 4y ago
  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 https://steampipe.io/docs/develop/writing-plugins 2 - https://github.com/topics/steampipe-plugin https://github.com/topics/steampipe-plugin 3 - https://steampipe.io/community/join https://steampipe.io/community/join 4 - https://hub.steampipe.io/plugins https://hub.steampipe.io/plugins
ComodoHacker 4y ago
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.
- judell 4y ago
  Here is one of our favorite examples: https://steampipe.io/blog/use-shodan-to-test-aws-public-ip https://steampipe.io/blog/use-shodan-to-test-aws-public-ip
- nathanwallace 4y ago
  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
- chousuke 4y ago
  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.

davesque 4y ago

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.

nathanwallace 4y ago
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 https://www.youtube.com/watch?v=2BNzIU5SFaw
- davesque 4y ago
  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.
  - nathanwallace 4y ago
    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.
    - davesque 4y ago
      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.
cube2222 4y ago
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 https://github.com/cube2222/octosql [1]: https://github.com/apache/arrow-datafusion https://github.com/apache/arrow-datafusion Disclaimer: author of OctoSQL
- davesque 4y ago
  Yep, I'm aware of DataFusion and I'd expect nothing less than top notch performance from it. Interesting comparison.

aaronharnly 4y ago

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?

nathanwallace 4y ago
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-bigquery-ae37f258090f 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 https://www.youtube.com/watch?v=2BNzIU5SFaw
- doublerebel 4y ago
  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.
  - nathanwallace 4y ago
    Sounds interesting - let us know how you go!

petercooper 4y ago

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/ https://golangweekly.com/ and featured it in today's issue. Hopefully kiyanwang is one of our readers :-D

nathanwallace 4y ago
Awesome - thanks for the shout out in golangweekly!
- petercooper 4y ago
  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!

Lorin 4y ago

FYI hiring page requires login to notion

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

klysm 4y ago

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

cube2222 4y ago

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.

nathanwallace 4y ago
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-automated-analysis 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_autoscaling_group#instances-information-attached-to-the-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 https://steampipe.io/docs/cloud/integrations/metabase

breck 4y ago

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

VectorLock 4y ago

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

rmetzler 4y ago
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.
- nathanwallace 4y ago
  Because it's just Postgres, connecting Jupyter notebooks to Steampipe via SQL works really well :-)
nathanwallace 4y ago
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 https://steampipe.io/docs/managing/service 2 - https://steampipe.io/docs/managing/containers https://steampipe.io/docs/managing/containers 3 - https://steampipe.io/docs/cloud/overview https://steampipe.io/docs/cloud/overview

ezekg 4y ago

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

nathanwallace 4y ago
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 https://steampipe.io/docs/develop/writing-plugins 2 - https://github.com/topics/steampipe-plugin https://github.com/topics/steampipe-plugin 3 - https://steampipe.io/community/join https://steampipe.io/community/join 4 - https://hub.steampipe.io/plugins https://hub.steampipe.io/plugins

ComodoHacker 4y ago

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.

judell 4y ago
Here is one of our favorite examples: https://steampipe.io/blog/use-shodan-to-test-aws-public-ip https://steampipe.io/blog/use-shodan-to-test-aws-public-ip
nathanwallace 4y ago
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
chousuke 4y ago
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.