Machine Learning with MySQL HeatWave - Build, Train, Deploy & Explain Machine Learning Models inside MySQL

Machine Learning with MySQL HeatWave
Build, Train, Deploy & Explain Machine Learning Models inside MySQL
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[email protected]
Blogs : www.dasini.net/blog/en
: www.dasini.net/blog/fr
Linkedin: www.linkedin.com/in/olivier-dasini
Twitter : @freshdaz

View Slide

Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
2
Me, Myself & I

MySQL Geek
 Addicted to MySQL for 15+ years
 Playing with databases for 20+ years

MySQL Writer, Blogger and Speaker
 Also: DBA, Consultant, Architect, Trainer, ...

MySQL Cloud Principal Solutions Architect EMEA at Oracle

Stay up to date!
 Blog: www.dasini.net/blog/en
 Linkedin: www.linkedin.com/in/olivier-dasini/
 Twitter: @freshdaz
Olivier DASINI

View Slide

4
MySQL
4
The world’s most popular open source database

View Slide

MySQL powers Open Source applications
Copyright © 2022, Oracle and/or its affiliates
Custom Apps
Development
Content management
and eCommerce
Learning platforms

View Slide

Common MySQL use cases
Social
applications
E-Commerce
Content management
Users profile
management
Digital
payments
Fraud detection
IoT monitoring
systems
Digital
marketing
Online gaming
Retail POS
systems
Employee portals
Authentication
systems
Logistics
applications

View Slide

Innovative organizations across many industries run MySQL
Social E-Commerce Tech Finance Manufacturing

View Slide

8
MySQL HeatWave Database Service
8
100% developed, managed, & supported by the
MySQL team

View Slide

100% developed, managed, and supported by Oracle MySQL
MySQL HeatWave: fully managed database service
Automation MySQL On-Premises MySQL HeatWave
Database
High Availability
Backup
Query Acceleration
Machine Learning
Security Patch & Upgrade
Provision & Configure
OS
OS Security Patch & Upgrade
OS Installation
Server Hardware Purchase & Maintenance
Storage Storage Purchase & Maintenance
Data Center
Rack & Space
Power, HVAC, Networking

View Slide

MySQL HeatWave Database Service - Highlights
10

View Slide

11
MySQL HeatWave
11
In-Memory Query Accelerator

View Slide

MySQL is optimized for OLTP, not designed for analytic processing
12
OLTP Applications
OLAP Applications
OLTP DB
OLAP DB
ETL Service
Separate analytics
database
Complex ETL
No real-time analytics
Security & compliance
risks
Increased costs

View Slide

Why is ETL a Problem?
13
Data Extraction
• Requires complex logic to
extract relevant data
Maps
Routes
Processes
Data Movement
• Requires data
transformation, security
access and loading to new
DB across networks
Multiple DB to Manage
• Need to manage analytic
and MySQL DB separately
Stale Data
• Data recently added or
updated is not available for
analytics
13

View Slide

One database is better than two
14
Analytics tools and
applications
Transactional
applications
OLTP
engine
HeatWave
analytics
cluster
One service for OTLP & OLAP
No ETL duplication
Unmatched performance, at a
fraction of the cost
Real-time analytics
Improved security
Applications work without
changes
1>2 with MySQL HeatWave
Easily run high performance analytics against your MySQL database, no ETL required
Query
Accelerator
InnoDB

View Slide

MySQL HeatWave performance and price comparison
15
30TB TPCH, MySQL HeatWave is faster, cheaper & easier to use than all the competitive database services
See documented performance comparisons that show how HeatWave is 6.5X faster than Amazon Redshift at half the cost, 1400X faster than Amazon Aurora at half the cost, and 5400X faster than Amazon RDS for MySQL at two-thirds the cost
www.oracle.com/mysql/heatwave/performance

View Slide

16
MySQL HeatWave ML
16
Build, train, deploy, & explain machine learning
models within MySQL HeatWave,
at no additional cost

View Slide

Need to ETL data to a separate ML solution for training and inference
And it gets worse when
using other databases...
• Complex, time-consuming
• Increases costs and risks
• Need to learn new tools/languages

View Slide

In-database machine learning with MySQL HeatWave
Accelerate ML initiatives, increase security, and reduce costs

View Slide

HeatWave ML automates the ML lifecycle and all models can be explained
Dataset
Data preprocessing
Algorithm selection
Adaptive sampling
Feature selection
Hyper-parameter tuning
Tuned model
Model explainer
Prediction explainer
Regulatory compliance
Fairness
Repeatability
Causality
Trust
HeatWave ML leverages Oracle AutoML technology to automate the process of training a machine learning model
https://dev.mysql.com/doc/heatwave/en/heatwave-machine-learning.html

