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 and sets the topk parameter to 2, so only two users are recommended.

mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.user_recommendations', 
                                  JSON_OBJECT('recommend', 'users', 
                                              'topk', 2, 
                                              'user_metadata', JSON_OBJECT('table_name', 'recommendation_data.users'), 
                                              'item_metadata', JSON_OBJECT('table_name', 'recommendation_data.items')));

Where:

Query the output table to review the recommended top two users for each item in the output table.

mysql> SELECT * from user_recommendations;
+---------+---------+--------+-------------------------------------------------------------------+
| user_id | item_id | rating | ml_results                                                        |
+---------+---------+--------+-------------------------------------------------------------------+
| 1       | 2       |    4.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
| 1       | 4       |    7.0 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
| 1       | 6       |    1.5 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
| 1       | 8       |    3.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
| 10      | 18      |    1.5 | {"predictions": {"user_id": ["5", "10"], "rating": [3.74, 3.63]}} |
| 10      | 2       |    6.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
| 10      | 5       |    3.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
| 10      | 6       |    5.5 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
| 2       | 1       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
| 2       | 3       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
| 2       | 5       |    2.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
| 2       | 7       |    6.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
| 3       | 18      |    7.0 | {"predictions": {"user_id": ["5", "10"], "rating": [3.74, 3.63]}} |
| 3       | 2       |    3.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
| 3       | 5       |    6.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
| 3       | 8       |    2.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
| 4       | 1       |    5.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
| 4       | 3       |    8.5 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
| 4       | 6       |    2.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
| 4       | 7       |    5.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
| 5       | 12      |    5.0 | {"predictions": {"user_id": ["5", "10"], "rating": [3.29, 3.2]}}  |
| 5       | 2       |    7.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
| 5       | 4       |    1.5 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
| 5       | 6       |    4.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
| 6       | 3       |    6.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
| 6       | 5       |    1.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
| 6       | 7       |    4.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
| 6       | 8       |    7.0 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
| 7       | 1       |    6.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
| 7       | 4       |    3.0 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
| 7       | 5       |    5.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
| 7       | 9       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.34, 3.17]}}  |
| 8       | 2       |    8.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
| 8       | 4       |    2.5 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
| 8       | 6       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
| 8       | 9       |    3.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.34, 3.17]}}  |
| 9       | 1       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
| 9       | 3       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
| 9       | 7       |    2.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
| 9       | 8       |    5.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
+---------+---------+--------+-------------------------------------------------------------------+
40 rows in set (0.0476 sec)

Review the recommended users in the ml_results column next to user_id. For example, for item 2, users 6 and 5 are the top users predicted to like it. Review the ratings in the ml_results column to review the expected ratings for each recommended item. For example, user 6 is expected to rate item 2 with a value of 3.02, and user 5 with a value of 2.9.