最近在推进一个从零搭建电商数仓的小项目按计划表走到了第三天。前两天的重点工作是数据探查和业务梳理把订单、用户、商品、支付这些核心业务域的源表结构、数据量级、更新频率摸了个大概。今天的任务非常明确把数仓的整体骨架搭起来也就是完成数仓分层设计、核心表结构建模并把第一版的ETL流程跑通。第三天做的工作量大不大对于第一次接触数仓的人来说可能会被“构建数仓”这四个字吓到感觉要设计一套多复杂的系统。实际上真正动手之后你会发现数仓搭建最核心的就三件事分层设计、建模规范、ETL开发。今天这篇文章就把我day3的实操过程完整放出来包括每一步的选择逻辑、踩过的坑、以及最终落地的SQL和配置新手可以直接照着抄作业有一定经验的也可以对比一下自己的方案有没有可以优化的地方。1 数仓分层的设计思路与各层职责1.1 为什么一定要做分层直接查业务库不行吗很多刚接触数据的人会有一个灵魂拷问业务数据就在MySQL里躺着BI要报表直接连业务库查不就行了为什么非要搞出一套数仓分层还要做ODS、DWD、DWS、ADS这么多层不是脱裤子放屁吗这个问题我当年也问过直到自己真正经历过一次线上事故才彻底想明白。有一次业务方要一份近30天的订单明细报表我们直接写SQL去查业务库的订单表结果这个表的数据量已经上亿一个带多表JOIN的聚合查询把业务库的CPU打到100%直接影响了线上交易。从那以后我坚定了一个认知数仓分层的第一价值不是“规范”而是“隔离”。分层的本质是让数据流像工厂流水线一样每个环节各司其职互不干扰。ODS层负责把业务数据原封不动地同步过来DWD层负责清洗和标准化DWS层负责做轻度的汇总ADS层负责输出最终的应用数据。每一层都有独立的存储和计算资源即使上层查询再复杂再频繁也不会反向影响底层的数据同步更不会波及业务系统。对于day3这个阶段分层还有一个很现实的好处当数据出问题时可以通过血缘关系快速定位是哪个环节出了问题。如果报表数据对不上先看ADS层有没有问题再看DWS层的汇总逻辑有没有变化一层一层往里查而不是像无头苍蝇一样到处翻SQL。1.2 四层结构里每一层具体放什么东西我这次搭建用的是最常见的四层架构ODS、DWD、DWS、ADS。这套结构不新鲜但胜在稳定、够用而且网上资料多遇到问题时比较好排查。ODS层Operational Data Store操作数据存储是数仓的数据源头职责很简单把业务库的表按原样同步过来不做任何业务逻辑处理。唯一要做的附加操作是增加一个数据日期分区字段dt用来标记这批数据是哪一天同步的。这样设计的好处是如果某一天的业务数据有误可以直接回溯到对应日期的ODS分区进行重新处理不用动历史数据。DWD层Data Warehouse Detail明细数据层是数仓里最考验功力的地方。这一层要对ODS层的原始数据进行清洗、去重、标准化然后按照业务过程重新组织成明细表。比如订单域源系统里可能有订单主表、订单明细表、订单状态流转表DWD层要做的事情就是把它们按照订单维度或订单明细维度整合成一张宽表或几张标准的明细表方便后续各种维度的分析。DWS层Data Warehouse Summary汇总数据层做的事情是“轻度汇总”按主题把DWD层的明细数据聚合成一些常用的统计指标。比如按用户维度汇总成用户购买行为表按商品维度汇总成商品销售表按日期维度汇总成每日经营总表。这层的核心目的是把高频使用的统计口径固化下来避免每一次做报表都重新跑一遍全量明细。ADS层Application Data Store应用数据层是最贴近业务的一层直接面向报表、BI、数据产品。这层的表结构通常是根据具体的报表需求来定制的一个报表对应一张表或者几个字段组合查询性能要求极高基本不做复杂的JOIN计算。我用一个生活化的类比来解释这套结构ODS层是仓库门口的收货区货物到了直接堆在那里不拆箱不验收DWD层是仓库内部的理货区拆箱、清点、贴标签、按照分类重新摆放DWS层是货架区把同类商品摆到一起方便快速取用ADS层是收银台顾客来说“我要一箱矿泉水”直接递给他不用再跑进仓库翻半天。1.3 公共维度的统一处理维度表单独建一层除了上述四个核心分层我还单独建了一个公共维度层放用户、商品、品类、店铺、日期等维度表。有人会问维度表不是应该在DWD层里吗这确实是很多数仓项目的争议点。我的做法是把维度表单独拆出来用dim_前缀命名放在一个独立的存储路径下。主要原因是维度表有自己独特的管理方式维度表的数据量通常不会像事实表那样无限膨胀但它们需要支持缓慢变化维SCD的处理比如用户的收货地址变了我们要保留历史记录这就需要在维度表里增加生效日期、失效日期这些字段。如果维度表混在DWD层里容易跟事实表的生命周期管理混淆。事实表通常按天增量更新维度表可能需要按天全量刷新或者做拉链处理。把它们分开可以在调度配置和存储策略上做差异化处理。2 数仓建模方法的选型与核心概念落地2.1 维度建模和范式建模怎么选建模方法论主要有两派一派是关系型数据库时代的范式建模3NF强调消除数据冗余、保证数据一致性另一派是Kimball提出的维度建模强调易用性和查询性能也就是我们常说的星型模型。数仓领域现在的主流实践基本是维度建模的天下。原因很简单数仓的核心消费场景是OLAP分析分析师和业务方需要的是“能快速看懂、能快速查询”的数据结构而不是一套严谨但复杂的ER图。范式建模在数据一致性上有优势但查询时往往需要关联十几张表口感和效率都不好。维度建模的核心要素是事实表和维度表。事实表记录业务过程产生的度量值比如订单金额、销售数量这些是数值型的可以进行汇总计算。维度表描述业务过程的上下文环境比如时间、用户、商品、地区这些是文本型的用来过滤和分组。以订单为例订单事实表里存的是订单ID、用户ID、商品ID、金额、数量这些字段其中用户ID和商品ID就是外键通过它们关联用户维度表和商品维度表。查询“华东地区三月份卖得最好的商品Top10”就是把订单事实表跟商品维度表、日期维度表、地区维度表关联起来做筛选和聚合。2.2 事实表的类型选择与粒度定义维度建模里事实表分为三种事务事实表、周期快照事实表、累积快照事实表。事务事实表最常用记录每个业务事件发生时的状态。比如下单事实表每一行是一个订单明细字段包含下单时间、商品ID、数量、金额等。它的特点是增量追加历史记录不会被修改适合做流量分析、销售分析这类需要追溯每一次行为的场景。周期快照事实表是在固定时间间隔比如每天记录某个对象的累计状态。比如每天记录一次每个用户的累计消费金额和累计订单数。它的特点是定期更新适合做存量分析比如用户生命周期价值分析、活跃用户统计。累积快照事实表记录一个业务流程从开始到结束的完整生命周期。比如订单累积快照表一行记录一个订单从下单、支付、发货、签收到完事的全部关键时间节点。它的特点是随业务流程推进不断更新适合做流程分析和漏斗转化分析。在day3建模时我首先确定了粒度定义。这是建模最核心的一步一张事实表的每一行到底代表什么。订单事务事实表的粒度定义为“订单中的一个商品明细行”也就是说如果一个订单里有三样商品那这张表里就有三行记录。这个粒度的好处是可以支持商品维度的分析比如“哪些商品经常被凑单一起购买”如果粒度定义在订单级别这种分析就做不了了。粒度的选择直接决定了事实表的行数级和指标的计算口径这个决定要慎之又慎。一旦表上线后再改粒度意味着所有下游ETL和报表都要重做代价非常巨大。2.3 缓慢变化维的几种策略day3先实现哪一种维度表里的数据会变化比如用户换了手机号、商品改了所属类目、店铺改了名称。如果直接更新原记录历史报表中关联出来的用户手机号、商品类目就都变了导致历史数据不准确。缓慢变化维SCD就是解决这个问题的。SCD有几种常见策略策略一是直接覆盖简单粗暴但丢历史策略二是新增一行记录旧记录保留新记录加一个新的维度代理键能完整保留历史策略三是增加几个固定字段来记录变化前后的值适用于只有少数几个属性可能变化的场景。对于day3的第一版我选择了策略一和策略二的组合。绝大多数维度表先按策略一处理每天全量刷新直接用最新数据覆盖保证当前状态的准确性。但用户维度和商品维度因为要做历史分析我加上了策略二的思想用自然键user_id/商品ID加生效日期来区分不同版本的记录。这里有个新手的常见误区直接用业务主键做主键。前两天我就踩了这个坑后续处理用户地址变更时因为主键冲突导致ETL流程挂了。强烈建议所有的维度表都加上一个自增的代理键dim_id业务主键降级为普通字段。代理键的好处是它与业务系统解耦业务主键的变化不会影响数仓内部的数据关联关系。3 实操过程从建表到ETL的全流程记录3.1 数仓开发环境与技术栈选择day3的实操环境我用了Hive作为数据仓库的存储和计算引擎。选Hive主要是三个原因一是它兼容SQL语法上手成本低写过MySQL的人基本能无缝切换二是它在处理超大数据量时比较稳定不用担心数据量大了之后撑不住三是社区生态成熟遇到问题能很快搜到解决方案。存储格式上我选用了Parquet列式存储格式。列式存储在查询时只需要读取涉及的列能省掉大量的I/O。再加上Snappy压缩既能省存储空间又不会太耗CPU。分区策略上所有表都按日期dt做分区每天一个分区这样在查询时可以通过分区裁剪快速过滤数据避免全表扫描。在动手建表之前我先花了一个多小时把命名规范定下来了。这个环节看着繁琐但后面省了我无数事。最终规范如下层前缀ODS层用ods_DWD层用dwd_DWS层用dws_ADS层用ads_维度表用dim_业务域订单域用trade用户域用user商品域用item表名结构层级前缀_业务域_业务过程比如dwd_trade_order_detail分区字段统一命名为dt类型为STRING格式为yyyyMMdd时间字段统一命名为xxx_time类型为TIMESTAMP3.2 DDL建表语句的具体写法建表的DDL我直接在生产环境跑了一遍这里把核心几张表的定义放出来。ODS层以订单表为例注意这里为了保持原样所有字段名跟源系统保持一致没有做任何转换。-- ODS层订单原始表 CREATE TABLE IF NOT EXISTS ods_trade_order_inc ( order_id BIGINT COMMENT 订单ID, order_sn STRING COMMENT 订单编号, user_id BIGINT COMMENT 用户ID, shop_id BIGINT COMMENT 店铺ID, item_id BIGINT COMMENT 商品ID, item_title STRING COMMENT 商品标题, category_id BIGINT COMMENT 类目ID, original_price DECIMAL(10,2) COMMENT 商品原价, actual_price DECIMAL(10,2) COMMENT 实际成交价, order_status TINYINT COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, order_time TIMESTAMP COMMENT 下单时间, pay_time TIMESTAMP COMMENT 支付时间, ship_time TIMESTAMP COMMENT 发货时间, finish_time TIMESTAMP COMMENT 完成时间, buyer_message STRING COMMENT 买家留言, create_time TIMESTAMP COMMENT 记录创建时间, update_time TIMESTAMP COMMENT 记录更新时间 ) COMMENT 订单原始表ODS层 PARTITIONED BY (dt STRING COMMENT 数据分区日期) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);DWD层的订单明细表就是在ODS基础上做了清洗转化。比如把order_status从TINYINT类型映射成可读的状态描述过滤掉测试订单和异常数据然后做了一个时间字段的标准化。这里我把订单的四个关键时间字段单独提取出来方便后续做时间维度的分析。DWS层的用户购买行为汇总表是按用户日期维度做聚合的核心是几个常用的统计指标。-- DWS层用户购买行为日汇总表 CREATE TABLE IF NOT EXISTS dws_user_buy_daily ( user_id BIGINT COMMENT 用户ID, order_count BIGINT COMMENT 下单次数, order_item_count BIGINT COMMENT 下单商品件数, paid_order_count BIGINT COMMENT 支付订单数, paid_item_count BIGINT COMMENT 支付商品件数, gmv_amount DECIMAL(16,2) COMMENT 下单金额GMV, pay_amount DECIMAL(16,2) COMMENT 实际支付金额, cancel_order_count BIGINT COMMENT 取消订单数, first_order_time TIMESTAMP COMMENT 当日首单时间, last_order_time TIMESTAMP COMMENT 当日末单时间 ) COMMENT 用户购买行为日汇总表 PARTITIONED BY (dt STRING COMMENT 数据分区日期) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);建表时有两个小细节容易被忽略。第一个是字段注释一定要写清楚尤其是DECIMAL类型的精度含义和状态字段的枚举值不然过两个月自己看着都模棱两可。第二个是分区字段的类型要统一用STRING不要有的地方用STRING有的地方用BIGINT否则在做分区修剪时容易出错。3.3 ETL开发从ODS到DWD的清洗转换实现ETL是整个数仓构建的核心环节。今天主要完成了ODS到DWD的清洗转换实战用一个完整的SQL当作教学素材来分析。第一段SQL做的事情是把ODS层的订单数据经过过滤去重之后落到DWD层INSERT OVERWRITE TABLE dwd_trade_order_detail PARTITION (dt ${bizdate}) SELECT order_id, order_sn, user_id, shop_id, item_id, category_id, original_price, actual_price, CASE order_status WHEN 0 THEN 待支付 WHEN 1 THEN 已支付 WHEN 2 THEN 已发货 WHEN 3 THEN 已完成 WHEN 4 THEN 已取消 ELSE 未知 END AS order_status_name, order_time, pay_time, ship_time, finish_time FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM ods_trade_order_inc WHERE dt ${bizdate} ) t WHERE t.rn 1 AND t.order_id IS NOT NULL AND t.actual_price 0;这段SQL里有几个关键点特别值得说一说。ROW_NUMBER()窗口函数的去重处理是应对源系统数据重复的保险手段。在真实的业务环境里同一个订单ID可能会因为数据重推、系统重试等原因出现多行记录如果用distinct只能处理字段完全重复的情况对于字段内容有差异的记录无能为力。用ROW_NUMBER()按update_time倒序取最新的那条记录能保证拿到的是每个订单最新的状态。关于过滤条件我做了两个限制订单ID不为空实际支付金额大于0。这里的逻辑是把一些脏数据拦截在DWD层之外避免脏数据污染上层应用。但这里有个经验教训清洗逻辑不是写得越严越好过度清洗可能会把正常数据误杀。比如actual_price 0就会把0元支付的优惠活动订单给过滤掉如果你的业务里存在这种情况就需要跟业务方仔细确认口径。3.4 DWD到DWS的汇总计算与调度配置ODS到DWD是数据清洗DWD到DWS做的是指标汇总。拿用户日汇总表来举例子它的SQL逻辑其实就是对DWD明细数据做GROUP BY聚合INSERT OVERWRITE TABLE dws_user_buy_daily PARTITION (dt ${bizdate}) SELECT user_id, COUNT(order_id) AS order_count, SUM(item_count) AS order_item_count, SUM(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS paid_order_count, SUM(CASE WHEN pay_time IS NOT NULL THEN item_count ELSE 0 END) AS paid_item_count, SUM(actual_price) AS gmv_amount, SUM(CASE WHEN pay_time IS NOT NULL THEN actual_price ELSE 0 END) AS pay_amount, SUM(CASE WHEN order_status_name 已取消 THEN 1 ELSE 0 END) AS cancel_order_count, MIN(order_time) AS first_order_time, MAX(order_time) AS last_order_time FROM dwd_trade_order_detail WHERE dt ${bizdate} GROUP BY user_id;这段SQL就是典型的轻度汇总逻辑。把用户在某一天的下单行为聚合成一条记录后续做用户分群、复购率分析、高价值用户识别时直接查这张表就行不用再跑全量明细。ETL写完就要配调度。调度系统的核心是依赖管理DWS层跑之前必须确认ODS层对应分区的数据已经就绪DWD层已经完成清洗。我今天的配置方式是ODS同步在凌晨2点执行DWD清洗在凌晨3点执行DWS汇总在凌晨4点执行ADS应用在凌晨5点执行每个环节都配置了失败自动重试和告警通知。第一天在配置调度时就把时间间隔设得太紧凑结果ODS因为业务库延迟10分钟才把数据准备好整个链路全部失败。所以做调度一定要预留一定的buffer时间不要卡得太死。4 常见问题与排查技巧实录4.1 数仓从0到1最容易踩的7个问题今天的实操中一共遇到了7个比较典型的问题我整理成了表格方便后续查阅。问题现象根本原因解决方案跑ETL时发现两天的数据量差异巨大源系统的订单表存在状态更新增量抽取漏掉了updateODS层按全量增量混合逻辑抽取主表按更新时间增量拉取JOIN结果出现重复行关联字段在维度表中不唯一先对维度表按自然键去重再加代理键关联SUM出来的金额对不上业务报表DECIMAL精度设置不一致所有金额字段统一使用DECIMAL(16,2)并在DWD层统一口径查询越跑越慢甚至跑不出来缺少分区过滤扫了全表强制要求所有查询带上dt分区条件配置查询拦截器数据同步到一半卡死源库连接超时同步任务无重试机制配置自动重试3次增加超时保护某天分区数据为0但任务显示成功源表当天确实没有新数据但下游依赖失败增加数据质量校验对空分区发出告警字段值出现乱码或null源系统编码不一致或脏数据DWD层增加编码统一处理和NULL值兜底逻辑4.2 数据倾斜问题的定位与处理经验如果要做个排行数据倾斜大概能登上数仓经典问题的前Top3。我今天在跑DWD层订单明细和商品维度表JOIN时也碰到了这个情况有一个头部商家的订单量占到了全平台的40%以上所有这个商家的订单明细都被分配到了同一个Reduce Task上其他Task早就跑完了就这个Task卡了两个小时。定位数据倾斜最直接的方法是看任务日志找到长时间未结束的Task看它处理的数据量和其他Task的差距有多大。如果差距达到数倍以上基本就可以判定是倾斜了。我这次用了两个方案来优化。方案一是把热点键加上随机前缀打散先用WITH子句把热点商家的订单数据单独提取出来给商家ID拼接一个随机字符串比如CONCAT(shop_id, _, FLOOR(RAND()*10))这样就能把数据均匀分布到10个Task上进行局部聚合最后再做一次整体聚合。方案二是用MAP JOIN把小表加载到内存中避免大表JOIN小表时触发数据倾斜。两个方案对比如下优化方案适用场景优点缺点热点键加随机前缀倾斜键值集中且数据量大能根治倾斜问题需要修改SQL逻辑二次聚合有额外开销MAP JOIN关联表是小表小于1GB简单直接几乎不用改SQL大表JOIN大表时不适用两阶段聚合倾斜键的值比较离散但某一类特别集中通用性好适合GROUP BY场景需要额外写一层子查询4.3 增量表与全量表的选择依据ODS层到DWD层的数据同步有两种模式增量同步和全量同步。很多新手会在这一步犹豫不决。我的个人经验是看两个指标数据量和更新模式。如果表的数据量在百万级以下全量同步最简单每天直接覆盖逻辑清晰容易排查如果表的数据量在千万级以上就要考虑增量同步。同时要分析业务表的更新模式只有新增没有修改的日志表适合增量既有新增又有修改的实体表需要增量更新捕获。订单表是典型的既新增又修改的场景订单状态从待支付变成已完成收货地址可能修改。所以我用了一个增量抽取方案每天从业务库抽取create_time或update_time在当天的数据存到ODS的对应分区。在ODS到DWD的处理中先用ROW_NUMBER按订单ID和更新时间去重取最新状态再关联历史明细做合并。如果直接全量同步每天的同步时间会随着业务量的增长越来越长如果只做简单增量订单的状态更新会丢失。4.4 分区字段选择时的隐藏陷阱分区字段的选择看似简单实际上有不少讲究。今天的实操里有一张订单流水表一开始设计的时候选择了用订单时间order_time作为分区字段后来排查的时候发现有几笔凌晨的订单数据一直跑到第二天的分区里去了搞得每天的数据量都不稳定。原因其实是业务方的订单时间写入逻辑不统一。大部分订单是用户直接下单订单时间就是当前时间但有一批特殊渠道的单子是人工补录的订单时间可能是过去的某个时间点。如果按订单时间做分区过去补录的订单会被写进历史分区而同步任务当天只处理当天时间创建的数据这些补录的订单会被漏掉。后来统一改成按数据写入时间也就是同步日期dt作为分区字段。这样做的逻辑是分区代表的是“数据什么时候进入数仓”而不是“业务事件什么时候发生”。所有的同步任务都只管当天新进入的数据不会出现凌晨的数据被算到第二天的情况。而业务时间则单独作为字段存储在表里统计时用WHERE条件过滤即可。这个坑比较隐蔽建议在第一天设计表结构时就充分考虑不然后面返工的代价非常昂贵。5 数仓质量校验与后续规划5.1 数据质量校验的几个维度第三天做完ETL之后我没有直接宣告完工而是花了不少时间做数据质量校验。数仓里有一个残酷的事实数据质量出问题往往不是同步或计算环节出问题而是质量问题一开始就存在只是没人发现。我在做校验时重点看四个维度。完整性方面要确认每个表的分区都已正常生成不能有空白分区同时核对主表数据量是否在合理范围内。准确性方面要用几个已知的指标做交叉验证比如把DWS层算出来的当日GMV跟业务后台的报表对比差距在1%以内可以接受超过就要排查。一致性方面要重点检查同一个指标在不同表里的口径是否一致比如“支付金额”在明细表里是字段求和在汇总表里是SUM(CASE WHEN pay_time IS NOT NULL)这两者对0元订单的处理方式必须统一。及时性方面则主要看数据产出的时间是否满足业务方的SLA要求。我今天校验时抓到一个典型问题DWS层的gmv_amount比业务后台的GMV少了约0.3%。排查后发现是有少量订单的actual_price字段为空在DWD层被过滤掉了但这些订单在业务后台是计入GMV的。解决方案是把DWD层的过滤逻辑从actual_price 0改成actual_price IS NOT NULL AND actual_price 0同时把空值统一替换成0。这类问题在开发时极其容易被忽略等数据上线了才发现交出去的报表对不上数信任度会大打折扣。5.2 day3之后的扩展方向目前day3搭建的是一个最小可用闭环ODS到DWD到DWS的全链路已经跑通核心的订单和用户主题已经覆盖。后面的计划是逐步把商品、营销、日志行为等主题加进来同时完善维度表的SCD策略。有一个排序问题值得说一下很多人搭数仓喜欢一上来就把所有业务域全部铺开结果东一榔头西一棒子最后哪块都没做好。我建议的路径是先打通一个核心业务域的完整链路把从ODS同步到ADS输出的整个流程跑顺然后再横向复制到其他业务域。业务域之间有共性的规范可以先沉淀成模板比如统一的命名规范、统一的时间处理逻辑、统一的汇总表结构这样后面加新域的时候只是套模板改字段不用每次从头开始设计。5.3 第一天开始就要建立的三个好习惯写到最后分享三个我反复踩坑之后才养成的习惯。如果现在还在数仓入门阶段建议从第一天就建立起来。第一个习惯是写开发文档。哪怕只是一个人开发也要把每张表的字段口径、每段SQL的业务逻辑记录下来。数仓是一个长期演进的项目三个月后再回来看自己写的SQL没有文档辅助重构会很痛苦口径文档是数仓项目里最能保值的资产。第二个习惯是做数据校验快照。每次ETL跑完后把关键表的数据量、关键指标的汇总值记录到一个校验表里长期积累下来能形成一套数据波动监控体系。这个习惯能让你在数据对不上时快速定位是哪天的任务出了状况。第三个习惯是随手沉淀返工经验。数值溢出、字符串隐式转换失效、时区差异导致日期错位这类问题每个数仓开发者都会遇到把问题和对应的排查过程记录下来不仅帮自己省时间也能沉淀成团队的知识库。构建数仓这件事技术本身不复杂复杂的是对业务的理解和规范的坚持。day3能完成的只是一个骨架后续的数据模型演进、性能调优、质量保障才是让这个骨架真正长出肌肉的过程。希望今天的分享能给同样走在数仓路上的朋友一些参考。 最近在推进一个从零搭建电商数仓的小项目按计划表走到了第三天。前两天的重点工作是数据探查和业务梳理把订单、用户、商品、支付这些核心业务域的源表结构、数据量级、更新频率摸了个大概。今天的任务非常明确把数仓的整体骨架搭起来也就是完成数仓分层设计、核心表结构建模并把第一版的ETL流程跑通。第三天做的工作量大不大对于第一次接触数仓的人来说可能会被“构建数仓”这四个字吓到感觉要设计一套多复杂的系统。实际上真正动手之后你会发现数仓搭建最核心的就三件事分层设计、建模规范、ETL开发。今天这篇文章就把我day3的实操过程完整放出来包括每一步的选择逻辑、踩过的坑、以及最终落地的SQL和配置新手可以直接照着抄作业有一定经验的也可以对比一下自己的方案有没有可以优化的地方。1 数仓分层的设计思路与各层职责1.1 为什么一定要做分层直接查业务库不行吗很多刚接触数据的人会有一个灵魂拷问业务数据就在MySQL里躺着BI要报表直接连业务库查不就行了为什么非要搞出一套数仓分层还要做ODS、DWD、DWS、ADS这么多层不是脱裤子放屁吗这个问题我当年也问过直到自己真正经历过一次线上事故才彻底想明白。有一次业务方要一份近30天的订单明细报表我们直接写SQL去查业务库的订单表结果这个表的数据量已经上亿一个带多表JOIN的聚合查询把业务库的CPU打到100%直接影响了线上交易。从那以后我坚定了一个认知数仓分层的第一价值不是“规范”而是“隔离”。分层的本质是让数据流像工厂流水线一样每个环节各司其职互不干扰。ODS层负责把业务数据原封不动地同步过来DWD层负责清洗和标准化DWS层负责做轻度的汇总ADS层负责输出最终的应用数据。每一层都有独立的存储和计算资源即使上层查询再复杂再频繁也不会反向影响底层的数据同步更不会波及业务系统。对于day3这个阶段分层还有一个很现实的好处当数据出问题时可以通过血缘关系快速定位是哪个环节出了问题。如果报表数据对不上先看ADS层有没有问题再看DWS层的汇总逻辑有没有变化一层一层往里查而不是像无头苍蝇一样到处翻SQL。1.2 四层结构里每一层具体放什么东西我这次搭建用的是最常见的四层架构ODS、DWD、DWS、ADS。这套结构不新鲜但胜在稳定、够用而且网上资料多遇到问题时比较好排查。ODS层Operational Data Store操作数据存储是数仓的数据源头职责很简单把业务库的表按原样同步过来不做任何业务逻辑处理。唯一要做的附加操作是增加一个数据日期分区字段dt用来标记这批数据是哪一天同步的。这样设计的好处是如果某一天的业务数据有误可以直接回溯到对应日期的ODS分区进行重新处理不用动历史数据。DWD层Data Warehouse Detail明细数据层是数仓里最考验功力的地方。这一层要对ODS层的原始数据进行清洗、去重、标准化然后按照业务过程重新组织成明细表。比如订单域源系统里可能有订单主表、订单明细表、订单状态流转表DWD层要做的事情就是把它们按照订单维度或订单明细维度整合成一张宽表或几张标准的明细表方便后续各种维度的分析。DWS层Data Warehouse Summary汇总数据层做的事情是“轻度汇总”按主题把DWD层的明细数据聚合成一些常用的统计指标。比如按用户维度汇总成用户购买行为表按商品维度汇总成商品销售表按日期维度汇总成每日经营总表。这层的核心目的是把高频使用的统计口径固化下来避免每一次做报表都重新跑一遍全量明细。ADS层Application Data Store应用数据层是最贴近业务的一层直接面向报表、BI、数据产品。这层的表结构通常是根据具体的报表需求来定制的一个报表对应一张表或者几个字段组合查询性能要求极高基本不做复杂的JOIN计算。我用一个生活化的类比来解释这套结构ODS层是仓库门口的收货区货物到了直接堆在那里不拆箱不验收DWD层是仓库内部的理货区拆箱、清点、贴标签、按照分类重新摆放DWS层是货架区把同类商品摆到一起方便快速取用ADS层是收银台顾客来说“我要一箱矿泉水”直接递给他不用再跑进仓库翻半天。1.3 公共维度的统一处理维度表单独建一层除了上述四个核心分层我还单独建了一个公共维度层放用户、商品、品类、店铺、日期等维度表。有人会问维度表不是应该在DWD层里吗这确实是很多数仓项目的争议点。我的做法是把维度表单独拆出来用dim_前缀命名放在一个独立的存储路径下。主要原因是维度表有自己独特的管理方式维度表的数据量通常不会像事实表那样无限膨胀但它们需要支持缓慢变化维SCD的处理比如用户的收货地址变了我们要保留历史记录这就需要在维度表里增加生效日期、失效日期这些字段。如果维度表混在DWD层里容易跟事实表的生命周期管理混淆。事实表通常按天增量更新维度表可能需要按天全量刷新或者做拉链处理。把它们分开可以在调度配置和存储策略上做差异化处理。2 数仓建模方法的选型与核心概念落地2.1 维度建模和范式建模怎么选建模方法论主要有两派一派是关系型数据库时代的范式建模3NF强调消除数据冗余、保证数据一致性另一派是Kimball提出的维度建模强调易用性和查询性能也就是我们常说的星型模型。数仓领域现在的主流实践基本是维度建模的天下。原因很简单数仓的核心消费场景是OLAP分析分析师和业务方需要的是“能快速看懂、能快速查询”的数据结构而不是一套严谨但复杂的ER图。范式建模在数据一致性上有优势但查询时往往需要关联十几张表口感和效率都不好。维度建模的核心要素是事实表和维度表。事实表记录业务过程产生的度量值比如订单金额、销售数量这些是数值型的可以进行汇总计算。维度表描述业务过程的上下文环境比如时间、用户、商品、地区这些是文本型的用来过滤和分组。以订单为例订单事实表里存的是订单ID、用户ID、商品ID、金额、数量这些字段其中用户ID和商品ID就是外键通过它们关联用户维度表和商品维度表。查询“华东地区三月份卖得最好的商品Top10”就是把订单事实表跟商品维度表、日期维度表、地区维度表关联起来做筛选和聚合。2.2 事实表的类型选择与粒度定义维度建模里事实表分为三种事务事实表、周期快照事实表、累积快照事实表。事务事实表最常用记录每个业务事件发生时的状态。比如下单事实表每一行是一个订单明细字段包含下单时间、商品ID、数量、金额等。它的特点是增量追加历史记录不会被修改适合做流量分析、销售分析这类需要追溯每一次行为的场景。周期快照事实表是在固定时间间隔比如每天记录某个对象的累计状态。比如每天记录一次每个用户的累计消费金额和累计订单数。它的特点是定期更新适合做存量分析比如用户生命周期价值分析、活跃用户统计。累积快照事实表记录一个业务流程从开始到结束的完整生命周期。比如订单累积快照表一行记录一个订单从下单、支付、发货、签收到完事的全部关键时间节点。它的特点是随业务流程推进不断更新适合做流程分析和漏斗转化分析。在day3建模时我首先确定了粒度定义。这是建模最核心的一步一张事实表的每一行到底代表什么。订单事务事实表的粒度定义为“订单中的一个商品明细行”也就是说如果一个订单里有三样商品那这张表里就有三行记录。这个粒度的好处是可以支持商品维度的分析比如“哪些商品经常被凑单一起购买”如果粒度定义在订单级别这种分析就做不了了。粒度的选择直接决定了事实表的行数级和指标的计算口径这个决定要慎之又慎。一旦表上线后再改粒度意味着所有下游ETL和报表都要重做代价非常巨大。2.3 缓慢变化维的几种策略day3先实现哪一种维度表里的数据会变化比如用户换了手机号、商品改了所属类目、店铺改了名称。如果直接更新原记录历史报表中关联出来的用户手机号、商品类目就都变了导致历史数据不准确。缓慢变化维SCD就是解决这个问题的。SCD有几种常见策略策略一是直接覆盖简单粗暴但丢历史策略二是新增一行记录旧记录保留新记录加一个新的维度代理键能完整保留历史策略三是增加几个固定字段来记录变化前后的值适用于只有少数几个属性可能变化的场景。对于day3的第一版我选择了策略一和策略二的组合。绝大多数维度表先按策略一处理每天全量刷新直接用最新数据覆盖保证当前状态的准确性。但用户维度和商品维度因为要做历史分析我加上了策略二的思想用自然键user_id/商品ID加生效日期来区分不同版本的记录。这里有个新手的常见误区直接用业务主键做主键。前两天我就踩了这个坑后续处理用户地址变更时因为主键冲突导致ETL流程挂了。强烈建议所有的维度表都加上一个自增的代理键dim_id业务主键降级为普通字段。代理键的好处是它与业务系统解耦业务主键的变化不会影响数仓内部的数据关联关系。3 实操过程从建表到ETL的全流程记录3.1 数仓开发环境与技术栈选择day3的实操环境我用了Hive作为数据仓库的存储和计算引擎。选Hive主要是三个原因一是它兼容SQL语法上手成本低写过MySQL的人基本能无缝切换二是它在处理超大数据量时比较稳定不用担心数据量大了之后撑不住三是社区生态成熟遇到问题能很快搜到解决方案。存储格式上我选用了Parquet列式存储格式。列式存储在查询时只需要读取涉及的列能省掉大量的I/O。再加上Snappy压缩既能省存储空间又不会太耗CPU。分区策略上所有表都按日期dt做分区每天一个分区这样在查询时可以通过分区裁剪快速过滤数据避免全表扫描。在动手建表之前我先花了一个多小时把命名规范定下来了。这个环节看着繁琐但后面省了我无数事。最终规范如下层前缀ODS层用ods_DWD层用dwd_DWS层用dws_ADS层用ads_维度表用dim_业务域订单域用trade用户域用user商品域用item表名结构层级前缀_业务域_业务过程比如dwd_trade_order_detail分区字段统一命名为dt类型为STRING格式为yyyyMMdd时间字段统一命名为xxx_time类型为TIMESTAMP3.2 DDL建表语句的具体写法建表的DDL我直接在生产环境跑了一遍这里把核心几张表的定义放出来。ODS层以订单表为例注意这里为了保持原样所有字段名跟源系统保持一致没有做任何转换。-- ODS层订单原始表 CREATE TABLE IF NOT EXISTS ods_trade_order_inc ( order_id BIGINT COMMENT 订单ID, order_sn STRING COMMENT 订单编号, user_id BIGINT COMMENT 用户ID, shop_id BIGINT COMMENT 店铺ID, item_id BIGINT COMMENT 商品ID, item_title STRING COMMENT 商品标题, category_id BIGINT COMMENT 类目ID, original_price DECIMAL(10,2) COMMENT 商品原价, actual_price DECIMAL(10,2) COMMENT 实际成交价, order_status TINYINT COMMENT 订单状态0待支付 1已支付 2已发货 3已完成 4已取消, order_time TIMESTAMP COMMENT 下单时间, pay_time TIMESTAMP COMMENT 支付时间, ship_time TIMESTAMP COMMENT 发货时间, finish_time TIMESTAMP COMMENT 完成时间, buyer_message STRING COMMENT 买家留言, create_time TIMESTAMP COMMENT 记录创建时间, update_time TIMESTAMP COMMENT 记录更新时间 ) COMMENT 订单原始表ODS层 PARTITIONED BY (dt STRING COMMENT 数据分区日期) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);DWD层的订单明细表就是在ODS基础上做了清洗转化。比如把order_status从TINYINT类型映射成可读的状态描述过滤掉测试订单和异常数据然后做了一个时间字段的标准化。这里我把订单的四个关键时间字段单独提取出来方便后续做时间维度的分析。DWS层的用户购买行为汇总表是按用户日期维度做聚合的核心是几个常用的统计指标。-- DWS层用户购买行为日汇总表 CREATE TABLE IF NOT EXISTS dws_user_buy_daily ( user_id BIGINT COMMENT 用户ID, order_count BIGINT COMMENT 下单次数, order_item_count BIGINT COMMENT 下单商品件数, paid_order_count BIGINT COMMENT 支付订单数, paid_item_count BIGINT COMMENT 支付商品件数, gmv_amount DECIMAL(16,2) COMMENT 下单金额GMV, pay_amount DECIMAL(16,2) COMMENT 实际支付金额, cancel_order_count BIGINT COMMENT 取消订单数, first_order_time TIMESTAMP COMMENT 当日首单时间, last_order_time TIMESTAMP COMMENT 当日末单时间 ) COMMENT 用户购买行为日汇总表 PARTITIONED BY (dt STRING COMMENT 数据分区日期) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);建表时有两个小细节容易被忽略。第一个是字段注释一定要写清楚尤其是DECIMAL类型的精度含义和状态字段的枚举值不然过两个月自己看着都模棱两可。第二个是分区字段的类型要统一用STRING不要有的地方用STRING有的地方用BIGINT否则在做分区修剪时容易出错。3.3 ETL开发从ODS到DWD的清洗转换实现ETL是整个数仓构建的核心环节。今天主要完成了ODS到DWD的清洗转换实战用一个完整的SQL当作教学素材来分析。第一段SQL做的事情是把ODS层的订单数据经过过滤去重之后落到DWD层INSERT OVERWRITE TABLE dwd_trade_order_detail PARTITION (dt ${bizdate}) SELECT order_id, order_sn, user_id, shop_id, item_id, category_id, original_price, actual_price, CASE order_status WHEN 0 THEN 待支付 WHEN 1 THEN 已支付 WHEN 2 THEN 已发货 WHEN 3 THEN 已完成 WHEN 4 THEN 已取消 ELSE 未知 END AS order_status_name, order_time, pay_time, ship_time, finish_time FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM ods_trade_order_inc WHERE dt ${bizdate} ) t WHERE t.rn 1 AND t.order_id IS NOT NULL AND t.actual_price 0;这段SQL里有几个关键点特别值得说一说。ROW_NUMBER()窗口函数的去重处理是应对源系统数据重复的保险手段。在真实的业务环境里同一个订单ID可能会因为数据重推、系统重试等原因出现多行记录如果用distinct只能处理字段完全重复的情况对于字段内容有差异的记录无能为力。用ROW_NUMBER()按update_time倒序取最新的那条记录能保证拿到的是每个订单最新的状态。关于过滤条件我做了两个限制订单ID不为空实际支付金额大于0。这里的逻辑是把一些脏数据拦截在DWD层之外避免脏数据污染上层应用。但这里有个经验教训清洗逻辑不是写得越严越好过度清洗可能会把正常数据误杀。比如actual_price 0就会把0元支付的优惠活动订单给过滤掉如果你的业务里存在这种情况就需要跟业务方仔细确认口径。3.4 DWD到DWS的汇总计算与调度配置ODS到DWD是数据清洗DWD到DWS做的是指标汇总。拿用户日汇总表来举例子它的SQL逻辑其实就是对DWD明细数据做GROUP BY聚合INSERT OVERWRITE TABLE dws_user_buy_daily PARTITION (dt ${bizdate}) SELECT user_id, COUNT(order_id) AS order_count, SUM(item_count) AS order_item_count, SUM(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS paid_order_count, SUM(CASE WHEN pay_time IS NOT NULL THEN item_count ELSE 0 END) AS paid_item_count, SUM(actual_price) AS gmv_amount, SUM(CASE WHEN pay_time IS NOT NULL THEN actual_price ELSE 0 END) AS pay_amount, SUM(CASE WHEN order_status_name 已取消 THEN 1 ELSE 0 END) AS cancel_order_count, MIN(order_time) AS first_order_time, MAX(order_time) AS last_order_time FROM dwd_trade_order_detail WHERE dt ${bizdate} GROUP BY user_id;这段SQL就是典型的轻度汇总逻辑。把用户在某一天的下单行为聚合成一条记录后续做用户分群、复购率分析、高价值用户识别时直接查这张表就行不用再跑全量明细。ETL写完就要配调度。调度系统的核心是依赖管理DWS层跑之前必须确认ODS层对应分区的数据已经就绪DWD层已经完成清洗。我今天的配置方式是ODS同步在凌晨2点执行DWD清洗在凌晨3点执行DWS汇总在凌晨4点执行ADS应用在凌晨5点执行每个环节都配置了失败自动重试和告警通知。第一天在配置调度时就把时间间隔设得太紧凑结果ODS因为业务库延迟10分钟才把数据准备好整个链路全部失败。所以做调度一定要预留一定的buffer时间不要卡得太死。4 常见问题与排查技巧实录4.1 数仓从0到1最容易踩的7个问题今天的实操中一共遇到了7个比较典型的问题我整理成了表格方便后续查阅。问题现象根本原因解决方案跑ETL时发现两天的数据量差异巨大源系统的订单表存在状态更新增量抽取漏掉了updateODS层按全量增量混合逻辑抽取主表按更新时间增量拉取JOIN结果出现重复行关联字段在维度表中不唯一先对维度表按自然键去重再加代理键关联SUM出来的金额对不上业务报表DECIMAL精度设置不一致所有金额字段统一使用DECIMAL(16,2)并在DWD层统一口径查询越跑越慢甚至跑不出来缺少分区过滤扫了全表强制要求所有查询带上dt分区条件配置查询拦截器数据同步到一半卡死源库连接超时同步任务无重试机制配置自动重试3次增加超时保护某天分区数据为0但任务显示成功源表当天确实没有新数据但下游依赖失败增加数据质量校验对空分区发出告警字段值出现乱码或null源系统编码不一致或脏数据DWD层增加编码统一处理和NULL值兜底逻辑4.2 数据倾斜问题的定位与处理经验如果要做个排行数据倾斜大概能登上数仓经典问题的前Top3。我今天在跑DWD层订单明细和商品维度表JOIN时也碰到了这个情况有一个头部商家的订单量占到了全平台的40%以上所有这个商家的订单明细都被分配到了同一个Reduce Task上其他Task早就跑完了就这个Task卡了两个小时。定位数据倾斜最直接的方法是看任务日志找到长时间未结束的Task看它处理的数据量和其他Task的差距有多大。如果差距达到数倍以上基本就可以判定是倾斜了。我这次用了两个方案来优化。方案一是把热点键加上随机前缀打散先用WITH子句把热点商家的订单数据单独提取出来给商家ID拼接一个随机字符串比如CONCAT(shop_id, _, FLOOR(RAND()*10))这样就能把数据均匀分布到10个Task上进行局部聚合最后再做一次整体聚合。方案二是用MAP JOIN把小表加载到内存中避免大表JOIN小表时触发数据倾斜。两个方案对比如下优化方案适用场景优点缺点热点键加随机前缀倾斜键值集中且数据量大能根治倾斜问题需要修改SQL逻辑二次聚合有额外开销MAP JOIN关联表是小表小于1GB简单直接几乎不用改SQL大表JOIN大表时不适用两阶段聚合倾斜键的值比较离散但某一类特别集中通用性好适合GROUP BY场景需要额外写一层子查询4.3 增量表与全量表的选择依据ODS层到DWD层的数据同步有两种模式增量同步和全量同步。很多新手会在这一步犹豫不决。我的个人经验是看两个指标数据量和更新模式。如果表的数据量在百万级以下全量同步最简单每天直接覆盖逻辑清晰容易排查如果表的数据量在千万级以上就要考虑增量同步。同时要分析业务表的更新模式只有新增没有修改的日志表适合增量既有新增又有修改的实体表需要增量更新捕获。订单表是典型的既新增又修改的场景订单状态从待支付变成已完成收货地址可能修改。所以我用了一个增量抽取方案每天从业务库抽取create_time或update_time在当天的数据存到ODS的对应分区。在ODS到DWD的处理中先用ROW_NUMBER按订单ID和更新时间去重取最新状态再关联历史明细做合并。如果直接全量同步每天的同步时间会随着业务量的增长越来越长如果只做简单增量订单的状态更新会丢失。4.4 分区字段选择时的隐藏陷阱分区字段的选择看似简单实际上有不少讲究。今天的实操里有一张订单流水表一开始设计的时候选择了用订单时间order_time作为分区字段后来排查的时候发现有几笔凌晨的订单数据一直跑到第二天的分区里去了搞得每天的数据量都不稳定。原因其实是业务方的订单时间写入逻辑不统一。大部分订单是用户直接下单订单时间就是当前时间但有一批特殊渠道的单子是人工补录的订单时间可能是过去的某个时间点。如果按订单时间做分区过去补录的订单会被写进历史分区而同步任务当天只处理当天时间创建的数据这些补录的订单会被漏掉。后来统一改成按数据写入时间也就是同步日期dt作为分区字段。这样做的逻辑是分区代表的是“数据什么时候进入数仓”而不是“业务事件什么时候发生”。所有的同步任务都只管当天新进入的数据不会出现凌晨的数据被算到第二天的情况。而业务时间则单独作为字段存储在表里统计时用WHERE条件过滤即可。这个坑比较隐蔽建议在第一天设计表结构时就充分考虑不然后面返工的代价非常昂贵。5 数仓质量校验与后续规划5.1 数据质量校验的几个维度第三天做完ETL之后我没有直接宣告完工而是花了不少时间做数据质量校验。数仓里有一个残酷的事实数据质量出问题往往不是同步或计算环节出问题而是质量问题一开始就存在只是没人发现。我在做校验时重点看四个维度。完整性方面要确认每个表的分区都已正常生成不能有空白分区同时核对主表数据量是否在合理范围内。准确性方面要用几个已知的指标做交叉验证比如把DWS层算出来的当日GMV跟业务后台的报表对比差距在1%以内可以接受超过就要排查。一致性方面要重点检查同一个指标在不同表里的口径是否一致比如“支付金额”在明细表里是字段求和在汇总表里是SUM(CASE WHEN pay_time IS NOT NULL)这两者对0元订单的处理方式必须统一。及时性方面则主要看数据产出的时间是否满足业务方的SLA要求。我今天校验时抓到一个典型问题DWS层的gmv_amount比业务后台的GMV少了约0.3%。排查后发现是有少量订单的actual_price字段为空在DWD层被过滤掉了但这些订单在业务后台是计入GMV的。解决方案是把DWD层的过滤逻辑从actual_price 0改成actual_price IS NOT NULL AND actual_price 0同时把空值统一替换成0。这类问题在开发时极其容易被忽略等数据上线了才发现交出去的报表对不上数信任度会大打折扣。5.2 day3之后的扩展方向目前day3搭建的是一个最小可用闭环ODS到DWD到DWS的全链路已经跑通核心的订单和用户主题已经覆盖。后面的计划是逐步把商品、营销、日志行为等主题加进来同时完善维度表的SCD策略。有一个排序问题值得说一下很多人搭数仓喜欢一上来就把所有业务域全部铺开结果东一榔头西一棒子最后哪块都没做好。我建议的路径是先打通一个核心业务域的完整链路把从ODS同步到ADS输出的整个流程跑顺然后再横向复制到其他业务域。业务域之间有共性的规范可以先沉淀成模板比如统一的命名规范、统一的时间处理逻辑、统一的汇总表结构这样后面加新域的时候只是套模板改字段不用每次从头开始设计。5.3 第一天开始就要建立的三个好习惯写到最后分享三个我反复踩坑之后才养成的习惯。如果现在还在数仓入门阶段建议从第一天就建立起来。第一个习惯是写开发文档。哪怕只是一个人开发也要把每张表的字段口径、每段SQL的业务逻辑记录下来。数仓是一个长期演进的项目三个月后再回来看自己写的SQL没有文档辅助重构会很痛苦口径文档是数仓项目里最能保值的资产。第二个习惯是做数据校验快照。每次ETL跑完后把关键表的数据量、关键指标的汇总值记录到一个校验表里长期积累下来能形成一套数据波动监控体系。这个习惯能让你在数据对不上时快速定位是哪天的任务出了状况。第三个习惯是随手沉淀返工经验。数值溢出、字符串隐式转换失效、时区差异导致日期错位这类问题每个数仓开发者都会遇到把问题和对应的排查过程记录下来不仅帮自己省时间也能沉淀成团队的知识库。构建数仓这件事技术本身不复杂复杂的是对业务的理解和规范的坚持。day3能完成的只是一个骨架后续的数据模型演进、性能调优、质量保障才是让这个骨架真正长出肌肉的过程。希望今天的分享能给同样走在数仓路上的朋友一些参考。