Running Queries
Once you've added a connection, you will be able to run SQL queries to explore your data, either interactively in the console or via any PostgreSQL-compatible client.
Exploring Schemas
If you navigate to your workspace and then select Steampipe from the Pipes tab, you'll land on the interactive Query console. By default, the Schema tab will be selected on the left-hand side panel, with the query editor to the right.
The schema tab allows you to explore and inspect schemas available in your workspace.
Clicking on a schema will expand it to reveal the tables it contains. You can then click on a table to view its columns along with their respective data types. Hovering over a table displays a tooltip with its name and a few commonly used queries.
Clicking on an example query from the tooltip will automatically populate it in the Query editor on the right and will execute it for you. This is a great way to quickly explore the data in a table without having to write the query yourself. You can edit the query in the editor to experiment and explore further.
Executing the example query above sets the search path prefix (visible in the top-right corner) to hackernews
. The results pane also indicates the search path prefix used for fetching the data. Note that this prefix is set prior to the default workspace search path and applies only to the current query. You can also choose a different search path prefix using the dropdown menu.
Hovering over a column shows a tooltip containing its name, data type, and a brief description.
The schema list supports flexible searching across schemas, tables, and columns. For example, if you search for hackernews
, you will find matching results displayed for the hackernews
schema and its tables.
By default, the schema list displays Datatanks, Aggregators and any non-aggregated Connections. The connections are listed in the order they appear in the search path. You can choose to show all schemas by clicking the Settings icon and selecting Show aggregated connections from the dropdown.
Exploring Queries
The Queries tab on the sidebar lets you explore and run example queries, as well as queries from your history.
The History folder displays a list of queries you have executed, with the most recent first. This gives you a quick way to view, edit, and re-run queries you have run in the past. Click on a query to populate the query editor and run it.
The example queries are grouped by plugin. Clicking a folder will expand it to reveal the queries it contains. Depending on the plugin, you may also find the examples grouped by service. For example, the AWS
plugin has queries grouped by EC2
, S3
, IAM
and so on.
You can search for queries by name or description from the search box at the top of the sidebar.
Click on a query to populate the query editor and run it.
Downloading Results
After you've run a query, you can download the results to a CSV file by clicking the Download button at the top right corner of the query results pane.
Saving Snapshots
To take a snapshot, click the Snap button at the top right of the query results pane after you have run the query you wish to snap.
This will then take you to the dashboard snapshot view.
You can manage this snapshot and browse others from the snapshots tab.
Scheduling Query Snapshots
Rather than manually capture query snapshots, Turbot Pipes allows you to schedule them and be notified when they are complete.
Scheduling a snapshot is as simple as navigating to the Query page, executing a query, and choosing the Schedule dropdown from the toolbar in the results pane.
From here, you can either choose to create a new schedule or view all scheduled snapshots.
If you select New Schedule, you'll be presented with the following screen.
Option | Description |
---|---|
Title | The title of the schedule. |
Frequency | How often scheduled snapshot should run:Weekly , Daily , Hourly , Custom For Weekly , Daily and Hourly frequencies, Turbot Pipes will automatically allocate a random time for these, with Weekly schedules being run at that time on a Sunday. For a Custom frequency, you can supply a cron schedule (not more than once every 15 minutes). |
Visibility | Optionally choose the visibility of the snapshot generated. By default, visibility is restricted to only those with access to your workspace, but you can choose to share it so that anyone on the internet with the link can view it. |
Notifications | Optionally send a summary notification to a Slack and/or Microsoft Teams webhook. This will contain a link back to the Snapshot. |
Snapshot tags | Add optional tags to the created snapshot. These can be used to easily find snapshots at a later date via the search functionality. |
After scheduling a snapshot, you will be taken to the schedule detail page, which shows you editable details of the schedule, information on its next run and last run status, and a link to the process logs.
When the schedule runs, Pipes will upload the snapshot to your workspace as the system
user rather than attribute the activity to the user creating the schedule.