{"id":5915,"date":"2022-07-05T08:44:08","date_gmt":"2022-07-05T07:44:08","guid":{"rendered":"https:\/\/dasini.net\/blog\/?p=5915"},"modified":"2025-07-11T09:13:12","modified_gmt":"2025-07-11T08:13:12","slug":"interactively-explore-visualize-your-mysql-heatwave-data-with-apache-zeppelin","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2022\/07\/05\/interactively-explore-visualize-your-mysql-heatwave-data-with-apache-zeppelin\/","title":{"rendered":"Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin"},"content":{"rendered":"\n

MySQL<\/strong> – The world’s most popular open source database<\/em> – is a very good and safe choice as a transactional database<\/strong>, on-premise<\/strong> and in the cloud<\/strong>.<\/p>\n\n\n\n

In the article – <\/a>Explore & visualize your MySQL HeatWave data with Superset<\/strong><\/a> – I introduced MySQL HeatWave<\/strong><\/a> – a fully managed database service<\/strong>, that combines transactions<\/strong>, analytics<\/strong>, and machine learning<\/strong> 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

Enabling a HeatWave Cluster also provides access to HeatWave Machine Learning<\/span><\/strong> (ML<\/strong>), which is a fully managed, highly scalable, cost-efficient, machine learning solution for data stored in MySQL.
HeatWave ML provides a simple SQL interface for training and using predictive machine learning models, which can be used by novice and experienced ML practitioners alike.
Machine learning expertise, specialized tools, and algorithms are not required.
With HeatWave ML, you can train a model with a single call to an SQL routine. <\/p>\n\n\n\n

Below some numbers comparing MySQL HeatWave ML<\/a> vs AWS Redshift ML<\/strong>:<\/p>\n\n\n

\n
\"MySQL
MySQL HeatWave ML is 25x faster than Redshift ML<\/strong><\/span><\/figcaption><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n
\n
\"MySQL
MySQL HeatWave ML is 1% of cost of Redshift ML<\/strong><\/span><\/figcaption><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n
\n
\"Better
Better performance with HeatWave ML with larger cluster size<\/strong><\/span><\/figcaption><\/figure>\n<\/div>\n\n\n
<\/div>\n\n\n\n

For more details & raw numbers read: Performance comparison of HeatWave ML with Redshift ML<\/a>.<\/p>\n\n\n\n

<\/div>\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

MySQL HeatWave ML can also takes models tuned in MySQL and use them to answer queries from popular machine learning notebook services for code and data, such as Jupyter<\/a> and Apache Zeppelin<\/a>.<\/p>\n\n\n\n

Apache Zeppelin<\/strong> is an open source web-based notebook that allows data ingestion, data discovery, data analytics, data visualization and data collaboration.
Apache Zeppelin supports many interpreters such as MySQL<\/a>, Python, JDBC, Shell, Spark, \u2026
More: https:\/\/zeppelin.apache.org\/<\/a><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
<\/div>\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 Zeppelin<\/strong> in order to take advantage of the Analytics and Machine Learning capabilities<\/strong> of MySQL HeatWave<\/strong><\/a> the MySQL in-memory query accelerator.<\/p>\n\n\n\n

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

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

I’m using 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

Now you have a MySQL HeatWave cluster up and running with some data loaded into the cluster;
Cheers!<\/p>\n\n\n\n

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

Apache Zeppelin<\/h1>\n\n\n\n

I assume that you already know what is Apache Zeppelin. If not RTFM<\/a>.<\/p>\n\n\n\n

Please note this is a test installation, probably<\/em> not suitable for a production environment.<\/p>\n\n\n\n

Requirements<\/h2>\n\n\n\n

Apache Zeppelin officially supports and is tested, among other, on Ubuntu 20.04, so I\u2019ve installed Zeppelin on a VM<\/a> Ubuntu 20.04 from Oracle Cloud Infrastructure<\/a> (OCI).<\/p>\n\n\n\n

In addition to the OS, you also need the good version of OpenJDK or Oracle JDK.<\/p>\n\n\n\n

$ lsb_release -a\nNo LSB modules are available.\nDistributor ID:\tUbuntu\nDescription:\tUbuntu 20.04.4 LTS\nRelease:\t20.04\nCodename:\tfocal<\/code><\/pre>\n\n\n\n
$ java -version\nopenjdk version \"17.0.3\" 2022-04-19\nOpenJDK Runtime Environment (build 17.0.3+7-Ubuntu-0ubuntu0.20.04.1)\nOpenJDK 64-Bit Server VM (build 17.0.3+7-Ubuntu-0ubuntu0.20.04.1, mixed mode, sharing)<\/code><\/pre>\n\n\n\n
I’m good to go!<\/p>\n\n\n\n
<\/div>\n\n\n\n
Binary<\/h2>\n\n\n\n
I downloaded the binary package with all interpreters<\/strong>. Available here<\/a>.<\/p>\n\n\n\n
wget https:\/\/dlcdn.apache.org\/zeppelin\/zeppelin-0.10.1\/zeppelin-0.10.1-bin-all.tgz<\/code><\/pre>\n\n\n\n
Unpack it in a directory of your choice and you’re ready to go!<\/p>\n\n\n\n
tar xvf zeppelin-0.10.1-bin-all.tgz\nmv zeppelin-0.10.1-bin-all\/ zeppelin\ncd zeppelin<\/code><\/pre>\n\n\n\n
<\/div>\n\n\n\n
Firewalls<\/h2>\n\n\n\n
Depending on your architecture, you may have to deal with some firewalls.<\/p>\n\n\n\n
In OCI<\/a> I must update my Ingress Rules<\/em> for the Default Security List<\/a><\/em> (OCI virtual firewall) for the protocol TCP and the port you are going to use, 8080 in this article:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/div>\n\n\n\n
\n
On the Ubuntu side I also had to update IPtable<\/a><\/strong>.<\/p>\n<\/blockquote>\n\n\n\n
<\/div>\n\n\n\n
Starting Apache Zeppelin<\/h2>\n\n\n\n
By default Zeppelin is listening at 127.0.0.1:8080<\/strong>, so you can’t access it when it is deployed on another remote machine.
To access a remote Zeppelin, you need to change zeppelin.server.addr<\/em> to 0.0.0.0 in conf\/zeppelin-site.xml<\/em>.<\/p>\n\n\n\n
~\/zeppelin$ cp conf\/zeppelin-site.xml.template conf\/zeppelin-site.xml\n\n~\/zeppelin$ vim conf\/zeppelin-site.xml\n...<\/code><\/pre>\n\n\n\n
When the update is done:<\/p>\n\n\n\n
~\/zeppelin$ grep -m2  -a4  \"<property>\" conf\/zeppelin-site.xml\n-->\n\n<configuration>\n\n<property>\n  <name>zeppelin.server.addr<\/name>\n  <value>0.0.0.0<\/value>\n  <description>Server binding address<\/description>\n<\/property>\n\n<property>\n  <name>zeppelin.server.port<\/name>\n  <value>8080<\/value>\n  <description>Server port.<\/description>\n<\/property><\/code><\/pre>\n\n\n\n
Address is 0.0.0.0 and Port is 8080.<\/p>\n\n\n\n
Now I can start Zeppelin:<\/p>\n\n\n\n
~\/zeppelin$ bin\/zeppelin-daemon.sh start<\/code><\/pre>\n\n\n\n
~\/zeppelin$ bin\/zeppelin-daemon.sh status\nZeppelin is running                                        [  OK  ]<\/code><\/pre>\n\n\n\n
Yippee!<\/p>\n\n\n\n
<\/div>\n\n\n\n
MySQL Connector J<\/h2>\n\n\n\n
The Zeppelin SQL interpreter needs the JDBC MySQL Connector<\/a>, available at https:\/\/dev.mysql.com\/downloads\/connector\/j\/<\/a><\/p>\n\n\n\n
Obviously, MySQL customers can use the commercial version available on MOS<\/a>.<\/p>\n\n\n\n
$ wget https:\/\/dev.mysql.com\/get\/Downloads\/Connector-J\/mysql-connector-java_8.0.29-1ubuntu20.04_all.deb\n\n$ sudo dpkg -i mysql-connector-java_8.0.29-1ubuntu20.04_all.deb<\/code><\/pre>\n\n\n\n
 The JDBC MySQL Connector is now installed!<\/p>\n\n\n\n
<\/div>\n\n\n\n
Setup the Zeppelin interpreter<\/h2>\n\n\n\n
<\/div>\n\n\n\n
\n