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

-- 作者：tianyu.chu
-- 日期：2026-06-10
-- 工单：(无)
-- 目的：埋点 raw → ods，解析脱敏后 _source JSON 拍平公共属性 + 保留 params_json；按文件日 dt 动态分区写入
-- 状态：[待执行]
-- 备注：日期范围 [${start_date}, ${stop_date}] 含两端、动态分区按 raw.dt 写——日常增量传 start=stop=单日，
--       历史回算传全区间，一份 SQL 两用（避免解析逻辑复制漂移，对齐 ADR-11）；
--       dt = 文件日（N=1 不归位）：实测 ~99.4% 事件日=文件日，~0.6% 迟到/未来小偏差按当天落，业务允许（见 kb/13 §8）；
--       动态覆盖本环境只覆盖 SELECT 出现的分区（kb/21 §8）；事件不可变，无双源 union / 无去重；时区随集群（东八区）

INSERT OVERWRITE TABLE ods.ods_usr_traces_apd_d PARTITION (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,
    dt                                                                                 AS dt
FROM raw.raw_usr_traces_apd_d
WHERE dt BETWEEN '${start_date}' AND '${stop_date}'
DISTRIBUTE BY dt;