PDF (US Ltr)
- 1.4Mb
PDF (A4)
- 1.4Mb
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
Grant Other Users
Access to a Model.
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 to use with AutoML.
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 canceled, there is no change to the
MODEL_CATALOG and to the
model_object_catalog.
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": {
['database', 'database']
['table', 'table']
}
}
model_metadata (preprocessed 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 preprocessed 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 toNULLfor MySQL HeatWave AutoML to generate a unique model handle See Work with Model Handles.
-
An example that imports a MySQL 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)