15 ms·

Select * from cloud

790 points by kiyanwang 4y ago

armchairhacker 4y ago
We have REST endpoints generated from SQL databases, and now we have SQL queries which connect to REST endpoints.
evilotto 4y ago
This sounds like what YQL should have grown into. https://en.wikipedia.org/wiki/Yahoo!_Query_Language https://en.wikipedia.org/wiki/Yahoo!_Query_Language
- drgath 4y ago
  ctrl+f “yql” and landed here. Was an amazing service for its time. Could even execute server-side JS in its engine (Rhino). Certainly the only E4X environment I ever coded in.
xwowsersx 4y ago
I'm in the process of determining which RDS instance type to switch to and I've had to have a browser open with a million tabs copying and pasting information and pricing data from AWS. Could I use steam pipe to help me with this?
- nathanwallace 4y ago
  I feel your pain - but Steampipe won't solve this (yet). We have AWS Thrifty [1] to scan your env for cost savings and other tables to query your spend [2], but haven't tackled the 1.8GB of pricing JSON data into a table yet. 1 - https://hub.steampipe.io/mods/turbot/aws_thrifty https://hub.steampipe.io/mods/turbot/aws_thrifty 2 - https://hub.steampipe.io/plugins/turbot/aws/tables?filter=cost https://hub.steampipe.io/plugins/turbot/aws/tables?filter=co...
  - xwowsersx 4y ago
    Haha thanks for the reply. Insane how much pricing data there is, right? The least AWS can do is offer an export on the particular pricing pages!
chrsstrm 4y ago
So this looks interesting and I wanted to test it on a use-case I have currently. I want to query both a csv and a zipped csv living online but it doesn't appear that is possible. The csv plugin implies you can only work with local files, nothing over http(s). This kind of seems like a big oversight for a product like this, no?
- nathanwallace 4y ago
  We have work in progress to support go-getter [1] allowing use of files from many different sources. There is a WIP branch [2] building that into the Steampipe Plugin SDK, so it can be immediately enabled for all our file based plugins (CSV, Terraform, etc) [3]. Watch this space and hopefully you can help us test! 1 - https://github.com/hashicorp/go-getter https://github.com/hashicorp/go-getter 2 - https://github.com/turbot/steampipe-plugin-sdk/compare/main...issue-434 https://github.com/turbot/steampipe-plugin-sdk/compare/main.... 3 - https://github.com/turbot/steampipe-plugin-terraform/tree/add-support-to-list-files-using-go-getter https://github.com/turbot/steampipe-plugin-terraform/tree/ad...
michaelsalim 4y ago
This makes me want to create a Grafana dashboard for everything. Instead of relying on custom plugins (or pulling & storing the data yourself), now it's just another postgres data source. I hope this becomes mainstream!
- nathanwallace 4y ago
  100% - works great with Grafana, Metabase, Tableau, etc because it’s just Postgres :-) If you are into the idea of "dashboards as code", you may also enjoy our HCL+SQL approach in mods [1] - runs in your cli, open source, composable, great DX [2] [1] - https://hub.steampipe.io/mods https://hub.steampipe.io/mods [2] - https://steampipe.io/blog/dashboards-as-code https://steampipe.io/blog/dashboards-as-code
husamia 4y ago
it's all about the data sources
iteratorx 4y ago
I have recently tried to set up Steampipe to query all our cloud costs, but I noticed that costs are only available for AWS. Azure, GCP, DigitalOcean costs are not available in their respective plugins. Has anyone found a way to get the costs in those plugins? It is a limitation of the plugins or of the underlying cloud APIs?
- nathanwallace 4y ago
  Please open GitHub issues (or PRs) for any tables you'd like to added! If there is a cost API then we'd definitely like to have a table to query it. In the meantime, you may find the "Thrifty" mods useful - they have many prebuilt queries to look for cost saving opportunities in AWS, Azure, GCP and DigitalOcean - https://hub.steampipe.io/mods?q=thrifty https://hub.steampipe.io/mods?q=thrifty
deleted 4y ago
[deleted]
specialist 4y ago
Wow. Steampipe is just bitchin'. I've been noodling on a half-baked notion to make REST API clients easier. Think something between cURL and Postman. Steampipe is just such a better idea. And from first looks, the implementation looks like a bullseye. Bravo.
g0dspeed 4y ago
tl;dr unnecessary abstraction. all of this can be managed by REST APIs, and is already done so, why revert back to disgusting, archaic looking SQL limitations?
niutech 4y ago
Anybody remembers Yahoo Pipes with YQL? This looks similar.
kohlerm 4y ago
They claim that a "Join" of APIs is possible. I wonder how that would work in the general case and what the performance characteristic are? Any experiences?
deleted 4y ago
[deleted]
haolez 4y ago
Looks fun. I assume it's just a visibility tool and there is no intention of offering INSERT, UPDATE and DELETE, right?
- nathanwallace 4y ago
  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.html https://www.postgresql.org/docs/current/ddl-foreign-data.htm... 2 - https://martinfowler.com/bliki/CQRS.html https://martinfowler.com/bliki/CQRS.html
