Explore & visualize your MySQL HeatWave data with Superset

June 9, 2022

MySQLThe world’s most popular open source database – is probably the best choice for a transactional database especially when considering the costs / performance ratio, and that on-premise or in the cloud.

But what about business intelligence (BI) needs? Analytics workloads?

We’ve got you covered now with the versatile MySQL HeatWave – a fully managed database service, that combines transactions, analytics, and machine learning services into one MySQL Database, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication.
MySQL HeatWave is a native MySQL solution thus current MySQL applications work without changes.

MySQL HeatWave is also fast, super fast, easy to use and provides an incredible price/performance ratio…

Details & more numbers: Performance comparison of MySQL HeatWave with Snowflake, Amazon Redshift, Amazon Aurora, and Amazon RDS for MySQL.

MySQL HeatWave is currently available on Oracle Cloud Infrastructure (OCI). Availability on Amazon Web Services (AWS) and Microsoft Azure is coming (Oracle’s “HeatWave” is going multicloud: How hot will things get for MySQL?).

Indeed, having a fast and easy to use database is valuable. But analytics also imply that you need to explore and visualize your data.
There are plenty of tools available on the market, one of the them is Apache Superset, an open source modern data exploration and visualization platform.

In this article I will show you how to properly configure Apache Superset in order to take advantage of a high performance, in-memory query accelerator: MySQL HeatWave.

MySQL HeatWave

I’m on OCI and I will not go into all the installation details (RTFM). Below the main steps.

Using your favorite Internet browser, open your OCI console, select a compartment and go to Databases / MySQL / DB Systems.

Create a DB Systems and select HeatWave.

The rest is pretty straightforward!

Please note the Show Advanced Options link at the bottom of the page.

It’s Data Import tab is useful if you want to create an instance with data.

More information in this article: Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump

Now we must add the HeatWave cluster.

On the left, under Resources click HeatWave.

Add the HeatWave Cluster…

Set the Node Count

I would recommend to estimate the node count, click Estimate Node Count then Generate Estimate:

Copy the Load Command.
It will be used to load the chosen data into the HeatWave cluster.

Apply the node count estimate:

Finally create the cluster by pushing the button: Add HeatWave Cluster

When the cluster is running, the very last step is to offload the data to the cluster by running the stored procedure (i.e. the “Load Command”) copied previously using your favorite MySQL client.

e.g. Offload to the HeatWave cluster all the tables inside the airportdb schema:
CALL sys.heatwave_load(JSON_ARRAY(‘airportdb’), NULL);

Apache Superset

I assume that you know what is Apache Superset. If not RTFM.
I also assume that it is already installed. You’ll find the necessary information in their documentation: Installing Superset from Scratch or Installing Superset Locally Using Docker Compose.

it is worth noting that I had some issues during the installation process following the documentation.

For the record, below what I’ve done using a VM Ubuntu 20.04 from Oracle Cloud Infrastructure (OCI).
Please note this is a test installation, not suitable for a production environment.

Install the required dependencies (Ubuntu or Debian):

Upgrade pip (and prevent some issues)

Install Superset

Binaries are in $HOME/.local/bin
I decided to put the following information into my ~/.bashrc (however not sure it is a good practice, but good enough for testing)

then

Initialize the database:

Please not that you will probably have to update your firewall.

If like me you are on OCI you must update the security list (OCI virtual firewall) for the protocol TCP and the port you are going to use, 8088 in this article

On Ubuntu you may have to update IPtable.

Start Superset

Superset is listening on port 8088

If everything worked, you should be able to navigate to hostname:port in your browser and login using the username and password you created.

If you have everything on your laptop, you can remove -h 0.0.0.0 and then only local connections will be allowed (i.e. navigate to localhost:8088).

Install MySQL Connector Python 8.0

Unlike what is written here, I would recommend to use MySQL 8.0, to download and use the official MySQL Python connector available on the MySQL website.

Go to https://dev.mysql.com/downloads/connector/python/ for the latest version.

Obviously, MySQL customers can use the commercial version available on MOS.

Configure Superset for MySQL HeatWave

Using the Apache Superset UI you can add a new backend database.

Go to Data / Databases

Then click + DATABASE

Add your MySQL HeatWave database.
There are 2 important parts:

1/ SQLALCHEMY URI :

mysql+mysqlconnector://<MySQL user>:<MySQL password>@<MySQL host>

You need the MySQL user, password and IP.

2/ ENGINE PARAMETERS

In order to take advantage of the power of MySQL HeatWave, autocommit must be enabled.

If autocommit is disabled, queries are not offloaded and execution is performed on the MySQL DB System. In other words, the queries will be much slower. Details here.

Go to Advanced / Other / ENGINE PARAMETERS

{“isolation_level”:”AUTOCOMMIT”}

You all set 🙂

Thanks to MySQL HeatWave, you can now add your datasets and build awesome and very fast dashboards, explore and visualize your data at the speed of light.

Resources

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Leave a Reply