HeatWave Release Notes
Use the ML_MODEL_IMPORT routine
to import a pre-trained model into the model catalog. HeatWave AutoML
supports the import of HeatWave AutoML and ONNX, Open Neural
Network Exchange, format models. After import, all the
HeatWave AutoML routines can be used with an ONNX model.
Models in ONNX format, .onnx. cannot be
loaded directly into a MySQL table. They require string
serialization and conversion to Base64 binary encoding. Before
running ML_MODEL_IMPORT, follow
the instructions in Section 3.14.2, “ONNX Model Import” to
pre-process and then load the model into a temporary table for
import to HeatWave.
MySQL 9.0.0 introduces support for large models that changes
how HeatWave AutoML stores models, see:
Section 3.14.1, “The Model Catalog”.
ML_MODEL_IMPORT upgrades older
models.
MySQL 9.0.0 also supports model import from a table. The supported model import formats are ONNX, and all the formats supported by the model catalog. The default import format is ONNX. HeatWave AutoML verifies models with the ONNX format, and stores them as ONNXv2.0.
The table should have the following columns, and their recommended parameters:
-
chunk_id:INT AUTO_INCREMENT PRIMARY KEY -
model_object:LONGTEXT NOT NULL -
model_metadata:JSON DEFAULT NULL
The table must meet the following criteria:
There must be one row, and only one row, in the table with
chunk_id= 1.The
model_metadatacorresponding tochunk_id= 1 must have the correct JSON key, value pair for the model format.
ML_MODEL_IMPORT will store the
model_metadata corresponding to
chunk_id = 1 in the model catalog, and
ignore the model_metadata from other rows.
If chunks in the
model_metadata corresponding to
chunk_id = 1 is not set, it will be set to
the number of rows in the input table.
If ML_MODEL_IMPORT fails or is
cancelled, there will be no change to the
MODEL_CATALOG and to the
model_object_catalog.
As of MySQL 9.0.0,
ML_MODEL_IMPORT can import a
model from a table as well as a a preprocessed model object.
This uses an alternative syntax for
model_metadata:
mysql> CALL sys.ML_MODEL_IMPORT (model_object, model_metadata, model_handle);
model_metadata: {
JSON_OBJECT("key","value"[,"key","value"] ...)
"key","value": {
['schema', 'schema']
['table', 'table']
}
}
model_metadata: {
JSON_OBJECT("key","value"[,"key","value"] ...)
"key","value": {
['task', {'classification'|'regression'|'forecasting'|'anomaly_detection'|'recommendation'}|NULL]
['build_timestamp', 'timestamp']
['target_column_name', 'column']
['train_table_name', 'table']
['column_names', JSON_ARRAY('column'[,'column'] ...)]
['model_explanation', ml_explain_options]
['notes', 'notes']
['format', 'format']
['status', {'creating'|'ready'|'error'}|NULL]
['model_quality', 'quality']
['training_time', 'time']
['algorithm_name', 'algorithm']
['training_score', 'score']
['n_rows', 'rows']
['n_columns', 'columns']
['n_selected_rows', 'rows']
['n_selected_columns', 'columns']
['optimization_metric', 'metric']
['selected_column_names', JSON_ARRAY('column'[,'column'] ...)]
['contamination', 'contamination']
['options', ml_train_options]
['training_params', ml_train_params]
['onnx_inputs_info', data_types_map]
['onnx_outputs_info', labels_map]
['training_drift_metric', JSON_OBJECT('mean', 'value', 'variance', 'value')]
['chunks', 'chunks']
}
Before MySQL 9.0.0, use
ML_MODEL_IMPORT to import a
preprocessed model object:
mysql> CALL sys.ML_MODEL_IMPORT (model_object, model_metadata, model_handle);
model_metadata: {
JSON_OBJECT("key","value"[,"key","value"] ...)
"key","value": {
['task', {'classification'|'regression'|'forecasting'|'anomaly_detection'|'recommendation'}|NULL]
['build_timestamp', 'timestamp']
['target_column_name', 'column']
['train_table_name', 'table']
['column_names', JSON_ARRAY('column'[,'column'] ...)]
['model_explanation', ml_explain_options]
['notes', 'notes']
['format', 'format']
['status', {'creating'|'ready'|'error'}|NULL]
['model_quality', 'quality']
['training_time', 'time']
['algorithm_name', 'algorithm']
['training_score', 'score']
['n_rows', 'rows']
['n_columns', 'columns']
['n_selected_rows', 'rows']
['n_selected_columns', 'columns']
['optimization_metric', 'metric']
['selected_column_names', JSON_ARRAY('column'[,'column'] ...)]
['contamination', 'contamination']
['options', ml_train_options]
['training_params', ml_train_params]
['onnx_inputs_info', data_types_map]
['onnx_outputs_info', labels_map]
['training_drift_metric', JSON_OBJECT('mean', 'value', 'variance', 'value')]
}
}
ML_MODEL_IMPORT parameters:
-
model_object:To import a model from a table: Set to
NULL.To import a model object: The preprocessed model object.
-
model_metadata:-
To import a model from a table:
schema: The name of the schema.table: The name of the table.
To import a model object: An optional JSON object literal that contains key-value pairs with model metadata. See Section 3.14.1.3, “Model Metadata”.
-
model_handle: The model handle for the model. The model is stored in the model catalog under this name and accessed using it. Specify a model handle that does not already exist in the model catalog. Set toNULLfor HeatWave AutoML to generate a unique model handle.
-
An example that imports a HeatWave AutoML model with metadata:
mysql> SET @hwml_model = "hwml_model"; Query OK, 0 rows affected (0.00 sec) mysql> CALL sys.ML_TRAIN('mlcorpus.input_train', 'target', NULL, @hwml_model); Query OK, 0 rows affected (23.36 sec) mysql> SET @hwml_object = (SELECT model_object FROM ML_SCHEMA_root.MODEL_CATALOG WHERE model_handle=@hwml_model); Query OK, 0 rows affected (0.00 sec) mysql> SET @hwml_metadata = (SELECT model_metadata FROM ML_SCHEMA_root.MODEL_CATALOG WHERE model_handle=@hwml_model); Query OK, 0 rows affected (0.00 sec) mysql> CALL sys.ML_MODEL_IMPORT(@hwml_object, @hwml_metadata, @imported_model); Query OK, 0 rows affected (0.01 sec) mysql> model_metadata->>'$.task' AS task, model_owner, model_metadata->>'$.build_timestamp' AS build_timestamp, model_metadata->>'$.target_column_name' AS target_column_name, model_metadata->>'$.train_table_name' AS train_table_name, model_object_size, model_metadata->>'$.model_explanation' AS model_explanation FROM ML_SCHEMA_root.MODEL_CATALOG; +----------------+----------------+-----------------+--------------------+----------------------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | task | model_owner | build_timestamp | target_column_name | train_table_name | model_object_size | model_explanation | +----------------+----------------+-----------------+--------------------+----------------------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | classification | root | 1679202888 | target | mlcorpus.input_train | 4748 | {"permutation_importance": {"C0": 0.0, "C1": 0.0, "C2": 0.0, "C3": 0.0, "C4": 0.0, "C5": 0.0, "C6": 0.0, "C7": 0.0, "C8": 0.0, "C9": 0.0, "C10": 0.0, "C11": 0.0, "C12": 0.0, "C13": 0.0}} | | classification | root | 1679202888 | target | mlcorpus.input_train | 4748 | {"permutation_importance": {"C0": 0.0, "C1": 0.0, "C2": 0.0, "C3": 0.0, "C4": 0.0, "C5": 0.0, "C6": 0.0, "C7": 0.0, "C8": 0.0, "C9": 0.0, "C10": 0.0, "C11": 0.0, "C12": 0.0, "C13": 0.0}} | +----------------+----------------+-----------------+--------------------+----------------------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT model_metadata->>'$.column_names' AS column_names FROM ML_SCHEMA_root.MODEL_CATALOG WHERE model_handle LIKE 'hwml%'; +------------------------------------------------------------------------------------------+ | column_names | +------------------------------------------------------------------------------------------+ | ["C0", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13"] | | ["C0", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13"] | +------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -
An example that imports an ONNX model without specifying metadata:
mysql> CALL sys.ML_MODEL_IMPORT(@onnx_encode, NULL, 'onnx_test'); -
An example that exports a model to a table, switches users, and then imports the model from that table:
mysql> CALL sys.ML_MODEL_EXPORT(@iris_model, 'ML_SCHEMA_user1.model_export'); Query OK, 0 rows affected (0.06 sec) mysql> SHOW CREATE TABLE ML_SCHEMA_user1.model_export; +--------------+--------------------------------------------------------------------+ | Table | Create Table | +--------------+--------------------------------------------------------------------+ | model_export | CREATE TABLE `model_export` ( `chunk_id` int NOT NULL AUTO_INCREMENT, `model_object` longtext, `model_metadata` json DEFAULT NULL, PRIMARY KEY (`chunk_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +--------------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) # switch to user2 mysql> CALL sys.ML_MODEL_IMPORT(NULL, JSON_OBJECT('schema', 'ML_SCHEMA_user1', 'table', 'model_export'), @iris_export); Query OK, 0 rows affected (0.19 sec) mysql> CALL sys.ML_MODEL_LOAD(@iris_export, NULL); Query OK, 0 rows affected (0.63 sec) mysql> SELECT model_object, model_object_size FROM ML_SCHEMA_user2.MODEL_CATALOG WHERE model_handle=@iris_export; +--------------+-------------------+ | model_object | model_object_size | +--------------+-------------------+ | NULL | 348954 | +--------------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT chunk_id, LENGTH(model_object) FROM ML_SCHEMA_user2.model_object_catalog WHERE model_handle=@iris_export; +----------+----------------------+ | chunk_id | LENGTH(model_object) | +----------+----------------------+ | 1 | 348954 | +----------+----------------------+ 1 row in set (0.00 sec) -
An example that imports a model in ONNX format from a table:
mysql> DROP TABLE IF EXISTS model_table; mysql> CREATE TABLE model_table ( chunk_id INT AUTO_INCREMENT PRIMARY KEY, model_object LONGTEXT NOT NULL, model_metadata JSON DEFAULT NULL); mysql> LOAD DATA INFILE '/onnx_examples/x00' INTO TABLE model_table CHARACTER SET binary FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' (model_object); Query OK, 1 row affected (34.96 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> LOAD DATA INFILE '/onnx_examples/x01' INTO TABLE model_table CHARACTER SET binary FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' (model_object); Query OK, 1 row affected (32.74 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> LOAD DATA INFILE '/onnx_examples/x02' INTO TABLE model_table CHARACTER SET binary FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' (model_object); Query OK, 1 row affected (11.90 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SET @model_metadata = JSON_OBJECT('task','classification','onnx_outputs_info',JSON_OBJECT('predictions_name','label','prediction_probabilities_name', 'probabilities'),'target_column_name','target'); mysql> UPDATE mlcorpus.model_table SET model_metadata=@model_metadata WHERE chunk_id=1; mysql> CALL sys.ML_MODEL_IMPORT(NULL, JSON_OBJECT('schema', 'mlcorpus', 'table', 'model_table'), @onnx_model); Query OK, 0 rows affected (18 min 7.29 sec) mysql> CALL sys.ML_MODEL_LOAD(@onnx_model, NULL); Query OK, 0 rows affected (6 min 51.37 sec) mysql> SELECT COUNT(*) FROM ML_SCHEMA_root.model_object_catalog WHERE model_handle=@onnx_model; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.01 sec) mysql> SELECT SUM(LENGTH(model_object)) FROM ML_SCHEMA_root.model_object_catalog WHERE model_handle=@onnx_model; +---------------------------+ | SUM(LENGTH(model_object)) | +---------------------------+ | 2148494845 | +---------------------------+ 1 row in set (57.36 sec)