feature production, best practices - Platform For AI - Centre de documentation Alibaba Cloud

FeatureStore, une plateforme de features intégrée à Platform for AI (PAI), normalise les opérations courantes d'ingénierie des features. Vous pouvez ainsi générer des centaines de features à partir de tables sources brutes avec seulement quelques lignes de code. Ce guide vous accompagne dans un scénario de recommandation : à partir de trois tables sources, vous générerez des tables de features pour les utilisateurs et les éléments, puis fusionnerez l'ensemble en un jeu de données d'entraînement complet, prêt pour l'apprentissage du modèle.

Ce que vous allez construire

À l'issue de ce guide, vous disposerez des éléments suivants :

Trois tables sources prétraitées (utilisateur, élément et comportement) synchronisées dans votre projet MaxCompute
Une table de comportement élargie et une table d'étiquettes dérivées de ces sources
Deux tables de features utilisateur générées à l'aide de AggregationTransform et WindowTransform
Deux tables de features élément générées de la même manière
Un jeu de données d'entraînement final (fs_demo_fs_engineering_v1_training_set) qui joint les sept tables

Prérequis

Avant de commencer, assurez-vous que les conditions suivantes sont remplies :

PAI

Activer PAI et créer un espace de travail PAI
Préparez votre AccessKey ID et votre AccessKey secret. Stockez-les sous forme de variables d'environnement plutôt que de les coder en dur dans votre code. Pour obtenir des instructions de configuration, consultez Créer une paire de clés d'accès et Configurer la clé d'accès d'authentification dans la variable d'environnement

MaxCompute

DataWorks

Activez DataWorks
Achetez un groupe de ressources exclusif pour la planification et associez-le à votre espace de travail DataWorks. Consultez Utiliser un groupe de ressources exclusif pour la planification
Ajoutez une source de données MaxCompute à votre espace de travail DataWorks. Consultez Lier une ressource de calcul MaxCompute

Fonctionnement

Ce guide suit quatre étapes :

Préparation — Synchronisez les trois tables sources de démonstration depuis pai_online_project vers votre projet MaxCompute, et installez le SDK Python FeatureStore.
Transformation — Fusionnez et prétraitez les tables sources pour produire une table de comportement élargie et une table d'étiquettes.
Génération de features — Exécutez les pipelines FeatureStore pour produire des tables de features d'agrégation et de fenêtre pour les utilisateurs et les éléments.
Construction du jeu de données d'entraînement — Joignez toutes les tables de features en une seule table d'échantillons.

Étape 1 : Préparer les tables sources et le SDK

Synchroniser les tables sources

Les trois tables sources de ce tutoriel sont stockées dans l'espace de travail partagé pai_online_project. Elles contiennent uniquement des données de simulation. Utilisez DataWorks pour les copier dans votre propre projet MaxCompute.

Table	Contenu
`rec_sln_demo_user_table_preprocess_v1`	Features utilisateur de base : genre, âge, ville, nombre d'abonnés
`rec_sln_demo_item_table_preprocess_v1`	Features élément de base : catégorie, auteur, clics cumulés, mentions « J'aime » cumulées
`rec_sln_demo_behavior_table_preprocess_v1`	Événements de comportement : quel utilisateur a cliqué sur quel élément et quand

Pour synchroniser les tables :

Connectez-vous à la console DataWorks.
Dans le volet de navigation de gauche, cliquez sur Data Development and O&M > Data Development.
Sélectionnez votre espace de travail DataWorks et cliquez sur Go to Data Studio.

Placez le curseur sur Create, puis choisissez Create Node > MaxCompute > ODPS SQL. Configurez les paramètres du nœud :

Paramètre	Valeur
Node Type	ODPS SQL
Path	Business Flow/Workflow/MaxCompute
Name	Saisissez un nom personnalisé

Cliquez sur Confirm.

Dans l'éditeur SQL, exécutez les instructions suivantes. Chaque bloc crée la structure de la table et charge les données pour la plage de dates allant de 20240530 à 20240605. Table utilisateur :

CREATE TABLE IF NOT EXISTS rec_sln_demo_user_table_preprocess_v1
LIKE pai_online_project.rec_sln_demo_user_table_preprocess_v1
STORED AS ALIORC
LIFECYCLE 90;

INSERT OVERWRITE TABLE rec_sln_demo_user_table_preprocess_v1 PARTITION(ds)
SELECT *
FROM pai_online_project.rec_sln_demo_user_table_preprocess_v1
WHERE ds >= '20240530' AND ds <= '20240605';

Table élément :

CREATE TABLE IF NOT EXISTS rec_sln_demo_item_table_preprocess_v1
LIKE pai_online_project.rec_sln_demo_item_table_preprocess_v1
STORED AS ALIORC
LIFECYCLE 90;

INSERT OVERWRITE TABLE rec_sln_demo_item_table_preprocess_v1 PARTITION(ds)
SELECT *
FROM pai_online_project.rec_sln_demo_item_table_preprocess_v1
WHERE ds >= '20240530' AND ds <= '20240605';

Table de comportement :

CREATE TABLE IF NOT EXISTS rec_sln_demo_behavior_table_preprocess_v1
LIKE pai_online_project.rec_sln_demo_behavior_table_preprocess_v1
STORED AS ALIORC
LIFECYCLE 90;

INSERT OVERWRITE TABLE rec_sln_demo_behavior_table_preprocess_v1 PARTITION(ds)
SELECT *
FROM pai_online_project.rec_sln_demo_behavior_table_preprocess_v1
WHERE ds >= '20240530' AND ds <= '20240605';