nathanwallace 4y ago
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
d0gsg0w00f 4y ago
Could someone use this query across many hundreds of accounts?
- nathanwallace 4y ago
  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 https://steampipe.io/blog/selective-select
TruthWillHurt 4y ago
wow.
xenophonf 4y ago
This cannot possibly require WSL2. What the heck? If I had an afternoon to waste RTFSing, I'd fix this. Some of us are stuck running WSL1 because Hyper-V breaks features we need in other hypervisors.
- nathanwallace 4y ago
  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-wont-init https://stackoverflow.com/questions/72358475/postgresql-wsl-... 2 - https://stackoverflow.com/questions/69808468/postgres-is-stuck-when-creating-a-database https://stackoverflow.com/questions/69808468/postgres-is-stu...
- hedora 4y ago
  On the one hand, I see your point; hacking it together probably isn't hard. On the other, they're a startup with limited resources, and time to market matters dearly for them. They probably don't even have product-market fit yet, and supporting Microsoft technologies that Microsoft has abandoned seems questionable. Anyway, I upvoted your comment, because it could be that their target market is running in weird enterprise-y environments and your problem is common. Feedback is helpful and hard fought at small companies. (No affiliation.)
  - xenophonf 4y ago
    The downvotes actually motivate me to fix this, except the docs have zero information on how to set up a development environment. I can tweak the CLI and `go build` it, but the plugin install process ends up overwriting my changes somehow. I've wasted about two hours learning Go and digging into the project's CI/CD pipeline, trying to figure out how everything works, and I haven't even begin debugging the actual problem I'm having. Ultimately, this goes on the pile of neat things I saw on HN but don't actually work IRL. It's too bad because this looks super cool.
mikessoft_gmail 4y ago
ryanmarsh 4y ago
Is this IaSQL reborn or a completely new product?
- nathanwallace 4y ago
  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.
blibble 4y ago
looks cool, think you could expose it as a rest API? (I kid, I kid)
- ashrodan 4y ago
  I'd want to know how to have a hosted solution. Ideally build reports and alerts
  - nathanwallace 4y ago
    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 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
  - CSDude 4y ago
    Steampipe has their own cloud at https://steampipe.io/docs/cloud/overview https://steampipe.io/docs/cloud/overview Alternative is Cloudquery but they don't offer cloud anymore. https://www.cloudquery.io/ https://www.cloudquery.io/ Plug, not based on Steampipe, but we also offer similar managed SQL on your cloud assets. https://www.resmo.com/ https://www.resmo.com/
- nathanwallace 4y ago
  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 https://steampipe.io/docs/cloud/integrations/overview 2 - https://steampipe.io/docs/cloud/develop/query-api https://steampipe.io/docs/cloud/develop/query-api
Canada 4y ago
This looks fantastic!
RicoElectrico 4y ago
https://developer.valvesoftware.com/wiki/SteamPipe https://developer.valvesoftware.com/wiki/SteamPipe No way they did not know about that name collision as SteamPipe has been around since 2013-ish.
- robertlagrant 4y ago
  I'm about to blow your mind, then. https://www.sincocustoms.com/shop/product/209312/steampipe--straights/ https://www.sincocustoms.com/shop/product/209312/steampipe--...
  - benbristow 4y ago
    I'm about to blow your mind, then https://en.wikipedia.org/wiki/Stephenson%27s_Rocket https://en.wikipedia.org/wiki/Stephenson%27s_Rocket
    - masukomi 4y ago
      I'm about to blow your mind, then https://en.wikipedia.org/wiki/Stirling_engine https://en.wikipedia.org/wiki/Stirling_engine
      - breck 4y ago
        Try dropping a few thousand years https://en.wikipedia.org/wiki/Aeolipile https://en.wikipedia.org/wiki/Aeolipile
- jcims 4y ago
  I've been around since 1973 and didn't know.
  - deleted 4y ago
    [deleted]
- isamuel 4y ago
  Name collision researcher here! This made me curious and I started doing research on other problematic name collisions. Did you know that Valve’s game distribution service has a name that’s already widely used in the scientific community for the gaseous form of heated water? It looks like steam has been around for several centuries at least so Valve really ought to have known better. Anyway, if you’re interested, I’m putting together a long investigation on whether Microsoft’s flagship operating system is in collision with a very common term used in the construction and glass industry. Hope to have it done soon
swalsh 4y ago
Since starting my career, the tech stack I use has changed numerous times. Technologies have come, gone, evolved. But except for a brief period in 2010 where we thought we could kill it, SQL is the one technology that has stayed around. It might be the only TECHNICAL knowledge I learned in the first year of my career that is still relavent. You're probably not making the wrong decision if you decide to build on top of SQL.
- matwood 4y ago
  > It might be the only TECHNICAL knowledge I learned in the first year of my career that is still relavent. Agree. IIRC, I wrote my first SQL is 1996 and wrote some today. While not perfect, it’s amazing at what it does. I suggest reading Joe Celko to really up your SQL skills.
  - johnthescott 4y ago
    yep. and i wrote my first quel in 1984 at BLI. and sql still won the war.
