Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin

July 18, 2022
Tags: , ,

MySQL – The world’s most popular open source database – is also probably the most popular database in the Cloud.
MySQL HeatWave database service is THE MySQL PaaS, a fully managed cloud service in Oracle Cloud Infrastructure (OCI).

The only MySQL service 100% developed, managed and supported by the MySQL Team.

In short:

MySQL HeatWave is a massively parallel, high performance, in-memory query accelerator that accelerates MySQL performance by orders of magnitude for analytics workloads, mixed workloads, and Machine Learning.

MySQL HeatWave Architecture

For your analytics needs you can explore and visualize your data using a data visualization tool like Tableau, Qlik, Oracle Analytics Cloud, Apache Superset (see: Explore & visualize your MySQL HeatWave data with Superset)…

For your Machine Learning needs you can use a notebook like Jupyter, Apache Zeppelin (see: Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin),…

In this article I will show you how easy it is to use the Machine Learning capabilities of MySQL HeatWave. We will use the famous Iris dataset and we’re going to build, train, deploy and explain a machine learning model within MySQL HeatWave using Apache Zeppelin.


To be able to reproduce this tutorial, you must have a MySQL HeatWave instance running (details here and also RTFM).
The data used in this article are available on my GitHub account here.

You need a MySQL client to load the data into MySQL HeatWave. I’m using MySQL Shell.
Furthermore if you want to use Zeppelin (although optional because we’re going to run mainly sql queries) , it must be properly setup.
Please read: Interactively explore & visualize your MySQL HeatWave data with Apache Zeppelin
You can also download the Zeppelin notebook here.

Finally, you might need to install some packages.
ex: (pip install pandas numpy unlzw3 sklearn seaborn matplotlib jupyter grpcio protobuf)

MySQL HeatWave ML

MySQL HeatWave ML makes it easy to use machine learning, whether you are a novice user or an experienced ML practitioner

You provide the data, and HeatWave ML analyzes the characteristics of the data and creates an optimized machine learning model that you can use to generate predictions and explanations. 

HeatWave ML supports supervised machine learning. That is, it creates a machine learning model by analyzing a labeled dataset to learn patterns that enable it to predict labels based on the features of the dataset. HeatWave ML supports both classification and regression models. 
More information here.

In-database Machine Learning with MySQL HeatWave
In-database Machine Learning with MySQL HeatWave

All HeatWave ML operations are initiated by running CALL or SELECT statements, which can be easily integrated into your applications.

HeatWave ML routines include:

Load the data

In a real life scenario, your data are already inside your MySQL instance. Although you might need to prepare them upfront, because the relevant data set must be inside a single table.

I’m using MySQL Shell (installed on the server that host Zeppelin) to load the data into the iris_ML schema. The dump file – MySQL_HeatWave-iris_ML.sql – was previously uploaded to the server into /home/ubuntu.

The dump contains 4 tables:

  • iris : the reference table ie source of truth, production data
  • iris_test : contains the test dataset
  • iris_train : contains the training dataset
  • iris_validate : contains the validation dataset

Check HeatWave ML status

HeatWave ML is enable by default as soon as your MySQL HeatWave Cluster is active.

The rapid_ml_status variable provides the status of HeatWave ML:

HeatWave ML is up and running \o/

Data exploration

The first step of any project based on data is to look at the data.
Summarize and visualize the data will help you to have a better understanding of the project.

It may surprise you but SQL provides some commands and functions for data exploration, although much less extensive than R or Python 🙂 :

iris simulate the live production table. It contains the original data (150 rows) and actually it will not be used (as is) by HeatWave ML.
To keep things simple as possible in this article, iris as the same structure than iris_train. But in a real life scenario it will most likely not the case.
This production table may have additional columns like for example: a primary key, timestamp, etc… any useful business related information that are not relevant for building our model.

