This topic describes how to grant other users access to a model you create.
This topic has the following sections.
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.
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.
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
andoutput_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)
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.*
andML_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.
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 foruser1
.mysql> CALL sys.ML_MODEL_IMPORT (model_object, model_metadata, model_handle);
Replace
model_object
,model_metadata
, andmodel_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
andmodel_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
andLENGTH(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 andLENGTH(model_object)
is not 0.
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)
Review Machine Learning Use Cases to create machine learning models with sample datasets.