- quickthrower2 4y ago
  The problem I see here is building SQL on top of the cloud, which is like building SQL on top of a database where the tables are always changing - not just adding columns but the meaning of the columns and rows, or even the existence of the table. I think this is a good idea, but the cloud doesn't seem to care too much about backwards compatibility. If you use MySQL/PostgreSQL/SQL Server, you can be fairly sure in 20 years time your SQL will still work :-). The need for this library is more an indictment on "the cloud" than anything. I am mostly an Azure user so AWS/Google might be better but man it changes it's UI and CLI interfaces alot - way too much.
- rsync 4y ago
  "Since starting my career, the tech stack I use has changed numerous times. Technologies have come, gone, evolved. But except for a brief period in 2010 where we thought we could kill it, SQL is the one technology that has stayed around." Ethernet comes to mind when I think of technologies that just can't be killed ...
yevpats 4y ago
Good to see steampipe here :) Im the author of CloudQuery (https://github.com/cloudquery https://github.com/cloudquery) and I believe we started at the same time though took some different approaches. PG FDW - is def an exciting PostgreSQL tech and is great for things like on-demand querying and filtering. We took a more standard ELT approach where we have a protocol between source plugins and destination plugins so we can support multiple databases, data-lakes, storage layers, kinda similar to Fivetran and airbyte but with focus on infrastructure tools. Also, as a more standard ELT approach our policies use standard SQL directly and dashboards re-use existing BI tools rather then implementing dashboarding in-house. I can see the power of all-in-one and FDW and def will be following this project!
- alexchantavy 4y ago
  /me waves Immediately thought of CloudQuery when I saw this and sure enough, here you are :)
chousuke 4y ago
Steampipe is unbeliveably powerful. Writing custom scripts for even the simplest of queries comes nowhere near the convenience of using PostgreSQL to get what you want. If you wanted to find and report all instances in an AWS account across all regions, with steampipe it's just: SELECT * FROM aws_ec2_instance; Even the simplest implementation with a Python script won't be nearly as nice, and once you start combining tables and doing more complicated queries, there's just no competition.
- phillu 4y ago
  I don't agree that this simple query wouldn't be as nice with boto3. But combining different services is definitely a nice feature, if you have the use case for it.
  - chousuke 4y ago
    the fun part of that query is that it'll search across all regions that you have configured steampipe to use. If you did that in Python, you at the very least have to manually iterate over each region, handle pagination yourself and combine the results. It gets much more convenient when you want to ask more complicated questions like "What is the most used instance type per region" or "How much EBS capacity do instances use across all regions, grouped by environment tag and disk type?"
- Hikikomori 4y ago
  Tried steampipe out to replace my simple python script. It wasn't able to handle our 1k+ accounts and multiple regions, initialization took over 1 hour before I was even able to do a query. Meanwhile my python script can run any API calls in all our accounts and regions and finish in a few minutes. Maybe 100 lines of python using boto3 and multiprocessing that outputs to json or yaml.
  - xwowsersx 4y ago
    1K accounts? What're you doing over there exactly? I'm intrigued!
almenon 4y ago
Does this have autocomplete in the terminal?
- nathanwallace 4y ago
  "steampipe query" has auto-complete for SQL queries in the interactive terminal - https://steampipe.io/docs/query/query-shell#autocomplete 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 https://steampipe.io/docs/query/third-party "steampipe completion" provides terminal completion for the Steampipe CLI - https://steampipe.io/docs/reference/cli/completion https://steampipe.io/docs/reference/cli/completion
