Personalized video recommendation (collaborative filtering)

Key concepts

• I2I (Item-to-Item): A content recommendation algorithm that recommends items based on the correlations between them. In this tutorial, an item is a video.

• U2I (User-to-Item): A user's interaction with an item (video), such as an impression, a click, or a like.

• U2I2I recall: A table that combines User-to-Item (U2I) and Item-to-Item (I2I) logic. It stores the recall list generated by the recommendation system based on user history and item correlations.

• Recall: A step in the recommendation process that filters a large pool of candidate items to create a smaller subset of items that the user might be interested in. In this tutorial, an item is a video.

• Video interactions (user behavior events): "expr" (an impression, where a video is shown to the user), "click" (the user watches the video), and "praise" (the user likes the video).

Implementation strategy

The implementation strategy consists of the following stages:

• Mine for similar videos (I2I): Discover correlations between videos based on user interaction data. You can adapt this process with your own data to create related content recommendations for your detail pages.

• Discover user interest points (U2I): Identify a user's interests by analyzing their video interaction history and applying a time decay effect.

• Generate personalized video recommendations (U2I2I): Expand the range of interesting videos by using the interest points and an I2I (Item-to-Item) correlation model. This step calculates a precise interest score for each user-video pair and generates a personalized recommendation list to match each user's unique preferences.

rec_sln_demo_behavior_table_v1

• Table data: A user-video interaction log table. This table records user interactions with various videos. Each row represents a single user interaction.

• Node description: This tutorial requires the last 30 days of user-video interaction data for training, so you must ensure the table contains at least 30 days of data. This node reads data from a public Alibaba Cloud PAI dataset and writes it to a new table.

• In the editor for the rec_sln_demo_behavior_table_v1 node, enter the following sample code:

  -- Table definition: "User-video interaction log data"
  -- Table data: This table records user interaction behaviors with videos. Each row represents a single user interaction.
  CREATE TABLE IF NOT EXISTS rec_sln_demo_behavior_table_v1
  (
      request_id  STRING COMMENT 'Tracking ID/Request ID'
      ,user_id    STRING COMMENT 'Unique user ID'
      ,exp_id     STRING COMMENT 'Experiment ID'
      ,page       STRING COMMENT 'Page'
      ,net_type   STRING COMMENT 'Network type'
      ,event_time BIGINT COMMENT 'Behavior time'
      ,item_id    STRING COMMENT 'Content ID'
      ,event      STRING COMMENT 'Behavior type'
      ,playtime   DOUBLE COMMENT 'Playback duration/Reading duration'
  )
  COMMENT 'Personalized video recommendation (collaborative filtering) - User-video interaction log data'
  PARTITIONED BY 
  (
      ds          STRING
  )
  LIFECYCLE 7
  ;
  -- Prepare the user-video interaction log data. The raw data is provided by Alibaba Cloud PAI and can be read directly.
  INSERT OVERWRITE TABLE rec_sln_demo_behavior_table_v1 PARTITION (ds)
  SELECT  *
  FROM    pai_online_project.rec_sln_demo_behavior_table
  WHERE   ds > "20221231"
  AND     ds < "20230217"
  ;

rec_test_etrec_behavior_weight_v1

• Table data: A behavior weight definition table. This table defines weights for user behavior events:

  • expr: An impression event, where a video is shown to a user. The event weight is 0, which means impressions are not considered.

  • click: A view event, where a user watches a video. The weight is 1.

  • praise: A like event, where a user likes a video. The weight is 3.

• Node description: Before training, you need to define weights for different behaviors in the video browsing scenario. This quantifies user actions, such as likes and clicks, to help the algorithm accurately assess a user's interest in video content.

