尚硅谷大数据项目电商数仓6.0学习记录----数据仓库（中）

1. ODS层数据装载与自动化调度实战在电商数仓项目中ODS层作为数据仓库的第一站承担着原始数据的蓄水池角色。我遇到过不少新手在数据装载环节踩坑最常见的问题就是手动执行SQL导致数据不一致。这里分享一个真实案例某次大促期间由于手动执行漏掉了分区字段导致后续所有报表数据错乱团队花了整整两天时间回滚数据。自动化装载脚本是解决这类问题的银弹。以日志表为例我们通常会编写Shell脚本封装HiveQL命令。下面这个脚本模板经过多个项目验证可以直接套用#!/bin/bash APPgmall if [ -n $1 ]; then do_date$1 else do_date$(date -d -1 day %F) fi echo 开始装载 $do_date 日志数据 hive -e SET hive.exec.dynamic.partition.modenonstrict; LOAD DATA INPATH /origin_data/$APP/log/topic_log/$do_date INTO TABLE ${APP}.ods_log_inc PARTITION(dt$do_date); 这个脚本有三个关键设计点支持手动传入日期参数也支持自动取前一天动态分区模式确保分区字段自动匹配明确的执行日志输出便于问题排查调度系统集成建议使用Azkaban或Airflow。在最近一个项目中我们这样配置Azkaban的调度流程每天00:30自动触发前置依赖HDFS数据就绪检查失败自动重试3次执行结果自动邮件通知对于增量表同步Maxwell的初始化操作需要特别注意。有次我忘记清空bootstrap表导致数据重复后来养成了写检查脚本的习惯-- Maxwell状态检查脚本 SELECT COUNT(*) FROM maxwell.positions WHERE client_idgmall;2. DWD层维度建模核心方法论维度建模是数据仓库的灵魂但很多开发者容易陷入理论陷阱。我总结了一套32实战法则3个设计原则2个验证方法。事实表设计要抓住三个关键点粒度选择比如订单事实表应该到sku级别而非订单头级别度量确定数值型字段要明确计算规则sum/count/avg退化维度常用描述字段直接冗余减少关联开销以电商订单为例典型的建表语句应该是这样CREATE TABLE dwd_order_detail ( id STRING COMMENT 订单明细ID, order_id STRING COMMENT 订单ID, sku_id STRING COMMENT 商品ID, user_id STRING COMMENT 用户ID, province_id STRING COMMENT 省份ID, activity_id STRING COMMENT 活动ID, coupon_id STRING COMMENT 优惠券ID, create_time TIMESTAMP COMMENT 创建时间, source_type STRING COMMENT 来源类型, source_id STRING COMMENT 来源ID, sku_num BIGINT COMMENT 商品数量, original_amount DECIMAL(16,2) COMMENT 原价, final_amount DECIMAL(16,2) COMMENT 实付金额 ) COMMENT 订单明细事实表 PARTITIONED BY (dt STRING) STORED AS PARQUET;维度表设计的坑更多。去年我们有个项目用户维度表竟然没有做缓慢变化维处理导致历史数据分析完全失真。正确的SCD2方案应该是CREATE TABLE dwd_user_info ( id STRING COMMENT 用户ID, name STRING COMMENT 姓名, gender STRING COMMENT 性别, birthday STRING COMMENT 生日, email STRING COMMENT 邮箱, user_level STRING COMMENT 用户等级, start_date STRING COMMENT 生效日期, end_date STRING COMMENT 失效日期 ) COMMENT 用户维度表 STORED AS PARQUET;验证模型是否合理我常用两种方法数据密度检查执行ANALYZE TABLE计算统计信息确保字段基数合理查询测试用典型分析场景SQL验证关联效率3. Spark on Hive高效数据处理技巧当数据量超过1TB时Hive MapReduce引擎就显得力不从心。最近帮客户优化一个千万级订单表关联改用Spark后耗时从45分钟降到3分钟。关键配置如下Spark执行引擎配置需要特别注意这些参数!-- hive-site.xml关键配置 -- property namehive.execution.engine/name valuespark/value /property property namespark.master/name valueyarn/value /property property namespark.executor.memory/name value4g/value /property property namespark.driver.memory/name value2g/value /property数据倾斜处理是Spark调优的重点。上周刚解决一个用户行为分析任务卡在99%的问题采用的分治方案-- 倾斜键单独处理 WITH skewed_users AS ( SELECT * FROM dwd_user_behavior WHERE user_id IN (12345,67890) -- 倾斜值 ), normal_users AS ( SELECT * FROM dwd_user_behavior WHERE user_id NOT IN (12345,67890) ) -- 分别处理后再合并 SELECT * FROM normal_users JOIN dim_user USING(user_id) UNION ALL SELECT * FROM skewed_users JOIN dim_user USING(user_id)小文件合并也是常见痛点。我们团队开发的自动合并脚本# 每天凌晨合并前一天分区小文件 spark.sql( ALTER TABLE dwd_order_detail PARTITION(dt${do_date}) CONCATENATE )4. 完整数据加工流水线示例结合某电商大促实战分享从ODS到DWD的完整处理流程日志数据ETL流程原始日志装载到ODS解析JSON公共字段展开动作数组炸裂关联维度获取中文描述INSERT INTO TABLE dwd_page_log PARTITION(dt2023-11-11) SELECT common.ar, common.ba, get_dim_name(channel, common.ch) AS channel_name, page.page_id, page.last_page_id, explode(actions) AS action FROM ods_log_inc LATERAL VIEW json_tuple(common, ar, ba, ch) t AS ar, ba, ch LATERAL VIEW json_tuple(page, page_id, last_page_id) p AS page_id, last_page_id LATERAL VIEW explode(actions) a AS actions WHERE dt2023-11-11;业务数据清洗要点空值处理COALESCE(field, 未知)编码转换CASE WHEN gender1 THEN 男 ELSE 女 END时间格式化FROM_UNIXTIME(ts/1000)枚举值校验确保status在(1,2,3)范围内质量检查脚本必不可少#!/bin/bash # 数据量检查 count$(hive -e SELECT COUNT(*) FROM dwd_order_detail WHERE dt$do_date) if [ $count -lt 1000 ]; then echo 数据量异常: $count | mail -s 数据告警 teamexample.com fi # 重要字段空值率检查 null_rate$(hive -e SELECT SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END)/COUNT(*) FROM dwd_order_detail WHERE dt$do_date) if (( $(echo $null_rate 0.05 | bc -l) )); then echo 空值率超标: $null_rate | mail -s 数据告警 teamexample.com fi在最近的双十一项目中这套流程每天处理20亿日志和千万级订单数据从数据产生到可分析状态延迟控制在15分钟内。关键是把每个环节都做成原子操作通过调度系统串联起来任何环节失败都能快速定位和重试。