diceduckmonk 4y ago
We built a UI-centric cross-cloud search engine, too. <edit: link redacted as other commenter perceived this to be an ad> The first VC we pitched to felt like this was too niche of a problem. They wanted us to come back with a different, grander, pitch, so we'll see. In the current fundraising climate, it's been difficult to gather data points on whether we are on the right track (this post makes us feel like we're not crazy). We reached out to investors outside America, where we're based, and we're quickly realizing the VCs aren't as tech-savvy as we expected. After going thru the YC application process this cycle, we've have much greater appreciation for YC. They understand tech and startups, both. For one, we're instructed to define a toy / small problem, "don't talk, do" as opposed to pretty slides and ideas. Best of luck to Steampipe and whoever else is working on this problem.
- payphonefiend 4y ago
  Please make your site mobile friendly.
  - diceduckmonk 4y ago
    Apologies. This was an experimental landing page haphazardly thrown together after the investors we're pitching to wanted a two-slide problem/solution. The rest of the app is actually made mobile-first.
- haggy 4y ago
  Not a fan of what feels like an advertisement hijacking the OPs post. Most would probably prefer you just make a separate HN post.
  - diceduckmonk 4y ago
    I'm sorry it was perceived as such. I should have been more mindful. I genuinely intended for the substantive part of the post to be about our frustrations as programmers navigating the business world. It felt like the short introductory sentence was necessary to establish context (I'll remove the link, but leave post intact so other viewers aren't confused). Still off topic but, at the time, it seemed to be an on-brand discussion for HackerNews. What we were really phishing for was rapport from other engineers that we aren't crazy. I even feel vulnerable putting our project out here, at this stage, and the quick cost-benefit analysis in my head was this was a net-neutral. I doubt Steampipe knew we existed before this post. It seems like fate because I stumbled upon Steampipe just last week. I deliberated how to craft a not-awkward email along the lines of "Hey guys, we just realized we're tackling the same problem. Cheers." When I made the original post, I felt like it was the less awkward way of saying hi but now I'm not sure. At any rate, I hope you understand we did it in good will and not malice. Edit: I also hope "Best of luck to Steampipe and whoever else is working on this problem" doesn't get misconstrued as being a passive aggressive jab. Our team would honestly be thrilled if all the friction points of IaaS goes away, regardless of who solves it. As a reverse-ad, I'll say a few nice things about Steampipe that stood out to me: written in Go, supports querying HackerNews, SQL/Tables are a really good medium.
Canada 4y ago
I really like how it just worked when I installed it and the AWS plugin. The second one I tried was tailscale... I get some different results to the same query which is weird and broken, and an error "device can only be managed by original tailnet (403) (SQLSTATE HV000)" Still, getting access to all this stuff with a SQL interface is very compelling so I'll try to get the plugins for all the stuff I have working properly.
- nathanwallace 4y ago
  Glad AWS worked. Sorry you hit a bump with tailscale - we'd love feedback / contributions for that plugin [1], we just released it this month so may be edges for more complex configurations :-( 1 - https://github.com/turbot/steampipe-plugin-tailscale https://github.com/turbot/steampipe-plugin-tailscale
FBISurveillance 4y ago
I wonder if we can connect this with Hasura and query our cloud inventory via GraphQL. I'm only half-joking, actually.
- nathanwallace 4y ago
  Because it's just PostgreSQL, Steampipe works out of the box with SQL clients, BI tools and other integrations [1]. Please let us know how you go! 1 - https://steampipe.io/docs/query/third-party https://steampipe.io/docs/query/third-party
ezconnect 4y ago
Yahoo was really ahead of its time, so sad it was mismanaged.
Aaronstotle 4y ago
I found Steampipe from a HN comment about a year and some change ago, in my last role I had to routinely audit cloud configurations and it was amazing to have a SQL query to check all S3 buckets that were encrypted, security groups, etc.
ashayh 4y ago
Highly recommend Steampipe. No other open source tool was able to deal with the large number of Route53 records I had to deal with.
ezekg 4y ago
How does this handle pagination and also how does it behave towards rate limiters? For example, my API (keygen.sh) has a page size limit of 100, and often times there can be hundreds or even thousands of pages for certain resources. How would Steampipe handle `select count(*) from keygen_licenses` while obeying the rate limiter? What if a heavy query is run twice?
- judell 4y ago
  Pagination may be handled by the API's golang SDK, if not that's a responsibility of the plugin author. The plugin SDK provides a default retry/backoff mechanism, and the plugin author can enhance that. If a heavy query runs twice it'll load from cache the second time, if within the (user-configurable) cache TTL.
pstuart 4y ago
Seems like the next step past https://osquery.io/ https://osquery.io/
- nathanwallace 4y ago
  osquery is awesome. It was definitely part of our inspiration!
jedberg 4y ago
Steampipe killed my startup. I couldn't be happier about it. I started a company six years ago to do exactly this -- make a SQL interface for the AWS API. Steampipe out-executed us and made a way better product, in part because their technology choices were much smarter than ours. I shut down the startup as soon as I saw steampipe. I wish them all the best, it's a great product!
- patrec 4y ago
  I'm curious: what were the bad technological choices you made and the good ones they made?
  - encryptluks2 4y ago
    Can't speak for op but one common mistake I see is having a bunch of JS developers try to make a CLI and being surprised it is slow.
  - jedberg 4y ago
    They use Postgres foreign data wrappers and caching with live queries against the Api. We were doing periodic scanning and then storing it in elastic. Then trying to convert the SQL to elastic queries. The elastic part let us do free text queries but converting from SQL was challenging (this was before elastic had it built in). And also everything was only as fresh as our last scan.
serhatcan 4y ago
Great to see steampipe here :) Founder of Resmo here! Select * from cloud & SaaS + a fully managed SaaS platform with a free offering. Make sure to check us out!
debarshri 4y ago
There is another contender in infrasql realm called cloudquery[1]. Steampipe has been around for very long. Mind you the steampipe has an AGPL license. [1]https://www.cloudquery.io https://www.cloudquery.io
hugofromboss 4y ago
Shameless plug for those of you who want to query your assets with Osuqery and cloud assets with Steampipe. Check out https://www.github.com/zercurity/zercurity https://www.github.com/zercurity/zercurity.
intrasight 4y ago
How does this relate to or differ from Multicorn? I see that in most of the "Type" columns in the "specific web wrappers" section of the Postgres FDW page https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- nathanwallace 4y ago
  Postgres FDWs are extensions installed in Postgres. Multicorn provides an easy way to implement Postgres FDW extensions in Python, leading to many FDW implementations. Steampipe runs as a Postgres FDW extension with a pluggable interface to easily build and manage data sources. Multicorn-based FDWs might be installed as part of your existing Postgres server, while Steampipe is optimized to run more standalone / alongside your existing Postgres server.
  - intrasight 4y ago
    So Steampipe runs alongside? In a separate process?