• In the editor for the rec_test_etrec_behavior_weight_v1 node, enter the following sample code:

  -- Table definition: "Behavior weight definition table"
  CREATE TABLE IF NOT EXISTS rec_test_etrec_behavior_weight_v1
  (
      event   STRING
      ,weight DOUBLE COMMENT 'Behavior weight'
  )
  COMMENT 'Personalized video recommendation (collaborative filtering) - Behavior weight table'
  LIFECYCLE 7
  ;
  -- 1. Write data: In this tutorial, the weight for a "click" is set to 1.0, a "praise" to 3.0, and an "expr" to 0.0.
  -- 2. Business context: By joining this table with user interaction data, you can assign a weight to each user interaction (like, click, etc.).
  --    Quantifying these behaviors helps the algorithm accurately assess a user's interest in video content.
  INSERT OVERWRITE TABLE rec_test_etrec_behavior_weight_v1 VALUES
          ('expr',0.0)
          ,('click',1.0)
          ,('praise',3.0)
  ;

rec_test_etrec_i2i_input_v1

• Table data: A user-video interaction log table with behavior weights. This table records user interactions and quantifies them with predefined weights.

• Node description: This node joins the last 30 days of user behavior data from rec_sln_demo_behavior_table_v1 with the behavior weight data from rec_test_etrec_behavior_weight_v1. It generates historical user preference data with weights and writes it to the latest partition of the rec_test_etrec_i2i_input_v1 table. In this tutorial, the data is written to the 20230216 partition.

• In the editor for the rec_test_etrec_i2i_input_v1 node, enter the following sample code:

  -- 1. Table definition: "User-video interaction log data with behavior weights (30 days)"
  -- 2. Table data: This table records user interactions with videos and quantifies them with predefined behavior weights.
  --    Each row represents a single user-video interaction with its weight (e.g., a view or a like).
  --    If a user interacts with the same video multiple times, the table stores multiple records for that user and video.
  -- 3. Business context: This table serves as the input for the Item-to-Item (I2I) recommendation engine. The weights reflect the influence of
  --    different user behaviors and help the algorithm accurately assess user interest.
  CREATE TABLE IF NOT EXISTS rec_test_etrec_i2i_input_v1
  (
      user_id     STRING COMMENT 'User ID'
      ,item_id    STRING COMMENT 'Content ID'
      ,event      STRING COMMENT 'Behavior type'
      ,event_time BIGINT COMMENT 'Behavior time'
      ,weight     DOUBLE COMMENT 'Behavior weight'
  )
  COMMENT 'Personalized video recommendation (collaborative filtering) - User-video interaction log data with behavior weights'
  PARTITIONED BY 
  (
      ds          STRING
  )
  LIFECYCLE 7
  ;
  -- Join the last 30 days of user interaction data from "rec_sln_demo_behavior_table_v1" with weight data from "rec_test_etrec_behavior_weight_v1".
  -- Write the resulting historical preference data to the latest partition of the rec_test_etrec_i2i_input_v1 table. This tutorial uses the 20230216 partition.
  INSERT OVERWRITE TABLE rec_test_etrec_i2i_input_v1 PARTITION (ds = '20230216')
  SELECT  CAST(sq0.user_id AS STRING) user_id
          ,CAST(sq0.item_id AS STRING) item_id
          ,sq0.event
          ,sq0.event_time
          ,sq1.weight
  FROM    (
              -- Get user video interaction data from the last 30 days.
              SELECT  *
              FROM    rec_sln_demo_behavior_table_v1
              WHERE   ds > TO_CHAR(DATEADD(TO_DATE('20230216','yyyymmdd'),-30,'dd'),'yyyymmdd')
              AND     ds <= '20230216'
          ) sq0
  JOIN    (
              -- Exclude "expr" events and keep "click" and "praise" events.
              SELECT  *
              FROM    rec_test_etrec_behavior_weight_v1
              WHERE   weight > 0
          ) sq1
  ON      sq0.event = sq1.event
  ;

rec_test_etrec_i2i_output_20230216_v1

• Table data: A temporary table for video-to-video correlation scores. Each row contains a video and its top N most correlated videos, along with their correlation scores.

• Node description: This node uses the Alibaba Cloud PAI collaborative filtering algorithm, PAI-eTrec, to calculate the correlation between videos based on the weighted user interaction data in rec_test_etrec_i2i_input_v1. The resulting scores are written to the rec_test_etrec_i2i_output_20230216_v1 table.

