Discovering Steampipe with Scalingo and Airtable plugins
I recently discovered Steampipe which allows you to query a lot of remote APIs live with SQL. Beyond having a common language to use any API, it is possible to mix several data sources with joins, to make checks and much more.
To add available data sources, it is necessary to create/install plugins.
The Scalingo plugin allows you to query a large number of resources from the Scalingo API, such as applications, addons, collaborators, etc …
For example this query will display all hosted applications:
select name, region, url from scalingo_app
Some use cases:
Find out which application uses a certain environment variable.
select a.name as app_name, e.name as env_name from scalingo_app as a join scalingo_environment as e on a.name = e.app_name where e.value = 'my variable value'
When someone leaves, it is easy to list all the applications to which they had access (and then delete them manually).
select a.name as app_name, c.email as email from scalingo_collaborator as c inner join scalingo_app as a on a.name = c.app_name where email = 'xxx'
The Airtable plugin allows you to query your Airtable tables using SQL. In addition to allowing you to use Airtable formulas, it also allows you to make joins, which is very useful in the case of Airtable.
Some use cases:
extract a list of HTML files referenced in Pix proofs that belong to a specific domain.
select c.fields->>'Référence' as ref, a.fields->>'Nom' as nom, e.id, e.fields->>'Consigne' as consigne, e.fields->>'Statut' as statut, e.fields->>'Embed URL' as embed from epreuves e, jsonb_array_elements_text(e.fields->'Acquis') acquis, acquis a, jsonb_array_elements_text(a.fields->'Compétences') competence, competences c where acquis = a.id and competence = c.id and c.fields->Code = '3' and (e.fields->>'Consigne' LIKE '%.html%' or e.fields->>'Embed URL' is not null) order by c.fields->'Référence'
find a duplicate id due to an error.
select fields->'id', count(*) as count from xxx group by fields->'id' having count(*) > 1
calculate a sum of a column by specifically selecting a subpart with a join.
select sum((a.fields->'PixValue')::float) FROM acquis a, jsonb_array_elements_text(a.fields->'Compétence (via Tube)') as cid, competences c where c.fields->>'Sous-domaine' = '2.1' and cid = c.id and a.fields->>'Status' = 'actif'
An evolution of the plugin is to use the metadata API when it will be available. It will be possible to offer a real introspection of tables with columns of the right type.
In short, Steampipe is really nice to use. Very quickly you can create your first queries and perform operations that would be difficult to perform otherwise.
Have a comment? Contact me by email.