3.14.8 Model Handles

When ML_TRAIN trains a model, it generates a model handle, which is required when running other HeatWave AutoML routines. You can replace the generated model handle with a model handle of your choice, which must be unique in the model catalog.

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:

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

To use your own model handle instead of a generated one, set the value of the session variable before calling the ML_TRAIN routine, like this:

mysql> SET @census_model = 'census_test';
mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', NULL, @census_model);

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

While the connection used to run ML_TRAIN remains active, that connection can retrieve the model handle by querying the session variable; for example:

mysql> SELECT @census_model;
+--------------------------------------------------+
| @census_model                                    |
+--------------------------------------------------+
| heatwaveml_bench.census_train_user1_1636729526   |
+--------------------------------------------------+

Note

The format of the generated model handle is subject to change.

While the session variable remains populated with the model handle, it can be specified in place of the model handle when running other ML_* routines. However, once the connection is terminated, the session variable data is lost. In this case, you can look up the model handle by querying the model catalog table; for example:

mysql> SELECT model_handle, model_owner, train_table_name
          FROM ML_SCHEMA_user1.MODEL_CATALOG;
+------------------------------------------------+-------------+-------------------------------+
| model_handle                                   | model_owner | train_table_name              |
+------------------------------------------------+-------------+-------------------------------+
| heatwaveml_bench.census_train_user1_1636729526 |     user1   | heatwaveml_bench.census_train |
+------------------------------------------------+-------------+-------------------------------+

You can specify the model handle in ML_ROUTINE_* calls directly; for example:

mysql> SELECT sys.ML_PREDICT_ROW(@row_input, 'heatwaveml_bench.census_train_user1_1636729526');

Alternatively, you can reassign a model handle to a session variable; for example:

• To assign a model handle to a session variable named @my_model:

  mysql> SET @my_model = 'heatwaveml_bench.census_train_user1_1636729526';

• To assign a model handle to a session variable named @my_model for the most recently trained model:

  mysql> SET @my_model = (SELECT model_handle FROM ML_SCHEMA_user1.MODEL_CATALOG 
            ORDER BY model_id DESC LIMIT 1);

  The most recently trained model is the last model inserted into the MODEL_CATALOG table. It has the most recently assigned model_id, which is a unique auto-incrementing numeric identifier.