CREATE TABLE IF NOT EXISTS loghubods.user_behavior
(
    apptype     STRING  COMMENT '应用类型',
    mid         STRING  COMMENT '用户标识',
    action_type STRING  COMMENT '行为类型: 分享/回流',
    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
       ,machinecode                                 AS mid
       ,CASE WHEN topic = 'share' THEN '分享'
             WHEN topic = 'click' THEN '回流'
        END                                         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 LIKE 'weixin_openid_%'
         OR machinecode RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$')
;
