poyee-data-warehouse/jobs/ods/usr/ods_usr_traces_apd_d.sql

-- 作者：tianyu.chu
-- 日期：2026-06-10
-- 工单：(无)
-- 目的：埋点 raw → ods，解析脱敏后 _source JSON 拍平公共属性 + 保留 params_json；按事件日 dt 静态分区写入
-- 状态：[待执行]
-- 备注：ES 按事件日分索引，raw.dt = 事件日，故 dt 静态写 ${dt}（不用动态分区，避开动态覆盖风险）；
--       事件不可变，无双源 union / 无 update_time 去重；es_id 单文件内唯一；时区随集群（东八区）

INSERT OVERWRITE TABLE ods.ods_usr_traces_apd_d PARTITION (dt = '${dt}')
SELECT
    es_id                                                                              AS es_id,
    event_name                                                                         AS event_name,
    get_json_object(raw_json, '$.type')                                                AS evt_type,
    CAST(CAST(get_json_object(raw_json, '$.time')      AS BIGINT) / 1000 AS TIMESTAMP) AS event_time,
    CAST(CAST(get_json_object(raw_json, '$.flushTime') AS BIGINT) / 1000 AS TIMESTAMP) AS flush_time,
    get_json_object(raw_json, '$.loginId')                                             AS login_id,
    get_json_object(raw_json, '$.distinctId')                                          AS distinct_id,
    get_json_object(raw_json, '$.anonymousId')                                         AS anonymous_id,
    get_json_object(raw_json, '$.properties.userId')                                   AS user_id,
    get_json_object(raw_json, '$.properties.userName')                                 AS user_name,
    get_json_object(raw_json, '$.properties.userLevel')                                AS user_lvl,
    get_json_object(raw_json, '$.lib.lib')                                             AS lib,
    get_json_object(raw_json, '$.lib.libVersion')                                      AS lib_version,
    get_json_object(raw_json, '$.lib.libMethod')                                       AS lib_method,
    get_json_object(raw_json, '$.properties.appId')                                    AS app_id,
    get_json_object(raw_json, '$.properties.appName')                                  AS app_name,
    get_json_object(raw_json, '$.properties.appVersion')                               AS app_version,
    get_json_object(raw_json, '$.properties.wgtVersion')                               AS wgt_version,
    get_json_object(raw_json, '$.properties.os')                                       AS os,
    get_json_object(raw_json, '$.properties.osVersion')                                AS os_version,
    get_json_object(raw_json, '$.properties.manufacturer')                             AS manufacturer,
    get_json_object(raw_json, '$.properties.brand')                                    AS brand,
    get_json_object(raw_json, '$.properties.model')                                    AS model,
    get_json_object(raw_json, '$.properties.deviceId')                                 AS device_id,
    get_json_object(raw_json, '$.properties.networkType')                              AS network_type,
    get_json_object(raw_json, '$.properties.carrier')                                  AS carrier,
    CAST(get_json_object(raw_json, '$.properties.wifi')                 AS BOOLEAN)     AS wifi,
    CAST(get_json_object(raw_json, '$.properties.screenWidth')          AS BIGINT)      AS screen_width,
    CAST(get_json_object(raw_json, '$.properties.screenHeight')         AS BIGINT)      AS screen_height,
    CAST(get_json_object(raw_json, '$.properties.timezoneOffset')       AS BIGINT)      AS timezone_offset,
    CAST(get_json_object(raw_json, '$.properties.isFirstDay')           AS BOOLEAN)     AS is_first_day,
    CAST(get_json_object(raw_json, '$.properties.isFirstTime')          AS BOOLEAN)     AS is_first_time,
    CAST(get_json_object(raw_json, '$.properties.resumeFromBackground') AS BOOLEAN)     AS resume_from_background,
    CAST(get_json_object(raw_json, '$.properties.eventDuration')        AS BIGINT)      AS event_duration,
    get_json_object(raw_json, '$.properties.params')                                   AS params_json
FROM raw.raw_usr_traces_apd_d
WHERE dt = '${dt}';