| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 |
- -- 作者：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}';
|
