MySQL :: HeatWave User Guide :: 6.9.2 Work with Model Handles

HeatWave User Guide / ... / Work with Model Handles

6.9.2 Work with Model Handles

When ML_TRAIN trains a model, you have the option to specify a name for the model, which is the model handle. If you do not specify a model handle name, a model handle is automatically generated that is based on the database name, input table name, the user name training the table, and a unique numerical identifier. You must use model handles to run MySQL HeatWave AutoML routines. All model handles must be unique in the model catalog.

This topic has the following sections.

Before You Begin

Review the The Model Catalog.

Model Handles Overview

If you set the model handle name to a session variable before training a model, the model handle takes that name. Otherwise, a unique model handle is automatically generated. To set your own model name, see Define Model Handle. The model handle is stored temporarily in a user-defined session variable specified in the ML_TRAIN call. In the following example, @census_model is defined as the model handle session variable with no set model handle name:

mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task', 'classification'), @census_model);

While the connection used to run ML_TRAIN remains active, that connection can retrieve the automatically generated model handle by querying the session variable. For example:

mysql> SELECT @census_model;
+--------------------------------------------------+
| @census_model                                    |
+--------------------------------------------------+
| census_classification_model                      |
+--------------------------------------------------+

Query the Model Handle

Since the session variable for a model handle is only valid for the current session, you can query the model handle name from the model catalog in new sessions.

The following example queries the model handle, the model owner, and the name of the training table from the model catalog table. Replace user1 with your own user name.

mysql> SELECT model_handle, model_owner, train_table_name FROM ML_SCHEMA_user1.MODEL_CATALOG;
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
| model_handle                                                      | model_owner | train_table_name                              |
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
| census_classification_model                                       | user1       | census_data.census_train                      |
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
18 rows in set (0.0014 sec)

Once you have the model handle, you can use it directly in AutoML routines instead of the session variable.

The following example runs ML_PREDICT_ROW and uses the model handle.

mysql> SELECT sys.ML_PREDICT_ROW(@row_input, 'census_classification_model', NULL);

Define Model Handle

Before training a model, it is good practice to define your own model handle instead of automatically generating one. This allows you to easily remember the model handle for future routines on the trained model instead of having to query it, or depending on the session variable that can no longer be used when the current connection terminates.

To define your own model handle:

Set the value of the session variable, which sets the model handle to this same value.
```
mysql> SET @variable = 'model_handle';
```
Replace @variable and model_handle with your own definitions. For example:
```
mysql> SET @census_model = 'census_classification_model';
```
When ML_TRAIN runs with this session variable, the model handle is set to census_test.

If you set a model handle that already appears in the model catalog, the ML_TRAIN routine returns an error.

Run the ML_TRAIN routine.

mysql> CALL sys.ML_TRAIN('table_name', 'target_column_name', JSON_OBJECT('task', 'task_name'), @variable);

Replace table_name, target_column_name, task_name, and variable with your own values.

The following example trains a model with the model handle variable previously set

mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', JSON_OBJECT('task', 'classification'), @census_model);

After training the model, query the model catalog to confirm the model handle you defined is there. Replace user1 with your own user name.

mysql> SELECT model_handle, model_owner, train_table_name FROM ML_SCHEMA_user1.MODEL_CATALOG;
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
| model_handle                                                      | model_owner | train_table_name                              |
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
| census_classification_model                                       | user1       | census_data.census_train                      |
+-------------------------------------------------------------------+-------------+-----------------------------------------------+
1 row in set (0.0014 sec)

Assign Session Variable to Model Handle

If you lose the session variable to a model handle due to a lost connection, you have the option of assigning a new session variable to a model handle in a new connection.

To assign a session variable to a model handle:

Set a variable to the model handle. If needed Query the Model Handle.
```
mysql> SET @my_model = 'model_handle';
```
The following example sets the @my_model session variable to a model handle.
```
mysql> SET @my_model = 'census_classification_model';
```

Confirm the session variable is assigned to the model handle by querying the session variable.

mysql> SELECT @my_model;
+-----------------------------------------------------+
| @my_model                                           |
+-----------------------------------------------------+
| census_classification_model                         |
+-----------------------------------------------------+

Alternatively, you can assign a session variable to the model handle for the most recently trained model.

Set a variable with the query to retrieve the most recent model handle by sorting with the build_timestamp parameter in the model catalog. Replace user1 with your own user name.
```
mysql> SET @variable = (SELECT model_handle FROM ML_SCHEMA_user1.MODEL_CATALOG ORDER BY build_timestamp DESC LIMIT 1);
```
The following example sets the latest_model variable.
```
mysql> SET @latest_model = (SELECT model_handle FROM ML_SCHEMA_user1.MODEL_CATALOG ORDER BY timestamp DESC LIMIT 1);
```

Confirm the session variable is assigned to the latest model handle by querying the session variable.

mysql> SELECT @latest_model;
+--------------------------+
| @latest_model            |
+--------------------------+
| recommendation_use_case4 |
+--------------------------+
1 row in set (0.0454 sec)

What's Next

Review how to Create a Machine Learning Model.
Review Machine Learning Use Cases to create machine learning models with sample datasets.

PREV HOME UP NEXT