## 一、承接上文数据进来了然后呢上一篇我们解决了「数据怎么进来」——用 DataX 把业务库的刷卡主数据和用户信息表搬到了 Hive 的 ODS 层。现在的状态是ODS 层有几张和业务库一模一样的表字段还是那副样子数据还是全量。这时候如果直接拿 ODS 去做报表会发生什么一个「进站人次排行」指标SQL 里要写一堆if、case when去清洗脏数据三个指标要写三遍几乎相同的清洗逻辑改一次口径要改三个脚本每次查询都要扫全量数据跑一次几分钟所以第二天的任务很明确DWD 层建模— 把 ODS 洗干净、规范好DIM 层建模— 把维度单独抽出来管理DWS 层建模— 做轻度聚合让指标能复用ADS 层建模— 算出最终的三个指标ClickHouse 搭建— 给大屏提供亚秒级查询能力宽表写入 CK— 把 ADS 结果推给 ClickHouse整条链路是这样一条「越走越窄、越走越快」的路ODS(原始) ─► DWD(明细) ─► DIM(维度) │ └─► DWS(轻聚合) ─► ADS(指标) ─► ClickHouse ─► 大屏[图1数仓分层数据流转图]二、原理为什么要分层这是本篇文章最该先想通的问题。很多人背得出「ODS/DWD/DWS/ADS」但说不清分层的动机。2.1 现象与原因现象不分层也能跑把所有逻辑堆在一条 SQL 里报表照样能出数。原因数据量小的时候确实能跑一旦表变多、口径变复杂代价会指数级上升痛点不分层分层之后口径统一每个脚本各写一遍清洗逻辑只在 DWD 写一次复用性无法复用DWS 结果被多个 ADS 指标共用排查成本一条几百行 SQL改不动逐层定位哪层错查哪层性能每次查都扫全量明细上层数据已被聚合/裁剪过解耦源表加个字段连锁崩上游变更被 DWD 挡住2.2 分层的本质说白了就两个词职责分离空间换时间。职责分离每一层只干一件事——ODS 负责「原样落地」DWD 负责「清洗规范」DIM 负责「维度管理」DWS 负责「轻聚合」ADS 负责「出指标」。一层只关心自己的上游。空间换时间DWS 把明细算成聚合ADS 直接读结果本质是提前算好、多存一份、换取查询变快。各层的定位对照分层定位粒度常见存储ODS原始数据层贴源不做处理与业务库一致Hive 外部表TextFileDWD明细数据层清洗规范化一行 一个业务事实Hive ORC分区DIM维度层一致性维度一行 一个维度对象Hive ORC拉链表DWS汇总层轻度聚合宽表天 维度组合Hive ORC分区ADS指标层面向应用一行 一个指标结果Hive ClickHouse三、DWD 层建模把脏数据变成「可用明细」3.1 核心职责DWD 只做四件事清洗、规范化、脱敏、维度退化。清洗去重、过滤无效记录金额为负、时间越界、空值统一规范化字段类型统一日期统一格式、金额统一精度、命名统一驼峰改下划线脱敏手机号、身份证等敏感字段做掩码 —— 数仓里这一步别省维度退化把常用的维度字段比如线路名、车站名直接冗余进事实表避免每次都去 join 维表最后一条是维度建模里很关键的思想为了查询性能主动接受冗余。3.2 两个必须做的决策决策一ETL 还是 ELTETL抽取→转换→加载先用外部工具算好再入库传统 Hadoop 时代ELT抽取→加载→转换先灌进 Hive再用 SQL 加工现代数仓主流我们选ELT。原因很实际Hive 的计算能力就摆在那儿何必再引一个转换工具数据先落地再加工出问题还能回溯原始数据。决策二增量还是全量方案适用场景代价每日全量小表、维表简单但数据量一大就崩每日增量大事实表刷卡表必须靠分区否则重跑没法幂等刷卡表这种每天新增几十万条的事实表必须用增量 分区。3.3 建表分区和存储格式不是可选项CREATETABLEdwd_swipe_detail(idBIGINT,card_no STRING,line_name STRING,station_name STRING,...)PARTITIONEDBY(dt STRING)STOREDASORC TBLPROPERTIES(orc.compressSNAPPY);这里每一行都在解决性能问题PARTITIONED BY (dt)按天分区。查询时只要where dt xxxHive 会做分区裁剪直接跳过其它天的目录不扫全表。这是 Hive 最重要的优化手段。STORED AS ORC列式存储 压缩。报表通常只查几列列存只读那几列ORC 还带谓词下推和统计信息能进一步减少扫描量。3.4 最容易踩的坑重跑不幂等现象任务失败后重跑一次DWD 表数据翻倍了。原因用了INSERT INTO它只追加不清空。而采集任务失败重跑是常态。对策永远用INSERT OVERWRITE配合分区。INSERTOVERWRITETABLEdwd_swipe_detailPARTITION(dt2026-10-14)SELECT...FROMods_swipeWHERE...overwrite 指定分区 只覆盖当天分区重跑多少次结果都一样这叫幂等。数仓任务的第一准则可重跑。四、DIM 层建模维度建模的两块基石4.1 事实表和维度表维度建模只认两种表事实表记录「发生了什么」是可累加的数字刷卡一次、金额多少行多、增长快维度表描述「谁、在哪、什么时候」是描述性属性用户、车站、线路行少、相对稳定围绕这个划分有两种模型模型特点取舍星型模型维度表不拆表一级扁平冗余换查询性能join 少、易理解 ← 我们用这个雪花模型维度表继续拆子表规范化省空间但 join 层级深、查询慢数仓里基本一边倒选星型。理由跟 DWD 的维度退化一致分析场景要的是查得快不是存得省。4.2 变化维维度会变历史要留这是 DIM 层真正有技术含量的地方。现象用户表里的「所在城市」字段用户搬家后就被覆盖了。结果三个月前那笔刷卡记录用现在的城市去分析口径全错。原因维度表的属性是会随时间变化的而事实表记录的是当时的状态。直接覆盖 历史事实和当时的维度对不上了。对策按场景选变化维策略策略做法是否留历史场景SCD1直接覆盖否改错了、修正数据SCD2拉链表加start_date/end_date是需要按历史状态分析 ← 我们用这个SCD3加一个「上一个值」列只有一版极少用4.3 拉链表的原理拉链表看着玄本质就一句话用两个日期字段标记每一条维度记录的有效区间。user_id | city | start_date | end_date --------|------|------------|----------- 1001 | 北京 | 2026-01-01 | 2026-10-13 ← 旧版本被关闭了 1001 | 上海 | 2026-10-14 | 9999-12-31 ← 新版本当前有效每天的处理逻辑分三步比对新旧把当日新增/变更的维度数据和历史拉链表做full outer join按主键找出「有新值的记录」关闭旧版本把历史里被更新的那批记录end_date改成昨天开新版本变更数据作为新记录写入start_date 今天end_date9999-12-31表示仍然有效9999-12-31这个「最大日期」是拉链表的固定套路用来表示当前生效版本方便查询时写where end_date 9999-12-31直接拿最新状态。4.4 一致性维度DIM 层还有个隐性要求同一个维度全公司只能有一份。比如「车站」这个维度客流分析要用、线路分析也要用。如果各主题各建一份同一车站在两张表里编码不同跨主题的指标就永远对不上。所以公认的维度统一放 DIM 层全局唯一 —— 这就是「一致性维度」。五、DWS 层建模让指标能复用5.1 为什么要有 DWS现象三个 ADS 指标里「进站人次排行」和「每日运输乘客最多的车站」都要按「车站 天」统计人次。如果没有 DWS这两个指标就要各自扫一遍 DWD 明细表。原因ADS 直接怼 DWD等于重复计算。数据量一大跑一次十几分钟还白烧一遍集群资源。对策中间加一层 DWS把「车站 天」的聚合结果算好存下来ADS 直接读。5.2 核心思想DWS 的设计有两个原则以维度为基准先想清楚「按什么粒度聚合」再往里面塞度量。所以 DWS 表名通常是dws_业务过程_粒度_天。按天累积一天一个分区只算增量天不清历史。5.3 一个重要提醒宽表不是越宽越好DWS 常被叫做「宽表」但宽是有代价的列越多单次扫描的 IO 和内存越大如果一张 DWS 表塞进了所有可能用到的度量那它就退化成了「另一种明细表」复用性没提升成本却上去了正确做法按主题域拆多张 DWS 表每张只服务一组关联指标让 ADS 能精准取数。六、ADS 层建模三个指标的口径落地ADS 是唯一面向「人」的一层它的用户是大屏、报表、运营。所以它的设计原则很直接一个指标一张表一次算好多次消费结果要小。下面拿大纲里的三个指标走一遍。指标建模先把口径说清楚再谈 SQL——口径不清晰代码写对了也是错的。指标表名口径定义数据来源指标1进站人次排行ads_in_station_rank按车站汇总进站刷卡人次按人次倒序排名DWS 车站-天聚合表指标2每日运输乘客最多的车站区ads_station_passenger_top每日按车站所属区域汇总运输乘客数取 Top NDWS DIM 车站维度指标3线路单程直达乘客耗时平均ads_line_direct_cost_avg同一乘客同一线路直达行程不含换乘的平均耗时DWD 明细表三个指标分别体现了三种典型模式指标1 是排行类在 DWS 上再套一层row_number() over (order by cnt desc)属于纯复用指标2 是维度下钻类需要 join DIM 的车站维度拿到「所属区域」属于跨层关联指标3 是明细回溯类直达行程要判断「同一乘客、同一线路、进出站配对」这类逻辑 DWS 聚合不出来必须回 DWD 明细算最后一条其实点出了分层的边界DWS 不是万能的。当指标口径依赖「行程级」的配对和判定时聚合层的抽象就不够了只能回到明细层 —— 这也是为什么 DWD 必须存在、且要保留完整明细。[图2ADS 三个指标的建表与结果预览]七、ClickHouse 搭建为什么要再引一个数据库7.1 现象Hive 出数了但大屏还是转圈现象ADS 层数据算好了大屏去查一个指标要等十几秒甚至几分钟。原因Hive 是为大批量离线计算设计的——explain一下就知道一个简单查询也要起 MapReduce 任务任务启动开销就是秒级。它擅长「扫几十亿行算出结果」不擅长「快速查出一小撮结果」。而大屏是高频、小结果集、要求秒级响应的场景。两者的设计目标根本不匹配。对策引一个 OLAP 数据库把算好的指标结果同步过去专供查询。这就是ClickHouse下称 CK。7.2 CK 为什么快原理CK 的快不是玄学是四件事叠出来的① 列式存储数据按列存而不是按行存。报表查车站 人次两列时只需读这两列的数据文件其它几十列完全不碰。行存做不到这点它必须把整行读进来。② 数据有序 稀疏主键索引CK 建表时用ORDER BY (列)指定物理排序键。数据按这个键排好序落盘后CK 每 8192 行打一个「标记」记录这一块的最小值/最大值。查询时凭标记就能整块整块地跳过不相关的数据 —— 这是 CK 查询快的最大功臣比索引本身还重要。③ 向量化执行 SIMD传统数据库是「取一行、算一行」函数调用开销大、CPU 流水线利用率低。CK 是「取一批block、算一批」用列存 SIMD 指令一次处理多个值同样时间处理的数据量高一个数量级。④ MergeTree 引擎类 LSM 的批处理友好结构MergeTree是 CK 最核心的表引擎。写入时数据先落成一个不可变的part后台线程再异步 merge成更大的 part。这个设计把「随机写」变成了「顺序写 后台整理」所以 CK特别欢迎大批量写入特别讨厌单条插入每插一条就产生一个 partmerge 追不上会报too many parts。围绕这个特性CK 还有几个变种引擎引擎作用MergeTree基础引擎ReplacingMergeTree按排序键后台去重注意是最终一致不是实时SummingMergeTree相同排序键的数值列自动求和7.3 搭建要点CREATETABLEads_in_station_rank_ck(station_name String,dtDate,cnt UInt64)ENGINEMergeTreePARTITIONBYdtORDERBY(station_name,dt);搭建时几件必须做的事很多人第一次装 CK 都栽在这里关 swap / 调max_memory_usageCK 查询吃内存内存一超就 OOM Kill。单机部署要显式配置内存上限。PARTITION BY用日期分区数别太多分区过细会产生大量小 part反而拖慢。ORDER BY就是主键它的选择直接决定查询性能按「查询最常用的过滤列」来排不是按唯一性来排。单机 vs 集群如果只是给大屏供数、数据量不大单机 CK 完全够用。集群分片 副本带来的是吞吐和容灾同时也带来运维复杂度别为了「看起来专业」硬上。八、宽表写入 CK把第一天的技能复用起来8.1 方案选择ADS 结果在 Hive需求是推到 CK。常见三条路方案原理评价DataXclickhousewriter插件直连 CK复用第一天技能改动最小← 选这个CK 的 Hive 表引擎CK 反向读 Hive配置依赖多排错难Spark 写 JDBC自己写代码灵活但重没必要选 DataX 的理由和第一天一致它是插件化架构换目标端只需要换 writer 插件、改 JSONReader 和调度逻辑一行不用动。这正是第一天讲的那个原理在发挥价值 —— 当初理解「Framework Plugin」现在就直接省掉了一整套新工具的学习成本。8.2 配置改动只有一处对比第一天的 HDFS 同步这次只有writer段变了writer:{name:clickhousewriter,parameter:{username:default,password:,connection:[{jdbcUrl:jdbc:clickhouse://ck-host:8123/ads_db}],table:[ads_in_station_rank_ck],batchSize:10000,column:[station_name,dt,cnt]}}两个关键点batchSize要给大上万级。原因回到 7.2 的第 ④ 条CK 最怕小批量写入产生海量 part批越大吞吐越高。列顺序必须和 CK 表定义严格对应。DataX 是按位置写入的不是按列名匹配 —— 顺序错了不会报错只会静默写脏数据这是最阴的一类 bug。8.3 幂等重跑问题在 CK 这边同样存在CK 的MergeTree不支持INSERT OVERWRITE重跑就会追加重复数据。对策二选一同步前先按分区ALTER TABLE ... DROP PARTITION再写入改用ReplacingMergeTree查询时用FINAL或自己去重注意ReplacingMergeTree 是后台异步去重的不保证实时查报表前要确认 merge 已完成这也是为什么我把「幂等」在两篇博客里各强调了一次从 Hive 到 CK任何一个环节不幂等重跑就是灾难。九、小结把两天的链路串起来回头看两天的内容其实是一条完整的链路Day1 ODS 采集 ──► Day2 DWD 清洗 ──► DIM 维度 └─► DWS 轻聚合 ──► ADS 指标 ──► CK ──► 大屏三个最值得记住的原理① 分层是为了职责分离每层只解决一个问题上游变了被本层挡住。而它的代价多存几份数据换来的是复用性和可维护性 —— 这就是「空间换时间」。② 维度建模的核心是「冗余换性能」无论 DWD 的维度退化、DIM 选星型而非雪花、还是 DWS 做成宽表背后都是同一个决策分析场景下查询性能比存储空间更值钱。③ CK 和 Hive 是互补不是替代Hive 擅长「大量数据算一次」CK 擅长「算好的结果查很多次」。所以正确姿势是在 Hive 里算在 CK 里查。理解各自的定位才不会拿大炮打蚊子。真正跑通这条链路之后你会发现最难的从来不是写 SQL而是想清楚每一层该放什么、每个指标的口径是什么。建模能力本质上就是这种「想清楚」的能力。