Installer le SDK FeatureStore

Exécutez la commande suivante dans un Jupyter Notebook (Python 3) :

%pip install https://feature-store-py.oss-cn-beijing.aliyuncs.com/package/feature_store_py-2.0.2-py3-none-any.whl

Importez ensuite les modules dont vous aurez besoin tout au long de ce guide :

import os
from feature_store_py import FeatureStoreClient
from feature_store_py.fs_datasource import MaxComputeDataSource
from feature_store_py.feature_engineering import (
    TableTransform, Condition, DayOf, ComboTransform, Feature,
    AggregationTransform, auto_count_feature_transform,
    WindowTransform, auto_window_feature_transform
)

Étape 2 : Transformer les tables sources

Avant de pouvoir générer les features, les trois tables sources doivent être restructurées en deux tables de travail : une table de comportement élargie qui fusionne les trois sources, et une table d'étiquettes qui extrait les étiquettes d'entraînement à partir des données de comportement.

Utilisez le nœud ODPS SQL créé à l'étape 1 pour exécuter les deux blocs SQL ci-dessous.

Créer la table de comportement élargie

Cette instruction joint les attributs utilisateur, les attributs élément et les événements de comportement dans une seule table dénormalisée que le SDK FeatureStore utilise pour le calcul des features.

CREATE TABLE IF NOT EXISTS rec_sln_demo_behavior_table_preprocess_wide_v1
(
    request_id bigint
    ,user_id string
    ,page string
    ,net_type string
    ,day_h bigint COMMENT 'The hour at which the behavior occurred.'
    ,week_day bigint COMMENT 'The day of the week on which the behavior occurred.'
    ,event_unix_time bigint
    ,item_id string
    ,event string
    ,playtime double
    ,gender string
    ,age bigint
    ,city string
    ,item_cnt bigint
    ,follow_cnt bigint
    ,follower_cnt bigint
    ,is_new_user bigint
    ,tags string
    ,duration double
    ,category string
    ,author bigint
    ,click_count bigint
    ,praise_count bigint
    ,is_new_item bigint
)
PARTITIONED BY
(
    ds string
)
LIFECYCLE 90;

INSERT OVERWRITE TABLE rec_sln_demo_behavior_table_preprocess_wide_v1 PARTITION(ds='${bdp.system.bizdate}')
SELECT  sq0.request_id
        ,sq0.user_id
        ,sq0.page
        ,sq0.net_type
        ,sq0.day_h
        ,sq0.week_day
        ,sq0.event_unix_time
        ,sq0.item_id
        ,sq0.event
        ,sq0.playtime
        ,sq1.gender
        ,sq1.age
        ,sq1.city
        ,sq1.item_cnt
        ,sq1.follow_cnt
        ,sq1.follower_cnt
        ,sq1.is_new_user
        ,sq1.tags
        ,sq2.duration
        ,sq2.category
        ,sq2.author
        ,sq2.click_count
        ,sq2.praise_count
        ,sq2.is_new_item
FROM    (
            SELECT  *
            FROM    rec_sln_demo_behavior_table_preprocess_v1
            WHERE   ds = '${bdp.system.bizdate}'
        ) sq0
LEFT JOIN (
              SELECT  *
              FROM    rec_sln_demo_user_table_preprocess_v1
              WHERE   ds = '${bdp.system.bizdate}'
          ) sq1
ON      sq0.user_id = sq1.user_id
LEFT JOIN (
              SELECT  *
              FROM    rec_sln_demo_item_table_preprocess_v1
              WHERE   ds = '${bdp.system.bizdate}'
          ) sq2
ON      sq0.item_id = sq2.item_id;

Créer la table d'étiquettes

Cette instruction dérive les étiquettes d'entraînement à partir de la table de comportement. Les étiquettes sont is_click (si l'utilisateur a cliqué), ln_playtime (temps de lecture total transformé par logarithme) et is_praise (si l'utilisateur a aimé l'élément).

CREATE TABLE IF NOT EXISTS rec_sln_demo_fs_label_table_v1
(
    request_id bigint
    ,user_id string
    ,page string
    ,net_type string
    ,day_h bigint COMMENT 'The hour at which the behavior occurred.'
    ,week_day bigint COMMENT 'The day of the week on which the behavior occurred.'
    ,day_min string
    ,event_unix_time bigint
    ,item_id string
    ,playtime double
    ,is_click BIGINT
    ,ln_playtime DOUBLE
    ,is_praise BIGINT
)
PARTITIONED BY
(
    ds string
)
LIFECYCLE 90;

INSERT OVERWRITE TABLE rec_sln_demo_fs_label_table_v1 PARTITION(ds='${bdp.system.bizdate}')
SELECT  request_id
        ,user_id
        ,MAX(page) page
        ,MAX(net_type) net_type
        ,MAX(day_h) day_h
        ,MAX(week_day) week_day
        ,TO_CHAR(FROM_UNIXTIME(MIN(event_unix_time)),'yyyymmddhhmi') day_min
        ,MAX(event_unix_time) event_unix_time
        ,item_id
        ,MAX(playtime) playtime
        ,MAX(IF(event='click', 1, 0)) is_click
        ,LN(SUM(playtime) + 1) ln_playtime
        ,MAX(IF(event='praise', 1, 0)) is_praise
FROM    rec_sln_demo_behavior_table_preprocess_v1
WHERE   ds = '${bdp.system.bizdate}'
GROUP BY request_id, user_id, item_id;

Une fois les deux instructions terminées, vous disposerez des éléments suivants :