forty 4y ago
I have discovered steampipe only recently. We work with a few AWS accounts and the steampipe dashboard and query are the best tool I have seen since terraform (of which I'm pretty sure it borrows the resource modules). Totally recommend it for making sense of your AWS accounts mess and for audits (soc2 style for example)

790 points by kiyanwang 4y ago

We have REST endpoints generated from SQL databases, and now we have SQL queries which connect to REST endpoints.

This sounds like what YQL should have grown into. https://en.wikipedia.org/wiki/Yahoo!_Query_Language https://en.wikipedia.org/wiki/Yahoo!_Query_Language

drgath 4y ago
ctrl+f “yql” and landed here. Was an amazing service for its time. Could even execute server-side JS in its engine (Rhino). Certainly the only E4X environment I ever coded in.

xwowsersx 4y ago

I'm in the process of determining which RDS instance type to switch to and I've had to have a browser open with a million tabs copying and pasting information and pricing data from AWS. Could I use steam pipe to help me with this?

nathanwallace 4y ago
I feel your pain - but Steampipe won't solve this (yet). We have AWS Thrifty [1] to scan your env for cost savings and other tables to query your spend [2], but haven't tackled the 1.8GB of pricing JSON data into a table yet. 1 - https://hub.steampipe.io/mods/turbot/aws_thrifty https://hub.steampipe.io/mods/turbot/aws_thrifty 2 - https://hub.steampipe.io/plugins/turbot/aws/tables?filter=cost https://hub.steampipe.io/plugins/turbot/aws/tables?filter=co...
- xwowsersx 4y ago
  Haha thanks for the reply. Insane how much pricing data there is, right? The least AWS can do is offer an export on the particular pricing pages!

chrsstrm 4y ago

So this looks interesting and I wanted to test it on a use-case I have currently. I want to query both a csv and a zipped csv living online but it doesn't appear that is possible. The csv plugin implies you can only work with local files, nothing over http(s). This kind of seems like a big oversight for a product like this, no?

nathanwallace 4y ago
We have work in progress to support go-getter [1] allowing use of files from many different sources. There is a WIP branch [2] building that into the Steampipe Plugin SDK, so it can be immediately enabled for all our file based plugins (CSV, Terraform, etc) [3]. Watch this space and hopefully you can help us test! 1 - https://github.com/hashicorp/go-getter https://github.com/hashicorp/go-getter 2 - https://github.com/turbot/steampipe-plugin-sdk/compare/main...issue-434 https://github.com/turbot/steampipe-plugin-sdk/compare/main.... 3 - https://github.com/turbot/steampipe-plugin-terraform/tree/add-support-to-list-files-using-go-getter https://github.com/turbot/steampipe-plugin-terraform/tree/ad...

michaelsalim 4y ago

This makes me want to create a Grafana dashboard for everything. Instead of relying on custom plugins (or pulling & storing the data yourself), now it's just another postgres data source. I hope this becomes mainstream!

nathanwallace 4y ago
100% - works great with Grafana, Metabase, Tableau, etc because it’s just Postgres :-) If you are into the idea of "dashboards as code", you may also enjoy our HCL+SQL approach in mods [1] - runs in your cli, open source, composable, great DX [2] [1] - https://hub.steampipe.io/mods https://hub.steampipe.io/mods [2] - https://steampipe.io/blog/dashboards-as-code https://steampipe.io/blog/dashboards-as-code

husamia 4y ago

it's all about the data sources

iteratorx 4y ago

I have recently tried to set up Steampipe to query all our cloud costs, but I noticed that costs are only available for AWS. Azure, GCP, DigitalOcean costs are not available in their respective plugins. Has anyone found a way to get the costs in those plugins? It is a limitation of the plugins or of the underlying cloud APIs?

nathanwallace 4y ago
Please open GitHub issues (or PRs) for any tables you'd like to added! If there is a cost API then we'd definitely like to have a table to query it. In the meantime, you may find the "Thrifty" mods useful - they have many prebuilt queries to look for cost saving opportunities in AWS, Azure, GCP and DigitalOcean - https://hub.steampipe.io/mods?q=thrifty https://hub.steampipe.io/mods?q=thrifty

deleted 4y ago

[deleted]

specialist 4y ago

Wow. Steampipe is just bitchin'. I've been noodling on a half-baked notion to make REST API clients easier. Think something between cURL and Postman. Steampipe is just such a better idea. And from first looks, the implementation looks like a bullseye. Bravo.

g0dspeed 4y ago

tl;dr unnecessary abstraction. all of this can be managed by REST APIs, and is already done so, why revert back to disgusting, archaic looking SQL limitations?

niutech 4y ago

Anybody remembers Yahoo Pipes with YQL? This looks similar.

kohlerm 4y ago

They claim that a "Join" of APIs is possible. I wonder how that would work in the general case and what the performance characteristic are? Any experiences?

deleted 4y ago

[deleted]

haolez 4y ago

Looks fun. I assume it's just a visibility tool and there is no intention of offering INSERT, UPDATE and DELETE, right?

nathanwallace 4y ago
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.html https://www.postgresql.org/docs/current/ddl-foreign-data.htm... 2 - https://martinfowler.com/bliki/CQRS.html https://martinfowler.com/bliki/CQRS.html

nathanwallace 4y ago

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

d0gsg0w00f 4y ago

