HeatWave Release Notes
MySQL 8.0.32 introduces progress tracking for
ML_TRAIN. Use a second MySQL
Client window to track progress with calls to the performance
schema.
MySQL 8.2.0 adds progress tracking for all HeatWave AutoML routines:
ML_MODEL_IMPORTfor models in ONNX format. Progress tracking does not supportML_MODEL_IMPORTfor models in HeatWave AutoML format.
For each of these routines, progress tracking tracks each individual operation, stages within routines, and includes a completed percentage value.
MySQL 8.2.0 adds batch processing for
ML_PREDICT_TABLE and
ML_EXPLAIN_TABLE.
-
As of MySQL 8.2.0, run
ML_TRAINfrom the first MySQL Client window:mysql> CALL sys.ML_TRAIN('mlcorpus_v5.`titanic_train`', 'survived', NULL, @model); Query OK, 0 rows affected (1 min 28.7369 sec)From the second MySQL Client window, run the following successive queries:
-
At the start of the
ML_TRAINoperation.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 1 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}} | {} | {} | | | 2 | 4294967295 | 4294967295 | ML_TRAIN | {"options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"} | {} | {} | | +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 2 rows in set (0.0005 sec) -
During the
ML_TRAINoperation.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 1 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}} | {} | {} | | | 2 | 4294967295 | 4294967295 | ML_TRAIN | {"status": "In Progress", "completedSteps": ["Initialization", "Preprocessing"], "completionPercentage": 11, "totalRunTime": null, "startTime": "06/15/2023, 18:55:47:732187 UTC", "progressItems": [{"type": "Initialization", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:093603 UTC", "completionPercentage": 100}, {"type": "Preprocessing", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:137747 UTC", "completionPercentage": 100}, {"type": "Algorithm Selection", "status": "In Progress", "lastUpdated": "06/15/2023, 18:55:56:202947 UTC", "completionPercentage": 47}, {"type": "Row Sampling", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:088933 UTC", "completionPercentage": 0}, {"type": "Column Selection", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:089422 UTC", "completionPercentage": 0}, {"type": "Hyperparameter Optimization", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:089931 UTC", "completionPercentage": 0}, {"type": "Model Generation", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:090460 UTC", "completionPercentage": 0}, {"type": "Prediction Explainer", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:090920 UTC", "completionPercentage": 0}, {"type": "Model Explainer", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:091456 UTC", "completionPercentage": 0}], "details": {"trainingScore": null, "optimizationMetric": null, "options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {} | {} | | +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 2 rows in set (0.0005 sec) -
When the
ML_TRAINoperation is complete.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 1 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}} | {} | {} | | | 2 | 4294967295 | 4294967295 | ML_TRAIN | {"status": "Completed", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection", "Row Sampling", "Column Selection", "Hyperparameter Optimization", "Model Generation", "Prediction Explainer", "Model Explainer"], "completionPercentage": 100, "totalRunTime": "84.9 Seconds", "startTime": "06/15/2023, 18:55:47:732187 UTC", "progressItems": [{"type": "Initialization", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:093603 UTC", "completionPercentage": 100}, {"type": "Preprocessing", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:137747 UTC", "completionPercentage": 100}, {"type": "Algorithm Selection", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:57:159736 UTC", "completionPercentage": 100}, {"type": "Row Sampling", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:58:210754 UTC", "completionPercentage": 100}, {"type": "Column Selection", "status": "Completed", "lastUpdated": "06/15/2023, 18:56:11:007284 UTC", "completionPercentage": 100}, {"type": "Hyperparameter Optimization", "status": "Completed", "lastUpdated": "06/15/2023, 18:56:55:725475 UTC", "completionPercentage": 100}, {"type": "Model Generation", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:11:779587 UTC", "completionPercentage": 100}, {"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:11:951237 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:12:633848 UTC", "completionPercentage": 100}], "details": {"trainingScore": -0.0774, "optimizationMetric": "neg_log_loss", "options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {} | {} | | | 3 | 4294967295 | 4294967295 | ML_MODEL_OBJECT | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.14 Seconds", "startTime": "06/15/2023, 18:57:14:500838 UTC", "progressItems": [], "details": {"operation": "model_object", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 4 | 4294967295 | 4294967295 | ML_MODEL_METADATA | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.09 Seconds", "startTime": "06/15/2023, 18:57:15:004316 UTC", "progressItems": [], "details": {"operation": "model_metadata", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 5 | 4294967295 | 4294967295 | ML_UNLOAD_MODEL | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.03 Seconds", "startTime": "06/15/2023, 18:57:15:491299 UTC", "progressItems": [], "details": {"operation": "unload_model", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 5 rows in set (0.0005 sec)
-
-
As of MySQL 8.2.0, as an example for other operations, run
ML_EXPLAINfrom the first MySQL Client window:mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); Query OK, 0 rows affected (0.5951 sec) mysql> CALL sys.ML_EXPLAIN('mlcorpus_v5.`titanic_train`', 'survived', @model, JSON_OBJECT('model_explainer', 'fast_shap', 'prediction_explainer', 'shap')); Query OK, 0 rows affected (8 min 35.2447 sec)From the second MySQL Client window, run the following successive queries:
-
At the start of the
ML_EXPLAINoperation. The first five rows relate to the progress ofML_TRAIN.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 6 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}} | {} | {} | | | 7 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}} | {} | {} | | | 8 | 4294967295 | 4294967295 | ML_EXPLAIN | {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"} | {} | {} | | +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 8 rows in set (0.0005 sec) -
During the
ML_EXPLAINoperation.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 6 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}} | {} | {} | | | 7 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}} | {} | {} | | | 8 | 4294967295 | 4294967295 | ML_EXPLAIN | {"status": "In Progress", "completedSteps": ["Prediction Explainer"], "completionPercentage": 50, "totalRunTime": null, "startTime": "06/15/2023, 19:01:19:237573 UTC", "progressItems": [{"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:01:25:559690 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "In Progress", "lastUpdated": "06/15/2023, 19:01:25:589740 UTC", "completionPercentage": 0}], "details": {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {} | {} | | +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 8 rows in set (0.0005 sec) -
When the
ML_EXPLAINoperation is complete.mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 6 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}} | {} | {} | | | 7 | 4294967295 | 4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}} | {} | {} | | | 8 | 4294967295 | 4294967295 | ML_EXPLAIN | {"status": "Completed", "completedSteps": ["Prediction Explainer", "Model Explainer"], "completionPercentage": 100, "totalRunTime": "511.79 Seconds", "startTime": "06/15/2023, 19:01:19:237573 UTC", "progressItems": [{"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:01:25:559690 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:09:51:031054 UTC", "completionPercentage": 100}], "details": {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}}| {} | {} | | | 9 | 4294967295 | 4294967295 | ML_MODEL_OBJECT | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.08 Seconds", "startTime": "06/15/2023, 19:09:53:234425 UTC", "progressItems": [], "details": {"operation": "model_object", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 10 | 4294967295 | 4294967295 | ML_MODEL_METADATA | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "06/15/2023, 19:09:53:553660 UTC", "progressItems": [], "details": {"operation": "model_metadata", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 10 rows in set (0.0005 sec)
-
-
As of MySQL 8.2.0, run
ML_PREDICT_ROWfrom the first MySQL Client window:mysql> SELECT sys.ML_PREDICT_ROW( JSON_OBJECT('pclass',`titanic_test`.`pclass`, 'name',`titanic_test`.`name`, 'sex',`titanic_test`.`sex`, 'age',`titanic_test`.`age`, 'sibsp',`titanic_test`.`sibsp`, 'parch',`titanic_test`.`parch`, 'ticket',`titanic_test`.`ticket`, 'fare',`titanic_test`.`fare`, 'cabin',`titanic_test`.`cabin`, 'embarked',`titanic_test`.`embarked`, 'boat',`titanic_test`.`boat`, 'body',`titanic_test`.`body`, 'home.dest',`titanic_test`.`home.dest` ), @model, NULL) FROM `titanic_test` LIMIT 4; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW(JSON_OBJECT('pclass',`titanic_test`.`pclass`,'name',`titanic_test`.`name`,'sex',`titanic_test`.`sex`,'age',`titanic_test`.`age`,'sibsp',`titanic_test`.`sibsp`,'parch',`titanic_test`.`parch`,'ticket',`titanic_test`.`ticket`,'fare',`titan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"age": 20.0, "sex": "male", "boat": null, "body": 89.0, "fare": 9.2250003815, "name": "Olsvigen, Mr. Thor Anderson", "cabin": null, "parch": 0, "sibsp": 0, "pclass": 3, "ticket": "6563", "embarked": "S", "home.dest": "Oslo, Norway Cameron, WI", "Prediction": 0, "ml_results": {"predictions": {"survived": 0}, "probabilities": {"0": 0.9900000095367432, "1": 0.009999999776482582}}} | | {"age": 4.0, "sex": "female", "boat": "2", "body": null, "fare": 22.0249996185, "name": "Kink-Heilmann, Miss. Luise Gretchen", "cabin": null, "parch": 2, "sibsp": 0, "pclass": 3, "ticket": "315153", "embarked": "S", "home.dest": null, "Prediction": 1, "ml_results": {"predictions": {"survived": 1}, "probabilities": {"0": 0.0, "1": 1.0}}} | | {"age": 42.0, "sex": "male", "boat": null, "body": 120.0, "fare": 7.6500000954, "name": "Humblen, Mr. Adolf Mathias Nicolai Olsen", "cabin": "F G63", "parch": 0, "sibsp": 0, "pclass": 3, "ticket": "348121", "embarked": "S", "home.dest": null, "Prediction": 0, "ml_results": {"predictions": {"survived": 0}, "probabilities": {"0": 0.9900000095367432, "1": 0.009999999776482582}}} | | {"age": 45.0, "sex": "male", "boat": "7", "body": null, "fare": 29.7000007629, "name": "Chevre, Mr. Paul Romaine", "cabin": "A9", "parch": 0, "sibsp": 0, "pclass": 1, "ticket": "PC 17594", "embarked": "C", "home.dest": "Paris, France", "Prediction": 1, "ml_results": {"predictions": {"survived": 1}, "probabilities": {"0": 0.009999999776482582, "1": 0.9900000095367432}}} | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (1.1977 sec)From the second MySQL Client window, run the following query:
mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ | 23 | 4294967295 | 4294967295 | ML_PREDICT_ROW | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.11 Seconds", "startTime": "06/15/2023, 19:44:10:248906 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 24 | 4294967295 | 4294967295 | ML_PREDICT_ROW | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.08 Seconds", "startTime": "06/15/2023, 19:44:10:726698 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 25 | 4294967295 | 4294967295 | ML_PREDICT_ROW | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.09 Seconds", "startTime": "06/15/2023, 19:44:11:171724 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | | 26 | 4294967295 | 4294967295 | ML_PREDICT_ROW | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.07 Seconds", "startTime": "06/15/2023, 19:44:11:502916 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {} | {} | | +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+ 26 rows in set (0.0005 sec) -
As of MySQL 8.2.0, call
ML_PREDICT_TABLEwith abatch_sizeof 2 on a table with 4 rows:mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); Query OK, 0 rows affected (0.5957 sec) mysql> CREATE TABLE `titanic_test_temp` AS SELECT * FROM `titanic_test` LIMIT 4; Query OK, 4 rows affected (0.0200 sec) mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus_v5.`titanic_test_temp`', @model, 'mlcorpus_v5.`titanic_predictions`', JSON_OBJECT('batch_size',2)); Query OK, 0 rows affected (3.6509 sec) mysql> SELECT * FROM titanic_predictions; +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+ | _id | pclass | name | sex | age | sibsp | parch | ticket | fare | cabin | embarked | boat | body | home.dest | survived | Prediction | ml_results | +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+ | 1 | 3 | Olsvigen, Mr. Thor Anderson | male | 20 | 0 | 0 | 6563 | 9.225 | NULL | S | NULL | 89 | Oslo, Norway Cameron, WI | 0 | 0 | {"predictions": {"survived": 0}, "probabilities": {"0": 0.99, "1": 0.01}} | | 2 | 3 | Kink-Heilmann, Miss. Luise Gretchen | female | 4 | 0 | 2 | 315153 | 22.025 | NULL | S | 2 | NULL | NULL | 1 | 1 | {"predictions": {"survived": 1}, "probabilities": {"0": 0.0, "1": 1.0}} | | 3 | 3 | Humblen, Mr. Adolf Mathias Nicolai Olsen | male | 42 | 0 | 0 | 348121 | 7.65 | F G63 | S | NULL | 120 | NULL | 0 | 0 | {"predictions": {"survived": 0}, "probabilities": {"0": 0.99, "1": 0.01}} | | 4 | 1 | Chevre, Mr. Paul Romaine | male | 45 | 0 | 0 | PC 17594 | 29.7 | A9 | C | 7 | NULL | Paris, France | 1 | 1 | {"predictions": {"survived": 1}, "probabilities": {"0": 0.01, "1": 0.99}} | +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+ 4 rows in set (0.0006 sec)From the second MySQL Client window, run the following query:
mysql> SELECT * FROM performance_schema.rpd_query_stats; +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+ | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT | QEXEC_TEXT | QKRN_TEXT | QEP_TEXT | INTERNAL_OFFLOAD_FAIL_TEXT | EXTERNAL_OFFLOAD_FAIL_TEXT | +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+ | 8 | 397099 | 10 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "12/13/2023, 00:55:00:549849 UTC", "progressItems": [], "details": {}} | {} | {} | | | | 9 | 397224 | 10 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.48 Seconds", "startTime": "12/13/2023, 00:55:04:780535 UTC", "progressItems": [], "details": {}} | {} | {} | | | | 10 | 595247 | 10 | ML_PREDICT_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "12/13/2023, 00:55:05:577699 UTC", "progressItems": [], "details": {"options": {"batch_size": 2}, "operation": "prediction_table", "user_name": "root", "table_name": "0266fbec8a7fdf3b12ac766650391345", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1702428631617"}} | {} | {} | | | | 11 | 595318 | 10 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.45 Seconds", "startTime": "12/13/2023, 00:55:06:679923 UTC", "progressItems": [], "details": {}} | {} | {} | | | | 12 | 793401 | 10 | ML_PREDICT_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "12/13/2023, 00:55:07:434696 UTC", "progressItems": [], "details": {"options": {"batch_size": 2}, "operation": "prediction_table", "user_name": "root", "table_name": "0266fbec8a7fdf3b12ac766650391345", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1702428631617"}} | {} | {} | | | +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+ 12 rows in set (0.0011 sec) -
As of MySQL 8.2.0, to extract the completed percentage for the
ML_TRAINoperation:mysql> SELECT QEXEC_TEXT INTO @progress_json FROM performance_schema.rpd_query_stats WHERE QUERY_TEXT='ML_TRAIN' ORDER BY QUERY_ID DESC limit 1; Query OK, 1 row affected (0.0009 sec) mysql> SELECT JSON_EXTRACT(@progress_json, '$.completionPercentage'); Query OK, 1 row affected (0.0009 sec) +--------------------------------------------------------+ | JSON_EXTRACT(@progress_json, '$.completionPercentage') | +--------------------------------------------------------+ | 63 | +--------------------------------------------------------+ 1 row in set (0.0005 sec) -
As of MySQL 8.2.0, to extract the status for the
ML_TRAINoperation:mysql> SELECT QEXEC_TEXT INTO @progress_json FROM performance_schema.rpd_query_stats WHERE QUERY_TEXT='ML_TRAIN' ORDER BY QUERY_ID DESC limit 1; Query OK, 1 row affected (0.0009 sec) mysql> SELECT JSON_EXTRACT(@progress_json, '$.status'); Query OK, 1 row affected (0.0009 sec) +------------------------------------------+ | JSON_EXTRACT(@progress_json, '$.status') | +------------------------------------------+ | "In Progress" | +------------------------------------------+ 1 row in set (0.0006 sec) -
As of MySQL 8.0.32, and before MySQL 8.2.0, run
ML_TRAINfrom the first MySQL Client window:mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task', 'classification'), @census_model);From the second MySQL Client window, run the following successive queries:
-
Before
ML_TRAINhas started. It might take several seconds beforeML_TRAINstarts with a large dataset.mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats WHERE QUERY_TEXT='ML_TRAIN' ORDER BY QUERY_ID DESC limit 1; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QEXEC_TEXT | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -
During the
ML_TRAINoperation.mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats WHERE QUERY_TEXT='ML_TRAIN' ORDER BY QUERY_ID DESC limit 1; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QEXEC_TEXT | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"status": "In Progress", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection"], "elapsedRunTime": 9.716094, "startTime": "11/28/2022, 09:02:36:910865", "progressItems": [{"type": "Initialization", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:662426"}, {"type": "Preprocessing", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:696405"}, {"type": "Algorithm Selection", "status": "Completed", "timestamp": "11/28/2022, 09:02:46:623882"}, {"type": "Row Sampling", "status": "In Progress", "timestamp": "11/28/2022, 09:02:46:626959"}, {"type": "Column Selection", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:655721"}, {"type": "Hyperparameter Optimization", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:656245"}, {"type": "Model Generation", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:656736"}], "options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ -
When the
ML_TRAINoperation is complete.mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats WHERE QUERY_TEXT='ML_TRAIN' ORDER BY QUERY_ID DESC limit 1; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QEXEC_TEXT | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"status": "Completed", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection", "Row Sampling", "Column Selection", "Hyperparameter Optimization", "Model Generation"], "elapsedRunTime": 69.678911, "startTime": "11/28/2022, 09:02:36:910865", "progressItems": [{"type": "Initialization", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:662426"}, {"type": "Preprocessing", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:696405"}, {"type": "Algorithm Selection", "status": "Completed", "timestamp": "11/28/2022, 09:02:46:623882"}, {"type": "Row Sampling", "status": "Completed", "timestamp": "11/28/2022, 09:02:47:252748"}, {"type": "Column Selection", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:055310"}, {"type": "Hyperparameter Optimization", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:055928"}, {"type": "Model Generation", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:589776"}], "options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-