rec_sln_demo_behavior_table_preprocess_wide_v1 — l'entrée pour la génération de features
rec_sln_demo_fs_label_table_v1 — l'entrée pour la table d'échantillons finale

Étape 3 : Générer les features

Les fonctions d'extension automatique de FeatureStore vous permettent de générer des centaines de features statistiques et de fenêtre à partir d'un seul appel de fonction, sans avoir à écrire manuellement du SQL d'agrégation. Les étapes ci-dessous génèrent séparément les features pour les utilisateurs et les éléments.

Tous les exemples utilisent l'API pipeline du SDK FeatureStore : définissez les sources de données et les transformations, puis appelez execute() pour lancer le calcul dans MaxCompute.

Initialiser le client

# Load credentials from environment variables — never hardcode keys in source code
access_key_id = os.environ.get("ALIBABA_CLOUD_ACCESS_KEY_ID")
access_key_secret = os.environ.get("ALIBABA_CLOUD_ACCESS_KEY_SECRET")

project = 'project_name'   # Your MaxCompute project name
region = 'cn-hangzhou'     # The region where your project resides

fs_client = FeatureStoreClient(
    access_key_id=access_key_id,
    access_key_secret=access_key_secret,
    region=region
)

Lier les tables sources aux sources de données

input_bhv_table_name = "rec_sln_demo_behavior_table_preprocess_wide_v1"
ds_bhv = MaxComputeDataSource(table=input_bhv_table_name, project=project)

input_user_table_name = "rec_sln_demo_user_table_preprocess_v1"
ds_user = MaxComputeDataSource(table=input_user_table_name, project=project)

input_item_table_name = "rec_sln_demo_item_table_preprocess_v1"
ds_item = MaxComputeDataSource(table=input_item_table_name, project=project)

Générer les features utilisateur

AggregationTransform

auto_count_feature_transform génère des features d'agrégation (somme, maximum, minimum, moyenne) pour chaque métrique sur plusieurs fenêtres temporelles — dans ce cas, des fenêtres de 3, 7 et 15 jours regroupées par user_id.

Définir les features :

agg_user_table_v1 = 'rec_sln_demo_user_table_preprocess_agg_v1'

name_prefix = "user"
input_list = ["playtime", "duration", "click_count", "praise_count"]
event_name = 'event'
event_type = 'expr'
group_by_key = "user_id"
window_size = [3, 7, 15]

user_count_feature_list = auto_count_feature_transform(
    name_prefix, input_list, event_name, event_type, group_by_key, window_size
)
print("len_count_feature_list =", len(user_count_feature_list))
print("count_feature_list =", user_count_feature_list)

Créer le pipeline :

agg_user_bhv_pipeline = fs_client.create_pipeline(ds_bhv).add_feature_transform(user_count_feature_list)
agg_user_pipeline = fs_client.create_pipeline(ds_user, agg_user_table_v1).merge(
    agg_user_bhv_pipeline, keep_input_columns=False
)

Exécuter et vérifier :

execute_date = '20240605'
output_agg_user_table = agg_user_pipeline.execute(execute_date, drop_table=True)

# Preview the first 20 rows
agg_user_ret = output_agg_user_table.to_pandas(execute_date, limit=20)
agg_user_ret

WindowTransform

auto_window_feature_transform génère des features de fenêtre multidimensionnelles — par exemple, comment le nombre de clics d'un utilisateur varie selon l'heure de la journée ou la catégorie de contenu sur différentes fenêtres temporelles.

Définir les features :

win_user_table_v1 = 'rec_sln_demo_user_table_preprocess_win_v1'

name_prefix = 'user'
input_list = ['day_h', 'category']
agg_field = ['duration', 'click_count']
event_name = 'event'
event_type = 'expr'
group_by_key = 'user_id'
window_size = [7, 15, 30, 45]

user_win_feature_list = auto_window_feature_transform(
    name_prefix, input_list, agg_field, event_name, event_type, group_by_key, window_size
)
print("len_user_win_feature_list =", len(user_win_feature_list))
print("user_win_feature_list =", user_win_feature_list)

Créer le pipeline :

win_user_bhv_pipeline = fs_client.create_pipeline(ds_bhv).add_feature_transform(user_win_feature_list)
win_user_pipeline = fs_client.create_pipeline(ds_user, win_user_table_v1).merge(
    win_user_bhv_pipeline, keep_input_columns=False
)

Exécuter et vérifier :

execute_date = '20240605'
output_win_user_table = win_user_pipeline.execute(execute_date, drop_table=True)

# Note: WindowTransform uses intermediate tables. On the first run, backfill all
# historical partitions by adding backfill_partitions=True — this may take a while:
# output_win_user_table = win_user_pipeline.execute(execute_date, drop_table=True, backfill_partitions=True)

win_user_ret = output_win_user_table.to_pandas(execute_date, limit=20)
win_user_ret

Générer les features élément

La génération de features élément suit le même schéma que celle des features utilisateur, avec item_id comme clé de regroupement.

AggregationTransform

Définir les features :

agg_item_table_v1 = 'rec_sln_demo_item_table_preprocess_agg_v1'

name_prefix = "item"
input_list = ["item_cnt", "follow_cnt", "follower_cnt"]
event_name = 'event'
event_type = 'expr'
group_by_key = "item_id"
window_size = [3, 7, 15]

item_count_feature_list = auto_count_feature_transform(
    name_prefix, input_list, event_name, event_type, group_by_key, window_size
)
print("len_count_feature_list =", len(item_count_feature_list))
print("count_feature_list =", item_count_feature_list)

Créer le pipeline :

