HeatWave Release Notes
This topic describes how to generate recommendations for similar items.
For known items, the output includes a list of predicted items that have similar ratings and are appreciated by similar users.
The predictions are expressed in cosine similarity, and range from 0, very dissimilar, to 1, very similar.
For a new item, there is no information to provide a prediction. This generates an error.
Review and complete the following tasks:
When you run ML_PREDICT_TABLE
to generate similar item recommendations, a default value of
three similar items are recommended. To change this value,
set the topk parameter.
As of MySQL 9.5.0, you have the option to include item and user metadata when generating predictions. These steps include that metadata in the command to generate predictions.
-
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_TABLEroutine.mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);Replace
table_name,model_handle, andoutput_table_namewith your own values. Addoptionsas needed.As of MySQL 9.4.1, 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_TABLEon the testing dataset previously created and sets thetopkparameter to 2, so only two similar items are generated.mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.similar_item_recommendations', JSON_OBJECT('recommend', 'items_to_items', 'topk', 2, 'user_metadata', JSON_OBJECT('table_name', 'recommendation_data.users'), 'item_metadata', JSON_OBJECT('table_name', 'recommendation_data.items')));Where:
recommendation_data.testing_datasetis the fully qualified name of the input table that contains the data to generate predictions for (database_name.table_name).@modelis the session variable for the model handle.recommendation_data.similar_item_recommendationsis the fully qualified name of the output table with recommendations (database_name.table_name).JSON_OBJECT('recommend', 'items_to_items', 'topk', 2)sets the recommendation task to recommend similar items. A maximum of two similar items is set.'user_metadata', JSON_OBJECT('table_name', 'recommendation_data.users')specifies the table that has user metadata to use when generating predictions.'item_metadata', JSON_OBJECT('table_name', 'recommendation_data.items')specifies the table that has item metadata to use when generating predictions.
-
Query the output table to review the top two similar items for each item in the output table.
mysql> SELECT * from similar_item_recommendations; +---------+---------+--------+----------------------------------------------------------------------------+ | user_id | item_id | rating | ml_results | +---------+---------+--------+----------------------------------------------------------------------------+ | 1 | 2 | 4.0 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}} | | 1 | 4 | 7.0 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}} | | 1 | 6 | 1.5 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}} | | 1 | 8 | 3.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}} | | 10 | 18 | 1.5 | {"predictions": {"item_id": ["16", "12"], "similarity": [0.9869, 0.9464]}} | | 10 | 2 | 6.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}} | | 10 | 5 | 3.0 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}} | | 10 | 6 | 5.5 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}} | | 2 | 1 | 5.0 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} | | 2 | 3 | 8.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} | | 2 | 5 | 2.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}} | | 2 | 7 | 6.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} | | 3 | 18 | 7.0 | {"predictions": {"item_id": ["16", "12"], "similarity": [0.9869, 0.9464]}} | | 3 | 2 | 3.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}} | | 3 | 5 | 6.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}} | | 3 | 8 | 2.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}} | | 4 | 1 | 5.5 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} | | 4 | 3 | 8.5 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} | | 4 | 6 | 2.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}} | | 4 | 7 | 5.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} | | 5 | 12 | 5.0 | {"predictions": {"item_id": ["18", "16"], "similarity": [0.9464, 0.9454]}} | | 5 | 2 | 7.0 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}} | | 5 | 4 | 1.5 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}} | | 5 | 6 | 4.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}} | | 6 | 3 | 6.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} | | 6 | 5 | 1.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}} | | 6 | 7 | 4.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} | | 6 | 8 | 7.0 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}} | | 7 | 1 | 6.5 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} | | 7 | 4 | 3.0 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}} | | 7 | 5 | 5.5 | {"predictions": {"item_id": ["16", "2"], "similarity": [0.9036, 0.8586]}} | | 7 | 9 | 8.0 | {"predictions": {"item_id": ["1", "4"], "similarity": [0.7721, 0.6838]}} | | 8 | 2 | 8.5 | {"predictions": {"item_id": ["14", "10"], "similarity": [0.9831, 0.965]}} | | 8 | 4 | 2.5 | {"predictions": {"item_id": ["9", "6"], "similarity": [0.6838, 0.6444]}} | | 8 | 6 | 5.0 | {"predictions": {"item_id": ["8", "17"], "similarity": [0.8991, 0.8412]}} | | 8 | 9 | 3.5 | {"predictions": {"item_id": ["1", "4"], "similarity": [0.7721, 0.6838]}} | | 9 | 1 | 5.0 | {"predictions": {"item_id": ["15", "17"], "similarity": [0.8462, 0.7966]}} | | 9 | 3 | 8.0 | {"predictions": {"item_id": ["19", "13"], "similarity": [0.9826, 0.8851]}} | | 9 | 7 | 2.5 | {"predictions": {"item_id": ["11", "15"], "similarity": [0.6959, 0.6724]}} | | 9 | 8 | 5.5 | {"predictions": {"item_id": ["6", "17"], "similarity": [0.8991, 0.7942]}} | +---------+---------+--------+----------------------------------------------------------------------------+ 40 rows in set (0.0401 sec)Review the recommended similar items in the
ml_resultscolumn next toitem_id. For example, for item 2, items 14 and 10 are the top items predicted to be most similar. Review the similarity values in theml_resultscolumn next tosimilarityto review the how similar each item is. For example, item 14 has a similarity value of 0.9831 to item 2, and item 10 has a similarity value of 0.965.
-
Learn how to generate different types of recommendations:
Learn how to Score a Recommendation Model.