{"id":5762,"date":"2022-06-09T10:39:01","date_gmt":"2022-06-09T09:39:01","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=5762"},"modified":"2025-07-11T09:09:18","modified_gmt":"2025-07-11T08:09:18","slug":"explore-visualize-your-mysql-heatwave-data-with-superset","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2022\/06\/09\/explore-visualize-your-mysql-heatwave-data-with-superset\/","title":{"rendered":"Explore & visualize your MySQL HeatWave data with Superset"},"content":{"rendered":"\n

MySQL<\/strong> – The world’s most popular open source database<\/em> – is probably the best choice for a transactional database<\/strong> especially when considering the costs \/ performance ratio<\/strong>, and that on-premise<\/strong> or in the cloud<\/strong>.<\/p>\n\n\n\n

But what about business intelligence<\/span> (BI) needs? Analytics<\/span> workloads? <\/p>\n\n\n\n

We’ve got you covered now with the versatile MySQL HeatWave<\/a> <\/strong>– 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<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

MySQL HeatWave is also fast, super fast, easy to use and provides an incredible price\/performance ratio…<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Details & more numbers<\/span>: Performance comparison of MySQL HeatWave with Snowflake, Amazon Redshift, Amazon Aurora, and Amazon RDS for MySQL<\/a>.<\/p>\n\n\n\n

MySQL HeatWave is currently available on Oracle Cloud Infrastructure<\/a> (OCI). Availability on Amazon Web Services (AWS) and Microsoft Azure is coming (Oracle\u2019s \u201cHeatWave\u201d is going multicloud: How hot will things get for MySQL?<\/a>).<\/p>\n\n\n\n

<\/div>\n\n\n\n

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<\/a><\/strong>, an open source modern data exploration and visualization platform.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

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

<\/div>\n\n\n\n

MySQL HeatWave<\/h1>\n\n\n\n

I’m on OCI<\/a> and I will not go into all the installation details (RTFM<\/a>). Below the main steps.<\/p>\n\n\n\n

Using your favorite Internet browser, open your OCI console, select a compartment and go to Databases \/ MySQL \/ DB System<\/em>s.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Create a DB Systems and select HeatWave<\/strong>.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
<\/div>\n\n\n\n

The rest is pretty straightforward!<\/p>\n\n\n\n

Please note the Show Advanced Options<\/strong><\/em> link at the bottom of the page. <\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
<\/div>\n\n\n\n

It’s Data Import<\/strong><\/em> tab is useful if you want to create an instance with data. <\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
<\/div>\n\n\n\n

More information in this article: Discovering MySQL Database Service \u2013 Episode 5 \u2013 Create a MySQL DB system from a MySQL Shell dump<\/em><\/strong><\/a><\/p>\n\n\n\n

<\/div>\n\n\n\n

Now we must add the HeatWave cluster.<\/p>\n\n\n\n

On the left, under Resources<\/em><\/strong> click HeatWave<\/em><\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Add the HeatWave Cluster…<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Set the Node Count <\/p>\n\n\n\n

\n
\"\"<\/figure>\n<\/figure>\n\n\n\n
<\/div>\n\n\n\n

I would recommend to estimate the node count, click Estimate Node Count<\/em><\/strong> then Generate Estimate<\/em><\/strong>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Copy the Load Command<\/em><\/strong>.
It will be used to load the chosen data into the HeatWave cluster.<\/p>\n\n\n\n

<\/div>\n\n\n\n

Apply the node count estimate:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Finally create the cluster by pushing the button: Add HeatWave Cluster<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

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 \u00ab\u00a0Load Command\u00a0\u00bb) copied previously using your favorite MySQL client. <\/p>\n\n\n\n

e.g. Offload to the HeatWave cluster all the tables inside the airportdb schema:
CALL sys.heatwave_load(JSON_ARRAY(‘airportdb’), NULL);<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n

Apache Superset<\/h1>\n\n\n\n

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

it is worth noting that I had some issues during the installation process following the documentation.<\/p>\n\n\n\n

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

<\/div>\n\n\n\n

Install the required dependencies (Ubuntu or Debian):<\/p>\n\n\n\n

