Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb


MySQL HeatWave User Guide  /  ...  /  Model Handles

3.7.7 Model Handles

When ML_TRAIN trains a model, it generates a model handle, which is required when running other HeatWave ML routines. 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);

While the connection used to run ML_TRAIN remains active, that connection can retrieve the generated 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 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:

    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:

    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.