6.9.6 Share a Model

Before You Begin

The ML_MODEL_EXPORT routine is available as of MySQL 9.0.0. Review ML_MODEL_EXPORT and ML_MODEL_IMPORT for more information on required versions.
Review MySQL HeatWave AutoML Privileges.
To follow along with the file used in the tasks, see Use MySQL HeatWave AutoML with Lakehouse.

Share Your Models

To share a model you created, you can use the ML_MODEL_EXPORT and ML_MODEL_IMPORT routines. ML_MODEL_EXPORT exports the model to share to a user-defined table that both users need the required privileges to access. ML_MODEL_IMPORT imports the model to the user's model catalog. The other user can then run AutoML commands on the imported model.

In the following tasks, the admin user gives access to their model to the user1 user. The trained table, bank_train, is in the bank_marketing database. To follow along with the file used, see Use MySQL HeatWave AutoML with Lakehouse.

Export the Model to Share

The admin user needs to export the model to share to a user-defined table that both users can access. In this use case, the user exports the model to their own model catalog.

As the admin user, train and load the model to export. See Train a Model and Load a Model.
Export the model to a table in the model catalog. Use the assigned session variable for the model handle. If you need to query the model handle, see Work with Model Handles.
```
mysql> CALL sys.ML_MODEL_EXPORT (model_handle, output_table_name);
```
Replace model_handle and output_table_name with your own values. For example:
```
mysql> CALL sys.ML_MODEL_EXPORT(@bank_model, 'ML_SCHEMA_admin.model_export');
```
Where:
- @bank_model is the assigned session variable for the model handle of the trained model.
- ML_SCHEMA_admin.model_export is the fully qualified name of the table that contains the training dataset (schema_name.table_name).

Run the SHOW CREATE TABLE command to confirm the table was created with the recommended parameters for importing. See ML_MODEL_IMPORT to learn more.

mysql> SHOW CREATE TABLE ML_SCHEMA_admin.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.0527 sec)

Set Up Other User with Required Privileges

The admin user needs to grant the required privileges to user1, so that user can access exported model and import it into their own model catalog.

If not done already, create the other user account (user1). See CREATE USER.
Run these commmands to grant the required privileges to the other user, so they can access the following:
- MySQL HeatWave AutoML routines on the MySQL sys schema.
- The model catalog for both users.
- The database with the trained model.
- External Lakehouse tables with the machine learning model dataset.
See MySQL HeatWave AutoML Privileges to learn more.
```
mysql> GRANT SELECT, EXECUTE ON sys.* TO 'user1'@'%';
mysql> GRANT SELECT, ALTER, INSERT, CREATE, UPDATE, DROP, GRANT OPTION ON ML_SCHEMA_user1.* TO 'user1'@'%';
mysql> GRANT SELECT, ALTER, INSERT, CREATE, UPDATE, DROP, GRANT OPTION ON ML_SCHEMA_admin.* TO 'user1'@'%';  
mysql> GRANT SELECT, ALTER, INSERT, CREATE, UPDATE, DROP, GRANT OPTION ON bank_marketing.* TO 'user1'@'%';
mysql> GRANT SELECT ON performance_schema.rpd_tables TO 'user1'@'%';
mysql> GRANT SELECT ON performance_schema.rpd_table_id TO 'user1'@'%';
mysql> GRANT SELECT ON performance_schema.rpd_query_stats TO 'user1'@'%';
mysql> GRANT SELECT ON performance_schema.rpd_ml_stats TO 'user1'@'%';
```
Where:
- ML_SCHEMA_user1.* and ML_SCHEMA_user1.* gives access to the model catalog for both users.
- bank_marketing is the database that contains the trained table.
- The remaining tables are required if the trained table is a Lakehouse external table.

Import Shared Model

The user1 user can now import the exported model to their own model catalog.

Log in to the DB system as the other user (user1).
Import the model the admin user previously exported into the model catalog for user1.
```
mysql> CALL sys.ML_MODEL_IMPORT (model_object, model_metadata, model_handle);
```
Replace model_object, model_metadata, and model_handle with your own values. For example:
```
mysql> CALL sys.ML_MODEL_IMPORT(NULL, JSON_OBJECT('schema', 'ML_SCHEMA_admin', 'table', 'model_export'), @bank_export);
```
- NULL means that a model from a table is imported, and not a model object.
- JSON_OBJECT sets key-value pairs for the database and table of the exported table to import.
- @bank_export is the assigned session variable for the imported model handle.
Load the imported model. Use the assigned session variable set for the imported model handle in the previous command.
```
mysql> CALL sys.ML_MODEL_LOAD(@bank_export, NULL);
```

