Connect to Turbot Pipes from Apache Superset
Apache Superset is a data exploration and visualization platform that connects to many databases, including Postgres, and enables users to explore, query, and visualize data.
Steampipe provides a single interface to all your cloud, code, logs and more. Because it's built on Postgres, Steampipe provides an endpoint that any Postgres-compatible client -- including Apache Superset -- can connect to.
You can get the information needed to connect to your Turbot Pipes database instance from the Query tab for your workspace. On the Query tab, click the info button at the top of the query window to show the connection information.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fquery-info-connect.png&w=3840&q=75)
Connect to Steampipe CLI from Apache Superset
You can also connect Apache Superset to
Steampipe CLI. To do that, run
steampipe service start --show-password
and use the displayed connection
details.
Steampipe service is running:
Database:
Host(s): localhost, 127.0.0.1, 192.168.29.204 Port: 9193 Database: steampipe User: steampipe Password: 99**_****_**8c Connection string: postgres://steampipe:99**_****_**8c@localhost:9193/steampipe
Getting started
Apache Superset is a free to use open-source application that runs in a container, or on the desktop. Here we run it locally using docker compose.
With Apache Superset up and running, open a browser to http://localhost:8080/
.
Click on Database Connections
under settings on the top right corner, then
click +Database
and select PostgreSQL
. Enter the connection details, toggle
the SSL mode to Require
and click connect.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-connection-success.png&w=3840&q=75)
Alternatively, you can connect to the database using the SQLALCHEMY URI
option
and use the connection string.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-uri-connection.png&w=3840&q=75)
Now add a Dataset
which is a representation of a table you want exposed for
querying. To do that click on +Dataset
from the Datasets tab and update the
database, schema and table details. Here we add a dataset using the
azure_compute_disk
table to list the Azure compute disks.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-dataset-creation.png&w=3840&q=75)
To create the table, select the azure_compute_disk
dataset, set the
QUERY MODE
to RAW RECORDS
, update the columns to be displayed and click
CREATE CHART
. Superset will then preview the data.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-disk-preview.png&w=3840&q=75)
Create charts with custom queries.
You can augment your SQL queries using Superset's SQL Lab
(under the SQL tab)
and visualize them with a suite of charts. To try that, click SQL Lab
and
paste this SQL that returns the number of compute disks per region.
select region, count(name)from azure_compute_diskgroup by region;
Once the result is returned, click CREATE CHART
and select PIE CHART
. Now
update the region and count columns into the dimensions field, set the metric
field to aggregate MAX
count and click UPDATE CHART
. Custom queries can also
be saved as datasets.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-custom-query.png&w=3840&q=75)
Create a dashboard to analyze resources
Charts that display data are the building blocks of Apache Superset dashboards.
We'll focus here on a creating a dashboard that monitors the Azure VMs and
disks. To begin, click +Dashboard
from the Dashboards tab, and click
+Create a New Chart
. Then create datasets and charts with
azure_compute_virtual_machine_metric_cpu_utilization_hourly
and
azure_compute_disk_metric_read_ops_daily
tables. Finally paste these queries
to create dataset charts for VMs with disks not encrypted by customer-managed
key, and for unattached disks.
select count(disk.encryption_type)from azure_compute_disk as disk join azure_compute_virtual_machine as vm on disk.name = vm.os_disk_namewhere not disk.encryption_type = 'EncryptionAtRestWithCustomerKey'
select name, disk_state, sku_tier, time_created, encryption_type, network_access_policyfrom azure_compute_diskwhere disk_state = 'Unattached';
Superset provides a variety of chart types. Here we use Bar Chart
for
azure_compute_virtual_machine_metric_cpu_utilization_hourly, Funnel Chart
for
azure_compute_disk_metric_read_ops_daily, Table
for list of unattached disks
and Gauge Chart
for VMs with disks not encrypted by customer managed key. The
dashboard can also be saved as an image by simply clicking on
Download as image
from the dashboard menu.
![](/_next/image?url=%2Fimages%2Fdocs%2Fpipes%2Fapache-superset-dashboard-preview.png&w=3840&q=75)
Summary
With Apache Superset and Turbot Pipes you can:
View tables in your Turbot Pipes workspace
Write custom queries for those tables
Create interactive dashboards driven by your tables and queries