• In the editor for the rec_test_etrec_i2i_output_20230216_v1 node, enter the following sample code:

  -- This node generates data for the "rec_test_etrec_i2i_output_20230216_v1" table.
  -- 1. Table definition: "Temporary table for video-to-video correlation scores"
  -- 2. Table data: Contains the correlation scores between each video and other videos. Each row records a video and its top N most correlated videos.
  -- 3. Business context: The scores are calculated by the PAI-eTrec algorithm based on user interactions and defined weights.
  -- Drop the existing item-to-item output table for the partition date 2023-02-16 to ensure the algorithm can create and write to a new one.
  DROP TABLE IF EXISTS rec_test_etrec_i2i_output_20230216_v1
  ;
  -- Use the PAI command for item-to-item calculation.
  -- 1. Data flow: Use the PAI-eTrec algorithm to calculate video correlation based on the weighted user interaction data in "rec_test_etrec_i2i_input_v1".
  --    The scores are written to the "rec_test_etrec_i2i_output_20230216_v1" table.
  -- 2. The -DtopN parameter specifies that only the top 100 most correlated videos are returned for each video. For more information about parameters, see https://www.alibabacloud.com/help/en/pai/user-guide/etrec-component
  PAI -name pai_etrec
  -project algo_public
  -DinputTableName="rec_test_etrec_i2i_input_v1"
  -DinputTablePartitions="ds=20230216"
  -DuserColName="user_id"
  -DitemColName="item_id"
  -DsimilarityType="wbcosine"
  -DtopN="100"
  -DmaxUserBehavior="1000"
  -DminUserBehavior="2" 
  -Doperator="add"
  -DitemDelimiter=";"
  -DkvDelimiter=","
  -DoutputTableName="rec_test_etrec_i2i_output_20230216_v1"
  -Dlifecycle="7"
  ;
  -- Query the video-to-video correlation score table "rec_test_etrec_i2i_output_20230216_v1".
  -- Each row represents a specific video (itemid) and a set of correlated videos with their scores (similarity).
  -------------------------------------------------------------------
  -- itemid         similarity
  -- Video1_ID      RelatedVideo2_ID:Score2;RelatedVideo3_ID:Score3
  -------------------------------------------------------------------
  -- SELECT * FROM rec_test_etrec_i2i_output_20230216_v1 LIMIT 10;

rec_test_etrec_i2i_item_score_v1

• Table data: Video-to-video correlation score data. Each row represents a single correlation score between two videos. This is created by splitting and flattening the data from the rec_test_etrec_i2i_output_20230216_v1 table.

• Node description: This node transforms and simplifies the correlation score data from rec_test_etrec_i2i_output_20230216_v1 to make it easier to query and use. To see the transformation, compare the code in this node with the code in the upstream rec_test_etrec_i2i_output_20230216_v1 node.

• In the editor for the rec_test_etrec_i2i_item_score_v1 node, enter the following sample code:

  -- Table definition: Video-to-video correlation score data (with the similarity field split)
  -- Table data: Each row represents a unique correlation score between two videos.
  CREATE TABLE IF NOT EXISTS rec_test_etrec_i2i_item_score_v1
  (
      trigger_id  STRING COMMENT 'The video ID that triggers the recommendation (the left item in I2I)'
      ,item_id    STRING COMMENT 'The correlated video ID, calculated based on the trigger_id'
      ,item_score STRING COMMENT 'The correlation score between the two videos, calculated based on common user viewing behavior'
  )
  COMMENT 'Personalized video recommendation (collaborative filtering) - Video-to-video correlation score data (eTrec I2I item score)'
  PARTITIONED BY 
  (
      ds          STRING
  )
  LIFECYCLE 7
  ;
  -- Process: Split the "similarity" field from the intermediate table "rec_test_etrec_i2i_output_20230216_v1".
  -- Write the results to the latest partition of the "rec_test_etrec_i2i_item_score_v1" table. This tutorial uses the 20230216 partition.
  -- Purpose of splitting: To store each pair of correlated videos and their scores in a separate row, which makes it easier to use the scores for recommendations.
  INSERT OVERWRITE TABLE rec_test_etrec_i2i_item_score_v1 PARTITION (ds = '20230216')
  SELECT  itemid trigger_id
          ,SPLIT(itemid_score,',')[0] item_id
          ,SPLIT(itemid_score,',')[1] item_score
  FROM    rec_test_etrec_i2i_output_20230216_v1
  LATERAL VIEW EXPLODE(SPLIT(similarity,';')) subview0 AS itemid_score
  ;
  -- Query the split video-to-video correlation score data from rec_test_etrec_i2i_item_score_v1.
  -------------------------------------------------------------------
  -- trigger_id     item_id            item_score
  -- Video1_ID      CorrelatedVideo2_ID   Score2
  -- Video1_ID      CorrelatedVideo3_ID   Score3
  -------------------------------------------------------------------
  -- SELECT * FROM rec_test_etrec_i2i_item_score_v1 WHERE ds = '20230216';

