If not already done, load the model. You can use the session variable for the model that is valid for the duration of the connection. Alternatively, you can use the model handle previously set. For the option to set the user name, you can set it to NULL.

The following example uses the session variable.

mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

The following example uses the model handle.

mysql> CALL sys.ML_MODEL_LOAD('recommendation_use_case', NULL);

Make predictions for the test dataset by using the ML_PREDICT_TABLE routine.

mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

Replace table_name, model_handle, and output_table_name with your own values. Add options as needed.

You have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.

The following example runs ML_PREDICT_TABLE on the testing dataset previously created.

mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.recommendations', NULL);

Where:

Query the output table to review the predicted ratings that users give for each user-item pair.

mysql> SELECT * from recommendations;
+---------+---------+--------+-----------------------------------+
| user_id | item_id | rating | ml_results                        |
+---------+---------+--------+-----------------------------------+
| 1       | 2       |    4.0 | {"predictions": {"rating": 2.71}} |
| 1       | 4       |    7.0 | {"predictions": {"rating": 3.43}} |
| 1       | 6       |    1.5 | {"predictions": {"rating": 1.6}}  |
| 1       | 8       |    3.5 | {"predictions": {"rating": 2.71}} |
| 10      | 18      |    1.5 | {"predictions": {"rating": 3.63}} |
| 10      | 2       |    6.5 | {"predictions": {"rating": 2.82}} |
| 10      | 5       |    3.0 | {"predictions": {"rating": 3.09}} |
| 10      | 6       |    5.5 | {"predictions": {"rating": 1.67}} |
| 2       | 1       |    5.0 | {"predictions": {"rating": 2.88}} |
| 2       | 3       |    8.0 | {"predictions": {"rating": 4.65}} |
| 2       | 5       |    2.5 | {"predictions": {"rating": 3.09}} |
| 2       | 7       |    6.5 | {"predictions": {"rating": 2.23}} |
| 3       | 18      |    7.0 | {"predictions": {"rating": 3.25}} |
| 3       | 2       |    3.5 | {"predictions": {"rating": 2.53}} |
| 3       | 5       |    6.5 | {"predictions": {"rating": 2.77}} |
| 3       | 8       |    2.5 | {"predictions": {"rating": 2.53}} |
| 4       | 1       |    5.5 | {"predictions": {"rating": 3.36}} |
| 4       | 3       |    8.5 | {"predictions": {"rating": 5.42}} |
| 4       | 6       |    2.0 | {"predictions": {"rating": 1.94}} |
| 4       | 7       |    5.5 | {"predictions": {"rating": 2.61}} |
| 5       | 12      |    5.0 | {"predictions": {"rating": 3.29}} |
| 5       | 2       |    7.0 | {"predictions": {"rating": 2.9}}  |
| 5       | 4       |    1.5 | {"predictions": {"rating": 3.68}} |
| 5       | 6       |    4.0 | {"predictions": {"rating": 1.72}} |
| 6       | 3       |    6.0 | {"predictions": {"rating": 4.98}} |
| 6       | 5       |    1.5 | {"predictions": {"rating": 3.31}} |
| 6       | 7       |    4.5 | {"predictions": {"rating": 2.4}}  |
| 6       | 8       |    7.0 | {"predictions": {"rating": 3.03}} |
| 7       | 1       |    6.5 | {"predictions": {"rating": 3.18}} |
| 7       | 4       |    3.0 | {"predictions": {"rating": 3.95}} |
| 7       | 5       |    5.5 | {"predictions": {"rating": 3.41}} |
| 7       | 9       |    8.0 | {"predictions": {"rating": 3.17}} |
| 8       | 2       |    8.5 | {"predictions": {"rating": 2.6}}  |
| 8       | 4       |    2.5 | {"predictions": {"rating": 3.3}}  |
| 8       | 6       |    5.0 | {"predictions": {"rating": 1.54}} |
| 8       | 9       |    3.5 | {"predictions": {"rating": 2.65}} |
| 9       | 1       |    5.0 | {"predictions": {"rating": 2.99}} |
| 9       | 3       |    8.0 | {"predictions": {"rating": 4.83}} |
| 9       | 7       |    2.5 | {"predictions": {"rating": 2.32}} |
| 9       | 8       |    5.5 | {"predictions": {"rating": 2.93}} |
+---------+---------+--------+-----------------------------------+
40 rows in set (0.0459 sec)

Review each user_id and item_id pair and the respective rating value in the ml_results column. For example, in the first row, user 1 is expected to give item 2 a rating of 2.71.

The values in the rating column refer to the past rating the user_id gave to the item_id. They are not relevant to the values in ml_results.

Alternatively, if you do not want to generate an entire table of predicted ratings or rankings, you can run ML_PREDICT_ROW to specify a user-item pair.

mysql> SELECT sys.ML_PREDICT_ROW(input_data, model_handle), [options]);

Replace input_data and model_handle with your own values. Add options as needed.

The following example runs ML_PREDICT_ROW and specifies user 2 and item 1.

mysql> SELECT sys.ML_PREDICT_ROW('{"user_id":"2", "item_id": "1"}', @model, NULL);
+-----------------------------------------------------------------------------------+
| sys.ML_PREDICT_ROW('{"user_id":"2", "item_id": "1"}', @model, NULL)               |
+-----------------------------------------------------------------------------------+
| {"item_id": "1", "user_id": "2", "ml_results": {"predictions": {"rating": 2.88}}} |
+-----------------------------------------------------------------------------------+
1 row in set (0.8726 sec)

The predicted rating of 2.88 for the user-item pair is the same as the one in the output table previously created.