Optionally, query model_object and model_object_size from the model catalog for the loaded model to confirm the model imported successfully.

mysql> SELECT model_object, model_object_size FROM ML_SCHEMA_user1.MODEL_CATALOG WHERE model_handle=@bank_export;
+--------------+-------------------+
| model_object | model_object_size |
+--------------+-------------------+
| NULL         |            331860 |
+--------------+-------------------+
1 row in set (0.0478 sec)

Confirm the model_object_size is not 0.

Optionally, query chunk_id and LENGTH(model_object) from the model object catalog for the loaded model to confirm the model imported successfully.

mysql> SELECT chunk_id, LENGTH(model_object) FROM ML_SCHEMA_user1.model_object_catalog WHERE model_handle=@bank_export;
+----------+----------------------+
| chunk_id | LENGTH(model_object) |
+----------+----------------------+
|        1 |               331860 |
+----------+----------------------+
1 row in set (0.0465 sec)

Confirm the chunk_id value is 1 and LENGTH(model_object) is not 0.

Run AutoML Routines on Imported Model

Confirm the user1 user can run AutoML commands. The following example generates a table of predictions for the imported model.

mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

Replace table_name, model_handle, output_table_name), and options with your own values. For example:

mysql> CALL sys.ML_PREDICT_TABLE('bank_marketing.bank_train', @bank_export, 'bank_marketing.bank_predictions', NULL);

Where:

bank_marketing.bank_train is the fully qualified name of the table that contains the training dataset (schema_name.table_name).
@bank_export is the assigned session variable for the imported model handle.
bank_marketing.bank_predictions is the fully qualified name of the output table that contains the predictions (schema_name.table_name).

Optionally, use the database with the output table and query a sample.

mysql> USE bank_marketing;
mysql> SELECT * FROM bank_predictions limit 5;
+-------------------+-----+--------------+---------+-----------+---------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+-----+------------+-------------------------------------------------------------------------------+
| _4aad19ca6e_pk_id | age | job          | marital | education | default | balance | housing | loan | contact   | day | month | duration | campaign | pdays | previous | poutcome | y   | Prediction | ml_results                                                                    |
+-------------------+-----+--------------+---------+-----------+---------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+-----+------------+-------------------------------------------------------------------------------+
|                 1 |  30 | management   | single  | tertiary  | no      |     149 | yes     | no   | unknown   |   3 | jun   |      220 |        2 |    -1 |        0 | unknown  | no  | no         | {"predictions": {"y": "no"}, "probabilities": {"no": 0.9965, "yes": 0.0035}}  |
|                 2 |  46 | blue-collar  | married | secondary | no      |   -1400 | yes     | no   | telephone |   6 | may   |      309 |        3 |   355 |        4 | failure  | no  | no         | {"predictions": {"y": "no"}, "probabilities": {"no": 0.9368, "yes": 0.0632}}  |
|                 3 |  33 | entrepreneur | married | secondary | no      |    -118 | yes     | yes  | unknown   |  27 | may   |      421 |        3 |    -1 |        0 | unknown  | no  | no         | {"predictions": {"y": "no"}, "probabilities": {"no": 0.9593, "yes": 0.0407}}  |
|                 4 |  43 | blue-collar  | married | secondary | no      |    2160 | no      | no   | cellular  |   8 | sep   |      261 |        1 |    98 |        1 | success  | yes | yes        | {"predictions": {"y": "yes"}, "probabilities": {"no": 0.1266, "yes": 0.8734}} |
|                 5 |  38 | management   | married | tertiary  | no      |    3452 | no      | no   | cellular  |  13 | aug   |      132 |        2 |    -1 |        0 | unknown  | no  | no         | {"predictions": {"y": "no"}, "probabilities": {"no": 0.969, "yes": 0.031}}    |
+-------------------+-----+--------------+---------+-----------+---------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+-----+------------+-------------------------------------------------------------------------------+
5 rows in set (0.0425 sec)

What's Next

Review Machine Learning Use Cases to create machine learning models with sample datasets.