rec_test_etrec_u2i2i_score_v1

• Table data: A personalized video recommendation list for users. This table includes videos a user has already shown interest in (historical preference) and other videos they might be interested in (inferred preference).

• Node description: This node generates the final personalized recommendation list by combining the direct user-to-item (U2I) interest scores from rec_test_etrec_u2i_trigger_score_v1 with the item-to-item (I2I) correlation scores from rec_test_etrec_i2i_item_score_v1. The result is written to rec_test_etrec_u2i2i_score_v1.

• In the editor for the rec_test_etrec_u2i2i_score_v1 node, enter the following sample code:

  -- 1. Table definition: "Personalized video recommendation list". In a recommendation scenario, this is a U2I2I recall table.
  -- 2. Table data: This table stores the final personalized recommendation list.
  -- 3. Business context: Based on a user's historical preferences (User-to-Item, U2I), this process expands the recommendation scope by using content correlation (Item-to-Item, I2I).
  --    This provides a more comprehensive personalized video recommendation list.
  CREATE TABLE IF NOT EXISTS rec_test_etrec_u2i2i_score_v1
  (
      user_id   STRING COMMENT 'User ID'
      ,item_ids STRING COMMENT 'A list of top-N recommended videos calculated by U2I2I. The format is item_ID:score, with item-score pairs separated by commas.'
  )
  COMMENT 'Personalized video recommendation (collaborative filtering) - Personalized video recommendation list for users (eTrec U2I2I recall table)'
  PARTITIONED BY 
  (
      ds        STRING
  )
  LIFECYCLE 7
  ;
  -- 1. Data flow: Generate the final personalized recommendation list based on direct User-to-Item (U2I) interest scores from "rec_test_etrec_u2i_trigger_score_v1"
  --    and Item-to-Item (I2I) correlation scores from "rec_test_etrec_i2i_item_score_v1".
  --    The final list is written to "rec_test_etrec_u2i2i_score_v1".
  -- 2. Processing logic:
  --    2.1 Combine U2I and I2I scores: For each user, select high-interest videos and find correlated videos and their scores.
  --    2.2 Calculate recommendation score: Multiply the U2I score by the I2I score. This product represents the user's potential interest in the correlated video.
  --    2.3 Aggregate and rank: Sum the calculated scores for each recommended video to get a final composite score for each user.
  -- 3. Ranking and filtering:
  --    3.1 Top-N ranking: Rank the composite video scores in descending order and select the top 100.
  --    3.2 Ranking: Use the ROW_NUMBER() window function to rank videos for each user, keeping only the top 100.
  --  Note: The final recommendation list in this tutorial may include videos the user has already watched. You can add a processing step to filter out watched videos.
  INSERT OVERWRITE TABLE rec_test_etrec_u2i2i_score_v1 PARTITION (ds = '20230216')
  SELECT  sq3.user_id -- Concatenate the recommended video ID and score for each user into a single field.
          ,WM_CONCAT(',',CONCAT(sq3.item_id,':',sq3.u2i_score)) item_ids
  FROM    (
              SELECT  sq2.user_id
                      ,sq2.item_id
                      ,ROUND(SUM(sq2.trigger_relation_score),4) u2i_score -- Assign a rank to each recommended video for each user.
                      ,ROW_NUMBER() OVER (PARTITION BY sq2.user_id ORDER BY SUM(sq2.trigger_relation_score) DESC ) rn
              FROM    (
                          SELECT  sq0.user_id
                                  ,sq1.item_id -- Potential interest = User interest in a trigger video (U2I score) * Correlation between that video and another (I2I score).
                                  -- Example: If a user shows high interest in Video A (high U2I score) and Video A is highly correlated with Video B (high I2I score),
                                  -- the user is likely to be interested in Video B as well.
                                  ,sq0.trigger_score * sq1.item_score trigger_relation_score
                          FROM    (
                                      -- Get the latest user-video interest score data.
                                      SELECT  *
                                      FROM    rec_test_etrec_u2i_trigger_score_v1
                                      WHERE   ds = '20230216'
                                  ) sq0
                          JOIN    (
                                      -- Get the latest video correlation score data.
                                      SELECT  *
                                      FROM    rec_test_etrec_i2i_item_score_v1
                                      WHERE   ds = '20230216'
                                  ) sq1
                          ON      sq0.item_id = sq1.trigger_id
                      ) sq2
              GROUP BY sq2.user_id
                       ,sq2.item_id
          ) sq3 -- Ensure that the final recommendation list includes only the top 100 videos with the highest composite scores.
  WHERE   sq3.rn <= 100
  GROUP BY sq3.user_id
  ;

