We’ve seen how Steampipe can unify access to APIs, drive metasearch, enforce KPIs as code, and detect configuration drift. The enabling plugins were, until recently, tightly bound to the Steampipe binary and to the instance 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 answer is yes—and more. But let’s focus on Postgres first.
Using a Steampipe plugin as a Postgres foreign data wrapper
Visit Steampipe downloads to find the installer for your OS, and run it to acquire the Postgres foreign data wrapper (FDW) distribution of a plugin—in this case, the GitHub plugin.
“`html
sudo /bin/sh -c “$(curl -fsSL https://steampipe.io/install/postgres.sh)”
“`
Enter the plugin name: github
Enter the version (latest):
Discovered:
– PostgreSQL version: 14
– PostgreSQL location: /usr/lib/postgresql/14
– Operating system: Linux
– System architecture: x86_64
Based on the above, steampipe_postgres_github.pg14.linux_amd64.tar.gz will be downloaded, extracted and installed at: /usr/lib/postgresql/14
Proceed with installing Steampipe PostgreSQL FDW for version 14 at /usr/lib/postgresql/14?
– Press ‘y’ to continue with the current version.
– Press ‘n’ to customize your PostgreSQL installation directory and select a different version. (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 completed.
Installing steampipe_postgres_github in /usr/lib/postgresql/14…
Successfully installed steampipe_postgres_github extension!
Files have been copied to:
– Library directory: /usr/lib/postgresql/14/lib
– Extension directory: /usr/share/postgresql/14/extension/
Now connect to your server as usual, using psql or another client, most typically as the postgres user. Then run these commands which are typical for any Postgres foreign data wrapper. As with all Postgres extensions, you start like this:
“`html
CREATE EXTENSION steampipe_postgres_fdw_github;
“`
To use a foreign data wrapper, you first create a server:
“`html
CREATE SERVER steampipe_github FOREIGN DATA WRAPPER steampipe_postgres_github OPTIONS (config ‘token=”ghp_…”‘);
“`
Use OPTIONS to configure the extension to use your GitHub access token. (Alternatively, the standard environment variables used to configure a Steampipe plugin—it’s just GITHUB_TOKEN in this case—will work if you set them before starting your instance of Postgres.)
The tables provided by the extension will live in a schema, so define one:
“`html
CREATE SCHEMA github;
“`
Now import the schema defined by the foreign server into the local schema you just created:
“`html
IMPORT FOREIGN SCHEMA github FROM SERVER steampipe_github INTO github;
“`
Now run a query! The foreign tables provided by the extension live in the github schema, so by default you’ll refer to tables like github.github_my_repository. If you set search_path=”github”, though, the schema becomes optional and you can write queries using unqualified table names.
“`html
select count(*) from github_my_repository;
“`
count
——-
468
If you have a lot of repos, the first run of that query will take a few seconds. The second run will return results instantly, though, because the extension includes a powerful and sophisticated cache.
And that’s all there is to it! Every Steampipe plugin is now also a foreign data wrapper that works exactly like this one. You can load multiple extensions in order to join across APIs. Of course you can join any of these API-sourced foreign tables with your own Postgres tables. And to save the results of any query, you can prepend create table NAME as or create materialized view NAME as to a query to persist results as a table or view.
Using a Steampipe plugin as a SQLite extension that provides virtual tables
Visit Steampipe downloads to find the installer for your OS, and run it to acquire the SQLite distribution of the same plugin.
“`html
sudo /bin/sh -c “$(curl -fsSL https://steampipe.io/install/sqlite.sh)”
“`
Enter the plugin name: github
Enter version (latest):
Enter location (current directory):
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 place this code in ~/.sqliterc if you want to run it every time you start sqlite.
“`html
.load /home/jon/steampipe-sqlite/steampipe_sqlite_github.so
select steampipe_configure_github(‘ token=”ghp_…” ‘);
“`
Now you can run the same query as above.
“`html
sqlite> select count(*) from github_my_repository;
“`
count(*)
468
What about the differences between Postgres-flavored and SQLite-flavored SQL? The Steampipe hub is your friend! For example, here are variants of a query that accesses a field inside a JSON column in order to tabulate the languages associated with your gists. IDG
Here too you can load multiple extensions in order to join across APIs. You can join any of these API-sourced foreign tables with your own SQLite tables. And you can prepend create table NAME as to a query to persist results as a table.
Using a Steampipe plugin as a stand-alone export tool
Visit Steampipe downloads to find the installer for your OS, and run it to acquire the export distribution of a plugin. Again, we’ll illustrate using the GitHub plugin.
“`html
sudo /bin/sh -c “$(curl -fsSL https://steampipe.io/install/export.sh)”
“`
Enter the plugin name: github
Enter the version (latest):
Enter location (/usr/local/bin):
Created temporary directory at /tmp/tmp.48QsUo6CLF.
Downloading steampipe_export_github.linux_amd64.tar.gz…
##############################################################################
100.0%
Deflating downloaded archive steampipe_export_github
Installing
Applying necessary permissions
Removing downloaded archive
steampipe_export_github was installed successfully to /usr/local/bin
$ steampipe_export_github -h
Export data using the github plugin. Find detailed usage information including table names, column names, and examples at the Steampipe Hub: https://hub.steampipe.io/plugins/turbot/github
Usage:
steampipe_export_github TABLE_NAME [flags]
Flags:
–config string Config file data
-h, –help help for steampipe_export_github
–limit int Limit data
–output string Output format: csv, json or jsonl (default “csv”)
–select strings Column data to display
–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:
“`html
steampipe_export_github github_my_gist –output json > gists.json
“`
To select only some columns and export to a CSV file:
“`html
steampipe_export_github github_my_gist –output csv –select “description,created_at,html_url” > gists.csv
“`
You can use –limit to limit the rows returned, and –where to filter them, but mostly you’ll use this tool to quickly and easily grab data that you’ll massage elsewhere, for example in a spreadsheet.
Tap into the Steampipe plugin ecosystem
Steampipe plugins aren’t just raw interfaces to underlying APIs. They use tables to model those APIs in useful ways.
Source link
