HeatWave Release Notes
Use the ML_MODEL_IMPORT routine
to import a pre-trained model into your model catalog.
To learn how to use
ML_MODEL_IMPORT to share models,
see Share a
Model.
This topic has the following sections.
MySQL HeatWave AutoML supports the import of MySQL HeatWave AutoML and Open Neural Network Exchange (ONNX) format models. After import, all the MySQL 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
Import an
External ONNX Model to pre-process and then load the
model into a temporary table for import to MySQL HeatWave.
MySQL 9.0.0 introduces support for large models that changes
how MySQL HeatWave AutoML stores models. See
The Model Object Catalog Table.
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. MySQL HeatWave AutoML verifies models with the ONNX format, and stores them as ONNXv2.0.
The table to import 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 NULLSee Model Metadata.
The table must meet the following criteria:
There must be 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 stores the
model_metadata corresponding to
chunk_id = 1 in the model catalog, and
ignores the model_metadata from other rows.
If chunks in the
model_metadata corresponding to
chunk_id = 1 is not set, it is set to the
number of rows in the input table.
If ML_MODEL_IMPORT fails or is
cancelled, there is no change to the
MODEL_CATALOG and to the
model_object_catalog.
As of MySQL 9.0.0,
ML_MODEL_IMPORTcan import a model from a table as well as a a pre-processed model object. This uses an alternative syntax formodel_metadata:Before MySQL 9.0.0,
ML_MODEL_IMPORTcan only import a pre-processed model object.
mysql> CALL sys.ML_MODEL_IMPORT (model_object, model_metadata, model_handle);
model_metadata (model from a table): {
JSON_OBJECT("key","value"[,"key","value"] ...)
"key","value": {
['schema', 'database']
['table', 'table']
}
}
model_metadata (pre-processed model object): {
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']
}Set the following parameters:
-
model_object:To import a model from a table: Set to
NULL.To import a model object: Define the pre-processed model object.
-
model_metadata:-
To import a model from a table:
database: The name of the database.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 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 toNULLto generate a unique model handle. See Work with Model Handles.
-
An example that exports a model to a table, switches users, and then imports the model from that table. To learn more, see Share a Model.
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. To learn more, see Import an External ONNX Model.
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)