目录一、整体架构理解1. 数据分层架构2. 事务事实表特点二、各表详细解析1. 工具域优惠券使用事务事实表业务场景深度解析数据流转细节装载逻辑详解典型分析场景2. 互动域收藏商品事务事实表业务场景深度解析数据装载技术细节数据分析应用3. 流量域页面浏览事务事实表业务场景深度解析字段详细解释复杂技术点解析流量分析实战4. 用户域用户注册事务事实表业务场景深度解析表关联逻辑深度解析注册分析实战5. 用户域用户登录事务事实表业务场景深度解析技术实现深度解析登录行为分析实战三、数据治理与质量控制1. 数据一致性检查2. 数据血缘分析3. 性能优化建议四、业务应用场景总结1. 运营分析2. 用户分析3. 产品分析4. 商业分析五、常见问题与解决方案1. 数据延迟问题2. 数据质量监控3. 数据回溯处理六、完整代码一、整体架构理解1. 数据分层架构原始数据 → ODS层原始数据层 → **DWD层数据明细层** → DWS层数据服务层 → ADS层应用数据层2. 事务事实表特点粒度最细粒度业务事件维度包含丰富的上下文维度信息度量主要是计数型指标更新策略首日全量 每日增量二、各表详细解析1. 工具域优惠券使用事务事实表业务场景深度解析使用时机用户下单支付时核销优惠券前置状态优惠券已领取但未使用触发事件订单支付成功业务价值计算优惠券核销率分析优惠券对订单转化率的影响评估营销活动ROI数据流转细节-- ODS层数据结构示例 ods_coupon_use_inc { type: bootstrap-insert 或 update, data: { id: 123, coupon_id: coupon_001, user_id: user_1001, order_id: order_20220608001, used_time: 2022-06-08 10:30:45 -- 关键字段不为空表示已使用 }, old: {used_time: null} -- 更新前值用于判断状态变更 }装载逻辑详解-- 首日装载处理历史全量数据 -- 筛选条件used_time不为空的记录 -- 这表示优惠券已经被使用核销 -- 每日增量装载处理当日状态变更 -- array_contains(map_keys(old),used_time) 解释 -- 1. map_keys(old)获取old字典的所有键 -- 2. array_contains检查数组中是否包含used_time -- 3. 业务含义记录从未使用状态变更为已使用状态 -- 4. 为什么不用data.used_time is not null -- 因为可能是其他字段的更新需要确保是状态变更典型分析场景-- 1. 每日优惠券核销统计 SELECT date_id, COUNT(DISTINCT coupon_id) as used_coupons, COUNT(DISTINCT user_id) as using_users, COUNT(DISTINCT order_id) as using_orders FROM dwd_tool_coupon_used_inc WHERE dt BETWEEN 2022-06-01 AND 2022-06-30 GROUP BY date_id ORDER BY date_id; -- 2. 优惠券核销时间分布 SELECT HOUR(payment_time) as hour_of_day, COUNT(*) as usage_count FROM dwd_tool_coupon_used_inc WHERE dt 2022-06-09 GROUP BY HOUR(payment_time) ORDER BY hour_of_day;2. 互动域收藏商品事务事实表业务场景深度解析用户行为主动表达对商品的兴趣业务价值用户兴趣画像收藏行为反映用户偏好商品热度分析被收藏次数多的商品更受欢迎转化预测收藏到购买的转化率分析推荐系统协同过滤算法的重要输入数据装载技术细节-- 首日装载全量历史收藏记录 -- 为什么用bootstrap-insert -- 这是数据初始化的一种约定表示首次全量导入 -- 每日装载只处理insert类型 -- 为什么不是update -- 收藏行为一般是新增取消收藏可能是delete或update状态 -- 这里只记录添加收藏的行为取消收藏不记录或记录在另一张事实表数据分析应用-- 1. 用户收藏行为分析 SELECT user_id, COUNT(*) as total_favors, COUNT(DISTINCT sku_id) as unique_sku_favors, MIN(create_time) as first_favor_time, MAX(create_time) as latest_favor_time FROM dwd_interaction_favor_add_inc WHERE dt 2022-06-09 GROUP BY user_id HAVING COUNT(*) 5; -- 找出重度收藏用户 -- 2. 商品收藏热度排行 SELECT sku_id, COUNT(*) as favor_count, COUNT(DISTINCT user_id) as favored_users, DATE_FORMAT(MIN(create_time), yyyy-MM-dd) as first_favor_date FROM dwd_interaction_favor_add_inc WHERE dt BETWEEN 2022-06-01 AND 2022-06-09 GROUP BY sku_id ORDER BY favor_count DESC LIMIT 100; -- 3. 收藏转化漏斗分析需要关联订单表 WITH favor_stats AS ( SELECT f.user_id, f.sku_id, f.create_time as favor_time, o.order_time, CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END as is_converted FROM dwd_interaction_favor_add_inc f LEFT JOIN dwd_order_info_inc o ON f.user_id o.user_id AND f.sku_id o.sku_id AND o.order_time f.create_time AND o.order_time DATE_ADD(f.create_time, 7, DAY) -- 7天内转化 WHERE f.dt 2022-06-01 ) SELECT COUNT(*) as total_favors, SUM(is_converted) as converted_favors, ROUND(SUM(is_converted) * 100.0 / COUNT(*), 2) as conversion_rate FROM favor_stats;3. 流量域页面浏览事务事实表业务场景深度解析页面类型分类首页、商品详情页、购物车页、订单确认页、支付页活动页、品牌页、搜索结果页、个人中心页用户路径分析页面流A→B→C跳出率只访问一个页面就离开转化路径浏览→点击→加购→下单→支付字段详细解释-- 设备维度 mid_id: device_001 -- 设备唯一标识用于设备级统计 brand: Apple -- 设备品牌分析用户设备偏好 model: iPhone 13 -- 设备型号 operate_system: iOS 15.4 -- 操作系统版本 version_code: 3.2.1 -- APP版本用于分析版本迭代效果 -- 用户维度 user_id: user_1001 -- 登录用户ID可能为空未登录用户 province_id: 440000 -- 省份ID基于IP解析 -- 页面维度 page_id: home -- 页面标识 last_page_id: search -- 上一个页面分析流量来源 page_item: sku_1001 -- 页面内容如商品ID page_item_type: sku -- 内容类型sku、activity、brand等 -- 流量来源 refer_id: channel_001 -- 营销渠道ID广告渠道、社交媒体等 from_pos_id: banner_01 -- 页面内的位置如banner位 from_pos_seq: 1 -- 同一位置多个内容的排序 -- 行为度量 during_time: 5000 -- 页面停留时长毫秒计算页面吸引力 view_time: 2022-06-08 10:30:45 -- 进入页面时间 session_id: session_001 -- 会话ID同一会话内多次页面浏览复杂技术点解析-- 1. 时间转换为什么要用from_utc_timestamp -- 原始日志中的ts通常是UTC时间戳如1654669845000 -- 业务分析需要本地时间GMT8北京时间 -- date_format(from_utc_timestamp(ts,GMT8),yyyy-MM-dd) 作用 -- a. from_utc_timestamp(ts,GMT8): 将UTC时间戳转为北京时间 -- b. date_format(...): 提取日期部分作为date_id -- 2. 为什么设置hive.cbo.enablefalse -- CBOCost Based Optimizer是Hive的基于成本的优化器 -- 在复杂查询中CBO可能生成非最优执行计划 -- 对于ETL处理有时关闭CBO可以获得更稳定的性能 -- 3. 页面浏览的会话划分逻辑 -- 会话(session)是用户连续访问的页面序列 -- 通常按以下规则划分 -- a. 同一设备mid_id同一用户user_id的连续访问 -- b. 相邻页面访问间隔不超过30分钟 -- c. 会话ID在日志采集时生成流量分析实战-- 1. 基础流量指标计算 WITH daily_traffic AS ( SELECT date_id, COUNT(*) as pv, -- 页面浏览量 COUNT(DISTINCT mid_id) as uv, -- 访客数基于设备 COUNT(DISTINCT user_id) as login_uv, -- 登录用户数 COUNT(DISTINCT session_id) as sessions, -- 会话数 AVG(during_time) as avg_duration, -- 平均页面停留时长 SUM(CASE WHEN user_id IS NOT NULL THEN 1 ELSE 0 END) as login_pv -- 登录用户PV FROM dwd_traffic_page_view_inc WHERE dt 2022-06-08 GROUP BY date_id ) SELECT * FROM daily_traffic; -- 2. 页面热力图分析 SELECT page_id, COUNT(*) as pv, COUNT(DISTINCT mid_id) as uv, COUNT(DISTINCT user_id) as login_users, AVG(during_time) as avg_duration, PERCENTILE(during_time, 0.5) as median_duration, -- 中位数更抗异常 SUM(CASE WHEN last_page_id IS NULL THEN 1 ELSE 0 END) as entry_count, -- 入口页面次数 SUM(CASE WHEN during_time 3000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as bounce_rate -- 跳出率估算 FROM dwd_traffic_page_view_inc WHERE dt 2022-06-08 GROUP BY page_id ORDER BY pv DESC; -- 3. 用户路径分析页面流 WITH page_flow AS ( SELECT session_id, page_id, last_page_id, view_time, LAG(page_id) OVER (PARTITION BY session_id ORDER BY view_time) as prev_page, LEAD(page_id) OVER (PARTITION BY session_id ORDER BY view_time) as next_page, ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY view_time) as page_seq FROM dwd_traffic_page_view_inc WHERE dt 2022-06-08 AND session_id IS NOT NULL ) SELECT COALESCE(last_page_id, entry) as from_page, page_id as to_page, COUNT(*) as transition_count, COUNT(DISTINCT session_id) as session_count FROM page_flow WHERE last_page_id IS NOT NULL OR page_seq 1 GROUP BY COALESCE(last_page_id, entry), page_id ORDER BY transition_count DESC LIMIT 20; -- 4. 转化漏斗分析以商品详情页到下单为例 WITH user_journey AS ( SELECT user_id, session_id, MAX(CASE WHEN page_id good_detail THEN 1 ELSE 0 END) as viewed_detail, MAX(CASE WHEN page_id cart THEN 1 ELSE 0 END) as viewed_cart, MAX(CASE WHEN page_id trade THEN 1 ELSE 0 END) as viewed_trade FROM dwd_traffic_page_view_inc WHERE dt 2022-06-08 AND user_id IS NOT NULL GROUP BY user_id, session_id ) SELECT COUNT(*) as total_sessions, SUM(viewed_detail) as detail_sessions, SUM(viewed_cart) as cart_sessions, SUM(viewed_trade) as trade_sessions, ROUND(SUM(viewed_detail) * 100.0 / COUNT(*), 2) as detail_rate, ROUND(SUM(viewed_cart) * 100.0 / SUM(viewed_detail), 2) as cart_conversion, ROUND(SUM(viewed_trade) * 100.0 / SUM(viewed_cart), 2) as trade_conversion FROM user_journey; -- 5. 渠道效果分析 SELECT channel, refer_id, COUNT(*) as pv, COUNT(DISTINCT mid_id) as uv, COUNT(DISTINCT user_id) as login_uv, SUM(CASE WHEN page_id good_detail THEN 1 ELSE 0 END) as detail_pv, SUM(CASE WHEN page_item_type sku THEN 1 ELSE 0 END) as sku_view_pv FROM dwd_traffic_page_view_inc WHERE dt 2022-06-08 AND refer_id IS NOT NULL GROUP BY channel, refer_id ORDER BY pv DESC;4. 用户域用户注册事务事实表业务场景深度解析注册渠道分析自然流量用户主动下载注册广告渠道通过广告点击下载注册社交分享通过分享链接注册地推扫码线下推广扫码注册用户质量评估注册设备分布注册地域分布注册时间分布表关联逻辑深度解析-- 关键关联用户信息表 LEFT JOIN 日志表 -- 为什么用LEFT JOIN -- 1. 以用户信息为主表确保所有注册用户都被记录 -- 2. 日志表中可能没有某些用户的注册页面记录原因可能包括 -- a. 老用户注册时日志系统未上线 -- b. 注册时网络问题导致日志丢失 -- c. 通过API注册不走页面流程 -- 关联条件详解 -- ui.user_id log.user_id -- 日志筛选条件 -- page.page_idregister -- 注册页面 -- common.uid is not null -- 用户ID不为空已注册 -- dt2022-06-08 -- 同一天T1处理假设注册和日志同天 -- 潜在问题与解决方案 -- 1. 时间差问题注册时间和日志时间可能不是同一天 -- 解决方案扩大日志查询时间范围如注册时间前后3天 -- 2. 多设备注册一个用户可能用多个设备注册 -- 处理逻辑取时间最近的设备信息 -- 3. 注册后立即登录日志可能记录的是登录页面而非注册页面 -- 需要根据业务逻辑调整筛选条件注册分析实战-- 1. 每日注册用户统计含渠道分布 SELECT date_id, COUNT(*) as total_registrations, COUNT(CASE WHEN channel appstore THEN user_id END) as appstore_reg, COUNT(CASE WHEN channel huawei_store THEN user_id END) as huawei_reg, COUNT(CASE WHEN channel LIKE %wechat% THEN user_id END) as wechat_reg, COUNT(CASE WHEN channel IS NULL THEN user_id END) as unknown_channel, COUNT(DISTINCT province_id) as province_count FROM dwd_user_register_inc WHERE dt BETWEEN 2022-06-01 AND 2022-06-30 GROUP BY date_id ORDER BY date_id; -- 2. 注册用户设备分析 SELECT operate_system, brand, model, COUNT(*) as user_count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage FROM dwd_user_register_inc WHERE dt 2022-06-09 GROUP BY operate_system, brand, model ORDER BY user_count DESC LIMIT 20; -- 3. 注册时间分布分析小时级 SELECT HOUR(create_time) as register_hour, COUNT(*) as register_count, AVG(COUNT(*)) OVER () as avg_per_hour, COUNT(*) - AVG(COUNT(*)) OVER () as diff_from_avg FROM dwd_user_register_inc WHERE dt 2022-06-09 GROUP BY HOUR(create_time) ORDER BY register_hour; -- 4. 注册用户地域分布 SELECT p.province_name, -- 需要关联省份维度表 COUNT(*) as register_count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage FROM dwd_user_register_inc r LEFT JOIN dim_province p ON r.province_id p.province_id WHERE r.dt 2022-06-09 GROUP BY p.province_name ORDER BY register_count DESC; -- 5. 注册后行为分析关联其他事实表 WITH registered_users AS ( SELECT user_id, create_time as register_time, channel as register_channel FROM dwd_user_register_inc WHERE dt 2022-06-01 ), user_activities AS ( SELECT r.user_id, r.register_time, r.register_channel, -- 注册后7天内是否有登录 MAX(CASE WHEN l.login_time r.register_time AND l.login_time DATE_ADD(r.register_time, 7, DAY) THEN 1 ELSE 0 END) as has_login_7d, -- 注册后7天内是否有下单 MAX(CASE WHEN o.order_time r.register_time AND o.order_time DATE_ADD(r.register_time, 7, DAY) THEN 1 ELSE 0 END) as has_order_7d, -- 注册后7天内下单金额 SUM(CASE WHEN o.order_time r.register_time AND o.order_time DATE_ADD(r.register_time, 7, DAY) THEN o.order_amount ELSE 0 END) as order_amount_7d FROM registered_users r LEFT JOIN dwd_user_login_inc l ON r.user_id l.user_id AND l.dt BETWEEN 2022-06-01 AND 2022-06-08 LEFT JOIN dwd_order_info_inc o ON r.user_id o.user_id AND o.dt BETWEEN 2022-06-01 AND 2022-06-08 GROUP BY r.user_id, r.register_time, r.register_channel ) SELECT register_channel, COUNT(*) as total_registrations, SUM(has_login_7d) as active_users_7d, SUM(has_order_7d) as paying_users_7d, ROUND(SUM(has_login_7d) * 100.0 / COUNT(*), 2) as login_rate_7d, ROUND(SUM(has_order_7d) * 100.0 / COUNT(*), 2) as conversion_rate_7d, AVG(order_amount_7d) as avg_order_amount_7d FROM user_activities GROUP BY register_channel ORDER BY conversion_rate_7d DESC;5. 用户域用户登录事务事实表业务场景深度解析登录方式账号密码登录手机验证码登录第三方授权登录微信、QQ生物识别登录指纹、人脸会话管理会话超时通常30分钟无操作后需要重新登录会话保持APP端可能长期保持登录状态多设备登录一个账号同时在多个设备登录技术实现深度解析-- 核心逻辑每个会话的第一次页面浏览视为登录 -- 为什么这样设计 -- 1. 实际登录可能没有独立的日志事件 -- 2. 登录后的第一个页面浏览可以准确标识登录时间 -- 3. 避免重复记录同一会话内的多次页面刷新 -- 窗口函数详解 -- row_number() over (partition by common.sid order by ts) rn -- partition by common.sid: 按会话分组 -- order by ts: 按时间戳排序 -- rn 1: 取每个会话的第一条记录 -- 筛选条件解释 -- page is not null: 确保是页面浏览事件不是其他事件如点击、曝光 -- common.uid is not null: 确保是已登录用户未登录用户common.uid为空 -- dt 2022-06-08: 处理当天的数据 -- 潜在问题 -- 1. 页面自动刷新可能被误判为多次登录 -- 解决方案结合APP的session管理或设置最小时间间隔 -- 2. 未登录用户浏览不会被记录 -- 这是符合业务定义的登录事实表只记录登录事件 -- 3. 同一用户多设备登录会被记录为多次登录 -- 这是符合业务实际的每次设备登录都应记录登录行为分析实战-- 1. 基础登录指标 SELECT date_id, COUNT(*) as login_times, -- 登录次数 COUNT(DISTINCT user_id) as dau, -- 日活跃用户 COUNT(DISTINCT mid_id) as devices, -- 活跃设备数 AVG(CASE WHEN channel appstore THEN 1 ELSE 0 END) as appstore_rate, AVG(CASE WHEN channel LIKE %wechat% THEN 1 ELSE 0 END) as wechat_rate FROM dwd_user_login_inc WHERE dt 2022-06-08 GROUP BY date_id; -- 2. 用户登录频次分析 WITH user_login_stats AS ( SELECT user_id, COUNT(*) as login_days, MIN(date_id) as first_login_date, MAX(date_id) as last_login_date, COUNT(DISTINCT brand) as device_brands, COUNT(DISTINCT channel) as login_channels FROM dwd_user_login_inc WHERE dt BETWEEN 2022-06-01 AND 2022-06-30 GROUP BY user_id ) SELECT login_days, COUNT(*) as user_count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage, AVG(device_brands) as avg_brands, AVG(login_channels) as avg_channels FROM user_login_stats GROUP BY login_days ORDER BY login_days; -- 3. 用户登录时段分析 SELECT HOUR(login_time) as login_hour, COUNT(*) as login_count, COUNT(DISTINCT user_id) as unique_users, COUNT(DISTINCT mid_id) as unique_devices, ROUND(AVG(CASE WHEN channel appstore THEN 1 ELSE 0 END) * 100, 2) as appstore_percent FROM dwd_user_login_inc WHERE dt 2022-06-09 GROUP BY HOUR(login_time) ORDER BY login_hour; -- 4. 用户登录设备偏好分析 SELECT user_id, -- 用户最常用的设备品牌 FIRST_VALUE(brand) OVER ( PARTITION BY user_id ORDER BY COUNT(*) DESC, MAX(login_time) DESC ) as preferred_brand, -- 用户最常用的设备型号 FIRST_VALUE(model) OVER ( PARTITION BY user_id ORDER BY COUNT(*) DESC, MAX(login_time) DESC ) as preferred_model, -- 用户使用的设备数 COUNT(DISTINCT mid_id) as device_count, -- 用户登录总次数 COUNT(*) as total_logins FROM dwd_user_login_inc WHERE dt BETWEEN 2022-06-01 AND 2022-06-30 GROUP BY user_id, brand, model QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) 1; -- 5. 登录到行为转化分析 WITH login_sessions AS ( SELECT l.user_id, l.login_time, l.channel, l.mid_id, -- 登录后是否有浏览 MAX(CASE WHEN p.view_time l.login_time AND p.view_time DATE_ADD(l.login_time, 1, HOUR) THEN 1 ELSE 0 END) as has_browse, -- 登录后是否有下单 MAX(CASE WHEN o.order_time l.login_time AND o.order_time DATE_ADD(l.login_time, 24, HOUR) THEN 1 ELSE 0 END) as has_order FROM dwd_user_login_inc l LEFT JOIN dwd_traffic_page_view_inc p ON l.user_id p.user_id AND l.dt p.dt LEFT JOIN dwd_order_info_inc o ON l.user_id o.user_id AND o.dt l.dt WHERE l.dt 2022-06-09 GROUP BY l.user_id, l.login_time, l.channel, l.mid_id ) SELECT channel, COUNT(*) as total_logins, SUM(has_browse) as browse_after_login, SUM(has_order) as order_after_login, ROUND(SUM(has_browse) * 100.0 / COUNT(*), 2) as browse_rate, ROUND(SUM(has_order) * 100.0 / COUNT(*), 2) as order_rate FROM login_sessions GROUP BY channel ORDER BY total_logins DESC;三、数据治理与质量控制1. 数据一致性检查-- 检查各事实表的数据完整性 SELECT dwd_tool_coupon_used_inc as table_name, COUNT(*) as total_rows, COUNT(DISTINCT dt) as partition_count, MIN(dt) as min_date, MAX(dt) as max_date, SUM(CASE WHEN payment_time IS NULL THEN 1 ELSE 0 END) as null_payment_time FROM dwd_tool_coupon_used_inc WHERE dt 2022-06-01 UNION ALL SELECT dwd_interaction_favor_add_inc, COUNT(*), COUNT(DISTINCT dt), MIN(dt), MAX(dt), SUM(CASE WHEN create_time IS NULL THEN 1 ELSE 0 END) FROM dwd_interaction_favor_add_inc WHERE dt 2022-06-01;2. 数据血缘分析ods_coupon_use_inc → dwd_tool_coupon_used_inc ods_favor_info_inc → dwd_interaction_favor_add_inc ods_log_inc → dwd_traffic_page_view_inc ods_user_info_inc ods_log_inc → dwd_user_register_inc ods_log_inc → dwd_user_login_inc3. 性能优化建议-- 1. 分区优化 -- 按月建立二级分区 PARTITIONED BY (year STRING, month STRING, day STRING) -- 2. 索引优化 -- 对常用查询字段建立索引 CREATE INDEX idx_user_id ON dwd_user_login_inc(user_id); CREATE INDEX idx_date_id ON dwd_user_login_inc(date_id); -- 3. 数据压缩 TBLPROPERTIES (orc.compress snappy) -- 已配置 -- 4. 小文件合并 -- 设置合并参数 SET hive.merge.mapfilestrue; SET hive.merge.mapredfilestrue; SET hive.merge.size.per.task256000000; SET hive.merge.smallfiles.avgsize128000000;四、业务应用场景总结1.运营分析优惠券使用率监控用户增长趋势分析渠道效果评估用户活跃度监控2.用户分析用户行为路径分析用户兴趣偏好挖掘用户生命周期管理用户分群与标签3.产品分析页面功能使用分析用户交互行为分析产品迭代效果评估A/B测试效果验证4.商业分析营销活动ROI计算用户价值评估预测模型构建业务健康度监控五、常见问题与解决方案1.数据延迟问题-- 解决方案建立数据延迟监控 SELECT table_name, MAX(dt) as latest_partition, DATEDIFF(CURRENT_DATE, MAX(dt)) as delay_days FROM ( SELECT dwd_tool_coupon_used_inc as table_name, MAX(dt) as dt FROM dwd_tool_coupon_used_inc UNION ALL SELECT dwd_user_login_inc, MAX(dt) FROM dwd_user_login_inc ) t GROUP BY table_name;2.数据质量监控-- 监控关键字段的null值率 SELECT dt, table_name, field_name, total_count, null_count, ROUND(null_count * 100.0 / total_count, 2) as null_rate FROM ( SELECT dt, dwd_user_register_inc as table_name, channel as field_name, COUNT(*) as total_count, SUM(CASE WHEN channel IS NULL THEN 1 ELSE 0 END) as null_count FROM dwd_user_register_inc WHERE dt 2022-06-09 GROUP BY dt ) t WHERE null_rate 5; -- 设置阈值3.数据回溯处理-- 当需要重新计算历史数据时 -- 1. 清除历史分区 ALTER TABLE dwd_tool_coupon_used_inc DROP IF EXISTS PARTITION (dt2022-06-08); -- 2. 重新装载数据 INSERT OVERWRITE TABLE dwd_tool_coupon_used_inc PARTITION(dt2022-06-08) SELECT ... FROM ods_coupon_use_inc WHERE dt2022-06-08 AND ...;六、完整代码-- 六、工具域优惠券使用(支付)事务事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_tool_coupon_used_inc; CREATE EXTERNAL TABLE dwd_tool_coupon_used_inc ( id STRING COMMENT 编号, coupon_id STRING COMMENT 优惠券ID, user_id STRING COMMENT 用户ID, order_id STRING COMMENT 订单ID, date_id STRING COMMENT 日期ID, payment_time STRING COMMENT 使用(支付)时间 ) COMMENT 优惠券使用支付事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_tool_coupon_used_inc/ TBLPROPERTIES (orc.compress snappy); -- 工具域优惠券使用(支付)事务事实表首日数据装载 set hive.exec.dynamic.partition.modenonstrict; insert overwrite table dwd_tool_coupon_used_inc partition(dt) select data.id, data.coupon_id, data.user_id, data.order_id, date_format(data.used_time,yyyy-MM-dd) date_id, data.used_time, date_format(data.used_time,yyyy-MM-dd) from ods_coupon_use_inc where dt2022-06-08 and typebootstrap-insert and data.used_time is not null; -- 工具域优惠券使用(支付)事务事实表每日数据装载 insert overwrite table dwd_tool_coupon_used_inc partition(dt2022-06-09) select data.id, data.coupon_id, data.user_id, data.order_id, date_format(data.used_time,yyyy-MM-dd) date_id, data.used_time from ods_coupon_use_inc where dt2022-06-09 and typeupdate and array_contains(map_keys(old),used_time); -- 七、互动域收藏商品事务事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_interaction_favor_add_inc; CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc ( id STRING COMMENT 编号, user_id STRING COMMENT 用户ID, sku_id STRING COMMENT SKU_ID, date_id STRING COMMENT 日期ID, create_time STRING COMMENT 收藏时间 ) COMMENT 互动域收藏商品事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_interaction_favor_add_inc/ TBLPROPERTIES (orc.compress snappy); -- 互动域收藏商品事务事实表首日数据装载 set hive.exec.dynamic.partition.modenonstrict; insert overwrite table dwd_interaction_favor_add_inc partition(dt) select data.id, data.user_id, data.sku_id, date_format(data.create_time,yyyy-MM-dd) date_id, data.create_time, date_format(data.create_time,yyyy-MM-dd) from ods_favor_info_inc where dt2022-06-08 and type bootstrap-insert; -- 互动域收藏商品事务事实表每日数据装载 insert overwrite table dwd_interaction_favor_add_inc partition(dt2022-06-09) select data.id, data.user_id, data.sku_id, date_format(data.create_time,yyyy-MM-dd) date_id, data.create_time from ods_favor_info_inc where dt2022-06-09 and type insert; -- 八、流量域页面浏览事务事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_traffic_page_view_inc; CREATE EXTERNAL TABLE dwd_traffic_page_view_inc ( province_id STRING COMMENT 省份ID, brand STRING COMMENT 手机品牌, channel STRING COMMENT 渠道, is_new STRING COMMENT 是否首次启动, model STRING COMMENT 手机型号, mid_id STRING COMMENT 设备ID, operate_system STRING COMMENT 操作系统, user_id STRING COMMENT 会员ID, version_code STRING COMMENT APP版本号, page_item STRING COMMENT 目标ID, page_item_type STRING COMMENT 目标类型, last_page_id STRING COMMENT 上页ID, page_id STRING COMMENT 页面ID , from_pos_id STRING COMMENT 点击坑位ID, from_pos_seq STRING COMMENT 点击坑位位置, refer_id STRING COMMENT 营销渠道ID, date_id STRING COMMENT 日期ID, view_time STRING COMMENT 跳入时间, session_id STRING COMMENT 所属会话ID, during_time BIGINT COMMENT 持续时间毫秒 ) COMMENT 流量域页面浏览事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_traffic_page_view_inc TBLPROPERTIES (orc.compress snappy); -- 流量域页面浏览事务事实表数据装载 set hive.cbo.enablefalse; insert overwrite table dwd_traffic_page_view_inc partition (dt2022-06-08) select common.ar province_id, common.ba brand, common.ch channel, common.is_new is_new, common.md model, common.mid mid_id, common.os operate_system, common.uid user_id, common.vc version_code, page.item page_item, page.item_type page_item_type, page.last_page_id, page.page_id, page.from_pos_id, page.from_pos_seq, page.refer_id, date_format(from_utc_timestamp(ts,GMT8),yyyy-MM-dd) date_id, date_format(from_utc_timestamp(ts,GMT8),yyyy-MM-dd HH:mm:ss) view_time, common.sid session_id, page.during_time from ods_log_inc where dt2022-06-08 and page is not null; set hive.cbo.enabletrue; -- 九用户域用户注册事务事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_user_register_inc; CREATE EXTERNAL TABLE dwd_user_register_inc ( user_id STRING COMMENT 用户ID, date_id STRING COMMENT 日期ID, create_time STRING COMMENT 注册时间, channel STRING COMMENT 应用下载渠道, province_id STRING COMMENT 省份ID, version_code STRING COMMENT 应用版本, mid_id STRING COMMENT 设备ID, brand STRING COMMENT 设备品牌, model STRING COMMENT 设备型号, operate_system STRING COMMENT 设备操作系统 ) COMMENT 用户域用户注册事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_user_register_inc/ TBLPROPERTIES (orc.compress snappy); -- 用户域用户注册事务事实表首日数据装载 set hive.exec.dynamic.partition.modenonstrict; insert overwrite table dwd_user_register_inc partition(dt) select ui.user_id, date_format(create_time,yyyy-MM-dd) date_id, create_time, channel, province_id, version_code, mid_id, brand, model, operate_system, date_format(create_time,yyyy-MM-dd) from ( select data.id user_id, data.create_time from ods_user_info_inc where dt2022-06-08 and typebootstrap-insert )ui left join ( select common.ar province_id, common.ba brand, common.ch channel, common.md model, common.mid mid_id, common.os operate_system, common.uid user_id, common.vc version_code from ods_log_inc where dt2022-06-08 and page.page_idregister and common.uid is not null )log on ui.user_idlog.user_id; -- 用户域用户注册事务事实表每日数据装载 insert overwrite table dwd_user_register_inc partition(dt2022-06-09) select ui.user_id, date_format(create_time,yyyy-MM-dd) date_id, create_time, channel, province_id, version_code, mid_id, brand, model, operate_system from ( select data.id user_id, data.create_time from ods_user_info_inc where dt2022-06-09 and typeinsert )ui left join ( select common.ar province_id, common.ba brand, common.ch channel, common.md model, common.mid mid_id, common.os operate_system, common.uid user_id, common.vc version_code from ods_log_inc where dt2022-06-09 and page.page_idregister and common.uid is not null )log on ui.user_idlog.user_id; -- 十\用户域用户登录事务事实表 -- 建表语句 DROP TABLE IF EXISTS dwd_user_login_inc; CREATE EXTERNAL TABLE dwd_user_login_inc ( user_id STRING COMMENT 用户ID, date_id STRING COMMENT 日期ID, login_time STRING COMMENT 登录时间, channel STRING COMMENT 应用下载渠道, province_id STRING COMMENT 省份ID, version_code STRING COMMENT 应用版本, mid_id STRING COMMENT 设备ID, brand STRING COMMENT 设备品牌, model STRING COMMENT 设备型号, operate_system STRING COMMENT 设备操作系统 ) COMMENT 用户域用户登录事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_user_login_inc/ TBLPROPERTIES (orc.compress snappy); -- 用户域用户登录事务事实表数据装载 insert overwrite table dwd_user_login_inc partition (dt 2022-06-08) select user_id, date_format(from_utc_timestamp(ts, GMT8), yyyy-MM-dd) date_id, date_format(from_utc_timestamp(ts, GMT8), yyyy-MM-dd HH:mm:ss) login_time, channel, province_id, version_code, mid_id, brand, model, operate_system from ( select user_id, channel, province_id, version_code, mid_id, brand, model, operate_system, ts from (select common.uid user_id, common.ch channel, common.ar province_id, common.vc version_code, common.mid mid_id, common.ba brand, common.md model, common.os operate_system, ts, row_number() over (partition by common.sid order by ts) rn from ods_log_inc where dt 2022-06-08 and page is not null and common.uid is not null) t1 where rn 1 ) t2;通过以上详细分析可以看出DWD层事实表设计充分考虑了业务需求和技术实现为上层的数据分析和应用提供了稳定、准确、高效的明细数据基础。