Steampipe Is Sql Magic
I tried Steampipe out for the first time today.
I’m seriously impressed.
I built a project go-aws-ami-metrics last year to test out some Go that would iterate through instances and AMIs to build out aging information on the instances.
I used it to help me work through how to use the AWS SDK to iterate through regions, instances, images, and more.
In 15 mins I just solved the equivalent issue in a way that would benefit anyone on a team. My inner skeptic was cynical, thinking this abstraction would be problematic and I’d be better served by just sticking with the raw power of the SDK.
It turns out this tool already is built on the SDK using the same underlying API calls I’d be writing from scratch.
First example: DescribeImage
This is the magic happening in the code.
This is the same SDK I used, but instead of having to build out all the calls, there is a huge library of data already returned.
There is no need to reinvent the wheel. Instead of iterating through regions, accounts, and more, Steampipe allows this in plain old SQL.
For example, to gather:
- EC2 Instances
- that use AWS Owned Images
- and use an image that created greater than
- and want the aging in days
There are plugins for GitHub, Azure, AWS, and many more.
You can even do cross-provider calls.
Imagine wanting to query a tagged instance and pulling the tag of the work item that approved this release. Join this data with Jira, find all associated users involved with the original request, and you now have an idea of the possibility of cross-provider data Steampipe could simplify.
Stiching this together is complicated. It would involve at least 2 SDK’s and their unique implementation.
I feel this is like Terraform for Cloud metadata, a way to provide a consistent experience with syntax that is comfortable to many, without the need to deal with provider specific quirks.
- I downloaded the recommended TablePlus with
brew install tableplus.
steampipe service startin my terminal.
- Copied the Postgres connection string from the terminal output and pasted into TablePlus.
- Pasted my query, ran, and results were right there as if I was connected to a database.
AWS has lots of ways to get data. AWS Config can aggregate across multiple accounts, SSM can do inventory, and other tools can do much of this.
AWS isn’t easy. Doing it right is hard. Security is hard.
Expertise in building all of this and consuming can be challenging.
🎉 Mission accomplished!
I think Steampipe is offering a fantastic way to get valuable information out of AWS, Azure, GitHub, and more with a common language that’s probably the single most universal development language in existenance: SQL.
One of the goals of Steampipe since we first started envisioning it is that it should be simple to install and use - you should not need to spend hours downloading pre-requisites, fiddling with config files, setting up credentials, or pouring over documentation. We’ve tried very hard to bring that vision to reality, and hope that it is reflected in Steampipe as well as our plugins.
Providing a cli with features like this is incredible.
- turn into an interactive terminal
- provide prompt completion to commands
- a background service to allow connection via IDE
The future is bright as long as
truncate ec2_instance doesn’t become a thing. 😀
If you want to explore the available schema, check out the thorough docs.
- AWS Tables List shows
212tables of metadata currently available.
- Use Named queries to build a library of easy queries to call on demand such as
- Use Mods to download a set of named queries and controls to validate things like security and compliance.
- Query multiple connections at once
- Design Principles