View Slide

HeatWave ML vs Redshift ML: Benchmarks 1/3

View Slide


View Slide

 Produces more accurate results
 Trains models 25X faster on average
 1% of the cost
 Scales as more modes are added
See Benchmark details:
https://www.oracle.com/mysql/heatwave/performance/

View Slide

23
MySQL HeatWave
23
Wrap-up

View Slide

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!
MySQL HeatWave - MySQL in-Memory Query Accelerator
The only MySQL service with a massively-scalable, native query accelerator

View Slide

MySQL HeatWave - MySQL in-Memory Query Accelerator
25 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
The only MySQL service with a massively-scalable, native query accelerator
Single MySQL database for OLTP &
Analytics & Machine Learning
All existing applications work without
any changes
Extreme performance: Accelerates
MySQL by orders of magnitude, scales to
thousands of cores
ML-based automation with MySQL
Autopilot
Dramatically faster and lower cost
compared to other cloud services

View Slide

Why MySQL HeatWave for new and existing applications?
Unmatched price
performance
Integrated in-memory query
accelerator
 6.5X faster than Redshift at
half the cost
 7X faster than Snowflake at
20% the cost
 1,400X faster than Aurora
at half the cost
MySQL Autopilot: automation
to achieve high query
performance at scale
Real-time, secure
analytics
Analytics queries always
access the most up-to-date
data
Avoid security risks of moving
data between databases and
ML services
Data always encrypted
Simplicity of one
managed service for
OLTP, OLAP, and ML
No separate analytics
database and machine
learning services
No complex, costly ETL
duplication
MySQL and Amazon
Aurora-based applications
work without changes

View Slide

27
Test Drive MySQL
Database Service For
Free Today
Get $300 in credits
and try MySQL Database Service
free for 30 days.
https://www.oracle.com/cloud/free/

View Slide

28
MySQL HeatWave ML
28
Demo Iris Data Set
Iris Versicolor
https://upload.wikimedia.org/wikipedia/commons/thumb/4/41/Iris_versicolor_3.jpg/1920px-Iris_versicolor_3.jpg

View Slide

• Tutorial: Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin
– https://dasini.net/blog/2022/07/18/iris-data-set-with-mysql-heatwave-machine-learning-zeppelin/
• The data set (dump) is freely available :
– https://github.com/freshdaz/MySQL_HeatWave_ML_Iris/blob/main/MySQL_HeatWave-iris_ML.sql
• Also available as a Zeppelin notebook
– https://github.com/freshdaz/Iris-Data-Set-with-MySQL-HeatWave-Machine-Learning-and-Zeppelin_notebook
• Tutorial: Configure Apache Zeppelin for MySQL HeatWave
– https://dasini.net/blog/2022/07/05/interactively-explore-visualize-your-mysql-heatwave-data-with-apache-zeppelin/
Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin
29

View Slide

Data visualization
30

View Slide

• The schema 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
Schema exploration
31

View Slide

• It’s classification problem
• The ML_TRAIN routine, produces a trained machine learning model
CALL sys.ML_TRAIN('iris_ML.iris_train', 'class', JSON_OBJECT('task', 'classification'), @iris_model);
• Display the current model:
SELECT @iris_model;
• Model information
SELECT model_id, model_handle, model_owner, target_column_name, train_table_name, model_type,
task, model_object_size
FROM ML_SCHEMA_admin.MODEL_CATALOG;
Training a Model
32

View Slide

• The ML_MODEL_LOAD routine loads a model from the model catalog
• A model remains loaded until the model is unloaded using the ML_MODEL_UNLOAD routine or until
HeatWave ML is restarted by a HeatWave Cluster restart
• Examples:
-- A call that specifies a session variable containing the model handle
CALL sys.ML_MODEL_LOAD(@iris_model, NULL);
-- A call with NULL specified, indicating that the model belongs to the user executing the command
CALL sys.ML_MODEL_LOAD('iris_ML.iris_train_admin_1657894319', NULL);
-- A call that specifies the model owner:
CALL sys.ML_MODEL_LOAD('iris_ML.iris_train_admin_1657894319', 'admin');
Load a Model
33

View Slide