Could someone use this query across many hundreds of accounts?

nathanwallace 4y ago
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 https://steampipe.io/blog/selective-select

TruthWillHurt 4y ago

wow.

xenophonf 4y ago

This cannot possibly require WSL2. What the heck? If I had an afternoon to waste RTFSing, I'd fix this. Some of us are stuck running WSL1 because Hyper-V breaks features we need in other hypervisors.

nathanwallace 4y ago
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-wont-init https://stackoverflow.com/questions/72358475/postgresql-wsl-... 2 - https://stackoverflow.com/questions/69808468/postgres-is-stuck-when-creating-a-database https://stackoverflow.com/questions/69808468/postgres-is-stu...
hedora 4y ago
On the one hand, I see your point; hacking it together probably isn't hard. On the other, they're a startup with limited resources, and time to market matters dearly for them. They probably don't even have product-market fit yet, and supporting Microsoft technologies that Microsoft has abandoned seems questionable. Anyway, I upvoted your comment, because it could be that their target market is running in weird enterprise-y environments and your problem is common. Feedback is helpful and hard fought at small companies. (No affiliation.)
- xenophonf 4y ago
  The downvotes actually motivate me to fix this, except the docs have zero information on how to set up a development environment. I can tweak the CLI and `go build` it, but the plugin install process ends up overwriting my changes somehow. I've wasted about two hours learning Go and digging into the project's CI/CD pipeline, trying to figure out how everything works, and I haven't even begin debugging the actual problem I'm having. Ultimately, this goes on the pile of neat things I saw on HN but don't actually work IRL. It's too bad because this looks super cool.

mikessoft_gmail 4y ago

ryanmarsh 4y ago

Is this IaSQL reborn or a completely new product?

nathanwallace 4y ago
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.

blibble 4y ago

looks cool, think you could expose it as a rest API? (I kid, I kid)

ashrodan 4y ago
I'd want to know how to have a hosted solution. Ideally build reports and alerts
- nathanwallace 4y ago
  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 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
- CSDude 4y ago
  Steampipe has their own cloud at https://steampipe.io/docs/cloud/overview https://steampipe.io/docs/cloud/overview Alternative is Cloudquery but they don't offer cloud anymore. https://www.cloudquery.io/ https://www.cloudquery.io/ Plug, not based on Steampipe, but we also offer similar managed SQL on your cloud assets. https://www.resmo.com/ https://www.resmo.com/
nathanwallace 4y ago
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 https://steampipe.io/docs/cloud/integrations/overview 2 - https://steampipe.io/docs/cloud/develop/query-api https://steampipe.io/docs/cloud/develop/query-api

Canada 4y ago

This looks fantastic!

RicoElectrico 4y ago

https://developer.valvesoftware.com/wiki/SteamPipe https://developer.valvesoftware.com/wiki/SteamPipe No way they did not know about that name collision as SteamPipe has been around since 2013-ish.

robertlagrant 4y ago
I'm about to blow your mind, then. https://www.sincocustoms.com/shop/product/209312/steampipe--straights/ https://www.sincocustoms.com/shop/product/209312/steampipe--...
- benbristow 4y ago
  I'm about to blow your mind, then https://en.wikipedia.org/wiki/Stephenson%27s_Rocket https://en.wikipedia.org/wiki/Stephenson%27s_Rocket
  - masukomi 4y ago
    I'm about to blow your mind, then https://en.wikipedia.org/wiki/Stirling_engine https://en.wikipedia.org/wiki/Stirling_engine
    - breck 4y ago
      Try dropping a few thousand years https://en.wikipedia.org/wiki/Aeolipile https://en.wikipedia.org/wiki/Aeolipile
jcims 4y ago
I've been around since 1973 and didn't know.
- deleted 4y ago
  [deleted]
isamuel 4y ago
Name collision researcher here! This made me curious and I started doing research on other problematic name collisions. Did you know that Valve’s game distribution service has a name that’s already widely used in the scientific community for the gaseous form of heated water? It looks like steam has been around for several centuries at least so Valve really ought to have known better. Anyway, if you’re interested, I’m putting together a long investigation on whether Microsoft’s flagship operating system is in collision with a very common term used in the construction and glass industry. Hope to have it done soon

swalsh 4y ago

Since starting my career, the tech stack I use has changed numerous times. Technologies have come, gone, evolved. But except for a brief period in 2010 where we thought we could kill it, SQL is the one technology that has stayed around. It might be the only TECHNICAL knowledge I learned in the first year of my career that is still relavent. You're probably not making the wrong decision if you decide to build on top of SQL.

matwood 4y ago
> It might be the only TECHNICAL knowledge I learned in the first year of my career that is still relavent. Agree. IIRC, I wrote my first SQL is 1996 and wrote some today. While not perfect, it’s amazing at what it does. I suggest reading Joe Celko to really up your SQL skills.
- johnthescott 4y ago
  yep. and i wrote my first quel in 1984 at BLI. and sql still won the war.