iris_train contains the training dataset a subset of iris table (120 rows), used to train the machine learning model.
This table is typically a subset of the reference table(s).

iris_test contains the test dataset a subset of iris table (30 rows), different than iris_train. Its structure is almost similar to iris_train but without the target column (class) .

iris_validate contains the validation dataset (30 rows). Same data than iris_test but same structure than iris_train in other words this table has the target column (class).

General requirements for HeatWave ML here.

Class distribution is well balanced:

  • Iris-virginica: 50 rows
  • Iris-setosa: 50 rows
  • Iris-versicolor: 50 rows

Data visualization

Visualize your data is probably the more convenient way to explore and understand them.

Below a little Python script to generate some graphs.

Beforehand, I had to edit the Zeppelin python interpreter – zeppelin.python – and replace python by python3:

Update zeppelin.python:  replace python by python3
zeppelin.python: python3:

The interpreter is: %python.ipython
The script is going to connect to MySQL HeatWave, thus you must update the database information (host, database, user, password)

HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.

Dans ce webinaire vous apprendrez

The information above come from the Iris table (reference table). It could interesting to compare these data with the training dataset (iris_train) and the test dataset in order to evaluate the quality of these samples.

Replace query = “SELECT * FROM iris;” by query = “SELECT * FROM iris_train;” and then query = “SELECT * FROM iris_test;”

Training a Model

Now we have a better understanding of the data, let’s moving forward and train the model. We’re having a classification problem.

The ML_TRAIN routine, when run on a training dataset, produces a trained machine learning (ML) model.

You can show the current model, selecting the session variable @iris_model:

The lifetime duration of a session variable is… the session lifetime duration. So when the session is closed the session variable content is lost.

ML_TRAIN stores the machine learning model – @iris_model – in the MODEL_CATALOG table:

You can take the last created model using the following query:

When the model is created (using ML_TRAIN) you should load it into HeatWave ML (ML_MODEL_LOAD):

Row Prediction

HeatWave ML allows you to make prediction for individual rows or the entire table.

Row(s) predictions are generated by running ML_PREDICT_ROW.
Data are specified in JSON format.

Row Explanation

Being able to understand and explain a prediction is important in order to trust your model and to be able to explain the results. It might also be required by your local regulation.

ML_EXPLAIN_ROW generates explanations for one or more rows of data. Explanations help you understand which features have the most influence on a prediction.
Feature importance is presented as a value ranging from -1 to 1.

  • A positive value indicates that a feature contributed toward the prediction.
  • A negative value indicates that the feature contributed toward a different prediction

Table Prediction

ML_PREDICT_TABLE generates predictions for an entire table and saves the results to an output table.

A new table called iris_predictions is created. You can display Its first 5 rows with:

Table Explanation

ML_EXPLAIN_TABLE explains predictions for an entire table and saves results to an output table.

A new table called iris_explanations is created. You can display Its first 5 rows with:


Scoring the model allows to assess the model’s reliability.

Models with a low score can be expected to perform poorly, producing predictions and explanations that cannot be relied upon. A low score typically indicates that the provided feature columns are not a good predictor of the target values.

HeatWave ML supports a variety of scoring metrics to help you understand how your model performs across a series of benchmarks. 
Details here.

ML_SCORE returns a computed metric indicating the quality of the model.

Other metrics are also available. See here.

Here we go!
We’ve seen the machine learning life cycle.

Machine Learning Life Cycle

I told you that Machine Learning with MySQL HeatWave was easy 🙂

You only need to use a limited set of SQL routines:

During the last 20 years MySQL has democratized the usage of transactional databases.
Now with MySQL HeatWave we are in the process of democratizing Analytics and Machine Learning.

With MySQL HeatWave,
valorizing your data has never been so easy!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Iris Versicolor
Iris Versicolor

One Response to “Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin”

  1. […] Lors de cette présentation, je vais m’appuyer sur cet article: Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin […]

Leave a Reply