agg_item_bhv_pipeline = fs_client.create_pipeline(ds_bhv).add_feature_transform(item_count_feature_list)
agg_item_pipeline = fs_client.create_pipeline(ds_item, agg_item_table_v1).merge(
    agg_item_bhv_pipeline, keep_input_columns=False
)

Exécuter et vérifier :

execute_date = '20240605'
output_agg_item_table = agg_item_pipeline.execute(execute_date, drop_table=True)

agg_item_ret = output_agg_item_table.to_pandas(execute_date, limit=20)
agg_item_ret

WindowTransform

Définir les features :

win_item_table_v1 = 'rec_sln_demo_item_table_preprocess_win_v1'

name_prefix = 'item'
input_list = ['day_h', 'category']
agg_field = ['click_count', 'praise_count']
event_name = 'event'
event_type = 'expr'
group_by_key = 'item_id'
window_size = [7, 15, 30, 45]

item_win_feature_list = auto_window_feature_transform(
    name_prefix, input_list, agg_field, event_name, event_type, group_by_key, window_size
)
print("len_item_win_feature_list =", len(item_win_feature_list))
print("item_win_feature_list =", item_win_feature_list)

Créer le pipeline :

win_item_bhv_pipeline = fs_client.create_pipeline(ds_bhv).add_feature_transform(item_win_feature_list)
win_item_pipeline = fs_client.create_pipeline(ds_item, win_item_table_v1).merge(
    win_item_bhv_pipeline, keep_input_columns=False
)

Exécuter et vérifier :

execute_date = '20240605'
output_win_item_table = win_item_pipeline.execute(execute_date, drop_table=True)

# On the first run, add backfill_partitions=True if intermediate partitions are missing:
# output_win_item_table = win_item_pipeline.execute(execute_date, drop_table=True, backfill_partitions=True)

win_item_ret = output_win_item_table.to_pandas(execute_date, limit=20)
win_item_ret

Étape 4 : Construire le jeu de données d'entraînement

À ce stade, vous disposez de sept tables : une table d'étiquettes, trois tables côté utilisateur (source, agrégation, fenêtre) et trois tables côté élément (source, agrégation, fenêtre). Le code SQL ci-dessous joint les sept tables en un seul jeu de données d'entraînement à l'aide de LEFT JOIN basés sur user_id et item_id.

Remplacez <project_name> par le nom réel de votre projet MaxCompute avant l'exécution.