Create a query node

• In this stage, create an ODPS SQL node named Query Personalized Video Recommendation Results. This node depends on all the preceding tasks.

• Connect the last node in the U2I2I Guess You Like group to the Personalized Video Recommendation Result node to establish the final dependency in the workflow.

• Node definition: After the preceding steps generate recommendations, this node queries the results.

• In the editor for the Query Personalized Video Recommendation Results node, enter the following sample code:

  --@exclude_input=rec_test_etrec_u2i2i_score_v1
  --@exclude_input=rec_test_etrec_u2i_trigger_score_v1
  --@exclude_input=rec_test_etrec_i2i_item_score_v1
  --@exclude_input=rec_test_etrec_i2i_output_20230216_v1
  --@exclude_input=rec_test_etrec_i2i_input_v1
  --@exclude_input=rec_test_etrec_behavior_weight_v1
  --@exclude_input=rec_sln_demo_behavior_table_v1
  --odps sql 
  --********************************************************************--
  --author:dataworks_demo2
  --create time:2024-01-04 18:16:50
  --********************************************************************--
  -- Query user-video interaction log data.
  SELECT * FROM rec_sln_demo_behavior_table_v1 WHERE ds='20230216' LIMIT 10;
  -- Query the behavior weight definition table.
  SELECT * FROM rec_test_etrec_behavior_weight_v1 LIMIT 10;
  -- Query user-video interaction log data with behavior weights.
  SELECT * FROM rec_test_etrec_i2i_input_v1 WHERE ds='20230216' LIMIT 10;
  -- Query the temporary table for video-to-video correlation scores.
  SELECT * FROM rec_test_etrec_i2i_output_20230216_v1 LIMIT 10;
  -- Query video-to-video correlation score data.
  SELECT * FROM rec_test_etrec_i2i_item_score_v1 WHERE ds='20230216' LIMIT 10;
  -- Query user-video interest score data.
  SELECT * FROM rec_test_etrec_u2i_trigger_score_v1 WHERE ds='20230216' LIMIT 10;
  -- Query the personalized video recommendation list for users.
  SELECT * FROM rec_test_etrec_u2i2i_score_v1 WHERE ds='20230216' LIMIT 10;
  SELECT '====================================You can run this node again to view the structured result set.====================================';

Run the query node

After the entire business flow has run, run the Query Personalized Video Recommendation Results node individually to view the recommendation results.

1. Double-click to open the editor for the Query Personalized Video Recommendation Results node.

2. In the toolbar, click the icon to run only this node and view the results.

   The results are displayed in the Result[7] tab. The result set contains three columns: user_id (user ID), item_ids (recommended video IDs and their scores, such as 248507619:0.1859), and ds (date partition). It presents a personalized video recommendation list for each user.