• 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
SET @row_input = JSON_OBJECT(
"sepal_length", 7.3,
"sepal_width", 2.9,
"petal_length", 6.3,
"petal_width", 1.8
);
SELECT sys.ML_PREDICT_ROW(@row_input, @iris_model);
Ex: {"Prediction": "Iris-virginica", "petal_width": 1.8, "sepal_width": 2.9, "petal_length": 6.3, "sepal_length": 7.3}
Row(s) Prediction
34

View Slide

• 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
SET @row_input = JSON_OBJECT(
"sepal_length", 7.3,
"sepal_width", 2.9,
"petal_length", 6.3,
"petal_width", 1.8
);
SELECT sys.ML_EXPLAIN_ROW(@row_input, @iris_model);
Ex: {"Prediction": "Iris-virginica", "petal_width": 1.8, "sepal_width": 2.9, "petal_length": 6.3, "sepal_length": 7.3, "petal_width_attribution": 0.2496, "petal_length_attribution": 0.9997}
Row(s) Explanation
35

View Slide

• ML_PREDICT_TABLE generates predictions for an entire table and saves the results to an output table
CALL sys.ML_PREDICT_TABLE('iris_ML.iris_test', @iris_model, 'iris_ML.iris_predictions');
• A new table called iris_predictions is created
• First rows :
SELECT * FROM iris_ML.iris_predictions LIMIT 3;
+--------------+-------------+--------------+-------------+-----------------+
| sepal_length | sepal_width | petal_length | petal_width | Prediction |
+--------------+-------------+--------------+-------------+-----------------+
| 5.9 | 3 | 4.2 | 1.5 | Iris-setosa |
| 6.9 | 3.1 | 5.4 | 2.1 | Iris-virginica |
| 5.1 | 3.3 | 1.7 | 0.5 | Iris-versicolor |
+--------------+-------------+--------------+-------------+-----------------+
Table Prediction
36

View Slide

• ML_EXPLAIN_TABLE explains predictions for an entire table and saves results to an output table
CALL sys.ML_EXPLAIN_TABLE('iris_ML.iris_test', @iris_model, 'iris_ML.iris_explanations');
• A new table called iris_explanations is created
• First rows :
SELECT * FROM iris_ML.iris_explanations LIMIT 3;
+--------------+-------------+--------------+-------------+-----------------+--------------------------+-------------------------+
| sepal_length | sepal_width | petal_length | petal_width | Prediction | petal_length_attribution | petal_width_attribution |
+--------------+-------------+--------------+-------------+-----------------+--------------------------+-------------------------+
| 5.9 | 3 | 4.2 | 1.5 | Iris-setosa | -0.0088 | 0.1793 |
| 6.9 | 3.1 | 5.4 | 2.1 | Iris-virginica | 0.9725 | 0.6482 |
| 5.1 | 3.3 | 1.7 | 0.5 | Iris-versicolor | 0.4917 | 0.3773 |
+--------------+-------------+--------------+-------------+-----------------+--------------------------+-------------------------+
Table Explanation
37

View Slide

• 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:
– https://dev.mysql.com/doc/heatwave/en/hwml-ml-score.html
• ML_SCORE returns a computed metric indicating the quality of the model
Scores
38

View Slide

• This example uses the accuracy score:
– Accuracy computes the fraction of labels a model predicts correctly
CALL sys.ML_SCORE('iris_ML.iris_validate', 'class', @iris_model, 'accuracy', @accuracy_score);
SELECT @accuracy_score;
+--------------------+
| @accuracy_score |
+--------------------+
| 0.9666666388511658 |
+--------------------+
Scores
39

View Slide

MySQL HeatWave ML uses a set of SQL routines
Machine Learning with MySQL HeatWave is so simple
You only need to use a limited set of SQL routines:
●
ML_TRAIN: Trains a machine learning model for a given training dataset
●
ML_PREDICT_ROW: Makes predictions for one or more rows of data
●
ML_PREDICT_TABLE: Makes predictions for a table of data
●
ML_EXPLAIN_ROW: Explains predictions for one or more rows of data
●
ML_EXPLAIN_TABLE: Explains predictions for a table of data
●
ML_SCORE: Computes the quality of a model
40

View Slide

41
Resources

View Slide

42
 Introducing the MySQL Database Service
https://blogs.oracle.com/mysql/introducing-the-mysql-database-service
 Migrate from on premise MySQL to MySQL Database Service
https://blogs.oracle.com/mysql/migrate-from-on-premise-mysql-to-mysql-database-service
 Setup Disaster Recovery for OCI MySQL Database Service
https://lefred.be/content/setup-disaster-recovery-for-oci-mysql-database-service/
 Cost Estimator
https://www.oracle.com/cloud/cost-estimator.html
 OCI Free Trial