CREATE TABLE IF NOT EXISTS fs_demo_fs_engineering_v1_training_set(
     request_id BIGINT,
     user_id STRING,
     page STRING,
     net_type STRING,
     day_h BIGINT,
     week_day BIGINT,
     day_min STRING,
     event_unix_time BIGINT,
     item_id STRING,
     playtime DOUBLE,
     is_click BIGINT,
     ln_playtime DOUBLE,
     is_praise BIGINT,
     duration DOUBLE,
     category STRING,
     author BIGINT,
     click_count BIGINT,
     praise_count BIGINT,
     is_new_item BIGINT,
     item__sum_item_cnt_3d BIGINT,
     item__sum_follow_cnt_3d BIGINT,
     item__sum_follower_cnt_3d BIGINT,
     item__max_item_cnt_3d BIGINT,
     item__max_follow_cnt_3d BIGINT,
     item__max_follower_cnt_3d BIGINT,
     item__min_item_cnt_3d BIGINT,
     item__min_follow_cnt_3d BIGINT,
     item__min_follower_cnt_3d BIGINT,
     item__avg_item_cnt_3d DOUBLE,
     item__avg_follow_cnt_3d DOUBLE,
     item__avg_follower_cnt_3d DOUBLE,
     item__sum_item_cnt_7d BIGINT,
     item__sum_follow_cnt_7d BIGINT,
     item__sum_follower_cnt_7d BIGINT,
     item__max_item_cnt_7d BIGINT,
     item__max_follow_cnt_7d BIGINT,
     item__max_follower_cnt_7d BIGINT,
     item__min_item_cnt_7d BIGINT,
     item__min_follow_cnt_7d BIGINT,
     item__min_follower_cnt_7d BIGINT,
     item__avg_item_cnt_7d DOUBLE,
     item__avg_follow_cnt_7d DOUBLE,
     item__avg_follower_cnt_7d DOUBLE,
     item__sum_item_cnt_15d BIGINT,
     item__sum_follow_cnt_15d BIGINT,
     item__sum_follower_cnt_15d BIGINT,
     item__max_item_cnt_15d BIGINT,
     item__max_follow_cnt_15d BIGINT,
     item__max_follower_cnt_15d BIGINT,
     item__min_item_cnt_15d BIGINT,
     item__min_follow_cnt_15d BIGINT,
     item__min_follower_cnt_15d BIGINT,
     item__avg_item_cnt_15d DOUBLE,
     item__avg_follow_cnt_15d DOUBLE,
     item__avg_follower_cnt_15d DOUBLE,
     item__kv_day_h_click_count_sum_7d STRING,
     item__kv_category_click_count_sum_7d STRING,
     item__kv_day_h_praise_count_sum_7d STRING,
     item__kv_category_praise_count_sum_7d STRING,
     item__kv_day_h_click_count_max_7d STRING,
     item__kv_category_click_count_max_7d STRING,
     item__kv_day_h_praise_count_max_7d STRING,
     item__kv_category_praise_count_max_7d STRING,
     item__kv_day_h_click_count_min_7d STRING,
     item__kv_category_click_count_min_7d STRING,
     item__kv_day_h_praise_count_min_7d STRING,
     item__kv_category_praise_count_min_7d STRING,
     item__kv_day_h_click_count_avg_7d STRING,
     item__kv_category_click_count_avg_7d STRING,
     item__kv_day_h_praise_count_avg_7d STRING,
     item__kv_category_praise_count_avg_7d STRING,
     item__kv_day_h_click_count_sum_15d STRING,
     item__kv_category_click_count_sum_15d STRING,
     item__kv_day_h_praise_count_sum_15d STRING,
     item__kv_category_praise_count_sum_15d STRING,
     item__kv_day_h_click_count_max_15d STRING,
     item__kv_category_click_count_max_15d STRING,
     item__kv_day_h_praise_count_max_15d STRING,
     item__kv_category_praise_count_max_15d STRING,
     item__kv_day_h_click_count_min_15d STRING,
     item__kv_category_click_count_min_15d STRING,
     item__kv_day_h_praise_count_min_15d STRING,
     item__kv_category_praise_count_min_15d STRING,
     item__kv_day_h_click_count_avg_15d STRING,
     item__kv_category_click_count_avg_15d STRING,
     item__kv_day_h_praise_count_avg_15d STRING,
     item__kv_category_praise_count_avg_15d STRING,
     item__kv_day_h_click_count_sum_30d STRING,
     item__kv_category_click_count_sum_30d STRING,
     item__kv_day_h_praise_count_sum_30d STRING,
     item__kv_category_praise_count_sum_30d STRING,
     item__kv_day_h_click_count_max_30d STRING,
     item__kv_category_click_count_max_30d STRING,
     item__kv_day_h_praise_count_max_30d STRING,
     item__kv_category_praise_count_max_30d STRING,
     item__kv_day_h_click_count_min_30d STRING,
     item__kv_category_click_count_min_30d STRING,
     item__kv_day_h_praise_count_min_30d STRING,
     item__kv_category_praise_count_min_30d STRING,
     item__kv_day_h_click_count_avg_30d STRING,
     item__kv_category_click_count_avg_30d STRING,
     item__kv_day_h_praise_count_avg_30d STRING,
     item__kv_category_praise_count_avg_30d STRING,
     item__kv_day_h_click_count_sum_45d STRING,
     item__kv_category_click_count_sum_45d STRING,
     item__kv_day_h_praise_count_sum_45d STRING,
     item__kv_category_praise_count_sum_45d STRING,
     item__kv_day_h_click_count_max_45d STRING,
     item__kv_category_click_count_max_45d STRING,
     item__kv_day_h_praise_count_max_45d STRING,
     item__kv_category_praise_count_max_45d STRING,
     item__kv_day_h_click_count_min_45d STRING,
     item__kv_category_click_count_min_45d STRING,
     item__kv_day_h_praise_count_min_45d STRING,
     item__kv_category_praise_count_min_45d STRING,
     item__kv_day_h_click_count_avg_45d STRING,
     item__kv_category_click_count_avg_45d STRING,
     item__kv_day_h_praise_count_avg_45d STRING,
     item__kv_category_praise_count_avg_45d STRING,
     gender STRING,
     age BIGINT,
     city STRING,
     item_cnt BIGINT,
     follow_cnt BIGINT,
     follower_cnt BIGINT,
     is_new_user BIGINT,
     tags STRING,
     user__sum_playtime_3d DOUBLE,
     user__sum_duration_3d DOUBLE,
     user__sum_click_count_3d BIGINT,
     user__sum_praise_count_3d BIGINT,
     user__max_playtime_3d DOUBLE,
     user__max_duration_3d DOUBLE,
     user__max_click_count_3d BIGINT,
     user__max_praise_count_3d BIGINT,
     user__min_playtime_3d DOUBLE,
     user__min_duration_3d DOUBLE,
     user__min_click_count_3d BIGINT,
     user__min_praise_count_3d BIGINT,
     user__avg_playtime_3d DOUBLE,
     user__avg_duration_3d DOUBLE,
     user__avg_click_count_3d DOUBLE,
     user__avg_praise_count_3d DOUBLE,
     user__sum_playtime_7d DOUBLE,
     user__sum_duration_7d DOUBLE,
     user__sum_click_count_7d BIGINT,
     user__sum_praise_count_7d BIGINT,
     user__max_playtime_7d DOUBLE,
     user__max_duration_7d DOUBLE,
     user__max_click_count_7d BIGINT,
     user__max_praise_count_7d BIGINT,
     user__min_playtime_7d DOUBLE,
     user__min_duration_7d DOUBLE,
     user__min_click_count_7d BIGINT,
     user__min_praise_count_7d BIGINT,
     user__avg_playtime_7d DOUBLE,
     user__avg_duration_7d DOUBLE,
     user__avg_click_count_7d DOUBLE,
     user__avg_praise_count_7d DOUBLE,
     user__sum_playtime_15d DOUBLE,
     user__sum_duration_15d DOUBLE,
     user__sum_click_count_15d BIGINT,
     user__sum_praise_count_15d BIGINT,
     user__max_playtime_15d DOUBLE,
     user__max_duration_15d DOUBLE,
     user__max_click_count_15d BIGINT,
     user__max_praise_count_15d BIGINT,
     user__min_playtime_15d DOUBLE,
     user__min_duration_15d DOUBLE,
     user__min_click_count_15d BIGINT,
     user__min_praise_count_15d BIGINT,
     user__avg_playtime_15d DOUBLE,
     user__avg_duration_15d DOUBLE,
     user__avg_click_count_15d DOUBLE,
     user__avg_praise_count_15d DOUBLE,
     user__kv_day_h_duration_sum_7d STRING,
     user__kv_category_duration_sum_7d STRING,
     user__kv_day_h_click_count_sum_7d STRING,
     user__kv_category_click_count_sum_7d STRING,
     user__kv_day_h_duration_max_7d STRING,
     user__kv_category_duration_max_7d STRING,
     user__kv_day_h_click_count_max_7d STRING,
     user__kv_category_click_count_max_7d STRING,
     user__kv_day_h_duration_min_7d STRING,
     user__kv_category_duration_min_7d STRING,
     user__kv_day_h_click_count_min_7d STRING,
     user__kv_category_click_count_min_7d STRING,
     user__kv_day_h_duration_avg_7d STRING,
     user__kv_category_duration_avg_7d STRING,
     user__kv_day_h_click_count_avg_7d STRING,
     user__kv_category_click_count_avg_7d STRING,
     user__kv_day_h_duration_sum_15d STRING,
     user__kv_category_duration_sum_15d STRING,
     user__kv_day_h_click_count_sum_15d STRING,
     user__kv_category_click_count_sum_15d STRING,
     user__kv_day_h_duration_max_15d STRING,
     user__kv_category_duration_max_15d STRING,
     user__kv_day_h_click_count_max_15d STRING,
     user__kv_category_click_count_max_15d STRING,
     user__kv_day_h_duration_min_15d STRING,
     user__kv_category_duration_min_15d STRING,
     user__kv_day_h_click_count_min_15d STRING,
     user__kv_category_click_count_min_15d STRING,
     user__kv_day_h_duration_avg_15d STRING,
     user__kv_category_duration_avg_15d STRING,
     user__kv_day_h_click_count_avg_15d STRING,
     user__kv_category_click_count_avg_15d STRING,
     user__kv_day_h_duration_sum_30d STRING,
     user__kv_category_duration_sum_30d STRING,
     user__kv_day_h_click_count_sum_30d STRING,
     user__kv_category_click_count_sum_30d STRING,
     user__kv_day_h_duration_max_30d STRING,
     user__kv_category_duration_max_30d STRING,
     user__kv_day_h_click_count_max_30d STRING,
     user__kv_category_click_count_max_30d STRING,
     user__kv_day_h_duration_min_30d STRING,
     user__kv_category_duration_min_30d STRING,
     user__kv_day_h_click_count_min_30d STRING,
     user__kv_category_click_count_min_30d STRING,
     user__kv_day_h_duration_avg_30d STRING,
     user__kv_category_duration_avg_30d STRING,
     user__kv_day_h_click_count_avg_30d STRING,
     user__kv_category_click_count_avg_30d STRING,
     user__kv_day_h_duration_sum_45d STRING,
     user__kv_category_duration_sum_45d STRING,
     user__kv_day_h_click_count_sum_45d STRING,
     user__kv_category_click_count_sum_45d STRING,
     user__kv_day_h_duration_max_45d STRING,
     user__kv_category_duration_max_45d STRING,
     user__kv_day_h_click_count_max_45d STRING,
     user__kv_category_click_count_max_45d STRING,
     user__kv_day_h_duration_min_45d STRING,
     user__kv_category_duration_min_45d STRING,
     user__kv_day_h_click_count_min_45d STRING,
     user__kv_category_click_count_min_45d STRING,
     user__kv_day_h_duration_avg_45d STRING,
     user__kv_category_duration_avg_45d STRING,
     user__kv_day_h_click_count_avg_45d STRING,
     user__kv_category_click_count_avg_45d STRING
)
PARTITIONED BY (ds STRING)
LIFECYCLE 90;

