做数仓的朋友应该都有感受一到交易域加购表往往是DWD层里“看着最简单、写起来最纠结”的一张表。说它简单是因为购物车加购这个动作在业务库就是一行记录字段不复杂说它纠结是因为它横跨事务事实表和周期快照表两种建模思路还牵扯维度退化、度量固化、分区策略、口径定义一个不小心下游漏斗分析就会得出两个互相打架的加购人数。这是我数仓搭建学习笔记的第28篇跟着项目完整走一遍交易域DWD层加购事务事实表的建表语句和逐段分析。会讲清楚每个字段为什么存在、每个存储参数为什么这么选也会把ODS到DWD的数据加工逻辑和实际环境中容易踩的坑一起复盘。适合正在学数仓分层建模、准备自己动手搭离线数仓的同学参考。1. DWD层到底在解决什么问题——加购数据这样建模才合理1.1 数仓分层中DWD的定位数仓经典分层是ODS、DWD、DWS、ADS每一层解决不同的问题。ODS层原样接入业务系统和日志的数据基本不做处理保留最原始的状态。DWS层面向主题做汇总把明细聚合成宽表。中间的DWD层很多人理解为“做一遍数据清洗”这个理解太浅了。DWD真正做的事有三件第一是清洗把空值、异常值、格式杂乱的数据处理掉第二是维度退化把ODS里孤零零的ID变成可以直接分析、筛选的维度属性第三是规范化业务过程让每一行数据的含义在同一张表内可以准确解释而不是让分析师去ODS的某个接口文档里猜某个字段代表什么。加购数据恰恰是这三件事的典型场景。ODS层的购物车表是业务库按MySQL规范设计的主键、外键、业务状态字段齐全但它面向的是业务系统不是分析。业务系统关心的是“当前购物车有哪些商品”数仓关心的是“用户什么时候把什么商品加入了购物车”。关注点不同建模方式就不同。1.2 为什么加购要建模成事务事实表事务事实表对应的是一个业务过程中的每一个事件一行数据就是一个事件发生时的快照。周期快照表则是定期记录某个时刻的累计状态。加购这个动作本身就是一个事件用户每次点击“加入购物车”就应该产生一行事实记录。有一个常见的误区ODS层已经有购物车表了为什么不能直接拿它分析因为在业务库里购物车表是可变状态的。用户加了商品A再点一次加购数量从1变成2这一行的主键不会变之前的记录被覆盖。如果直接用这张表分析加购行为你只能看到购物车的最终状态看不到“用户是几点几分加购的”“加购了几次”“当时的价格是多少”。所以DWD层要把业务库的“状态表”转成“事件流水表”用事务事实表来建模。事实表的特点是只追加、不更新每一行对应一次独立的加购事件。这样下游无论是做漏斗分析、行为序列分析还是计算加购转化率都有据可依。事务事实表和周期快照表的取舍我整理了一个简单的对照维度事务事实表周期快照表粒度每次事件一行每周期如每天一行更新方式只追加不修改历史每天覆盖反映状态典型场景用户行为分析、漏斗转化库存快照、购物车存量加购场景适用度高还原每一次加购低只能看最后状态1.3 度量为什么要固化在事件里事务事实表里最核心的是度量字段。加购表涉及的度量有三个加购数量、加购时的单价、加购总金额。为什么单价和总金额必须在建表时固化而不是分析时再临时关联商品表去算因为商品价格是会变的。用户周二晚上加购了一个商品价格是99元到了周五商家改价成89元如果分析时再去关联商品维表拿到的是最新价格算出来的加购金额和用户当时看到的价格完全不是一回事。任何事务事实表度量都必须反映事件发生时的事实这是事务表建模的铁律。因此在加工DWD时加购表会把当时的sku单价冗余下来并直接算出数量乘单价的金额。这样后续做用户加购金额分布、价格敏感度分析时拿到的都是准确的历史事实。这也是DWD层做“规范化度量”的意义所在。2. 建表语句逐段拆解——每个字段和参数都不是随便写的2.1 完整DDL先摆上来先给出建表语句。这个版本是离线数仓常用的标准写法基于Hive语法。项目里表名用了dwd_trade_cart_add_inc含义是交易域加购事务事实表。CREATE EXTERNAL TABLE IF NOT EXISTS dwd_trade_cart_add_inc ( id STRING COMMENT 加购记录主键id, user_id STRING COMMENT 用户id, sku_id STRING COMMENT 商品sku_id, spu_id STRING COMMENT 商品spu_id, category1_id STRING COMMENT 一级品类id, category2_id STRING COMMENT 二级品类id, category3_id STRING COMMENT 三级品类id, tm_id STRING COMMENT 品牌id, sku_num DECIMAL(16, 2) COMMENT 加购商品数量, sku_price DECIMAL(16, 2) COMMENT 加购时商品单价, split_total_amount DECIMAL(16, 2) COMMENT 加购总金额, create_time STRING COMMENT 加购时间, operate_time STRING COMMENT 操作时间, province_id STRING COMMENT 省份id, is_checked STRING COMMENT 是否勾选, source_type STRING COMMENT 来源类型, source_id STRING COMMENT 来源编号 ) COMMENT 交易域加购事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_trade_cart_add_inc TBLPROPERTIES (orc.compress snappy);建表语句看着不长但每一行都有值得展开的地方。下面拆开讲。2.2 字段分组解读维度、度量、时间与业务状态这张表的字段可以分成四组理解了分组逻辑后面遇到类似的DWD表都能举一反三。第一组是事件标识类字段包括id。它来自ODS层购物车表的主键id。在事务事实表中主键保证每一行有唯一标识后续做去重、关联、问题排查都靠它。第二组是维度字段包括user_id、sku_id、spu_id、category1_id、category2_id、category3_id、tm_id、province_id、source_type、source_id。其中user_id和sku_id是业务过程自然带出的品类、品牌、SPU则是在DWD层通过关联商品维表退化的属性。这里有一个重要的设计思路在ODS层购物车表通常只存sku_id你要知道用户加购的商品属于哪个品类、哪个品牌必须去关联商品维表。如果让每个下游分析师都去做这个关联第一是麻烦第二是容易关联错第三是维表数据每天变化不同人拿到的维度属性可能不一致。所以DWD层一次性把这些维度退化进来把商品维表、用户维表的信息整合成宽表下游拿到就能直接用。第三组是度量字段包括sku_num、sku_price、split_total_amount。这类字段是分析的核心。sku_num代表加购数量sku_price是加购时的商品单价split_total_amount是加购总金额。前面说过这三个值都必须反映事件发生当时的情况特别是价格千万不能留到下游实时去商品表获取。第四组是时间字段和业务状态字段包括create_time、operate_time、is_checked。create_time是加购发生的时间operate_time是这条购物车记录最后一次被修改的时间is_checked表示购物车中的商品是否被用户勾选。勾选状态是一个容易被忽略但实际很有用的字段因为很多用户在购物车里勾选部分商品后才会去结算它是分析用户加购后是否进入结算流程的重要参考。2.3 外部表、ORC、Snappy、dt分区的选型理由建表语句里几个关键参数再单独说说。为什么用EXTERNAL TABLE外部表。外部表的特点是元数据由Hive管理但数据文件放在独立的HDFS目录中。数仓里ODS、DWD层的表统一用外部表好处是如果表结构需要重构或废弃直接删除元数据不会误删底层数据文件安全性更好。另外数仓的数据通常还要被其他计算引擎读取使用独立目录也更灵活。为什么分区字段选dt。离线数仓按天调度几乎所有的任务都是以“天”为粒度跑的。每天处理前一天的数据写入当天的分区。分区字段用dt格式统一yyyy-MM-dd这样下游在写SQL时能清晰区分日期范围也方便按分区做数据生命周期管理。这个方案简单、直观最大的优势是排查问题时能一眼定位某个日期分区的数据是否正常。为什么存储格式用ORC压缩用Snappy。ORC是Hive生态下成熟的列式存储格式列式存储对于分析型查询非常友好因为分析场景经常只取少数几个字段列存可以只读取需要的列块。Snappy压缩的压缩比不算最高但解压速度快适合Hive查询这种需要频繁读取数据的场景。实际生产中可以对比一下Zlib和Snappy数据量特别大、磁盘紧张时用Zlib查询性能优先时用Snappy。这个项目选Snappy是合理的。字段类型方面时间和金额值得专门说。所有时间字段统一用STRING格式为yyyy-MM-dd HH:mm:ss而不是用TIMESTAMP。原因在于Hive的TIMESTAMP处理带有时区解析逻辑当集群时区配置不一致时会出现时间偏移而字符串格式可控、直观、与分区字段dt的判断逻辑一致不会因为时区问题导致数据错误。金额字段统一用DECIMAL(16, 2)精确到分这是金额字段的通用约定。用DOUBLE存金额是很多人犯过的错误浮点数在计算累加时会产生误差做金额汇总时会出现“差一分钱”的尴尬问题DECIMAL是定点数不存在这个隐患。3. 从ODS到DWD加购数据加工链路中的关键处理3.1 数据来源与ODS层的初步清洗建完表接下来就是把数据从ODS层加工进DWD。加购数据的来源通常有两种一种是从业务库的购物车表通过同步工具采集另一种是从用户行为日志中解析加购事件。这个项目以业务库购物车表为主ODS层对应表一般是ods_cart_info。ODS层表的结构与业务库基本一致但数据质量参差不齐。DWD层加工前需要先做一轮清洗主要包括三类操作。一是空值处理比如user_id为空的加购记录基本是异常数据需要过滤掉二是时间格式规范化业务库的时间有可能是datetime类型在同步后要统一转成字符串格式三是对同一分区内的数据做去重确保主键唯一防止业务库的重复同步或同步任务重复执行造成的数据冗余。这一步通常可以直接在加工SQL的where条件中完成不需要单独建一张清洗中间表减少链路长度和调度复杂度。3.2 维度退化SQL与关联逻辑说明完成基础清洗后核心操作是把维度属性退化进来。下面是一段典型的加工SQL按天写入某个分区insert overwrite table dwd_trade_cart_add_inc partition (dt 2024-06-15) select ci.id, ci.user_id, ci.sku_id, sku.spu_id, sku.category1_id, sku.category2_id, sku.category3_id, sku.tm_id, ci.sku_num, sku.price as sku_price, ci.sku_num * sku.price as split_total_amount, ci.create_time, ci.operate_time, ci.province_id, ci.is_checked, ci.source_type, ci.source_id from ods_cart_info ci left join dim_sku_info sku on ci.sku_id sku.id and sku.dt 2024-06-15 where ci.dt 2024-06-15 and ci.user_id is not null and ci.sku_id is not null;这段SQL的关键点有几个。为什么join维度表时要带上sku.dt2024-06-15这个条件。商品维度表通常是每日全量快照分区每天的维度数据可能不同比如商品改价、改类目、改品牌。关联时必须用同一天的维度快照才能与加购事件发生在同一天保持口径一致。如果用维度表的最新分区去关联历史加购数据就会出现历史加购记录被贴上最新分类、最新价格的错误。为什么用left join而不是inner join。教学项目中偏好保留全部加购记录即使某些商品在维度表中没有关联到也先把事实数据保留下来避免因为维度表缺失导致整个事实行丢失。但生产环境更建议在关联后做一次扫描重点排查那些关联为空的记录占比高不高如果占比过高说明维度表同步有问题需要修复后再跑任务。为什么金额的计算放在DWD而不是DWS。split_total_amount用ci.sku_num * sku.price计算直接固化在DWD。这样DWS层做汇总时拿到的是已经计算好的金额不需要再回头关联商品价格。价格字段是不断变化的越早固化口径越稳定这和省钱记账必须记录购买当时的商品价格是一个道理。3.3 调度依赖与任务配置建议加工SQL写好后调度配置是另一个容易出问题的地方。加购DWD任务的依赖逻辑按顺序应为ODS层购物车表同步完成、商品维度表同步完成、用户维度表同步完成然后才能启动DWD加工任务。如果商品维度表还没跑完DWD任务启动后会出现大量维度关联为空的记录而且因为是insert overwrite会把之前没问题的分区数据也覆盖掉造成不可逆的数据质量事故。有一点需要强调任务失败重跑时要确认依赖的上游表也都已经成功运行否则单独重跑DWD没有意义。离线数仓的“跑批失败”大多不是父任务失败而是下游在前置条件不满足的情况下强行启动。调度系统里把这些依赖关系配好能省去大量排查时间。4. 加购事务事实表与订单事实表的粒度差异——两个表千万别搞混4.1 一张表行数的含义完全不同拿到建好的加购表后第一个要养成的习惯是搞清楚表粒度。加购事务事实表的粒度是“一次加购事件”一行记录代表用户某一次加购某个商品。订单事实表的粒度则是“某个订单中的某一条商品明细”一行记录代表一个订单中的一行商品明细。两张表行数含义的区别非常关键。如果拿dwd_trade_cart_add_inc求加购数量直接count(1)得到的是加购事件次数。拿dwd_trade_order_detail求订单量count(1)得到的是订单明细条数不是订单数。一个是事件量一个是明细量两者在衡量交易规模时口径完全不同。这也意味着下游做关联分析时不能简单地认为“加购表的一行对应订单表的一行”。一个用户可能把同一个商品加了多次购最后只下一次单一个订单也可能包含多个商品每个商品对应独立的一行。行与行不是一一对应的关系。4.2 漏斗分析的正确join姿势最常见的加购分析是“加购-下单-支付”漏斗。很多人第一步就写错直接把加购表和订单表按用户ID关联然后用count(distinct user_id)统计。这样出来的数字往往是错的因为在多对多关联下会产生笛卡尔积式的数据膨胀导致加购记录被放大。一个用户加了3次购物车下了2个订单如果直接把两张表join可能产生6条组合结果而不是实际的3条加购和2个订单。正确的做法是先确定分析粒度再在对应粒度上做去重。比如分析用户维度的加购到下单转化率就应该先按user_id去重统计“有加购行为的用户数”和“有下单行为的用户数”再计算转化率。如果一定要把两张表join后再算也必须先分别按分析粒度去重再关联而不是让底层明细表直接join。4.3 指标口径才是真正的坑这里必须展开说说加购三件套加购次数、加购人数、加购转化率。加购次数的定义是从加购事实表中直接count(1)代表加购事件的次数。加购人数的定义是count(distinct user_id)代表有多少用户产生了加购行为。这两者在语义上差很多如果只看加购次数而忽略人数大促期间一个“囤货型”用户连续加购50件商品就会让指标看起来非常夸张。加购转化率更要先明确分子分母口径。是“加购用户到下单用户的转化率”还是“加购商品到下单商品的转化率”或者是“加购次数到下单次数的转化率”三种口径算出来的数字完全不同如果不写清楚运营同学和数据分析师会拿着不同的数字开一上午会。实践中的建议是在出具指标时把口径定义直接写在表注释或指标说明里。比如“加购转化率用户口径 当日下单用户数 / 当日加购用户数”。等下游使用这张表时不会因为口径不统一而出现争论。5. 建表之外的真实踩坑记录——加购表建模的五个高频问题5.1 维度关联不上导致NULL扩散第一批数据跑完后习惯性查了一下各类目加购分布发现结果里有一批记录显示NULL。排查过程不算复杂先抽了几条NULL记录去ODS层查源数据发现这些记录的商品ID在商品维度表中查不到。原因是当时商品维度表同步任务比DWD加工任务晚启动了几分钟DWD任务启动时商品维度表当天分区还没生成left join全部落空。这个问题光靠SQL解决不了必须在调度系统里确认依赖关系。此外更稳妥的做法是加工SQL中对核心维度字段品类、品牌增加一个简单的空值拦截空值率超过阈值直接告警让数据负责人第一时间介入而不是等下游报表出现异常才发现。5.2 重复加购到底保留还是合并购物车业务里用户对同一个商品连点两次“加入购物车”业务上可能表现为数量1也可能生成两条加购记录取决于接口设计。在事务事实表中我建议保留每一次原始事件不在DWD层做合并。用户的连续点击行为本身是有价值的分析数据它体现了用户的犹豫、比较、囤货等心理。如果下游要做“有多少用户加购了商品A”这类去重统计可以在指标计算层根据实际业务需要去重。但如果DWD层就早早合并掉后面想分析用户加购频次分布、单次加购到下单的时间间隔就完全没有数据基础了。DWD做明细保留DWS/ADS做口径加工各司其职。5.3 时间口径不统一业务库和日志差在哪加购表关联行为日志时经常遇到时间对不上的情况。业务库的create_time记录的是真正写入数据库的时间用户点击“加入购物车”按钮后请求经过网络到达服务器再写入MySQL这个时间可能已经有几秒到几十秒的延迟。行为日志记录的是用户点击行为发生的时间通常在客户端埋点时就带上了。两种时间的差异在秒级或毫秒级单条记录看不出来但做时间窗口分析比如“加购后5分钟内是否下单”时几秒钟的偏差也会影响统计结果。在DWD加工之前建议把加购表的create_time和日志中的行为时间各保留一个字段并在表注释中明确说明两者的来源和语义不要混用。5.4 ORC格式下的小文件问题这里分享一个调优经验。如果每天同步数据的频率很高比如每小时同步一次而加工任务又是按天写入一个分区一天下来分区内会有多个小文件。ORC格式下小文件过多会严重影响查询性能因为每次读取要打开大量文件NameNode和DataNode的压力都会增加。解决思路有几种。最简单的是在调度配置中合并输出文件比如在insert任务中设置hive.merge.mapfilestrue等参数或者每完成一轮同步后对分区执行一次合并操作。如果觉得运维复杂度太高更推荐的做法是按照业务数据量调整同步频率数据量不大的情况下每天同步一次就够了没必要每小时都往ODS里灌一小批。5.5 大促场景下的热点SKU倾斜最后一坑是大促期间加购数据量的剧烈波动。某款爆品在促销开始后一分钟内的加购量可能超过平时一天的数据量。这些记录在按SKU维度做统计时会集中在某几个Reduce任务上出现节点忙死、其他节点空闲的情况也就是数据倾斜。在建表阶段能做的预防是把分区字段和常用的过滤字段设计好比如sku_id、dt这些字段一定要有这样下游分析时可以先用分区裁剪缩小数据范围。加工任务层面可以给热点SKU的key加随机前缀或者采用两阶段聚合的思路。DWD层本身不需要为一次大促做过度优化但要有预案知道最可能倾斜的地方在哪里。加购事务事实表是整个交易域DWD层里性价比非常高的一张表表结构不算复杂但承载了下游不少核心指标。把它建好把口径定义清楚后面做DWS层汇总、ADS层报表都会顺手很多。
数仓DWD层加购事务事实表建模详解：从建表到踩坑
做数仓的朋友应该都有感受一到交易域加购表往往是DWD层里“看着最简单、写起来最纠结”的一张表。说它简单是因为购物车加购这个动作在业务库就是一行记录字段不复杂说它纠结是因为它横跨事务事实表和周期快照表两种建模思路还牵扯维度退化、度量固化、分区策略、口径定义一个不小心下游漏斗分析就会得出两个互相打架的加购人数。这是我数仓搭建学习笔记的第28篇跟着项目完整走一遍交易域DWD层加购事务事实表的建表语句和逐段分析。会讲清楚每个字段为什么存在、每个存储参数为什么这么选也会把ODS到DWD的数据加工逻辑和实际环境中容易踩的坑一起复盘。适合正在学数仓分层建模、准备自己动手搭离线数仓的同学参考。1. DWD层到底在解决什么问题——加购数据这样建模才合理1.1 数仓分层中DWD的定位数仓经典分层是ODS、DWD、DWS、ADS每一层解决不同的问题。ODS层原样接入业务系统和日志的数据基本不做处理保留最原始的状态。DWS层面向主题做汇总把明细聚合成宽表。中间的DWD层很多人理解为“做一遍数据清洗”这个理解太浅了。DWD真正做的事有三件第一是清洗把空值、异常值、格式杂乱的数据处理掉第二是维度退化把ODS里孤零零的ID变成可以直接分析、筛选的维度属性第三是规范化业务过程让每一行数据的含义在同一张表内可以准确解释而不是让分析师去ODS的某个接口文档里猜某个字段代表什么。加购数据恰恰是这三件事的典型场景。ODS层的购物车表是业务库按MySQL规范设计的主键、外键、业务状态字段齐全但它面向的是业务系统不是分析。业务系统关心的是“当前购物车有哪些商品”数仓关心的是“用户什么时候把什么商品加入了购物车”。关注点不同建模方式就不同。1.2 为什么加购要建模成事务事实表事务事实表对应的是一个业务过程中的每一个事件一行数据就是一个事件发生时的快照。周期快照表则是定期记录某个时刻的累计状态。加购这个动作本身就是一个事件用户每次点击“加入购物车”就应该产生一行事实记录。有一个常见的误区ODS层已经有购物车表了为什么不能直接拿它分析因为在业务库里购物车表是可变状态的。用户加了商品A再点一次加购数量从1变成2这一行的主键不会变之前的记录被覆盖。如果直接用这张表分析加购行为你只能看到购物车的最终状态看不到“用户是几点几分加购的”“加购了几次”“当时的价格是多少”。所以DWD层要把业务库的“状态表”转成“事件流水表”用事务事实表来建模。事实表的特点是只追加、不更新每一行对应一次独立的加购事件。这样下游无论是做漏斗分析、行为序列分析还是计算加购转化率都有据可依。事务事实表和周期快照表的取舍我整理了一个简单的对照维度事务事实表周期快照表粒度每次事件一行每周期如每天一行更新方式只追加不修改历史每天覆盖反映状态典型场景用户行为分析、漏斗转化库存快照、购物车存量加购场景适用度高还原每一次加购低只能看最后状态1.3 度量为什么要固化在事件里事务事实表里最核心的是度量字段。加购表涉及的度量有三个加购数量、加购时的单价、加购总金额。为什么单价和总金额必须在建表时固化而不是分析时再临时关联商品表去算因为商品价格是会变的。用户周二晚上加购了一个商品价格是99元到了周五商家改价成89元如果分析时再去关联商品维表拿到的是最新价格算出来的加购金额和用户当时看到的价格完全不是一回事。任何事务事实表度量都必须反映事件发生时的事实这是事务表建模的铁律。因此在加工DWD时加购表会把当时的sku单价冗余下来并直接算出数量乘单价的金额。这样后续做用户加购金额分布、价格敏感度分析时拿到的都是准确的历史事实。这也是DWD层做“规范化度量”的意义所在。2. 建表语句逐段拆解——每个字段和参数都不是随便写的2.1 完整DDL先摆上来先给出建表语句。这个版本是离线数仓常用的标准写法基于Hive语法。项目里表名用了dwd_trade_cart_add_inc含义是交易域加购事务事实表。CREATE EXTERNAL TABLE IF NOT EXISTS dwd_trade_cart_add_inc ( id STRING COMMENT 加购记录主键id, user_id STRING COMMENT 用户id, sku_id STRING COMMENT 商品sku_id, spu_id STRING COMMENT 商品spu_id, category1_id STRING COMMENT 一级品类id, category2_id STRING COMMENT 二级品类id, category3_id STRING COMMENT 三级品类id, tm_id STRING COMMENT 品牌id, sku_num DECIMAL(16, 2) COMMENT 加购商品数量, sku_price DECIMAL(16, 2) COMMENT 加购时商品单价, split_total_amount DECIMAL(16, 2) COMMENT 加购总金额, create_time STRING COMMENT 加购时间, operate_time STRING COMMENT 操作时间, province_id STRING COMMENT 省份id, is_checked STRING COMMENT 是否勾选, source_type STRING COMMENT 来源类型, source_id STRING COMMENT 来源编号 ) COMMENT 交易域加购事务事实表 PARTITIONED BY (dt STRING) STORED AS ORC LOCATION /warehouse/gmall/dwd/dwd_trade_cart_add_inc TBLPROPERTIES (orc.compress snappy);建表语句看着不长但每一行都有值得展开的地方。下面拆开讲。2.2 字段分组解读维度、度量、时间与业务状态这张表的字段可以分成四组理解了分组逻辑后面遇到类似的DWD表都能举一反三。第一组是事件标识类字段包括id。它来自ODS层购物车表的主键id。在事务事实表中主键保证每一行有唯一标识后续做去重、关联、问题排查都靠它。第二组是维度字段包括user_id、sku_id、spu_id、category1_id、category2_id、category3_id、tm_id、province_id、source_type、source_id。其中user_id和sku_id是业务过程自然带出的品类、品牌、SPU则是在DWD层通过关联商品维表退化的属性。这里有一个重要的设计思路在ODS层购物车表通常只存sku_id你要知道用户加购的商品属于哪个品类、哪个品牌必须去关联商品维表。如果让每个下游分析师都去做这个关联第一是麻烦第二是容易关联错第三是维表数据每天变化不同人拿到的维度属性可能不一致。所以DWD层一次性把这些维度退化进来把商品维表、用户维表的信息整合成宽表下游拿到就能直接用。第三组是度量字段包括sku_num、sku_price、split_total_amount。这类字段是分析的核心。sku_num代表加购数量sku_price是加购时的商品单价split_total_amount是加购总金额。前面说过这三个值都必须反映事件发生当时的情况特别是价格千万不能留到下游实时去商品表获取。第四组是时间字段和业务状态字段包括create_time、operate_time、is_checked。create_time是加购发生的时间operate_time是这条购物车记录最后一次被修改的时间is_checked表示购物车中的商品是否被用户勾选。勾选状态是一个容易被忽略但实际很有用的字段因为很多用户在购物车里勾选部分商品后才会去结算它是分析用户加购后是否进入结算流程的重要参考。2.3 外部表、ORC、Snappy、dt分区的选型理由建表语句里几个关键参数再单独说说。为什么用EXTERNAL TABLE外部表。外部表的特点是元数据由Hive管理但数据文件放在独立的HDFS目录中。数仓里ODS、DWD层的表统一用外部表好处是如果表结构需要重构或废弃直接删除元数据不会误删底层数据文件安全性更好。另外数仓的数据通常还要被其他计算引擎读取使用独立目录也更灵活。为什么分区字段选dt。离线数仓按天调度几乎所有的任务都是以“天”为粒度跑的。每天处理前一天的数据写入当天的分区。分区字段用dt格式统一yyyy-MM-dd这样下游在写SQL时能清晰区分日期范围也方便按分区做数据生命周期管理。这个方案简单、直观最大的优势是排查问题时能一眼定位某个日期分区的数据是否正常。为什么存储格式用ORC压缩用Snappy。ORC是Hive生态下成熟的列式存储格式列式存储对于分析型查询非常友好因为分析场景经常只取少数几个字段列存可以只读取需要的列块。Snappy压缩的压缩比不算最高但解压速度快适合Hive查询这种需要频繁读取数据的场景。实际生产中可以对比一下Zlib和Snappy数据量特别大、磁盘紧张时用Zlib查询性能优先时用Snappy。这个项目选Snappy是合理的。字段类型方面时间和金额值得专门说。所有时间字段统一用STRING格式为yyyy-MM-dd HH:mm:ss而不是用TIMESTAMP。原因在于Hive的TIMESTAMP处理带有时区解析逻辑当集群时区配置不一致时会出现时间偏移而字符串格式可控、直观、与分区字段dt的判断逻辑一致不会因为时区问题导致数据错误。金额字段统一用DECIMAL(16, 2)精确到分这是金额字段的通用约定。用DOUBLE存金额是很多人犯过的错误浮点数在计算累加时会产生误差做金额汇总时会出现“差一分钱”的尴尬问题DECIMAL是定点数不存在这个隐患。3. 从ODS到DWD加购数据加工链路中的关键处理3.1 数据来源与ODS层的初步清洗建完表接下来就是把数据从ODS层加工进DWD。加购数据的来源通常有两种一种是从业务库的购物车表通过同步工具采集另一种是从用户行为日志中解析加购事件。这个项目以业务库购物车表为主ODS层对应表一般是ods_cart_info。ODS层表的结构与业务库基本一致但数据质量参差不齐。DWD层加工前需要先做一轮清洗主要包括三类操作。一是空值处理比如user_id为空的加购记录基本是异常数据需要过滤掉二是时间格式规范化业务库的时间有可能是datetime类型在同步后要统一转成字符串格式三是对同一分区内的数据做去重确保主键唯一防止业务库的重复同步或同步任务重复执行造成的数据冗余。这一步通常可以直接在加工SQL的where条件中完成不需要单独建一张清洗中间表减少链路长度和调度复杂度。3.2 维度退化SQL与关联逻辑说明完成基础清洗后核心操作是把维度属性退化进来。下面是一段典型的加工SQL按天写入某个分区insert overwrite table dwd_trade_cart_add_inc partition (dt 2024-06-15) select ci.id, ci.user_id, ci.sku_id, sku.spu_id, sku.category1_id, sku.category2_id, sku.category3_id, sku.tm_id, ci.sku_num, sku.price as sku_price, ci.sku_num * sku.price as split_total_amount, ci.create_time, ci.operate_time, ci.province_id, ci.is_checked, ci.source_type, ci.source_id from ods_cart_info ci left join dim_sku_info sku on ci.sku_id sku.id and sku.dt 2024-06-15 where ci.dt 2024-06-15 and ci.user_id is not null and ci.sku_id is not null;这段SQL的关键点有几个。为什么join维度表时要带上sku.dt2024-06-15这个条件。商品维度表通常是每日全量快照分区每天的维度数据可能不同比如商品改价、改类目、改品牌。关联时必须用同一天的维度快照才能与加购事件发生在同一天保持口径一致。如果用维度表的最新分区去关联历史加购数据就会出现历史加购记录被贴上最新分类、最新价格的错误。为什么用left join而不是inner join。教学项目中偏好保留全部加购记录即使某些商品在维度表中没有关联到也先把事实数据保留下来避免因为维度表缺失导致整个事实行丢失。但生产环境更建议在关联后做一次扫描重点排查那些关联为空的记录占比高不高如果占比过高说明维度表同步有问题需要修复后再跑任务。为什么金额的计算放在DWD而不是DWS。split_total_amount用ci.sku_num * sku.price计算直接固化在DWD。这样DWS层做汇总时拿到的是已经计算好的金额不需要再回头关联商品价格。价格字段是不断变化的越早固化口径越稳定这和省钱记账必须记录购买当时的商品价格是一个道理。3.3 调度依赖与任务配置建议加工SQL写好后调度配置是另一个容易出问题的地方。加购DWD任务的依赖逻辑按顺序应为ODS层购物车表同步完成、商品维度表同步完成、用户维度表同步完成然后才能启动DWD加工任务。如果商品维度表还没跑完DWD任务启动后会出现大量维度关联为空的记录而且因为是insert overwrite会把之前没问题的分区数据也覆盖掉造成不可逆的数据质量事故。有一点需要强调任务失败重跑时要确认依赖的上游表也都已经成功运行否则单独重跑DWD没有意义。离线数仓的“跑批失败”大多不是父任务失败而是下游在前置条件不满足的情况下强行启动。调度系统里把这些依赖关系配好能省去大量排查时间。4. 加购事务事实表与订单事实表的粒度差异——两个表千万别搞混4.1 一张表行数的含义完全不同拿到建好的加购表后第一个要养成的习惯是搞清楚表粒度。加购事务事实表的粒度是“一次加购事件”一行记录代表用户某一次加购某个商品。订单事实表的粒度则是“某个订单中的某一条商品明细”一行记录代表一个订单中的一行商品明细。两张表行数含义的区别非常关键。如果拿dwd_trade_cart_add_inc求加购数量直接count(1)得到的是加购事件次数。拿dwd_trade_order_detail求订单量count(1)得到的是订单明细条数不是订单数。一个是事件量一个是明细量两者在衡量交易规模时口径完全不同。这也意味着下游做关联分析时不能简单地认为“加购表的一行对应订单表的一行”。一个用户可能把同一个商品加了多次购最后只下一次单一个订单也可能包含多个商品每个商品对应独立的一行。行与行不是一一对应的关系。4.2 漏斗分析的正确join姿势最常见的加购分析是“加购-下单-支付”漏斗。很多人第一步就写错直接把加购表和订单表按用户ID关联然后用count(distinct user_id)统计。这样出来的数字往往是错的因为在多对多关联下会产生笛卡尔积式的数据膨胀导致加购记录被放大。一个用户加了3次购物车下了2个订单如果直接把两张表join可能产生6条组合结果而不是实际的3条加购和2个订单。正确的做法是先确定分析粒度再在对应粒度上做去重。比如分析用户维度的加购到下单转化率就应该先按user_id去重统计“有加购行为的用户数”和“有下单行为的用户数”再计算转化率。如果一定要把两张表join后再算也必须先分别按分析粒度去重再关联而不是让底层明细表直接join。4.3 指标口径才是真正的坑这里必须展开说说加购三件套加购次数、加购人数、加购转化率。加购次数的定义是从加购事实表中直接count(1)代表加购事件的次数。加购人数的定义是count(distinct user_id)代表有多少用户产生了加购行为。这两者在语义上差很多如果只看加购次数而忽略人数大促期间一个“囤货型”用户连续加购50件商品就会让指标看起来非常夸张。加购转化率更要先明确分子分母口径。是“加购用户到下单用户的转化率”还是“加购商品到下单商品的转化率”或者是“加购次数到下单次数的转化率”三种口径算出来的数字完全不同如果不写清楚运营同学和数据分析师会拿着不同的数字开一上午会。实践中的建议是在出具指标时把口径定义直接写在表注释或指标说明里。比如“加购转化率用户口径 当日下单用户数 / 当日加购用户数”。等下游使用这张表时不会因为口径不统一而出现争论。5. 建表之外的真实踩坑记录——加购表建模的五个高频问题5.1 维度关联不上导致NULL扩散第一批数据跑完后习惯性查了一下各类目加购分布发现结果里有一批记录显示NULL。排查过程不算复杂先抽了几条NULL记录去ODS层查源数据发现这些记录的商品ID在商品维度表中查不到。原因是当时商品维度表同步任务比DWD加工任务晚启动了几分钟DWD任务启动时商品维度表当天分区还没生成left join全部落空。这个问题光靠SQL解决不了必须在调度系统里确认依赖关系。此外更稳妥的做法是加工SQL中对核心维度字段品类、品牌增加一个简单的空值拦截空值率超过阈值直接告警让数据负责人第一时间介入而不是等下游报表出现异常才发现。5.2 重复加购到底保留还是合并购物车业务里用户对同一个商品连点两次“加入购物车”业务上可能表现为数量1也可能生成两条加购记录取决于接口设计。在事务事实表中我建议保留每一次原始事件不在DWD层做合并。用户的连续点击行为本身是有价值的分析数据它体现了用户的犹豫、比较、囤货等心理。如果下游要做“有多少用户加购了商品A”这类去重统计可以在指标计算层根据实际业务需要去重。但如果DWD层就早早合并掉后面想分析用户加购频次分布、单次加购到下单的时间间隔就完全没有数据基础了。DWD做明细保留DWS/ADS做口径加工各司其职。5.3 时间口径不统一业务库和日志差在哪加购表关联行为日志时经常遇到时间对不上的情况。业务库的create_time记录的是真正写入数据库的时间用户点击“加入购物车”按钮后请求经过网络到达服务器再写入MySQL这个时间可能已经有几秒到几十秒的延迟。行为日志记录的是用户点击行为发生的时间通常在客户端埋点时就带上了。两种时间的差异在秒级或毫秒级单条记录看不出来但做时间窗口分析比如“加购后5分钟内是否下单”时几秒钟的偏差也会影响统计结果。在DWD加工之前建议把加购表的create_time和日志中的行为时间各保留一个字段并在表注释中明确说明两者的来源和语义不要混用。5.4 ORC格式下的小文件问题这里分享一个调优经验。如果每天同步数据的频率很高比如每小时同步一次而加工任务又是按天写入一个分区一天下来分区内会有多个小文件。ORC格式下小文件过多会严重影响查询性能因为每次读取要打开大量文件NameNode和DataNode的压力都会增加。解决思路有几种。最简单的是在调度配置中合并输出文件比如在insert任务中设置hive.merge.mapfilestrue等参数或者每完成一轮同步后对分区执行一次合并操作。如果觉得运维复杂度太高更推荐的做法是按照业务数据量调整同步频率数据量不大的情况下每天同步一次就够了没必要每小时都往ODS里灌一小批。5.5 大促场景下的热点SKU倾斜最后一坑是大促期间加购数据量的剧烈波动。某款爆品在促销开始后一分钟内的加购量可能超过平时一天的数据量。这些记录在按SKU维度做统计时会集中在某几个Reduce任务上出现节点忙死、其他节点空闲的情况也就是数据倾斜。在建表阶段能做的预防是把分区字段和常用的过滤字段设计好比如sku_id、dt这些字段一定要有这样下游分析时可以先用分区裁剪缩小数据范围。加工任务层面可以给热点SKU的key加随机前缀或者采用两阶段聚合的思路。DWD层本身不需要为一次大促做过度优化但要有预案知道最可能倾斜的地方在哪里。加购事务事实表是整个交易域DWD层里性价比非常高的一张表表结构不算复杂但承载了下游不少核心指标。把它建好把口径定义清楚后面做DWS层汇总、ADS层报表都会顺手很多。