quickthrower2 4y ago
The problem I see here is building SQL on top of the cloud, which is like building SQL on top of a database where the tables are always changing - not just adding columns but the meaning of the columns and rows, or even the existence of the table. I think this is a good idea, but the cloud doesn't seem to care too much about backwards compatibility. If you use MySQL/PostgreSQL/SQL Server, you can be fairly sure in 20 years time your SQL will still work :-). The need for this library is more an indictment on "the cloud" than anything. I am mostly an Azure user so AWS/Google might be better but man it changes it's UI and CLI interfaces alot - way too much.
rsync 4y ago
"Since starting my career, the tech stack I use has changed numerous times. Technologies have come, gone, evolved. But except for a brief period in 2010 where we thought we could kill it, SQL is the one technology that has stayed around." Ethernet comes to mind when I think of technologies that just can't be killed ...

yevpats 4y ago

Good to see steampipe here :) Im the author of CloudQuery (https://github.com/cloudquery https://github.com/cloudquery) and I believe we started at the same time though took some different approaches. PG FDW - is def an exciting PostgreSQL tech and is great for things like on-demand querying and filtering. We took a more standard ELT approach where we have a protocol between source plugins and destination plugins so we can support multiple databases, data-lakes, storage layers, kinda similar to Fivetran and airbyte but with focus on infrastructure tools. Also, as a more standard ELT approach our policies use standard SQL directly and dashboards re-use existing BI tools rather then implementing dashboarding in-house. I can see the power of all-in-one and FDW and def will be following this project!

alexchantavy 4y ago
/me waves Immediately thought of CloudQuery when I saw this and sure enough, here you are :)

chousuke 4y ago

Steampipe is unbeliveably powerful. Writing custom scripts for even the simplest of queries comes nowhere near the convenience of using PostgreSQL to get what you want. If you wanted to find and report all instances in an AWS account across all regions, with steampipe it's just: SELECT * FROM aws_ec2_instance; Even the simplest implementation with a Python script won't be nearly as nice, and once you start combining tables and doing more complicated queries, there's just no competition.

phillu 4y ago
I don't agree that this simple query wouldn't be as nice with boto3. But combining different services is definitely a nice feature, if you have the use case for it.
- chousuke 4y ago
  the fun part of that query is that it'll search across all regions that you have configured steampipe to use. If you did that in Python, you at the very least have to manually iterate over each region, handle pagination yourself and combine the results. It gets much more convenient when you want to ask more complicated questions like "What is the most used instance type per region" or "How much EBS capacity do instances use across all regions, grouped by environment tag and disk type?"
Hikikomori 4y ago
Tried steampipe out to replace my simple python script. It wasn't able to handle our 1k+ accounts and multiple regions, initialization took over 1 hour before I was even able to do a query. Meanwhile my python script can run any API calls in all our accounts and regions and finish in a few minutes. Maybe 100 lines of python using boto3 and multiprocessing that outputs to json or yaml.
- xwowsersx 4y ago
  1K accounts? What're you doing over there exactly? I'm intrigued!

almenon 4y ago

Does this have autocomplete in the terminal?

nathanwallace 4y ago
"steampipe query" has auto-complete for SQL queries in the interactive terminal - https://steampipe.io/docs/query/query-shell#autocomplete 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 https://steampipe.io/docs/query/third-party "steampipe completion" provides terminal completion for the Steampipe CLI - https://steampipe.io/docs/reference/cli/completion https://steampipe.io/docs/reference/cli/completion

diceduckmonk 4y ago