INSERT OVERWRITE TABLE fs_demo_fs_engineering_v1_training_set PARTITION (ds = '${bdp.system.bizdate}')
SELECT
sq0.request_id,
sq0.user_id,
sq0.page,
sq0.net_type,
sq0.day_h,
sq0.week_day,
sq0.day_min,
sq0.event_unix_time,
sq0.item_id,
sq0.playtime,
sq0.is_click,
sq0.ln_playtime,
sq0.is_praise,
sq2.duration,
sq2.category,
sq2.author,
sq2.click_count,
sq2.praise_count,
sq2.is_new_item,
sq5.item__sum_item_cnt_3d,
sq5.item__sum_follow_cnt_3d,
sq5.item__sum_follower_cnt_3d,
sq5.item__max_item_cnt_3d,
sq5.item__max_follow_cnt_3d,
sq5.item__max_follower_cnt_3d,
sq5.item__min_item_cnt_3d,
sq5.item__min_follow_cnt_3d,
sq5.item__min_follower_cnt_3d,
sq5.item__avg_item_cnt_3d,
sq5.item__avg_follow_cnt_3d,
sq5.item__avg_follower_cnt_3d,
sq5.item__sum_item_cnt_7d,
sq5.item__sum_follow_cnt_7d,
sq5.item__sum_follower_cnt_7d,
sq5.item__max_item_cnt_7d,
sq5.item__max_follow_cnt_7d,
sq5.item__max_follower_cnt_7d,
sq5.item__min_item_cnt_7d,
sq5.item__min_follow_cnt_7d,
sq5.item__min_follower_cnt_7d,
sq5.item__avg_item_cnt_7d,
sq5.item__avg_follow_cnt_7d,
sq5.item__avg_follower_cnt_7d,
sq5.item__sum_item_cnt_15d,
sq5.item__sum_follow_cnt_15d,
sq5.item__sum_follower_cnt_15d,
sq5.item__max_item_cnt_15d,
sq5.item__max_follow_cnt_15d,
sq5.item__max_follower_cnt_15d,
sq5.item__min_item_cnt_15d,
sq5.item__min_follow_cnt_15d,
sq5.item__min_follower_cnt_15d,
sq5.item__avg_item_cnt_15d,
sq5.item__avg_follow_cnt_15d,
sq5.item__avg_follower_cnt_15d,
sq6.item__kv_day_h_click_count_sum_7d,
sq6.item__kv_category_click_count_sum_7d,
sq6.item__kv_day_h_praise_count_sum_7d,
sq6.item__kv_category_praise_count_sum_7d,
sq6.item__kv_day_h_click_count_max_7d,
sq6.item__kv_category_click_count_max_7d,
sq6.item__kv_day_h_praise_count_max_7d,
sq6.item__kv_category_praise_count_max_7d,
sq6.item__kv_day_h_click_count_min_7d,
sq6.item__kv_category_click_count_min_7d,
sq6.item__kv_day_h_praise_count_min_7d,
sq6.item__kv_category_praise_count_min_7d,
sq6.item__kv_day_h_click_count_avg_7d,
sq6.item__kv_category_click_count_avg_7d,
sq6.item__kv_day_h_praise_count_avg_7d,
sq6.item__kv_category_praise_count_avg_7d,
sq6.item__kv_day_h_click_count_sum_15d,
sq6.item__kv_category_click_count_sum_15d,
sq6.item__kv_day_h_praise_count_sum_15d,
sq6.item__kv_category_praise_count_sum_15d,
sq6.item__kv_day_h_click_count_max_15d,
sq6.item__kv_category_click_count_max_15d,
sq6.item__kv_day_h_praise_count_max_15d,
sq6.item__kv_category_praise_count_max_15d,
sq6.item__kv_day_h_click_count_min_15d,
sq6.item__kv_category_click_count_min_15d,
sq6.item__kv_day_h_praise_count_min_15d,
sq6.item__kv_category_praise_count_min_15d,
sq6.item__kv_day_h_click_count_avg_15d,
sq6.item__kv_category_click_count_avg_15d,
sq6.item__kv_day_h_praise_count_avg_15d,
sq6.item__kv_category_praise_count_avg_15d,
sq6.item__kv_day_h_click_count_sum_30d,
sq6.item__kv_category_click_count_sum_30d,
sq6.item__kv_day_h_praise_count_sum_30d,
sq6.item__kv_category_praise_count_sum_30d,
sq6.item__kv_day_h_click_count_max_30d,
sq6.item__kv_category_click_count_max_30d,
sq6.item__kv_day_h_praise_count_max_30d,
sq6.item__kv_category_praise_count_max_30d,
sq6.item__kv_day_h_click_count_min_30d,
sq6.item__kv_category_click_count_min_30d,
sq6.item__kv_day_h_praise_count_min_30d,
sq6.item__kv_category_praise_count_min_30d,
sq6.item__kv_day_h_click_count_avg_30d,
sq6.item__kv_category_click_count_avg_30d,
sq6.item__kv_day_h_praise_count_avg_30d,
sq6.item__kv_category_praise_count_avg_30d,
sq6.item__kv_day_h_click_count_sum_45d,
sq6.item__kv_category_click_count_sum_45d,
sq6.item__kv_day_h_praise_count_sum_45d,
sq6.item__kv_category_praise_count_sum_45d,
sq6.item__kv_day_h_click_count_max_45d,
sq6.item__kv_category_click_count_max_45d,
sq6.item__kv_day_h_praise_count_max_45d,
sq6.item__kv_category_praise_count_max_45d,
sq6.item__kv_day_h_click_count_min_45d,
sq6.item__kv_category_click_count_min_45d,
sq6.item__kv_day_h_praise_count_min_45d,
sq6.item__kv_category_praise_count_min_45d,
sq6.item__kv_day_h_click_count_avg_45d,
sq6.item__kv_category_click_count_avg_45d,
sq6.item__kv_day_h_praise_count_avg_45d,
sq6.item__kv_category_praise_count_avg_45d,
sq1.gender,
sq1.age,
sq1.city,
sq1.item_cnt,
sq1.follow_cnt,
sq1.follower_cnt,
sq1.is_new_user,
sq1.tags,
sq3.user__sum_playtime_3d,
sq3.user__sum_duration_3d,
sq3.user__sum_click_count_3d,
sq3.user__sum_praise_count_3d,
sq3.user__max_playtime_3d,
sq3.user__max_duration_3d,
sq3.user__max_click_count_3d,
sq3.user__max_praise_count_3d,
sq3.user__min_playtime_3d,
sq3.user__min_duration_3d,
sq3.user__min_click_count_3d,
sq3.user__min_praise_count_3d,
sq3.user__avg_playtime_3d,
sq3.user__avg_duration_3d,
sq3.user__avg_click_count_3d,
sq3.user__avg_praise_count_3d,
sq3.user__sum_playtime_7d,
sq3.user__sum_duration_7d,
sq3.user__sum_click_count_7d,
sq3.user__sum_praise_count_7d,
sq3.user__max_playtime_7d,
sq3.user__max_duration_7d,
sq3.user__max_click_count_7d,
sq3.user__max_praise_count_7d,
sq3.user__min_playtime_7d,
sq3.user__min_duration_7d,
sq3.user__min_click_count_7d,
sq3.user__min_praise_count_7d,
sq3.user__avg_playtime_7d,
sq3.user__avg_duration_7d,
sq3.user__avg_click_count_7d,
sq3.user__avg_praise_count_7d,
sq3.user__sum_playtime_15d,
sq3.user__sum_duration_15d,
sq3.user__sum_click_count_15d,
sq3.user__sum_praise_count_15d,
sq3.user__max_playtime_15d,
sq3.user__max_duration_15d,
sq3.user__max_click_count_15d,
sq3.user__max_praise_count_15d,
sq3.user__min_playtime_15d,
sq3.user__min_duration_15d,
sq3.user__min_click_count_15d,
sq3.user__min_praise_count_15d,
sq3.user__avg_playtime_15d,
sq3.user__avg_duration_15d,
sq3.user__avg_click_count_15d,
sq3.user__avg_praise_count_15d,
sq4.user__kv_day_h_duration_sum_7d,
sq4.user__kv_category_duration_sum_7d,
sq4.user__kv_day_h_click_count_sum_7d,
sq4.user__kv_category_click_count_sum_7d,
sq4.user__kv_day_h_duration_max_7d,
sq4.user__kv_category_duration_max_7d,
sq4.user__kv_day_h_click_count_max_7d,
sq4.user__kv_category_click_count_max_7d,
sq4.user__kv_day_h_duration_min_7d,
sq4.user__kv_category_duration_min_7d,
sq4.user__kv_day_h_click_count_min_7d,
sq4.user__kv_category_click_count_min_7d,
sq4.user__kv_day_h_duration_avg_7d,
sq4.user__kv_category_duration_avg_7d,
sq4.user__kv_day_h_click_count_avg_7d,
sq4.user__kv_category_click_count_avg_7d,
sq4.user__kv_day_h_duration_sum_15d,
sq4.user__kv_category_duration_sum_15d,
sq4.user__kv_day_h_click_count_sum_15d,
sq4.user__kv_category_click_count_sum_15d,
sq4.user__kv_day_h_duration_max_15d,
sq4.user__kv_category_duration_max_15d,
sq4.user__kv_day_h_click_count_max_15d,
sq4.user__kv_category_click_count_max_15d,
sq4.user__kv_day_h_duration_min_15d,
sq4.user__kv_category_duration_min_15d,
sq4.user__kv_day_h_click_count_min_15d,
sq4.user__kv_category_click_count_min_15d,
sq4.user__kv_day_h_duration_avg_15d,
sq4.user__kv_category_duration_avg_15d,
sq4.user__kv_day_h_click_count_avg_15d,
sq4.user__kv_category_click_count_avg_15d,
sq4.user__kv_day_h_duration_sum_30d,
sq4.user__kv_category_duration_sum_30d,
sq4.user__kv_day_h_click_count_sum_30d,
sq4.user__kv_category_click_count_sum_30d,
sq4.user__kv_day_h_duration_max_30d,
sq4.user__kv_category_duration_max_30d,
sq4.user__kv_day_h_click_count_max_30d,
sq4.user__kv_category_click_count_max_30d,
sq4.user__kv_day_h_duration_min_30d,
sq4.user__kv_category_duration_min_30d,
sq4.user__kv_day_h_click_count_min_30d,
sq4.user__kv_category_click_count_min_30d,
sq4.user__kv_day_h_duration_avg_30d,
sq4.user__kv_category_duration_avg_30d,
sq4.user__kv_day_h_click_count_avg_30d,
sq4.user__kv_category_click_count_avg_30d,
sq4.user__kv_day_h_duration_sum_45d,
sq4.user__kv_category_duration_sum_45d,
sq4.user__kv_day_h_click_count_sum_45d,
sq4.user__kv_category_click_count_sum_45d,
sq4.user__kv_day_h_duration_max_45d,
sq4.user__kv_category_duration_max_45d,
sq4.user__kv_day_h_click_count_max_45d,
sq4.user__kv_category_click_count_max_45d,
sq4.user__kv_day_h_duration_min_45d,
sq4.user__kv_category_duration_min_45d,
sq4.user__kv_day_h_click_count_min_45d,
sq4.user__kv_category_click_count_min_45d,
sq4.user__kv_day_h_duration_avg_45d,
sq4.user__kv_category_duration_avg_45d,
sq4.user__kv_day_h_click_count_avg_45d,
sq4.user__kv_category_click_count_avg_45d
FROM
(
    SELECT *
    FROM rec_sln_demo_fs_label_table_v1
    WHERE ds = '${bdp.system.bizdate}'
) sq0
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_user_table_preprocess_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq1 ON sq0.user_id = sq1.user_id
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_item_table_preprocess_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq2 ON sq0.item_id = sq2.item_id
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_user_table_preprocess_agg_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq3 ON sq0.user_id = sq3.user_id
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_user_table_preprocess_win_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq4 ON sq0.user_id = sq4.user_id
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_item_table_preprocess_agg_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq5 ON sq0.item_id = sq5.item_id
LEFT JOIN (
    SELECT *
    FROM <project_name>.rec_sln_demo_item_table_preprocess_win_v1
    WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), -1,'dd'),'yyyymmdd')
) sq6 ON sq0.item_id = sq6.item_id;

Le résultat est fs_demo_fs_engineering_v1_training_set, une table partitionnée avec un cycle de vie de 90 jours qui combine toutes les features utilisateur, les features élément et les étiquettes d'entraînement dans une seule ligne par requête.

Étapes suivantes

Pour comprendre l'intégralité de la plateforme de features FeatureStore, y compris la diffusion en ligne et l'enregistrement des features, consultez Présentation de FeatureStore.
Pour la référence complète de l'API concernant les transformations d'ingénierie des features, consultez Plateforme de features et ingénierie des features.
Pour une assistance technique, rejoignez le groupe DingTalk 34415007523.