4.6.5.3 Training a Recommendation Model

After preparing the data for a recommendation model, you can train the model.

This topic has the following sections.

• Before You Begin

• Requirements for Recommendation Training

• Options for All Recommendation Model Types

• Recommendation Training Models

• Options for Recommendation Models with Explicit Feedback

• Options for Recommendation Models with Implicit Feedback

• Options for Content-Based Recommendation Models

• Unsupported Routines

• Training the Model

• What's Next

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 All Recommendation Model Types

See Common ML_TRAIN Options to view available options for training recommendation models.

Recommendation Training Models

The default recommendation training model is the TwoTower model with Pytorch. You cannot add the TwoTower model with the model_list option. Adding the model generates an error. The TwoTower model is already set as the default recommendation model if model_list is not specified. Review the list of available Recommendation Models.

The TwoTower model is a deep learning recommender system pipeline that provides enhanced quality and faster speed than other recommender training models. It uses user-item interactions and user-item features to train embedding vectors for users and items, which allows for quicker predictions. To enable faster predictions, the model generates tables with embeddings for users and items. The tables are used as a representation for each user and item. The model also generates a table of interactions that stores each interaction between a user and item. You can view these tables in the ML_SCHEMA_MySQL_username database. For example, ML_SCHEMA_user1.abc123_users, ML_SCHEMA_user1.abc123_items, and ML_SCHEMA_user1.abc123_interactions. Providing item_metadata and user_metadata is optional for the TwoTower model.

To review limitations related to the TwoTower model, see Routine and Query Limitations and Other Limitations.

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 descriptions. It is a JSON object that has the table_name option as a key, which specifies the table that has item descriptions. One column must be the same as the item_id in the input table.

• user_metadata: Defines the table that has user descriptions. It is a JSON object that has the table_name option as a key, which specifies the table that has user descriptions. One column must be the same as the user_id in the input table.

  • table_name: To be used with the item_metadata and user_metadata options. It specifies the table name that has item or user 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:

• ML_EXPLAIN

• ML_EXPLAIN_ROW

• ML_EXPLAIN_TABLE

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.

1. 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.

2. 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.

3. 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 |
   +----------+-------------------------+--------------------------------------+

4. If the model is trained with the TwoTower recommendation model, you can view the tables with embeddings and the table of interactions in the ML_SCHEMA_MySQL_username database. To view the names of the generated tables, run the following query. Replace user1 with your MySQL account name.

   mysql> SHOW TABLES FROM ML_SCHEMA_user1;
   +---------------------------+
   | Tables_in_ML_SCHEMA_admin |
   +---------------------------+
   | 3e094aa4ba_interactions   |
   | 3e094aa4ba_items          |
   | 3e094aa4ba_users          |
   | MODEL_CATALOG             |
   | MODEL_CATALOG_BACKUP_v3   |
   | catalog_version_v1        |
   | catalog_version_v2        |
   | catalog_version_v3        |
   | model_object_catalog      |
   +---------------------------+