We built a UI-centric cross-cloud search engine, too. <edit: link redacted as other commenter perceived this to be an ad> The first VC we pitched to felt like this was too niche of a problem. They wanted us to come back with a different, grander, pitch, so we'll see. In the current fundraising climate, it's been difficult to gather data points on whether we are on the right track (this post makes us feel like we're not crazy). We reached out to investors outside America, where we're based, and we're quickly realizing the VCs aren't as tech-savvy as we expected. After going thru the YC application process this cycle, we've have much greater appreciation for YC. They understand tech and startups, both. For one, we're instructed to define a toy / small problem, "don't talk, do" as opposed to pretty slides and ideas. Best of luck to Steampipe and whoever else is working on this problem.

payphonefiend 4y ago
Please make your site mobile friendly.
- diceduckmonk 4y ago
  Apologies. This was an experimental landing page haphazardly thrown together after the investors we're pitching to wanted a two-slide problem/solution. The rest of the app is actually made mobile-first.
haggy 4y ago
Not a fan of what feels like an advertisement hijacking the OPs post. Most would probably prefer you just make a separate HN post.
- diceduckmonk 4y ago
  I'm sorry it was perceived as such. I should have been more mindful. I genuinely intended for the substantive part of the post to be about our frustrations as programmers navigating the business world. It felt like the short introductory sentence was necessary to establish context (I'll remove the link, but leave post intact so other viewers aren't confused). Still off topic but, at the time, it seemed to be an on-brand discussion for HackerNews. What we were really phishing for was rapport from other engineers that we aren't crazy. I even feel vulnerable putting our project out here, at this stage, and the quick cost-benefit analysis in my head was this was a net-neutral. I doubt Steampipe knew we existed before this post. It seems like fate because I stumbled upon Steampipe just last week. I deliberated how to craft a not-awkward email along the lines of "Hey guys, we just realized we're tackling the same problem. Cheers." When I made the original post, I felt like it was the less awkward way of saying hi but now I'm not sure. At any rate, I hope you understand we did it in good will and not malice. Edit: I also hope "Best of luck to Steampipe and whoever else is working on this problem" doesn't get misconstrued as being a passive aggressive jab. Our team would honestly be thrilled if all the friction points of IaaS goes away, regardless of who solves it. As a reverse-ad, I'll say a few nice things about Steampipe that stood out to me: written in Go, supports querying HackerNews, SQL/Tables are a really good medium.

Canada 4y ago

I really like how it just worked when I installed it and the AWS plugin. The second one I tried was tailscale... I get some different results to the same query which is weird and broken, and an error "device can only be managed by original tailnet (403) (SQLSTATE HV000)" Still, getting access to all this stuff with a SQL interface is very compelling so I'll try to get the plugins for all the stuff I have working properly.

nathanwallace 4y ago
Glad AWS worked. Sorry you hit a bump with tailscale - we'd love feedback / contributions for that plugin [1], we just released it this month so may be edges for more complex configurations :-( 1 - https://github.com/turbot/steampipe-plugin-tailscale https://github.com/turbot/steampipe-plugin-tailscale

FBISurveillance 4y ago

I wonder if we can connect this with Hasura and query our cloud inventory via GraphQL. I'm only half-joking, actually.

nathanwallace 4y ago
Because it's just PostgreSQL, Steampipe works out of the box with SQL clients, BI tools and other integrations [1]. Please let us know how you go! 1 - https://steampipe.io/docs/query/third-party https://steampipe.io/docs/query/third-party

ezconnect 4y ago

Yahoo was really ahead of its time, so sad it was mismanaged.

Aaronstotle 4y ago

I found Steampipe from a HN comment about a year and some change ago, in my last role I had to routinely audit cloud configurations and it was amazing to have a SQL query to check all S3 buckets that were encrypted, security groups, etc.

ashayh 4y ago

Highly recommend Steampipe. No other open source tool was able to deal with the large number of Route53 records I had to deal with.

ezekg 4y ago

How does this handle pagination and also how does it behave towards rate limiters? For example, my API (keygen.sh) has a page size limit of 100, and often times there can be hundreds or even thousands of pages for certain resources. How would Steampipe handle `select count(*) from keygen_licenses` while obeying the rate limiter? What if a heavy query is run twice?

judell 4y ago
Pagination may be handled by the API's golang SDK, if not that's a responsibility of the plugin author. The plugin SDK provides a default retry/backoff mechanism, and the plugin author can enhance that. If a heavy query runs twice it'll load from cache the second time, if within the (user-configurable) cache TTL.

pstuart 4y ago

Seems like the next step past https://osquery.io/ https://osquery.io/

nathanwallace 4y ago
osquery is awesome. It was definitely part of our inspiration!

jedberg 4y ago

Steampipe killed my startup. I couldn't be happier about it. I started a company six years ago to do exactly this -- make a SQL interface for the AWS API. Steampipe out-executed us and made a way better product, in part because their technology choices were much smarter than ours. I shut down the startup as soon as I saw steampipe. I wish them all the best, it's a great product!

patrec 4y ago
I'm curious: what were the bad technological choices you made and the good ones they made?
- encryptluks2 4y ago
  Can't speak for op but one common mistake I see is having a bunch of JS developers try to make a CLI and being surprised it is slow.
- jedberg 4y ago
  They use Postgres foreign data wrappers and caching with live queries against the Api. We were doing periodic scanning and then storing it in elastic. Then trying to convert the SQL to elastic queries. The elastic part let us do free text queries but converting from SQL was challenging (this was before elastic had it built in). And also everything was only as fresh as our last scan.

serhatcan 4y ago

Great to see steampipe here :) Founder of Resmo here! Select * from cloud & SaaS + a fully managed SaaS platform with a free offering. Make sure to check us out!

debarshri 4y ago

There is another contender in infrasql realm called cloudquery[1]. Steampipe has been around for very long. Mind you the steampipe has an AGPL license. [1]https://www.cloudquery.io https://www.cloudquery.io

hugofromboss 4y ago

Shameless plug for those of you who want to query your assets with Osuqery and cloud assets with Steampipe. Check out https://www.github.com/zercurity/zercurity https://www.github.com/zercurity/zercurity.

intrasight 4y ago

How does this relate to or differ from Multicorn? I see that in most of the "Type" columns in the "specific web wrappers" section of the Postgres FDW page https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers

nathanwallace 4y ago
Postgres FDWs are extensions installed in Postgres. Multicorn provides an easy way to implement Postgres FDW extensions in Python, leading to many FDW implementations. Steampipe runs as a Postgres FDW extension with a pluggable interface to easily build and manage data sources. Multicorn-based FDWs might be installed as part of your existing Postgres server, while Steampipe is optimized to run more standalone / alongside your existing Postgres server.
- intrasight 4y ago
  So Steampipe runs alongside? In a separate process?

forty 4y ago

I have discovered steampipe only recently. We work with a few AWS accounts and the steampipe dashboard and query are the best tool I have seen since terraform (of which I'm pretty sure it borrows the resource modules). Totally recommend it for making sense of your AWS accounts mess and for audits (soc2 style for example)