Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 4 MySQL 4 The world’s most popular open source database

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Common MySQL use cases Copyright © 2022, Oracle and/or its affiliates 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

Slide 6

Slide 6 text

Innovative organizations across many industries run MySQL Copyright © 2022, Oracle and/or its affiliates Social E-Commerce Tech Finance Manufacturing

Slide 7

Slide 7 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 8 MySQL HeatWave Database Service 8 100% developed, managed, & supported by the MySQL team

Slide 8

Slide 8 text

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 Copyright © 2022, Oracle and/or its affiliates

Slide 9

Slide 9 text

MySQL HeatWave Database Service - Highlights Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10

Slide 10

Slide 10 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 11 MySQL HeatWave 11 In-Memory Query Accelerator

Slide 11

Slide 11 text

MySQL is optimized for OLTP, not designed for analytic processing Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 12

Slide 12 text

Why is ETL a Problem? Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 13

Slide 13 text

One database is better than two Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 14

Slide 14 text

MySQL HeatWave performance and price comparison Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 15

Slide 15 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 16 MySQL HeatWave ML 16 Build, train, deploy, & explain machine learning models within MySQL HeatWave, at no additional cost

Slide 16

Slide 16 text

Copyright © 2022, Oracle and/or its affiliates 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

Slide 17

Slide 17 text

Copyright © 2022, Oracle and/or its affiliates In-database machine learning with MySQL HeatWave Accelerate ML initiatives, increase security, and reduce costs

Slide 18

Slide 18 text

HeatWave ML automates the ML lifecycle and all models can be explained Copyright © 2022, Oracle and/or its affiliates 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

Slide 19

Slide 19 text

Copyright © 2022, Oracle and/or its affiliates HeatWave ML vs Redshift ML: Benchmarks 1/3

Slide 20

Slide 20 text

Copyright © 2022, Oracle and/or its affiliates HeatWave ML vs Redshift ML: Benchmarks 2/3

Slide 21

Slide 21 text

 Produces more accurate results  Trains models 25X faster on average  1% of the cost  Scales as more modes are added HeatWave ML vs Redshift ML: Benchmarks 3/3 Copyright © 2022, Oracle and/or its affiliates See Benchmark details: https://www.oracle.com/mysql/heatwave/performance/

Slide 22

Slide 22 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 23 MySQL HeatWave 23 Wrap-up

Slide 23

Slide 23 text

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! Copyright © 2022, Oracle and/or its affiliates MySQL HeatWave - MySQL in-Memory Query Accelerator The only MySQL service with a massively-scalable, native query accelerator

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Why MySQL HeatWave for new and existing applications? Copyright © 2022, Oracle and/or its affiliates 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

Slide 26

Slide 26 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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/

Slide 27

Slide 27 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 28

Slide 28 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 29

Slide 29

Slide 29 text

Data visualization Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 30

Slide 30

Slide 30 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 31

Slide 31

Slide 31 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 32

Slide 32

Slide 32 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 33

Slide 33

Slide 33 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 34

Slide 34

Slide 34 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 35

Slide 35

Slide 35 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 36

Slide 36

Slide 36 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 37

Slide 37

Slide 37 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 38

Slide 38

Slide 38 text

• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 39

Slide 39

Slide 39 text

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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 40

Slide 40

Slide 40 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41 Resources

Slide 41

Slide 41 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 42

Slide 42 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 43

Slide 43 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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/

Slide 44

Slide 44 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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/

Slide 45

Slide 45 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 46 Follow us on Social Media

Slide 46

Slide 46 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 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

Slide 47

Slide 47 text

No content