In addition to the OCI Console, there are many ways to inspect the status of Oracle Cloud resources – including the OCI CLI, Terraform, Grafana OCI Plugin, REST API, OCI App. The open source tool Steampipe (Select * from Cloud) enables SQL queries to inspect many non-SQL data sources and through the OCI plugin for Steampipe, this means you can query the (metadata on) OCI resources like you query EMP and DEPT.
I have written and presented about Steampipe in an earlier article and presentation: Steampipe–analyze data from cloud, file, platform, IaC using SQL queries (may 2022) and SlideShare Steampipe – use SQL to retrieve data from cloud, platforms and files (Conclusion Code Café May 2022).
Apache Superset is an open source end user tool for analyzing and visualizing data. Dozens of chart types are available out of the box, dashboards can be created, finetuned and shared/published. Superset can work with virtually any SQL data source. (I have recently written an introduction to Superset and created a Gitpod workspace for trying it out).
So here we have Superset great at analyzing and visualizing SQL based data, Steampipe great at turning non-SQL data into SQL like tables and the OCI plugin for Steampipe that exposes metadata on Oracle Cloud resources as SQL datasets. In this article I will bring these elements together to form a Dashboard platform for analyzing and visualizing the OCI resources. This looks as shown in this picture:
And I will even provide an ephemeral development environment that you can enter immediately to start exploring this combination (in the form of a Gitpod workspace definition). Click here to start going in a workspace with Superset, Steampipe and the OCI plugin. The Gitpod workspace is visualized as follows:
Getting started
Open the Gitpod workspace with this link: https://gitpod.io/#https://github.com/lucasjellema/gitpod-oci-steampipe-superset
Once the workspace is up and running, the following components will have been installed:
- Apache Superset (six Docker containers – coordinated by Docker Compose including PostgreSQL (metadata store for Superset) and Redis)
- Steampipe (including OCI plugin)
- PostgreSQL
- VS Code
The README.md file is opened when the workspace is launched and this file provides the instructions to get started. This is what you will in your browser when the workspace has started up.
If you click on Open Browser for port 8088, you will jump into Apache Superset.
Username and password are both admin.
Configure OCI – Config file and Private Key
Before you can actually start working with Oracle Cloud Infrastructure, a little configuration is required. The configuration you probably have set up many times.
The files *config* and *oci_api_key.pem* in directory *.oci* need to contain proper connection details for your OCI account. Please edit both files, provide the correct information. Then these two files config and oci_api_key.pem need to be copied to directory *~/.oci*. Please execute these commands in the terminal to perform that copy action:
mkdir ~/.oci
cp .oci/config ~/.oci
cp .oci/oci_api_key.pem ~/.oci
Steampipe
Once the workspace has fully started and Steampipe is available, you can execute batch-mode (non-interactive) queries like this one:
steampipe query “select
name,
id,
is_mfa_activated
from
oci_identity_user;”
In order to look at data from Steampipe in Superset, we first need to add a database connection in Superset to Steampipe’s PostgreSQL database.
Superset – and Connection to Steampipe
Open port 8088 to enter the Superset web UI.
Login with user admin and password admin.
Click on the plus icon. In the dropdown list select *Data*. In the child menu, click on *Connect database*.
The Database Connection configuration wizard appears. Click on the PostgreSQL tile.
To configure the connection to the PostgreSQL database that Steampipe is running on, enter the following values:
- Host: 172.18.0.1
- Port: 9193
- database name: steampipe
- user: steampipe
- password: check in terminal window where steampipe was installed (or run: `steampipe service status –show-password `)
- Display Name: anything you like, for example *Steampipe*
The password created for the *steampipe* user can be seen in the terminal window labeled *Install Steampipe with RSS Plugin*:
Click on the Connect button.
Note: if you get the message that “port is closed” (for port 9193) what may help is restart the Steampipe service:
steampipe service restart
A window appears that indicates that the database has been connected. Press the Finish button.
The New Dataset page appears. Select PostgreSQL | Steampipe as the database and oci as the schema.
A long list of tables is presented that all expose data for specific types of OCI Resources.
You can of course explore any of these tables. One you can try is *oci_identity_compartments*:
With this data set in hand, it is fairly easy to create this chart that shows when I have created compartments in my tenancy:
Treemap on Object Storage Buckets and Contents
Let’s create a new Data Set for the OCI_OBJECTSTORAGE_OBJECT:
Click on the button *Create Data Set and Create Chart*.
Select chart type Tree Map and click on button *Create New Chart*.
Drag the column *bucket_name* to the Dimensions box and the column *size* to the Metrics box. Specify Sum as the aggregation operator.
Then click on Update Chart. This is the result in my case:
I next dragged column *content_type* the Dimensions box and refreshed the chart The treemap has now an additional level of nesting: each bucket is subdivided by *content_type*. Note that the order of the columns in the Dimensions definition is meaningful.
Conclusion
Browsing through the configuration and actual status of OCI resources can be done in Superset in quite a nice way. Intuitive, pleasing to the eye, powerful and read-only (you can share the Superset dashboard without giving away more than read access). I find this combination of Superset and Steampipe with OCI plugin very valuable. And I have only just begun scratching the surface of what it can do. Once I start using SQL Lab in Superset to write queries that involve multiple Steampipe tables to create datasets that join, enrich, filter, compare across compartments, resources and potentially even different cloud environments the options increase even further.
One thing left to be desired perhaps is the support in the OCI plugin for many more OCI resource types. The OCI plugin has 125 tables at the time of writing – which is considerable, But at the same time, many OCI resources are not yet supported. The obvious answer of course is to help developing this plugin. And I have mind to do exactly that. Go to https://github.com/turbot/steampipe-plugin-oci, fork the repository, clone into a Gitpod workspace and start contributing. If I find the time and first: the courage.
Resources
OCI plugin for Steampipe – https://hub.steampipe.io/plugins/turbot/oci
My article introducing the combination of Apache Superset and Steampipe: Analyze and Visualize data from any cloud and anywhere with Apache Superset and Steampipe
My article introducing Apache Superset: https://technology.amis.nl/data-analytics/getting-started-working-with-apache-superset-the-open-source-data-exploration-and-visualization-platform/
I have written and presented about Steampipe in an earlier article and presentation: Steampipe–analyze data from cloud, file, platform, IaC using SQL queries (may 2022) and SlideShare Steampipe – use SQL to retrieve data from cloud, platforms and files (Conclusion Code Café May 2022).
GitHub repo with the Gidpod Workspace definition: https://github.com/lucasjellema/gitpod-oci-steampipe-superset
Link to open a Gitpod workspace based on this definition: https://gitpod.io/#https://github.com/lucasjellema/gitpod-oci-steampipe-superset
