produção de features, melhores práticas - Platform For AI: Central de Documentação do Alibaba Cloud

O FeatureStore, plataforma de features do Platform for AI (PAI), padroniza operações comuns de engenharia de features para gerar centenas de features a partir de tabelas source brutas com apenas algumas linhas de código. Este guia apresenta um cenário de recomendação: a partir de três tabelas source, você criará tabelas de features de usuário e item e unificará tudo em um conjunto de dados de treinamento completo, pronto para treinar o modelo.

O que você construirá

Ao final deste guia, você terá:

Três tabelas source pré-processadas (usuário, item e comportamento) sincronizadas no seu projeto MaxCompute
Uma tabela ampla de comportamento e uma tabela de rótulos derivadas dessas fontes
Duas tabelas de features de usuário geradas com AggregationTransform e WindowTransform
Duas tabelas de features de item geradas da mesma forma
Um conjunto de dados de treinamento final (fs_demo_fs_engineering_v1_training_set) que une todas as sete tabelas

Pré-requisitos

Antes de começar, verifique se os seguintes itens estão configurados:

PAI

Ativar o PAI e criar um workspace do PAI
Prepare seu AccessKey ID e AccessKey secret. Armazene-os como variáveis de ambiente em vez de codificá-los diretamente no código. Para instruções de configuração, consulte Criar um par de AccessKey e Configurar o AccessKey de autenticação na variável de ambiente

MaxCompute

DataWorks

Ative o DataWorks
Adquira um grupo de recursos exclusivo para agendamento e anexe-o ao seu workspace do DataWorks. Consulte Usar um grupo de recursos exclusivo para agendamento
Adicione uma fonte de dados MaxCompute ao seu workspace do DataWorks. Consulte Vincular um recurso de computação MaxCompute

Como funciona

Este guia segue quatro etapas:

Preparação — Sincronize as três tabelas source de demonstração do pai_online_project para o seu projeto MaxCompute e instale o SDK Python do FeatureStore.
Transformação — Una e pré-processe as tabelas source para produzir uma tabela ampla de comportamento e uma tabela de rótulos.
Geração de features — Execute pipelines do FeatureStore para produzir tabelas de features de agregação e janela tanto para usuários quanto para itens.
Construção do conjunto de dados de treinamento — Una todas as tabelas de features em uma única tabela de amostras.

Etapa 1: Preparar tabelas source e SDK

Sincronizar tabelas source

As três tabelas source deste tutorial estão armazenadas no workspace compartilhado pai_online_project e contêm apenas dados simulados. Use o DataWorks para copiá-las para o seu próprio projeto MaxCompute.

Tabela	Conteúdo
`rec_sln_demo_user_table_preprocess_v1`	Features básicas de usuário: gênero, idade, cidade, número de seguidores
`rec_sln_demo_item_table_preprocess_v1`	Features básicas de item: categoria, autor, cliques acumulados, elogios acumulados
`rec_sln_demo_behavior_table_preprocess_v1`	Eventos de comportamento: qual usuário clicou em qual item e quando

Para sincronizar as tabelas:

Acesse o console do DataWorks.
No painel de navegação à esquerda, clique em Data Development and O&M > Data Development.
Selecione seu workspace do DataWorks e clique em Go to Data Studio.

Passe o mouse sobre Create e escolha Create Node > MaxCompute > ODPS SQL. Configure os parâmetros do nó:

Parâmetro	Valor
Node Type	ODPS SQL
Path	Business Flow/Workflow/MaxCompute
Name	Insira um nome personalizado

Clique em Confirm.

No editor SQL, execute as instruções a seguir. Cada bloco cria a estrutura da tabela e carrega dados do intervalo de datas de 20240530 a 20240605. Tabela de usuário:

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';

Tabela de item:

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';

Tabela de comportamento:

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';

Instalar o SDK do FeatureStore

Execute o seguinte comando em um 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

Em seguida, importe os módulos necessários para este guia:

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
)

Etapa 2: Transformar tabelas source

Antes de iniciar a geração de features, remodele as três tabelas source em duas tabelas de trabalho: uma tabela ampla de comportamento que une as três fontes e uma tabela de rótulos que extrai rótulos de treinamento dos dados de comportamento.

Utilize o nó ODPS SQL criado na etapa 1 para executar ambos os blocos SQL abaixo.

Criar a tabela ampla de comportamento

Esta instrução une atributos de usuário, atributos de item e eventos de comportamento em uma única tabela desnormalizada. O SDK do FeatureStore utiliza essa tabela para computar 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;

Criar a tabela de rótulos

Esta instrução deriva rótulos de treinamento da tabela de comportamento. Os rótulos são is_click (se o usuário clicou), ln_playtime (tempo total de reprodução transformado em log) e is_praise (se o usuário elogiou o item).

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;

Após a conclusão de ambas as instruções, você terá:

rec_sln_demo_behavior_table_preprocess_wide_v1 — entrada para geração de features
rec_sln_demo_fs_label_table_v1 — entrada para a tabela final de amostras

Etapa 3: Gerar features

As funções de extensão automática do FeatureStore permitem gerar centenas de features estatísticas e de janela com uma única chamada de função, eliminando a necessidade de escrever SQL de agregação manualmente. As etapas abaixo geram features separadamente para usuários e itens.

Todos os exemplos utilizam a API de pipeline do SDK do FeatureStore: defina as fontes de dados e as transformações e chame execute() para executar a computação no MaxCompute.

Inicializar o cliente

# 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
)

Vincular tabelas source a fontes de dados

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)

Gerar features de usuário

AggregationTransform

A função auto_count_feature_transform gera features de agregação (soma, máximo, mínimo, média) para cada métrica em múltiplas janelas de tempo — neste caso, janelas de 3, 7 e 15 dias agrupadas por user_id.

Defina as 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)

Crie o 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
)

Execute e verifique:

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

A função auto_window_feature_transform gera features de janela multidimensionais — por exemplo, como a contagem de cliques de um usuário varia por hora do dia ou categoria de conteúdo em diferentes janelas de tempo.

Defina as 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)

Crie o 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
)

Execute e verifique:

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

Gerar features de item

A geração de features de item segue o mesmo padrão das features de usuário, utilizando item_id como chave de agrupamento.

AggregationTransform

Defina as 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)

Crie o 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
)

Execute e verifique:

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

Defina as 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)

Crie o 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
)

Execute e verifique:

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

Etapa 4: Construir o conjunto de dados de treinamento

Neste ponto, você possui sete tabelas: uma tabela de rótulos, três tabelas do lado do usuário (source, agregação, janela) e três tabelas do lado do item (source, agregação, janela). O SQL abaixo une todas as sete em um único conjunto de dados de treinamento usando LEFT JOINs com base em user_id e item_id.

Substitua <project_name> pelo nome real do seu projeto MaxCompute antes de executar.

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;

O resultado é a tabela fs_demo_fs_engineering_v1_training_set, particionada e com ciclo de vida de 90 dias, que combina todas as features de usuário, features de item e rótulos de treinamento em uma única linha por solicitação.

Próximos passos

Para compreender a plataforma completa de features do FeatureStore, incluindo serviço online e registro de features, consulte Visão geral do FeatureStore.
Para a referência completa da API de transformações de engenharia de features, consulte Plataforma de features e engenharia de features.
Para suporte técnico, participe do grupo DingTalk 34415007523.