MySQL :: MySQL AI 9.4 :: 4.6.5.3 Training a Recommendation Model

Before You Begin

Review and complete all the tasks to Prepare Data for a Recommendation Model.

Requirements for Recommendation Training

Define the following as required to train a recommendation model.

Set the task parameter to recommendation to train a recommendation model.
users: Specifies the column name corresponding to the user IDs. Values in this column must be in a STRING data type, otherwise an error is returned during training.
items: Specifies the column name corresponding to the item IDs. Values in this column must be in a STRING data type, otherwise an error is returned during training.

If the users or items column contains NULL values, the corresponding rows are dropped and are not be considered during training.

Options for Recommendation Models with Explicit Feedback

Define the following JSON options to train a recommendation model with explicit feedback. To learn more about recommendation models, see Recommendation Model Types.

feedback: Set to explicit. If not set, the default value is explicit.

Options for Recommendation Models with Implicit Feedback

Define the following JSON options to train a recommendation model with implicit feedback. To learn more about recommendation models, see Recommendation Model Types.

feedback: Set to implicit.
feedback_threshold: The feedback threshold for a recommendation model that uses implicit feedback. It represents the threshold required to be considered positive feedback. For example, if numerical data records the number of times users interact with an item, you might set a threshold with a value of 3. This means users would need to interact with an item more than three times to be considered positive feedback.

Options for Content-Based Recommendation Models

Define the following JSON options to train a content-based recommendation model. To learn more about recommendation models, see Recommendation Model Types.

item_metadata: Defines the table that has item description. It is a JSON object that can have the table_name option as a key, which specifies the table that has item descriptions. This table must only have two columns: one corresponding to the item_id, and the other with a TEXT data type (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) that has the description of the item.
- table_name: To be used with the item_metadata option. It specifies the table name that has item descriptions. It must be a string in a fully qualified format (schema_name.table_name) that specifies the table name.

Unsupported Routines

You cannot run the following routines for a trained recommendation model:

Training the Model

Train the model with the ML_TRAIN routine and use the training_data table previously created. Before training the model, it is good practice to define the model handle instead of automatically creating one. See Defining Model Handle.

Optionally, 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 @model='recommendation_use_case';
```
The model handle is set to recommendation_use_case.
Run the ML_TRAIN routine.
```
mysql> CALL sys.ML_TRAIN('table_name', 'target_column_name', JSON_OBJECT('task', 'task_name'), model_handle);
```
Replace table_name, target_column_name, task_name, and model_handle with your own values.

The following example runs ML_TRAIN on the training dataset previously created.
```
mysql> CALL sys.ML_TRAIN('recommendation_data.training_dataset', 'rating', 
                          JSON_OBJECT('task', 'recommendation', 
                                      'users', 'user_id', 
                                      'items', 'item_id'), @model);
```
Where:
- recommendation_data.training_dataset is the fully qualified name of the table that contains the training dataset (database_name.table_name).
- rating is the name of the target column, which contains ground truth values (item ratings).
- JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id') specifies the machine learning task type and defines the users and items columns. Since no model type is defined, the default value of a recommendation model using explicit feedback is trained.
- @model is the session variable previously set that defines the model handle to the name defined by the user: recommendation_use_case. If you do not define the model handle before training the model, the model handle is automatically generated, and the session variable only stores the model handle for the duration of the connection. User variables are written as @var_name. Any valid name for a user-defined variable is permitted. See Work with Model Handles to learn more.

When the training operation finishes, the model handle is assigned to the @model session variable, and the model is stored in the model catalog. View the entry in the model catalog with the following query. Replace user1 with your MySQL account name.

mysql> SELECT model_id, model_handle, train_table_name FROM ML_SCHEMA_user1.MODEL_CATALOG  WHERE model_handle = 'recommendation_use_case';
+----------+-------------------------+--------------------------------------+
| model_id | model_handle            | train_table_name                     |
+----------+-------------------------+--------------------------------------+
|        5 | recommendation_use_case | recommendation_data.training_dataset |
+----------+-------------------------+--------------------------------------+

What's Next

Learn how to Generate Predictions for a Recommendation Model.
Review additional Syntax Examples for Recommendation Training.