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

Machine Learning with MySQL HeatWave

HeatWave ML includes everything users need to build, train, deploy, and explain machine learning models within MySQL HeatWave, at no additional cost.
In this immersive webinar, you will learn:
What is needed to start using ML with MySQL
How HeatWave ML works
What ML algorithms can be used with HeatWave ML
How to configure ML models with your data
How to explain the results provided by HeatWave ML

Take this opportunity to ask your questions directly to the MySQL team!

Transcript

1. Machine Learning with MySQL HeatWave Build, Train, Deploy & Explain Machine Learning Models inside MySQL Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz
2. 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
3. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 4 MySQL 4 The world’s most popular open source database
4. MySQL powers Open Source applications Copyright © 2022, Oracle and/or its affiliates Custom Apps Development Content management and eCommerce Learning platforms
5. 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
6. Innovative organizations across many industries run MySQL Copyright © 2022, Oracle and/or its affiliates Social E-Commerce Tech Finance Manufacturing
7. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 8 MySQL HeatWave Database Service 8 100% developed, managed, & supported by the MySQL team
8. 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
9. MySQL HeatWave Database Service - Highlights Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10
10. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 11 MySQL HeatWave 11 In-Memory Query Accelerator
11. 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
12. 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
13. 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
14. 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
15. 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
16. 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
17. Copyright © 2022, Oracle and/or its affiliates In-database machine learning with MySQL HeatWave Accelerate ML initiatives, increase security, and reduce costs
18. 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
19. Copyright © 2022, Oracle and/or its affiliates HeatWave ML vs Redshift ML: Benchmarks 1/3
20. Copyright © 2022, Oracle and/or its affiliates HeatWave ML vs Redshift ML: Benchmarks 2/3
21.  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/
22. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 23 MySQL HeatWave 23 Wrap-up
23. 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
24. 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
25. 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
26. 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/
27. 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
28. • 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
29. Data visualization Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 30
30. • 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
31. • 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
32. • 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
33. • 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
34. • 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
35. • 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
36. • 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
37. • 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
38. • 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
39. 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
40. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41 Resources
41. 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
42. 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
43. 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/
44. 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/
45. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 46 Follow us on Social Media
46. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 47 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz