Steampipe unbundled: From cloud APIs to your database

We’ve seen how Steampipe can unify entry to APIs, drive metasearch, implement KPIs as code, and detect configuration drift. The enabling plugins have been, till just lately, tightly certain to the Steampipe binary and to the occasion of Postgres that Steampipe launches and controls. That led members of Steampipe’s open-source group to ask, “Can we use the plugins in our personal Postgres databases?” Now the reply is sure—and extra. However let’s give attention to Postgres first.

Utilizing a Steampipe plugin as a Postgres overseas information wrapper

Go to Steampipe downloads to seek out the installer in your OS, and run it to accumulate the Postgres overseas information wrapper (FDW) distribution of a plugin—on this case, the GitHub plugin.

$ sudo /bin/sh -c "$(curl -fsSL https://steampipe.io/set up/postgres.sh)"
Enter the plugin title: github
Enter the model (newest): 

Found:
- PostgreSQL model:   14
- PostgreSQL location:  /usr/lib/postgresql/14
- Working system:     Linux
- System structure:  x86_64

Based mostly on the above, steampipe_postgres_github.pg14.linux_amd64.tar.gz will likely be downloaded, extracted and put in at: /usr/lib/postgresql/14

Proceed with putting in Steampipe PostgreSQL FDW for model 14 at /usr/lib/postgresql/14?
- Press 'y' to proceed with the present model.
- Press 'n' to customise your PostgreSQL set up listing and choose a distinct model. (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.management
steampipe_postgres_github.pg14.linux_amd64/steampipe_postgres_github--1.0.sql
steampipe_postgres_github.pg14.linux_amd64/set up.sh
steampipe_postgres_github.pg14.linux_amd64/README.md

Obtain and extraction accomplished.

Putting in steampipe_postgres_github in /usr/lib/postgresql/14...

Efficiently put in steampipe_postgres_github extension!

Recordsdata have been copied to:
- Library listing: /usr/lib/postgresql/14/lib
- Extension listing: /usr/share/postgresql/14/extension/  

Now hook up with your server as common, utilizing psql or one other consumer, most usually because the postgres person. Then run these instructions that are typical for any Postgres overseas information wrapper. As with all Postgres extensions, you begin like this:

CREATE EXTENSION steampipe_postgres_fdw_github;

To make use of a overseas information wrapper, you first create a server:

CREATE SERVER steampipe_github FOREIGN DATA WRAPPER steampipe_postgres_github OPTIONS (config 'token="ghp_..."');

Use OPTIONS to configure the extension to make use of your GitHub entry token. (Alternatively, the usual atmosphere variables used to configure a Steampipe plugin—it’s simply GITHUB_TOKEN on this case—will work in the event you set them earlier than beginning your occasion of Postgres.)

The tables offered by the extension will dwell in a schema, so outline one:

CREATE SCHEMA github;

Now import the schema outlined by the overseas server into the native schema you simply created:

IMPORT FOREIGN SCHEMA github FROM SERVER steampipe_github INTO github;

Now run a question!

The overseas tables offered by the extension dwell within the github schema, so by default you’ll confer with tables like github.github_my_repository. If you happen to set search_path="github", although, the schema turns into elective and you’ll write queries utilizing unqualified desk names.

choose
  rely(*)
from
  github_my_repository;

 rely
-------
   468

You probably have a whole lot of repos, the primary run of that question will take just a few seconds. The second run will return outcomes immediately, although, as a result of the extension features a highly effective and complex cache.

And that’s all there’s to it! Each Steampipe plugin is now additionally a overseas information wrapper that works precisely like this one. You may load a number of extensions as a way to be a part of throughout APIs. After all you possibly can be a part of any of those API-sourced overseas tables with your personal Postgres tables. And to avoid wasting the outcomes of any question, you possibly can prepend create desk NAME as or create materialized view NAME as to a question to persist outcomes as a desk or view.

Utilizing a Steampipe plugin as a SQLite extension that gives digital tables

Go to Steampipe downloads to seek out the installer in your OS, and run it to accumulate the SQLite distribution of the identical plugin.

$ sudo /bin/sh -c "$(curl -fsSL https://steampipe.io/set up/sqlite.sh)"
Enter the plugin title: github
Enter model (newest): 
Enter location (present listing): 

Downloading steampipe_sqlite_github.linux_amd64.tar.gz...
############################################################################ 100.0%
steampipe_sqlite_github.so

steampipe_sqlite_github.linux_amd64.tar.gz downloaded and extracted efficiently at /residence/jon/steampipe-sqlite.

Right here’s the setup. You may place this code in ~/.sqliterc if you wish to run it each time you begin sqlite.

.load /residence/jon/steampipe-sqlite/steampipe_sqlite_github.so

choose steampipe_configure_github('
  token="ghp_..."
');

Now you possibly can run the identical question as above.

sqlite> choose rely(*) from github_my_repository;
rely(*)
468  

What in regards to the variations between Postgres-flavored and SQLite-flavored SQL? The Steampipe hub is your pal! For instance, listed below are variants of a question that accesses a subject inside a JSON column as a way to tabulate the languages related together with your gists.

IDG

Right here too you possibly can load a number of extensions as a way to be a part of throughout APIs. You may be a part of any of those API-sourced overseas tables with your personal SQLite tables. And you may prepend create desk NAME as to a question to persist outcomes as a desk.

Utilizing a Steampipe plugin as a stand-alone export device

Go to Steampipe downloads to seek out the installer in your OS, and run it to accumulate the export distribution of a plugin. Once more, we’ll illustrate utilizing the GitHub plugin.

$ sudo /bin/sh -c "$(curl -fsSL https://steampipe.io/set up/export.sh)"
Enter the plugin title: github
Enter the model (newest): 
Enter location (/usr/native/bin): 
Created momentary listing at /tmp/tmp.48QsUo6CLF.

Downloading steampipe_export_github.linux_amd64.tar.gz...
############################################################################## 100.0%
Deflating downloaded archive
steampipe_export_github
Putting in
Making use of needed permissions
Eradicating downloaded archive
steampipe_export_github was put in efficiently to /usr/native/bin

$ steampipe_export_github -h
Export information utilizing the github plugin.

Discover detailed utilization data together with desk names, column names, and 
examples on the Steampipe Hub: https://hub.steampipe.io/plugins/turbot/github

Utilization:
  steampipe_export_github TABLE_NAME [flags]

Flags:
      --config string       Config file information
  -h, --help                assist for steampipe_export_github
      --limit int           Restrict information
      --output string       Output format: csv, json or jsonl (default "csv")
      --select strings      Column information to show
      --where stringArray   the place clause information

There’s no SQL engine within the image right here; this device is only an exporter. To export all of your gists to a JSON file:

steampipe_export_github github_my_gist --output json > gists.json

To pick out 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 should utilize --limit to restrict the rows returned, and --where to filter them, however principally you’ll use this device to shortly and simply seize information that you just’ll therapeutic massage elsewhere, for instance in a spreadsheet.

Faucet into the Steampipe plugin ecosystem

Steampipe plugins aren’t simply uncooked interfaces to underlying APIs. They use tables to mannequin these APIs in helpful methods. For instance, the github_my_repository desk exemplifies a design sample that applies constantly throughout the suite of plugins. From the GitHub plugin’s documentation:

You may personal repositories individually, or you possibly can share possession of repositories with different folks in a company. The github_my_repository desk will checklist repos that you just personal, that you just collaborate on, or that belong to your organizations. To question ANY repository, together with public repos, use the github_repository desk.

Different plugins comply with the identical sample. For instance, the Microsoft 365 plugin offers each microsoft_my_mail_message and microsoft_mail_message, and the Google Workspace plugin offers googleworkspace_my_gmail_message and googleworkspace_gmail. The place doable, plugins consolidate views of sources from the attitude of an authenticated person.

Whereas plugins usually present tables with fastened schemas, that’s not at all times the case. Dynamic schemas, carried out by the Airtable, CSV, Kubernetes, and Salesforce plugins (amongst others), are one other key sample. Right here’s a CSV instance utilizing a stand-alone Postgres FDW.

IMPORT FOREIGN SCHEMA csv FROM SERVER steampipe_csv INTO csv OPTIONS(config 'paths=["/home/jon/csv"]');

Now all of the .csv recordsdata in /residence/jon/csv will automagically be Postgres overseas tables. Suppose you retain monitor of legitimate homeowners of EC2 cases in a file known as ec2_owner_tags. Right here’s a question in opposition to the corresponding desk.

choose * from csv.ec2_owner_tags;
     proprietor      |            _ctx
----------------+----------------------------
 Pam Beesly     | {"connection_name": "csv"}
 Dwight Schrute | {"connection_name": "csv"}

You might be a part of that desk with the AWS plugin’s aws_ec2_instance desk to report proprietor tags on EC2 cases which are (or will not be) listed within the CSV file.

choose 
    ec2.proprietor,
    case 
        when csv.proprietor is null then 'false'
        else 'true'
    finish as is_listed
from 
    (choose distinct tags ->> 'proprietor' as proprietor from aws.aws_ec2_instance) ec2
left be a part of 
    csv.ec2_owner_tags csv on ec2.proprietor = csv.proprietor;

     proprietor      | is_listed
----------------+-----------
 Dwight Schrute | true
 Michael Scott  | false

Throughout the suite of plugins there are greater than 2,300 pre-defined fixed-schema tables that you should use in these methods, plus an infinite variety of dynamic tables. And new plugins are continuously being added by Turbot and by Steampipe’s open-source group. You may faucet into this ecosystem utilizing Steampipe or Turbot Pipes, from your personal Postgres or SQLite database, or immediately from the command line.