We’ve seen how Steampipe can combine access to APIs, drive metasearch, implement KPIs as code, and identify configuration drift. The enabling plugins were, till recently, tightly bound to the Steampipe binary and to the circumstances of Postgres that Steampipe launches and controls. That led members of Steampipe’s open-source community to ask,”Can we use the plugins in our own Postgres databases?”Now the response is yes– and more. However let’s focus on Postgres first.Using a Steampipe plugin as a Postgres foreign information wrapper See Steampipe downloads to discover the installer for your OS, and run it to acquire the Postgres foreign information wrapper(FDW )distribution of a plugin– in this case, the GitHub plugin.$ sudo/ bin/sh- c” $(curl- fsSL https://steampipe.io/install/postgres.sh )”Enter the plugin name: github Get in the version(most current): Found:-PostgreSQL variation
: 14-PostgreSQL location:/ usr/lib/postgresql/ 14-Running system: Linux-System architecture: x86_64 Based on the above, steampipe_postgres_github. pg14.linux _ amd64.tar.gz will be downloaded, extracted and set up at:/ usr/lib/postgresql/ 14 Proceed with setting up Steampipe PostgreSQL FDW for variation 14 at/ usr/lib/postgresql/ 14? -Press ‘y’to continue with the current variation.-Press ‘n’ to personalize your PostgreSQL setup directory and select a different variation. (Y/n ): Downloading steampipe_postgres_github. pg14.linux _ amd64.tar.gz … ########################################################################################### 100.0%steampipe_postgres_github. pg14.linux _ amd64/ steampipe_postgres_github. pg14.linux _ amd64/steampipe _ postgres_github. so steampipe_postgres_github. pg14.linux _ amd64/steampipe _ postgres_github. control steampipe_postgres_github. pg14.linux _ amd64/steampipe _ postgres_github– 1.0.sql steampipe_postgres_github. pg14.linux _ amd64/install. sh steampipe_postgres_github. pg14.linux _ amd64/README. md Download and extraction finished. Installing steampipe_postgres_github in/ usr/lib/postgresql/ 14 … Successfully set up steampipe_postgres_github extension! Files have actually been copied to:-Library directory site:/ usr/lib/postgresql/ 14/lib-Extension directory site:/ usr/share/postgresql/ 14/extension/ Now connect to your server as typical, utilizing psql or another client, a lot of typically as the postgres user. Then run these commands which are normal for any Postgres foreign data wrapper. As with all Postgres extensions, you start like this: DEVELOP EXTENSION steampipe_postgres_fdw_github; To utilize a foreign information wrapper, you initially create a server: DEVELOP SERVER steampipe_github FOREIGN DATA WRAPPER steampipe_postgres_github OPTIONS(config’token=”ghp _ …”‘); Usage choices to set up the extension to
utilize your GitHub gain access to token.(Additionally, the standard environment variables used to configure a Steampipe plugin– it’s simply GITHUB_TOKEN in this case– will work if you set them before beginning your circumstances of Postgres.) The tables provided by the extension will live in a schema, so define one: DEVELOP SCHEMA github; Now import the schema defined by the foreign server into the regional schema you just created: IMPORT FOREIGN SCHEMA github FROM SERVER steampipe_github INTO github; Now run a query! The foreign tables offered by the extension live in the github schema, so by default you’ll describe tables like github.github _ my_repository. If you set search_path= ‘github’, however, the schema ends up being optional and you can compose questions utilizing unqualified table names.select count(*)
or create emerged view NAME regarding an inquiry to continue results
as a table or view.Using a Steampipe plugin as a SQLite extension that offers virtual tables Check out Steampipe downloads to discover the installer for your OS, and run it to get the SQLite circulation of the very same plugin. $sudo/ bin/sh-c”$(curl-fsSL https://steampipe.io/install/sqlite.sh
)”Get in the plugin name: github Go into variation (most current ): Get in location(present directory site): Downloading steampipe_sqlite_github. linux_amd64. tar.gz … ############################################################################ 100.0%steampipe_sqlite_github. so steampipe_sqlite_github. linux_amd64. tar.gz downloaded and extracted successfully at/ home/jon/steampipe-sqlite. Here’s the setup. You can position this code in ~/. sqliterc if you wish to run it whenever you start sqlite. load/ home/jon/steampipe-sqlite/ steampipe_sqlite_github. so select steampipe_configure_github(‘token =”ghp _ …”‘); Now you
join across APIs. You can sign up with any of these API-sourced foreign tables with your own SQLite tables. And you can prepend
create table NAME regarding a query to continue results as a table. Using a Steampipe plugin as a stand-alone export tool Go to
Steampipe downloads to find the installer for
your OS, and run it to acquire the export
distribution of a plugin. Once again, we’ll highlight using the GitHub plugin.$sudo/ bin/sh-c “$(curl-fsSL https://steampipe.io/install/export.sh) “Get in the plugin name: github Get in the version(most current): Get in location(/ usr/local/bin): Produced short-term directory at/ tmp/tmp.48 QsUo6CLF
. Downloading steampipe_export_github. linux_amd64. tar.gz … ############################################################################## 100.0 %Deflating downloaded archive steampipe_export_github Setting up Applying needed approvals Eliminating downloaded archive steampipe_export_github was set up effectively to/ usr/local/bin $ steampipe_export_github-h Export information using the github plugin . Find comprehensive usage info consisting of table names, column names, and examples at the Steampipe Hub: https://hub.steampipe.io/plugins/turbot/github Use: steampipe_export_github TABLE_NAME [flags] Flags:– config string Config file information-h,– help for steampipe_export_github– limitation int Limit data– output string Output format: csv, json or jsonl(default”csv”)– select strings Column information to show– where stringArray where clause data There’s no SQL engine in the picture here; this tool is purely an exporter. To export all your gists to a JSON file: steampipe_export_github github_my_gist– output json > gists.json To pick just some columns and export to a CSV file: steampipe_export_github github_my_gist– output csv– select”description, created_at, html_url”> gists.csv You can use– limitation to restrict the rows returned, and– where to filter them, but mainly you’ll utilize this tool to quickly and quickly get data that you’ll massage in other places, for example in a spreadsheet.Tap into the Steampipe plugin environment Steampipe plugins aren’t just raw interfaces to underlying APIs. They use tables to model those APIs in beneficial ways. For instance, the github_my_repository table exemplifies a style pattern that uses regularly throughout the suite of plugins. From the GitHub plugin’s documents: You can own repositories separately, or
you can share ownership of repositories with other people in a company. The github_my_repository table will note repos that you own, that you team up on, or that belong to your organizations
. To query ANY repository, including public repos, utilize the github_repository table.Other plugins follow the very same pattern. For example, the Microsoft 365 plugin provides both microsoft_my_mail_message and microsoft_mail_message, and the Google Workspace plugin offers googleworkspace_my_gmail_message and googleworkspace_gmail. Where possible, plugins combine views of resources from the point of view of a validated user.While plugins generally offer tables with repaired schemas, that’s not always the case. Dynamic schemas, implemented by the Airtable, CSV, Kubernetes, and Salesforce plugins(to name a few ), are another crucial pattern. Here’s a CSV example utilizing a stand-alone Postgres FDW.IMPORT FOREIGN SCHEMA csv FROM SERVER steampipe_csv INTO csv alternatives (config’courses=[
.”/ home/jon/csv”]); Now all the.csv files in/ home/jon/csv will automagically be Postgres foreign tables. Suppose you monitor legitimate owners of EC2 instances in a file called ec2_owner_tags. Here’s an inquiry versus the matching table.select * from csv.ec2 _ owner_tags; owner|_ ctx —————- +—————————- Pam Beesly|”connection_name”:”csv” Dwight Schrute| You might sign up with that table with the AWS plugin’s aws_ec2_instance table to report owner tags on EC2 instances that are(or are not)listed in the CSV file.select ec2.owner, case when csv.owner is null then ‘incorrect
‘else’real’end as is_listed from(choose distinct tags->>’owner’as owner from aws.aws _ ec2_instance)ec2 left sign up with csv.ec2 _ owner_tags csv on ec2.owner =csv.owner; owner |
is_listed —————-+———– Dwight Schrute|true Michael Scott|incorrect Throughout the suite of plugins there are more than 2,300 pre-defined fixed-schema tables that you can use in these ways, plus an unlimited number of dynamic tables. And new plugins are constantly being included by Turbot and by Steampipe’s open-source neighborhood. You can tap into this community utilizing Steampipe or Turbot Water lines, from your own Postgres or SQLite database, or directly from the command line. Copyright © 2024 IDG Communications, Inc. Source