5. Run the following queries to view a sample of each generated table.

   mysql> SELECT * FROM ML_SCHEMA_user1.3e094aa4ba_users LIMIT 5;
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | user_id | embedding_vector                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 1       | 0x543B13BDDD328D3D6B3F6CBEB56B39BEA75052BEAA25C5BDAE894A3D721013BE3324B23D59779D3DD35B8C3D0642643E1299073EE00EFB3D8B22AFBD2948A13DA3F1A3BDC83D28BEED5B753E30197D3D98FA59BEC7D9C2BECA80F8BD9AE9E9BAE8BFB03E460549BE5ABD17BB852DECBDB75E3CBD2D8B5EBE91550BBEC33994BD11EA29BEDD93CABD5DE6353E5833D43DD70610BE897B543EDDB9893D2A10B43D61C8E53D6C68B03D5AE1BCBD3DEB86BEB24BE83D906F66BDD714723EB293963EC814F6BD19EDD03D69239DBCC038F7BD4B9701BE993B30BECD4E673D99B6D9BD1F61353EE5B60E3E19EC8C3DFF572B3ED8905D3C7BD08CBDF5B978BD72EA93BC |
   | 10      | 0xC3551FBEDA3F34BE2FD6583EEC0BC13D650262BE860F1C3D09DBEC3CAFBD803D006CD5BD3C26A5BD5FB9FC3D685A67BE5C9DB43BD8C2A93DBF8428BE5627363D7C9FFABDE6BA323DDD8846BEDC774FBD5145073CE01456BDF7F697BD9715163C1D4C13BAA0EB873D096BEABC996B5B3DC4A29ABEF4F3D03D35E65FBC48A7B33D49CC23BE0835D33D28806BBDA9A2423E8FC7C4BB0D2302BE154323BE057E09BCDC4DC93DFA79B13B1BB09EBE45EA3FBEF0B6683ECE76FE3D18486B3CC1A37CBEF31C84BEA10E11BE133202BE091F3B3EC5C8F9BC5A24E1BDB1720BBE48631ABEBCCA393D0B46263E56A1953D2DA68ABE8A8D5EBBE5EA53BE0B36A7BE8DE2AABD |
   | 2       | 0x73A719BECAD5E33C9C71973E164E203D7C4635BEB7ECB53ADCA1803EF48F553E927B34BEEEFF8DBD32F474BE489D83BDEFF0A73AE5AB34BEFC9C083D1689833EC2903B3E2E9B023EDF9559BDC69357BEB06AFF3DEA74723D007D57BECC02903D2B124ABE048B3FBDD936D8BD0AB1393EF2C88DBD657D283E2A67203E07BA983E39F42B3DBCE4B5BD14B8A83ED816993D61E641BEF0833B3E970F40BDBA4687BCB7E49F3D7616043C205F64BD9D13F03D445223BE842F933E213905BE124E22BDC8794A3ED59E573D9E7101BD2502513E4234B03D721A62BDBBEC603E6EBF5A3E6412943D6FD69C3D4F6CD33C80A3013C84B399BE227348BED8E8E9BD537DDB3D |
   | 3       | 0x520298BEA7516EBE6A7C3C3CAC9CD6BD2296C63DF76ECC3DDF1C01BE79B0193D75F381BED1D4B0BD5018633D281B04BC4ACC5DBDD4A6013E355D76BD7BEE6BBEC1D6E7BD9C81853DF466913EB1F9C53D666C44BEF95E4E3DDFFC82BD22B0AF3D27D30F3E85D6903DD0DA2FBD50F1A1BBD26FE4BD5F942FBEF644F4BC7CD9DA3DDB198EBD90B4AE3D17A00DBDDB2CE73DCB2F803D3C8EDDBD98682E3E9012DC3C1826763EC43578BC545761BD3DDEFABDC5B8103E3B97F13DD1FF6E3EB88DA3BC7D045EBEF42721BE0EC60ABE45A898BD3B0F56BE193E49BE21CD01BD0E04D4BDA498F83D6A69673C83EB8C3EEA1DD5BD6524063E48704F3D20C306BC5D6CF33D |
   | 4       | 0x992518BE74C5FDBD4AB0C73DB422B33D45F74EBE81F25FBE653D5A3E7F4134BDDAD13B3ED95E7E3DDA11B33D13EEFABDFE46A63DEFA242BE6C065D3E70F5983D63F96F3E71EE583D07C8C3BE8CDED7BDE8354E3D8FB349BE14CBD23CC258CEBD9AE865BD89A7A13D70FD2CBEC705BF3C2935EFBDD83F973DF9FC043E55E82BBD19453C3C44FE093E1BBA483D12F8F1BC98DE04BE704B1FBD5021073D8DBEAFBD3F692FBECE7C44BD00BACA3D07DF25BE7BC0F9BD3AF6643EBD8CC43DC1B2823D14EC2BBE9F9232BEAB19173E6483763ED59F9E3E4402333C69AC823CEBC6863D0A868EBDB8913A3EDB9D8FBE905127BED63036BEE9764ABECD7B4ABEADAFBD3B |
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   5 rows in set (0.0409 sec)
   mysql> SELECT * FROM ML_SCHEMA_user1.3e094aa4ba_items LIMIT 5;
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | item_id | embedding_vector                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 1       | 0xED5A45BEF6D032BE667454BE9DB66FBE62B6F13D392199BE9FE6D63D8718353E1E26CCBC9B6F133EC0F0CE3D90DC79BE561E8CBB94F39ABD86E82B3D4D5BE0BC8D6EA43D929C74BDCA30B43E90B87FBE0B336EBEB6ACA6BD960A9BBC165BF93D4F12833E5143A4BD4050643ECF13803E3BA084BEFDD228BEDA468BBDB708ABBDC3480CBE362B8C3BA5C4CA3DD8B05CBEA58439BEB887853DEC22E6BD17E179BBD12B4BBEDB5416BD4B6B35BEB721A8BD491B383E71293A3DBCE5B8BDE26B95BD8BF54B3C8B58643EE3C82F3E43B341BDDB8AD43D0F75253EF1FD15BE2915883DC3F6AC3DD11BAC3D135C41BCC9FEC3BD8DD2E43D0B5D813DFC49AABDA52291BD |
   | 10      | 0x80C883BC1DCE29BE126039BE1817133D70C63D3E3E795CBE62C33F3DDE1D5F3C84264B3D66672A3E427B39BC48B33EBE5676B23B59570EBE319E3CBE95F641BE5AD44A3E2B1033BDE5FE173E1C3B30BDC2A82BBEDDB314BDD764E23B3B12AB3D4E180B3E74A32F3ED0A5EA3D6723E73C25D372BE631865BE1E12233E8B131BBDDDAE93BDA49267BC49996B3EB7678E3C230CB53DEB0A3A3D5A2F65BDD8AC053EEFD4D6BD7A7452BD40C926BEAF49C63DACA112BD68D20B3D764D82BDD62E5B3C66D81E3E1ABFB0BD82B38E3EE783383EDD36813ECF743DBD8276483D77A0143D8AB079BEC85471BDB46218BD336580BC42113C3D7580B73DC6F9723EBD26A73D |
   | 11      | 0x105B08BE9084D63D126CD3BC6C0117BCCCBC683D04328B3CEE562A3E18F8FEBCE03F423D507486BD2D22443DBC28913DBB53213CDBDA40BE75E7A93D3F1218BD45BE55BE0F28C8BDC3E544BE48223ABDE143413D7E9FA3BE23FE353E8F3A26BED8797CBD4DA0F0BB744119BED9BD30BD1D16D03DAA881DBE382AC93D6C5B503E9A69CE3DB2AFC83C0D28C3BD79B1403EAE9B563D71ECCE3D97B6D53B7776783D5AD7E2BCAB5A4B3DD964B9BD41D204BE9DBBD3BD24AAEC3B31B15C3E21570D3E7CBB493DC721E93D488935BD7B19A33D9A3D15BD6B87633C0432323C1B2E4DBD910F24BD9BCE73BD1EB23D3E52C5FF3DFE20C4BDF1B2BEBDDE64C1BD5F0896BD |
   | 12      | 0x757894BC9644373D7A1DA63DA3E5ECBD3B978E3D9B3A0B3E4E35343EF921CD3B2CEA753D68D0C3BD65C2F5BCD47262BED19A91BB306C4D3D8B4FF5BBDD97043E7134B4BD817318BD30BF82BC639DBB3DFBA78EBD4205853D0E12DC3D97794D3E9A633DBE613E403D6B46AC3D3A02ACBC01F1C2BBD4A7083D0852153E99D99B3EDB9350BD80B0073D63B0E03C78B8223DB7D07ABC2DA0063EB0E7363EAD5B27BCA9EFADBC5D10AE3D4AAAF03B583603BEDF2CAF3D101B1CBEACE3CDBD6F84473D651A94BD8DAC4EBCBC8A9A3D47647C3D3F31C33DFB9A93BDC40DDE3DB4ED743CE3A93EBEDDC6563DC0B0323E4742263D9A06083C6FCA52BEBE9825BD28BB103E |
   | 13      | 0x37DEEFBC545030BC2EF980BD3712923DCC0142BE9C5E143E129309BD4A3E02BE85C001BEA1140E3ED73323BEB6E2FDBD4781EB3DADEE39BD6C2A2ABE33D54B3D45F8183E1F55683DBC8F79BE6AAB68BE5081EEBD4067B03C86A2583D2D6882BD8EFFA43D4E7383BD2A8B23BC17C6EE3DA88B833DE4D24F3D1294C4BBF372F63B9F2F783ED06323BD64F9813E5011FD3DD6DDB1BBC274783EA2C888BE5827D0BDB6A7DF3C4B3748BE38383BBE4023DE3E9F64ED3CA8411DBE5E3F953D2097063E2F7B26BD18819E3973C5283E95FE973D28BB03BD64DBE5BD620C373C27F2A2BDC9A616BEB36AD33D680327BD181FCBBD301C9D3DF700AF3D0EC7473E35EF17BE |
   +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   5 rows in set (0.0419 sec)
   mysql> SELECT * FROM ML_SCHEMA_user1.3e094aa4ba_interactions LIMIT 5;
   +-------------------+---------+---------+
   | _4aad19ca6e_pk_id | user_id | item_id |
   +-------------------+---------+---------+
   |                 1 | 1       | 1       |
   |                 2 | 1       | 11      |
   |                 3 | 1       | 13      |
   |                 4 | 1       | 15      |
   |                 5 | 1       | 17      |
   +-------------------+---------+---------+
   5 rows in set (0.0454 sec)

What's Next

• Learn how to Generate Predictions for a Recommendation Model.

• Review additional Syntax Examples for Recommendation Training.