-- DROP TABLE IF EXISTS loghubods.user_relation;

CREATE TABLE IF NOT EXISTS loghubods.user_relation
(
    apptype   STRING  COMMENT '应用类型',
    from_mid  STRING  COMMENT '分享者标识',
    to_mid    STRING  COMMENT '点击者标识',
    item_id   STRING  COMMENT '视频ID',
    ts        BIGINT  COMMENT '点击时间戳(秒)',
    root_mid  STRING  COMMENT '根分享者标识',
    ext_json  STRING  COMMENT '扩展信息'
)
PARTITIONED BY (dt STRING)
;

INSERT OVERWRITE TABLE loghubods.user_relation PARTITION (dt = '${dt}')
SELECT  apptype
       ,from_mid
       ,machinecode                                                          AS to_mid
       ,clickobjectid                                                        AS item_id
       ,CAST(clienttimestamp / 1000 AS BIGINT)                               AS ts
       ,CASE WHEN root_mid LIKE 'weixin_openid_%'
                  OR root_mid 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}$'
             THEN root_mid
        END                                                                 AS root_mid
       ,CONCAT('{"depth":', CAST(usersharedepth AS BIGINT), '}')             AS ext_json
FROM (
    SELECT  apptype, shareid, rootshareid, machinecode, clickobjectid, clienttimestamp, usersharedepth
           ,REGEXP_REPLACE(shareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') AS from_mid
           ,REGEXP_REPLACE(rootshareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') AS root_mid
    FROM    loghubods.user_share_log
    WHERE   dt = '${dt}'
    AND     topic = 'click'
) t
WHERE   from_mid LIKE 'weixin_openid_%'
OR      from_mid 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}$'
;
