MySQL AI 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
Grant Other Users
Access to a Model.
MySQL AI supports the import of AutoML and Open Neural Network Exchange (ONNX) format models. After import, all the 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 to 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)