https://www.oracle.com/cloud/free/
 Documentations
https://docs.cloud.oracle.com/en-us/iaas/mysql-database/index.html
https://www.oracle.com/mysql/
 Iris Data Set with MySQL HeatWave Machine Learning & Zeppelin
https://dasini.net/blog/2022/07/18/iris-data-set-with-mysql-heatwave-machine-learning-zeppelin/
 Configure Apache Zeppelin for MySQL HeatWave
https://dasini.net/blog/2022/07/05/interactively-explore-visualize-your-mysql-heatwave-data-with-apache-zeppelin/
MySQL HeatWave Database Service Resources

View Slide

43
 Discovering MySQL Database Service – Episode 1 – Introduction
http://dasini.net/blog/2021/08/03/discovering-mysql-database-service-episode-1-introduction/
 Discovering MySQL Database Service – Episode 2 – Create a compartment
http://dasini.net/blog/2021/08/10/discovering-mysql-database-service-episode-2-create-a-compartment/
 Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network
http://dasini.net/blog/2021/08/17/discovering-mysql-database-service-episode-3-create-a-virtual-cloud-network/
 Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket
http://dasini.net/blog/2021/08/24/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket/
 Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump
http://dasini.net/blog/2021/08/31/discovering-mysql-database-service-episode-5-create-a-mysql-db-system-from-a-mysql-shell-dump/
 Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List
http://dasini.net/blog/2021/09/07/discovering-mysql-database-service-episode-6-update-the-private-subnet-security-list/
 Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session
http://dasini.net/blog/2021/09/14/discovering-mysql-database-service-episode-7-use-a-bastion-ssh-port-forwarding-session/
 Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell
http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/
 Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench
http://dasini.net/blog/2021/09/28/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench/
 Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell
http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/
Discovering MySQL HeatWave Database Service
Tutorials where I show you, step by step, how to use MDS and some other OCI services
www.dasini.net/blog/en

View Slide

44
MySQL HeatWave — New Machine Learning Capabilities (2022)
●
What is MySQL HeatWave?
– https://www.youtube.com/watch?v=cTUCzsYAi94
●
MySQL HeatWave Database Service
– https://www.oracle.com/mysql/
●
In-Database Machine Learning with MySQL HeatWave
– https://blogs.oracle.com/mysql/post/in-database-machine-learning-with-mysql-heatwave
●
MySQL HeatWave ML
– https://www.oracle.com/a/ocom/docs/mysql/mysql-heatwave-ml-technical-brief.pdf
●
Estuda.com increases query responses by 300X with MySQL HeatWave
– https://www.youtube.com/watch?v=9cedEkFEKLs
●
VRGlass increases database performance 5X with MySQL HeatWave
– https://www.youtube.com/watch?v=D4z-Ewk9bh8
●
Genius Sonority speeds game analytics by 90X with MySQL HeatWave
– https://www.youtube.com/watch?v=zwmVYq0MsPs
One MySQL Database for OLTP, OLAP, and machine learning (ML)
https://www.oracle.com/fr/events/live/mysql-heatwave-ml/on-demand/

View Slide

45
 Highlights: Announcing MySQL Database Service—New HeatWave Innovations
https://youtu.be/FUbnOTlJ7mI
 Demonstration of MySQL HeatWave Autopilot
https://youtu.be/CyuBxBryJVA
 Tetris.co speeds real-time insights with MySQL HeatWave
https://www.oracle.com/customers/tetris-co/
 Red3i increases insights by 1,000X with MySQL HeatWave
https://www.oracle.com/customers/red3i/
 FANCOMI accelerates ad analytics by 10X with MySQL HeatWave
https://www.oracle.com/customers/fancomi/
 Customer feedack: Tetris.co
https://youtu.be/TrYAluHRXXs
 Customer feedack: Fan Communications
https://youtu.be/SiBHyYPlSNU
MySQL Database Service — New HeatWave Innovations (2021)
https://www.oracle.com/events/live/mysql-heatwave-innovations/

View Slide

46
Follow us on Social Media

View Slide

47
Merci!
Q&R
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[email protected]
Blogs : www.dasini.net/blog/en
: www.dasini.net/blog/fr
Linkedin: www.linkedin.com/in/olivier-dasini
Twitter : @freshdaz

View Slide

View Slide

Machine Learning with MySQL HeatWave - Build, Train, Deploy & Explain Machine Learning Models inside MySQL

Machine Learning with MySQL HeatWave - Build, Train, Deploy & Explain Machine Learning Models inside MySQL

Olivier DASINI

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript