HeatWave Release Notes
This quickstart illustrates an end-to-end example of creating and
using a predictive machine learning model using MySQL HeatWave AutoML. It
steps through preparing data, using the
ML_TRAIN routine to train a model,
and using ML_PREDICT_* and
ML_EXPLAIN_* routines to generate predictions
and explanations. The tutorial also demonstrates how to assess the
quality of a model using the
ML_SCORE routine, and how to view a
model explanation to understand how the model works.
For an online workshop based on this tutorial, see Get started with MySQL MySQL HeatWave AutoML.
The tutorial uses the publicly available Iris Data Set from the UCI Machine Learning Repository.
Dua, D. and Graff, C. (2019). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information.
This quickstart contains the following sections:
The Iris Data Set has the following data,
where the sepal and petal features are used to predict the
class label, which is the type of Iris plant:
sepal length (cm)
sepal width (cm)
petal length (cm)
petal width (cm)
-
class. Possible values include:
Iris Setosa
Iris Versicolour
Iris Virginica
Data is stored in the MySQL database in the following schema and tables:
ml_dataschema: The schema containing training and test dataset tables.iris_traintable: The training dataset (labeled). Includes feature columns (sepal length, sepal width, petal length, petal width) and a populatedclasstarget column with ground truth values.iris_testtable: The test dataset (unlabeled). Includes feature columns (sepal length, sepal width, petal length, petal width) but no target column.iris_validatetable: The validation dataset (labeled). Includes feature columns (sepal length, sepal width, petal length, petal width) and a populatedclasstarget column with ground truth values.
Review the MySQL HeatWave Quickstart Requirements.
-
Create and use the the database
ml_datato store the table of data.mysql> CREATE SCHEMA ml_data; mysql> USE ml_data; -
Create the table of training data.
mysql> CREATE TABLE `iris_train` ( `sepal length` float DEFAULT NULL, `sepal width` float DEFAULT NULL, `petal length` float DEFAULT NULL, `petal width` float DEFAULT NULL, `class` varchar(16) DEFAULT NULL); -
Insert the training data into the table. Copy and paste the following commands.
INSERT INTO iris_train VALUES(6.4,2.8,5.6,2.2,'Iris-virginica'); INSERT INTO iris_train VALUES(5.0,2.3,3.3,1.0,'Iris-setosa'); INSERT INTO iris_train VALUES(4.9,2.5,4.5,1.7,'Iris-virginica'); INSERT INTO iris_train VALUES(4.9,3.1,1.5,0.1,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.7,3.8,1.7,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.4,3.2,1.3,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.4,3.4,1.5,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.9,3.1,5.1,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(6.7,3.1,4.4,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(5.1,3.7,1.5,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.2,2.7,3.9,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(6.9,3.1,4.9,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(5.8,4.0,1.2,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.4,3.9,1.7,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.7,3.8,6.7,2.2,'Iris-virginica'); INSERT INTO iris_train VALUES(6.3,3.3,4.7,1.6,'Iris-setosa'); INSERT INTO iris_train VALUES(6.8,3.2,5.9,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(7.6,3.0,6.6,2.1,'Iris-virginica'); INSERT INTO iris_train VALUES(6.4,3.2,5.3,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(5.7,4.4,1.5,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.7,3.3,5.7,2.1,'Iris-virginica'); INSERT INTO iris_train VALUES(6.4,2.8,5.6,2.1,'Iris-virginica'); INSERT INTO iris_train VALUES(5.4,3.9,1.3,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.1,2.6,5.6,1.4,'Iris-virginica'); INSERT INTO iris_train VALUES(7.2,3.0,5.8,1.6,'Iris-virginica'); INSERT INTO iris_train VALUES(5.2,3.5,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.8,2.6,4.0,1.2,'Iris-setosa'); INSERT INTO iris_train VALUES(5.9,3.0,5.1,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(5.4,3.0,4.5,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(6.7,3.0,5.0,1.7,'Iris-setosa'); INSERT INTO iris_train VALUES(6.3,2.3,4.4,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(5.1,2.5,3.0,1.1,'Iris-setosa'); INSERT INTO iris_train VALUES(6.4,3.2,4.5,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(6.8,3.0,5.5,2.1,'Iris-virginica'); INSERT INTO iris_train VALUES(6.2,2.8,4.8,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(6.9,3.2,5.7,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(6.5,3.2,5.1,2.0,'Iris-virginica'); INSERT INTO iris_train VALUES(5.8,2.8,5.1,2.4,'Iris-virginica'); INSERT INTO iris_train VALUES(5.1,3.8,1.5,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.8,3.0,1.4,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.9,3.8,6.4,2.0,'Iris-virginica'); INSERT INTO iris_train VALUES(5.8,2.7,5.1,1.9,'Iris-virginica'); INSERT INTO iris_train VALUES(6.7,3.0,5.2,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(5.1,3.8,1.9,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.7,3.2,1.6,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.0,2.2,5.0,1.5,'Iris-virginica'); INSERT INTO iris_train VALUES(4.8,3.4,1.6,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.7,2.6,6.9,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(4.6,3.6,1.0,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.2,3.2,6.0,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(5.0,3.3,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.6,3.0,4.4,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(6.1,2.8,4.0,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(5.0,3.2,1.2,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.0,3.2,4.7,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(6.0,3.0,4.8,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(7.4,2.8,6.1,1.9,'Iris-virginica'); INSERT INTO iris_train VALUES(5.8,2.7,5.1,1.9,'Iris-virginica'); INSERT INTO iris_train VALUES(6.2,3.4,5.4,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(5.0,2.0,3.5,1.0,'Iris-setosa'); INSERT INTO iris_train VALUES(5.6,2.5,3.9,1.1,'Iris-setosa'); INSERT INTO iris_train VALUES(6.7,3.1,5.6,2.4,'Iris-virginica'); INSERT INTO iris_train VALUES(6.3,2.5,5.0,1.9,'Iris-virginica'); INSERT INTO iris_train VALUES(6.4,3.1,5.5,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(6.2,2.2,4.5,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(7.3,2.9,6.3,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(4.4,3.0,1.3,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.2,3.6,6.1,2.5,'Iris-virginica'); INSERT INTO iris_train VALUES(6.5,3.0,5.5,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(5.0,3.4,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.7,3.2,1.3,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.6,2.9,4.6,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(5.5,3.5,1.3,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.7,3.0,6.1,2.3,'Iris-virginica'); INSERT INTO iris_train VALUES(6.1,3.0,4.9,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(4.9,3.1,1.5,0.1,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.5,2.4,3.8,1.1,'Iris-setosa'); INSERT INTO iris_train VALUES(5.7,2.9,4.2,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(6.0,2.9,4.5,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(6.4,2.7,5.3,1.9,'Iris-virginica'); INSERT INTO iris_train VALUES(5.4,3.7,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.1,2.9,4.7,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(6.5,2.8,4.6,1.5,'Iris-setosa'); INSERT INTO iris_train VALUES(5.6,2.7,4.2,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(6.3,3.4,5.6,2.4,'Iris-virginica'); INSERT INTO iris_train VALUES(4.9,3.1,1.5,0.1,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.8,2.8,4.8,1.4,'Iris-setosa'); INSERT INTO iris_train VALUES(5.7,2.8,4.5,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(6.0,2.7,5.1,1.6,'Iris-setosa'); INSERT INTO iris_train VALUES(5.0,3.5,1.3,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.5,3.0,5.2,2.0,'Iris-virginica'); INSERT INTO iris_train VALUES(6.1,2.8,4.7,1.2,'Iris-setosa'); INSERT INTO iris_train VALUES(5.1,3.5,1.4,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.6,3.1,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.5,3.0,5.8,2.2,'Iris-virginica'); INSERT INTO iris_train VALUES(4.6,3.4,1.4,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.6,3.2,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(7.7,2.8,6.7,2.0,'Iris-virginica'); INSERT INTO iris_train VALUES(5.9,3.2,4.8,1.8,'Iris-setosa'); INSERT INTO iris_train VALUES(5.1,3.8,1.6,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.9,3.0,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.9,2.4,3.3,1.0,'Iris-setosa'); INSERT INTO iris_train VALUES(4.5,2.3,1.3,0.3,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.8,2.7,4.1,1.0,'Iris-setosa'); INSERT INTO iris_train VALUES(5.0,3.4,1.6,0.4,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.2,3.4,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.3,3.7,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.0,3.6,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.6,2.9,3.6,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(4.8,3.1,1.6,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.3,2.7,4.9,1.8,'Iris-virginica'); INSERT INTO iris_train VALUES(5.7,2.8,4.1,1.3,'Iris-setosa'); INSERT INTO iris_train VALUES(5.0,3.0,1.6,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(6.3,3.3,6.0,2.5,'Iris-virginica'); INSERT INTO iris_train VALUES(5.0,3.5,1.6,0.6,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.5,2.6,4.4,1.2,'Iris-setosa'); INSERT INTO iris_train VALUES(5.7,3.0,4.2,1.2,'Iris-setosa'); INSERT INTO iris_train VALUES(4.4,2.9,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_train VALUES(4.8,3.0,1.4,0.1,'Iris-versicolor'); INSERT INTO iris_train VALUES(5.5,2.4,3.7,1.0,'Iris-setosa'); -
Create the table of test data, which has the same columns as the table of training data.
mysql> CREATE TABLE `iris_test` LIKE `iris_train`; -
Insert the test data into the table. Copy and paste the following commands.
INSERT INTO iris_test VALUES(5.9,3.0,4.2,1.5,'Iris-setosa'); INSERT INTO iris_test VALUES(6.9,3.1,5.4,2.1,'Iris-virginica'); INSERT INTO iris_test VALUES(5.1,3.3,1.7,0.5,'Iris-versicolor'); INSERT INTO iris_test VALUES(6.0,3.4,4.5,1.6,'Iris-setosa'); INSERT INTO iris_test VALUES(5.5,2.5,4.0,1.3,'Iris-setosa'); INSERT INTO iris_test VALUES(6.2,2.9,4.3,1.3,'Iris-setosa'); INSERT INTO iris_test VALUES(5.5,4.2,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_test VALUES(6.3,2.8,5.1,1.5,'Iris-virginica'); INSERT INTO iris_test VALUES(5.6,3.0,4.1,1.3,'Iris-setosa'); INSERT INTO iris_test VALUES(6.7,2.5,5.8,1.8,'Iris-virginica'); INSERT INTO iris_test VALUES(7.1,3.0,5.9,2.1,'Iris-virginica'); INSERT INTO iris_test VALUES(4.3,3.0,1.1,0.1,'Iris-versicolor'); INSERT INTO iris_test VALUES(5.6,2.8,4.9,2.0,'Iris-virginica'); INSERT INTO iris_test VALUES(5.5,2.3,4.0,1.3,'Iris-setosa'); INSERT INTO iris_test VALUES(6.0,2.2,4.0,1.0,'Iris-setosa'); INSERT INTO iris_test VALUES(5.1,3.5,1.4,0.2,'Iris-versicolor'); INSERT INTO iris_test VALUES(5.7,2.6,3.5,1.0,'Iris-setosa'); INSERT INTO iris_test VALUES(4.8,3.4,1.9,0.2,'Iris-versicolor'); INSERT INTO iris_test VALUES(5.1,3.4,1.5,0.2,'Iris-versicolor'); INSERT INTO iris_test VALUES(5.7,2.5,5.0,2.0,'Iris-virginica'); INSERT INTO iris_test VALUES(5.4,3.4,1.7,0.2,'Iris-versicolor'); INSERT INTO iris_test VALUES(5.6,3.0,4.5,1.5,'Iris-setosa'); INSERT INTO iris_test VALUES(6.3,2.9,5.6,1.8,'Iris-virginica'); INSERT INTO iris_test VALUES(6.3,2.5,4.9,1.5,'Iris-setosa'); INSERT INTO iris_test VALUES(5.8,2.7,3.9,1.2,'Iris-setosa'); INSERT INTO iris_test VALUES(6.1,3.0,4.6,1.4,'Iris-setosa'); INSERT INTO iris_test VALUES(5.2,4.1,1.5,0.1,'Iris-versicolor'); INSERT INTO iris_test VALUES(6.7,3.1,4.7,1.5,'Iris-setosa'); INSERT INTO iris_test VALUES(6.7,3.3,5.7,2.5,'Iris-virginica'); INSERT INTO iris_test VALUES(6.4,2.9,4.3,1.3,'Iris-setosa'); -
Create the table of validation data. It has the same columns and data as the test data.
mysql> CREATE TABLE `iris_validate` LIKE `iris_test`; mysql> INSERT INTO `iris_validate` SELECT * FROM `iris_test`;
Train the model with the ML_TRAIN
routine and use the iris_train table
previously created. Before training the model, it is good
practice to define the model handle instead of automatically
creating one. See
Define Model Handle.
-
Optionally, set the value of the session variable, which sets the model handle to this same value.
mysql> SET @model='iris_model';The model handle is set to
iris_model. -
Run the
ML_TRAINroutine.mysql> CALL sys.ML_TRAIN('ml_data.iris_train', 'class', JSON_OBJECT('task', 'classification'), @model); -
When the training operation finishes, the model handle is assigned to the
@modelsession variable, and the model is stored in the model catalog. View the entry in the model catalog with the following query. Replaceuser1with your MySQL account name.mysql> SELECT model_id, model_handle, train_table_name FROM ML_SCHEMA_user1.MODEL_CATALOG WHERE model_handle = 'iris_model'; +----------+----------------------------------------------+--------------------+ | model_id | model_handle | train_table_name | +----------+----------------------------------------------+--------------------+ | 1 | iris_model | ml_data.iris_train | +----------+----------------------------------------------+--------------------+
Load the model into MySQL HeatWave AutoML using
ML_MODEL_LOAD routine:
mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);A model must be loaded before you can use it. The model remains loaded until you unload it or the MySQL HeatWave Cluster is restarted.
You can make predictions on a single row of data or on the table of data.
-
Make a prediction for a single row of data using the
ML_PREDICT_ROWroutine. In this example, data is assigned to a@row_inputsession variable, and the variable is called by the routine. The model handle is called using the@modelsession variable. Optionally, use\Gto view the output in an easily readable format.mysql> SET @row_input = JSON_OBJECT( "sepal length", 7.3, "sepal width", 2.9, "petal length", 6.3, "petal width", 1.8); mysql> SELECT sys.ML_PREDICT_ROW(@row_input, @model, NULL)\G *************************** 1. row *************************** sys.ML_PREDICT_ROW(@row_input, @iris_model, NULL): {"Prediction": "Iris-virginica", "ml_results": {"predictions": {"class": "Iris-virginica"}, "probabilities": {"Iris-setosa": 0.0, "Iris-virginica": 0.87, "Iris-versicolor": 0.13}}, "petal width": 1.8, "sepal width": 2.9, "petal length": 6.3, "sepal length": 7.3} 1 row in set (0.7336 sec)Based on the feature inputs that were provided, the model predicts that the Iris plant is of the class
Iris-virginica. The feature values used to make the prediction are also shown. -
Make predictions for a table of data using the
ML_PREDICT_TABLEroutine. The routine takes data from theiris_testtable as input and writes the predictions to aniris_predictionsoutput table.mysql> CALL sys.ML_PREDICT_TABLE('ml_data.iris_test', @model, ml_data.iris_predictions', NULL);To view
ML_PREDICT_TABLEresults, query the output table. Optionally, use\Gto view the output in an easily readable format. For example:mysql> SELECT * from ml_data.iris_predictions LIMIT 5\G *************************** 1. row *************************** _4aad19ca6e_pk_id: 1 sepal length: 5.9 sepal width: 3 petal length: 4.2 petal width: 1.5 class: Iris-setosa Prediction: Iris-setosa ml_results: {"predictions": {"class": "Iris-setosa"}, "probabilities": {"Iris-setosa": 0.9938, "Iris-versicolor": 0.0018, "Iris-virginica": 0.4621}} *************************** 2. row *************************** _4aad19ca6e_pk_id: 2 sepal length: 6.9 sepal width: 3.1 petal length: 5.4 petal width: 2.1 class: Iris-virginica Prediction: Iris-virginica ml_results: {"predictions": {"class": "Iris-virginica"}, "probabilities": {"Iris-setosa": 0.4405, "Iris-versicolor": 0.0059, "Iris-virginica": 0.9976}} *************************** 3. row *************************** _4aad19ca6e_pk_id: 3 sepal length: 5.1 sepal width: 3.3 petal length: 1.7 petal width: 0.5 class: Iris-versicolor Prediction: Iris-versicolor ml_results: {"predictions": {"class": "Iris-versicolor"}, "probabilities": {"Iris-setosa": 0.4237, "Iris-versicolor": 0.9951, "Iris-virginica": 0.0}} *************************** 4. row *************************** _4aad19ca6e_pk_id: 4 sepal length: 6 sepal width: 3.4 petal length: 4.5 petal width: 1.6 class: Iris-setosa Prediction: Iris-setosa ml_results: {"predictions": {"class": "Iris-setosa"}, "probabilities": {"Iris-setosa": 0.9846, "Iris-versicolor": 0.001, "Iris-virginica": 0.5288}} *************************** 5. row *************************** _4aad19ca6e_pk_id: 5 sepal length: 5.5 sepal width: 2.5 petal length: 4 petal width: 1.3 class: Iris-setosa Prediction: Iris-setosa ml_results: {"predictions": {"class": "Iris-setosa"}, "probabilities": {"Iris-setosa": 0.9982, "Iris-versicolor": 0.0018, "Iris-virginica": 0.4367}} 5 rows in set (0.0474 sec)The table shows the predictions and the feature column values used to make each prediction.
After creating predictions (either on a single row of data or the table of data), you can generate explanations to understand how the predictions were made and review which features had the most influence on predictions.
-
Generate an explanation for a prediction made on a row of data using the
ML_EXPLAIN_ROWroutine with the Permutation Importance prediction explainer. Optionally, use\Gto view the output in an easily readable format:mysql> SELECT sys.ML_EXPLAIN_ROW(JSON_OBJECT("sepal length", 7.3, "sepal width", 2.9, "petal length", 6.3, "petal width", 1.8), @iris_model, JSON_OBJECT('prediction_explainer', 'permutation_importance'))\G *************************** 1. row *************************** sys.ML_EXPLAIN_ROW(JSON_OBJECT("sepal length", 7.3, "sepal width", 2.9, "petal length", 6.3, "petal width", 1.8), @iris_model, JSON_OBJECT('prediction_explainer', 'permutation_importance')): {"Notes": "petal length (6.3) contributed the most against predicting Iris-virginica", "Prediction": "Iris-virginica", "ml_results": {"notes": "petal length (6.3) contributed the most against predicting Iris-virginica", "predictions": {"class": "Iris-virginica"}, "attributions": {"petal width": -0.13, "petal length": -0.8, "sepal length": -0.02}}, "petal width": 1.8, "sepal width": 2.9, "petal length": 6.3, "sepal length": 7.3, "petal width_attribution": -0.13, "petal length_attribution": -0.8, "sepal length_attribution": -0.02} 1 row in set (1.6709 sec)For the Permutation Importance prediction explainer, the attribution values show which features contributed most to the prediction, with petal length and petal width being the most important features. The other features have a 0 value indicating that they did not contribute to the prediction.
-
Generate explanations for predictions made for a table of data using the
ML_EXPLAIN_TABLEroutine with the Permutation Importance prediction explainer.For the Permutation Importance prediction explainer, feature importance is presented as an attribution value ranging from -1 to 1. A positive value indicates that a feature contributed toward the prediction. A negative value indicates that the feature contributes positively towards one of the other possible predictions.
mysql> CALL sys.ML_EXPLAIN_TABLE('ml_data.iris_test', @iris_model, 'ml_data.iris_explanations', JSON_OBJECT('prediction_explainer', 'permutation_importance'));To view
ML_EXPLAIN_TABLEresults, query the output table. Optionally, use\Gto view the output in an easily readable format. For example:mysql> SELECT * FROM ml_data.iris_explanations LIMIT 5\G *************************** 1. row *************************** _4aad19ca6e_pk_id: 1 sepal length: 5.9 sepal width: 3 petal length: 4.2 petal width: 1.5 class: Iris-setosa Prediction: Iris-setosa sepal length_attribution: -0.66 petal width_attribution: -0.5 petal length_attribution: -0.37 Notes: sepal length (5.900000095367432) contributed the most against predicting Iris-setosa ml_results: {"attributions": {"sepal length": -0.66, "petal width": -0.5, "petal length": -0.37}, "predictions": {"class": "Iris-setosa"}, "notes": "sepal length (5.900000095367432) contributed the most against predicting Iris-setosa"} *************************** 2. row *************************** _4aad19ca6e_pk_id: 2 sepal length: 6.9 sepal width: 3.1 petal length: 5.4 petal width: 2.1 class: Iris-virginica Prediction: Iris-virginica sepal length_attribution: -0.03 petal width_attribution: -0.49 petal length_attribution: -0.92 Notes: petal length (5.400000095367432) contributed the most against predicting Iris-virginica ml_results: {"attributions": {"sepal length": -0.03, "petal width": -0.49, "petal length": -0.92}, "predictions": {"class": "Iris-virginica"}, "notes": "petal length (5.400000095367432) contributed the most against predicting Iris-virginica"} *************************** 3. row *************************** _4aad19ca6e_pk_id: 3 sepal length: 5.1 sepal width: 3.3 petal length: 1.7 petal width: 0.5 class: Iris-versicolor Prediction: Iris-versicolor sepal length_attribution: -0.04 petal width_attribution: -0.79 petal length_attribution: -0.77 Notes: petal width (0.5) contributed the most against predicting Iris-versicolor ml_results: {"attributions": {"sepal length": -0.04, "petal width": -0.79, "petal length": -0.77}, "predictions": {"class": "Iris-versicolor"}, "notes": "petal width (0.5) contributed the most against predicting Iris-versicolor"} *************************** 4. row *************************** _4aad19ca6e_pk_id: 4 sepal length: 6 sepal width: 3.4 petal length: 4.5 petal width: 1.6 class: Iris-setosa Prediction: Iris-setosa sepal length_attribution: -0.97 petal width_attribution: -0.51 petal length_attribution: -0.38 Notes: sepal length (6.0) contributed the most against predicting Iris-setosa ml_results: {"attributions": {"sepal length": -0.97, "petal width": -0.51, "petal length": -0.38}, "predictions": {"class": "Iris-setosa"}, "notes": "sepal length (6.0) contributed the most against predicting Iris-setosa"} *************************** 5. row *************************** _4aad19ca6e_pk_id: 5 sepal length: 5.5 sepal width: 2.5 petal length: 4 petal width: 1.3 class: Iris-setosa Prediction: Iris-setosa sepal length_attribution: -0.47 petal width_attribution: -0.54 petal length_attribution: -0.57 Notes: petal length (4.0) contributed the most against predicting Iris-setosa ml_results: {"attributions": {"sepal length": -0.47, "petal width": -0.54, "petal length": -0.57}, "predictions": {"class": "Iris-setosa"}, "notes": "petal length (4.0) contributed the most against predicting Iris-setosa"} 5 rows in set (0.0447 sec)
Score the model with ML_SCORE to
assess the reliability of the model. This example uses the
balanced_accuracy metric, which is one of the
many scoring metrics that MySQL HeatWave AutoML supports.
mysql> CALL sys.ML_SCORE('ml_data.iris_validate', 'class', @iris_model, 'balanced_accuracy', @score, NULL);
To retrieve the computed score, query the
@score session variable.
mysql> SELECT @score;
+--------------------+
| @score |
+--------------------+
| 0.9583333134651184 |
+--------------------+
Unload the model using
ML_MODEL_UNLOAD:
mysql> CALL sys.ML_MODEL_UNLOAD(@model);To avoid consuming too much memory, it is good practice to unload a model when you are finished using it.