HeatWave User Guide  /  ...  /  ML_MODEL_IMPORT

10.2.4 ML_MODEL_IMPORT

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.

ML_MODEL_IMPORT Overview

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 NULL

    See Model Metadata.

The table must meet the following criteria:

  • There must be only one row in the table with chunk_id = 1.

  • The model_metadata corresponding to chunk_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.

ML_MODEL_IMPORT Syntax

  • As of MySQL 9.0.0, ML_MODEL_IMPORT can import a model from a table as well as a a pre-processed model object. This uses an alternative syntax for model_metadata:

  • Before MySQL 9.0.0, ML_MODEL_IMPORT can 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']
}

ML_MODEL_IMPORT Parameters

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 to NULL for MySQL HeatWave AutoML to generate a unique model handle See Work with Model Handles.

Syntax Examples

  • 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)