data_analysis/table_gen/loghubods.user_behavior.sql

CREATE TABLE IF NOT EXISTS loghubods.user_behavior
(
    app_type    STRING  COMMENT '应用类型',
    mid         STRING  COMMENT '用户标识',
    action_type STRING  COMMENT '行为类型: share=分享 / click=回流',
    item_id     STRING  COMMENT '视频ID',
    ts          BIGINT  COMMENT '事件时间戳(秒)',
    scene_json  STRING  COMMENT '场景信息(预留)',
    ext_json    STRING  COMMENT '扩展信息(预留)'
)
PARTITIONED BY (dt STRING)
;

INSERT OVERWRITE TABLE loghubods.user_behavior PARTITION (dt = '${dt}')
SELECT  apptype                                     AS app_type
       ,machinecode                                 AS mid
       ,topic                                       AS action_type
       ,CASE WHEN topic = 'share' THEN shareobjectid
             WHEN topic = 'click' THEN clickobjectid
        END                                         AS item_id
       ,CAST(clienttimestamp / 1000 AS BIGINT)       AS ts
       ,NULL                                        AS scene_json
       ,NULL                                        AS ext_json
FROM    loghubods.user_share_log
WHERE   dt = '${dt}'
AND     topic IN ('share', 'click')
AND     machinecode IS NOT NULL
;