$ sudo apt update && sudo apt -y install build-essential libssl-dev libffi-dev python3-dev python3-pip libsasl2-dev libldap2-dev default-libmysqlclient-dev libprotobuf17 python3-protobuf<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Upgrade pip (and prevent some issues)<\/p>\n\n\n\n
$ pip3 install --upgrade pip\n\n$ pip3 install zipp==3.8.0\n\n$ pip3 install Flask-WTF==0.14.3<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Install  Superset<\/p>\n\n\n\n
$ pip3 install apache-superset<\/code><\/pre>\n\n\n\n
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)<\/p>\n\n\n\n
export PATH=\"$HOME\/.local\/bin:$PATH\"\nexport FLASK_APP=superset<\/code><\/pre>\n\n\n\n
then<\/p>\n\n\n\n
$ source ~\/.bashrc<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Initialize the database:<\/p>\n\n\n\n
$ superset db upgrade\n\n$ superset fab create-admin\n\n\n# If you want to load the embeded examples\n# superset load_examples\n\n# Create default roles and permissions\n$ superset init<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Please not that you will probably have to update your firewall.<\/p>\n\n\n\n
If like me you are on OCI<\/a> you must update the security list<\/a> (OCI virtual firewall) for the protocol TCP and the port you are going to use, 8088 in this article<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
\n
On Ubuntu you may have to update IPtable.<\/p>\n<\/blockquote>\n\n\n\n
<\/div>\n\n\n\n
Start Superset<\/h2>\n\n\n\n
Superset is listening on port 8088<\/p>\n\n\n\n
$ superset run -h 0.0.0.0 -p 8088 --with-threads --reload<\/code><\/pre>\n\n\n\n
If everything worked, you should be able to navigate to hostname:port<\/code> in your browser  and login using the username and password you created.<\/p>\n\n\n\n
If you have everything on your laptop, you can remove -h 0.0.0.0<\/em><\/code>  and then only local connections will be allowed (i.e. navigate to localhost:8088<\/code>).<\/p>\n\n\n\n
<\/div>\n\n\n\n
Install MySQL Connector Python 8.0<\/h1>\n\n\n\n
Unlike what is written here<\/a>, I would recommend to use MySQL 8.0, to download and use the official MySQL Python connector<\/a> available on the MySQL<\/a> website.<\/p>\n\n\n\n
Go to https:\/\/dev.mysql.com\/downloads\/connector\/python\/<\/a> for the latest version.<\/p>\n\n\n\n
$ wget https:\/\/dev.mysql.com\/get\/Downloads\/Connector-Python\/mysql-connector-python-py3_8.0.29-1ubuntu20.04_amd64.deb\n\n$ sudo dpkg -i mysql-connector-python-py3_8.0.29-1ubuntu20.04_amd64.deb<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Obviously, MySQL customers can use the commercial version available on MOS<\/a>.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Configure Superset for MySQL HeatWave<\/h1>\n\n\n\n
Using the Apache Superset UI you can add a new backend database.<\/p>\n\n\n\n
Go to Data \/ Databases<\/em><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
Then click + DATABASE<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
Add your MySQL HeatWave database.
There are 2 important parts:<\/p>\n\n\n\n
1\/ SQLALCHEMY URI :<\/h3>\n\n\n\n
mysql+mysqlconnector:\/\/<MySQL user>:<MySQL password>@<MySQL host><\/strong><\/p>\n\n\n\n
<\/div>\n\n\n\n
You need the MySQL user, password and IP.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
2\/ ENGINE PARAMETERS<\/h3>\n\n\n\n
In order to take advantage of the power of MySQL HeatWave, autocommit<\/strong><\/em><\/code><\/a> must be enabled. <\/p>\n\n\n\n
If autocommit<\/strong><\/em> 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<\/a>.<\/p>\n\n\n\n
Go to Advanced \/ Other \/ ENGINE PARAMETERS<\/em><\/p>\n\n\n\n
{\u00ab\u00a0isolation_level\u00a0\u00bb:\u00a0\u00bbAUTOCOMMIT\u00a0\u00bb}<\/strong><\/p>\n\n\n\n
<\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
You all set \ud83d\ude42<\/p>\n\n\n\n
Thanks to MySQL HeatWave<\/strong>, you can now add your datasets and build awesome and very fast dashboards, explore and visualize your data at the speed of light.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Resources<\/h2>\n\n\n\n