3.11.1 Training a Recommendation Model

The recommendation model introduces two mandatory options: users and items.

Run the ML_TRAIN routine to create a recommendation model, and use the following JSON options:

• task: recommendation: Specifies the machine learning task.

• users: Specifies the column name corresponding to the user ids.

• items: Specifies the column name corresponding to the item ids.

• feedback: The type of feedback for a recommendation model, explicit, the default, or implicit.

• feedback_threshold: The feedback threshold for a recommendation model that uses implicit feedback.

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

HeatWave AutoML does not support recommendation tasks with a text column.

See Section 3.5, “Training a Model”, and for full details of all the options, see ML_TRAIN.

Syntax Examples for Explicit Feedback

• An ML_TRAIN example that specifies the recommendation task type.

  mysql> CALL sys.ML_TRAIN('table_train', 'target_column_feature', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_column_feature', 
            'items', 'item_column_feature'), 
            @model);

• An ML_TRAIN example that specifies the SVD model type.

  mysql> CALL sys.ML_TRAIN('mlcorpus.foursquare_NYC_train', 'rating', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'model_list', JSON_ARRAY('SVD')), 
            @model);
  Query OK, 0 rows affected (11.31 sec)
  
  mysql> SELECT model_type FROM ML_SCHEMA_root.MODEL_CATALOG 
            WHERE model_handle=@model;
  +------------+
  | model_type |
  +------------+
  | SVD        |
  +------------+
  1 row in set (0.00 sec)

• An ML_TRAIN example that specifies the SVDpp model type.

  mysql> CALL sys.ML_TRAIN('mlcorpus.foursquare_NYC_train', 'rating', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'model_list',  JSON_ARRAY('SVDpp')), 
            @model);
  Query OK, 0 rows affected (13.97 sec)
  
  mysql> SELECT model_type FROM ML_SCHEMA_root.MODEL_CATALOG 
            WHERE model_handle=@model;
  +------------+
  | model_type |
  +------------+
  | SVDpp      |
  +------------+
  1 row in set (0.00 sec)

• An ML_TRAIN example that specifies the NMF model type.

  mysql> CALL sys.ML_TRAIN('mlcorpus.foursquare_NYC_train', 'rating', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'model_list', JSON_ARRAY('NMF')), 
            @model);
  Query OK, 0 rows affected (12.28 sec)
  
  mysql> SELECT model_type FROM ML_SCHEMA_root.MODEL_CATALOG 
            WHERE model_handle=@model;
  +------------+
  | model_type |
  +------------+
  | NMF        |
  +------------+
  1 row in set (0.00 sec)

• An ML_TRAIN example that specifies three models for the model_list option.

  mysql> SET @allowed_models = JSON_ARRAY('SVD', 'SVDpp', 'NMF');
  
  mysql> CALL sys.ML_TRAIN('mlcorpus.foursquare_NYC_train', 'rating',
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'model_list', CAST(@allowed_models AS JSON)), 
            @model);
  Query OK, 0 rows affected (14.88 sec)
  
  mysql> SELECT model_type FROM ML_SCHEMA_root.MODEL_CATALOG 
            WHERE model_handle=@model;
  +------------+
  | model_type |
  +------------+
  | SVD        |
  +------------+
  1 row in set (0.00 sec)

• An ML_TRAIN example that specifies five models for the exclude_model_list option.

  mysql> SET @exclude_models= JSON_ARRAY('NormalPredictor', 'Baseline', 'SlopeOne', 'CoClustering', 'SVD');
  
  mysql> CALL sys.ML_TRAIN('mlcorpus.foursquare_NYC_train', 'rating', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'exclude_model_list', CAST(@exclude_models AS JSON)), 
            @model);
  Query OK, 0 rows affected (14.71 sec)
  
  mysql> SELECT model_type FROM ML_SCHEMA_root.MODEL_CATALOG 
            WHERE model_handle=@model;
  +------------+
  | model_type |
  +------------+
  | SVDpp      |
  +------------+
  1 row in set (0.00 sec)

Syntax Examples for Implicit Feedback

• An ML_TRAIN example that specifies the recommendation task with implicit feedback.

  mysql> SELECT * FROM training_table LIMIT 10;
  +---------+---------+--------+
  | user_id | item_id | rating |
  +---------+---------+--------+
  | 836     | 226     |      1 |
  | 3951    | 14918   |      1 |
  | 1048    | 2498    |      1 |
  | 4312    | 2559    |      1 |
  | 4882    | 12930   |      1 |
  | 3226    | 8400    |      1 |
  | 3455    | 5120    |      1 |
  | 830     | 12537   |      1 |
  | 4525    | 791     |      1 |
  | 2303    | 14243   |      1 |
  +---------+---------+--------+
  
  mysql> CALL sys.ML_TRAIN('mlcorpus.training_table', 'rating', 
            JSON_OBJECT('task', 'recommendation', 
            'users', 'user_id', 
            'items', 'item_id', 
            'feedback', 'implicit'), 
            @model);
  Query OK, 0 rows affected (2 min 13.6415 sec)