视频讲解/数仓建模

全量表增量表快照表

报表3200,真实120万?

章节

  • 0:00 开场
  • 0:36 一句话回答
  • 0:54 全量表
  • 1:37 增量表
  • 2:26 快照表
  • 3:17 原因一 · 回答的问题不同
  • 4:32 原因二 · 成本和复杂度
  • 5:28 三类表对比
  • 6:34 落到代码
  • 7:58 原因三 · 历史追溯
  • 8:40 延伸一 · CDC
  • 9:19 延伸二 · 拉链表
  • 10:02 全量表 = 快照表?
  • 10:57 选型决策树
  • 11:53 面试回答模板
  • 12:51 高频追问
  • 14:48 回到开头

文稿

开场

老板问今天有多少 VIP 用户,报表上写着 3200,真实数字是 120 万。原因只有一个:这条 SQL 查的是增量表,昨天就是 VIP、今天没变动的老用户,全被漏掉了。很多人答这道题只背定义:全量就是全部数据,增量就是新增数据,快照就是拍照。这话不算错,但太薄。再被追问一句:全量表和快照表是不是一回事?很容易卡住。真正要讲清的是三件事:每类表解决什么问题,什么场景用哪一种,用错了会出什么事故。

一句话回答

一句话回答:全量表,存某一批次的完整数据;增量表,存一段时间内发生变化的数据;快照表,存某个时间点的业务状态。选哪种表,核心看你要的是当前全貌、变化过程,还是历史状态还原。

全量表

先看全量表:不管数据有没有变,每次同步,都把源表当前的全部数据搬一遍。比如 user 用户表有 5000 万行,今天同步,就是 5000 万行。明天哪怕只新增了 10 万用户,也还是整表再搬一遍,5010 万行。全量表常见两种落法。第一种不分区,每次直接覆盖成最新全量,比如 ods_user_full。第二种按天分区,每个 dt 分区存一份当天的全量。命名上要注意:常见约定里,df 是日全量,di 是日增量。按天存全量的表,应该叫 ods_user_df,别写成 di。就像每天把全班花名册完整复印一份:好处是简单,坏处是纸很费。

增量表

再看增量表:它存的是变化数据,不是完整数据。它关心的是:今天新增了哪些订单,哪些用户改了资料,哪些商品被删了。增量通常有三种抽法:按 create_time 抽新增;按 update_time 抽新增和修改;或者用 Binlog、CDC,抓 insert、update、delete 三种变化日志。比如订单表历史累计 2 亿单,今天新增 80 万单,另有 20 万单状态变更。全量同步每天要搬 2 亿行;增量只搬今天的新增加变更,大约 100 万行,成本差距非常大。就像班级日志:花名册不用每天重抄,只记今天来了转学生、张三改了手机号、李四转走了。记变化很省事,可要看完整名单,就得把历史日志一条条合并起来。

快照表

最后是快照表:它存的是某个时间点的状态。它不关心今天变了什么,而关心今天结束时,数据长什么样。典型例子是库存快照、账户余额快照、用户等级快照。比如商品 A:6 月 23 日日终库存 100 件,24 日日终 75 件,25 日日终 120 件。把 25 日展开:早上库存 75 件,11 点卖出 20 件,剩 55 件;16 点入库 75 件,变成 130 件;20 点再卖出 10 件,剩 120 件。快照表在日终只取最后的状态:商品 A,库存 120,中间每一次进出,不一定记录。前面专题讲过的周期快照事实表,存的就是这种时间点状态。像每晚给货架拍一张照片:能看出今晚还剩多少,看不出白天每一笔进货和卖货。

原因一 · 回答的问题不同

为什么这三类表不能乱用?第一个原因:它们回答的问题不一样。全量表回答现在全貌是什么,增量表回答这段时间变了什么,快照表回答某个历史时点是什么状态。老板问今天 VIP 有多少,要的是今天结束时所有 VIP 的全貌,应该查当天的全量分区或快照分区。误查增量表,只会统计今天有变化的 VIP;昨天已经是 VIP、今天没变的用户,全部漏掉。线上最常见的数据事故之一,就是把增量表当全量表用。正确思路是按问题选表:统计当前 VIP 总数,查全量;统计今日新增 VIP,查增量;还原某天的 VIP 总数,查快照。看 SQL。错误写法:FROM ods_user_inc,取 6 月 25 日分区,过滤 VIP,再对 user_id 去重计数。它只能数出当天有变化的 VIP,没变更的老 VIP,一个都进不来。正确写法:换成 dim_user_snapshot 的当天分区,同样过滤 VIP,再加上 is_deleted 等于 0。关键点是:这个 dt 分区里,要包含当天结束时的全部有效用户。一句话记住:增量表不是小号全量表,它只记录变化,不代表当前完整状态。

原因二 · 成本和复杂度

第二个原因:成本和复杂度完全不同。全量表最简单但最费资源;增量表最省资源,但要处理合并逻辑;快照表查历史最方便,但存储成本更高。假设用户表 5000 万行,每天变化 20 万行。全量表每天同步 5000 万行,开发简单,但同步慢、存储大,适合小表或低频表。增量表每天只同步 20 万行,成本低,但必须处理新增、修改、删除,适合大表和流水表。快照表每天保存一个状态截面,查历史方便,适合状态型分析;可如果每天存全量快照,存储会持续膨胀。在 MaxCompute 里,按天分区的表通常会设生命周期。分区从最后一次修改算起,超过设定天数没有变动,就会被自动回收。所以,生命周期设多少天,就决定了最多能往回查多少天。Hive 4.0 也有类似的分区保留属性,不过按分区的创建时间算。

三类表对比

把三类表放进一张表对比。核心含义:某批次完整数据,某段时间变化数据,某时间点状态数据。数据量:全量通常最大,每天全表搬运;增量通常最小,只存变化;快照取决于粒度,可能很大。开发难度:全量最低,覆盖或分区写入即可;增量较高,要处理更新和删除;快照中等,要定好快照时间点。查当前状态:全量直接查最新分区,快照查最新快照,都方便;增量要合并历史,不方便。查变化过程:增量最擅长,变化天然都在;全量只能拿两天的全量对比;快照只看到截面差异,不完整。历史还原:快照最适合按天还原;全量要保留每日分区才能还原;增量能还原,但逻辑复杂。存储成本:全量高,增量低,快照中到高。典型场景:全量用于小表、维表、配置表;增量用于订单流水、用户行为、CDC 日志;快照用于库存、余额、用户等级、账户状态。

落到代码

落到代码上,全量同步很直接:INSERT OVERWRITE 当天分区,SELECT 源表全部字段。INSERT OVERWRITE 会覆盖目标分区原有的数据,重跑一遍,结果不变;前面专题讲过,这就是分区幂等。增量就不能写入完事了,通常还要合并成一张当前状态表或快照表。做法是:昨天的快照 FULL OUTER JOIN 今天的增量,按 user_id 对齐。两边都有,取今天的新值;只有昨天有,保留旧值;只有今天有,就是新用户。最后过滤删除标记,被删的用户不进入当天的有效快照。这段合并 SQL 有两处要改。第一,很多人每个字段都写 COALESCE,取今天的值,取不到再用昨天的。可 COALESCE 返回的是第一个非空值;今天某个字段真的被改成了空,它会当成没变,又把旧值拿回来。更稳的写法是按整行判断:inc.user_id 不为空,整行取增量;否则整行取昨天的快照。第二,同一个用户一天可能变好几次,合并前要先按主键去重,只留 update_time 最新的一条。还要注意,今天的快照依赖昨天的快照;哪天算错了,就得从那天开始按天串行往后重跑,前面专题讲过。记一句话:全量表省脑子,增量表省机器,快照表省查询;真实项目看成本和需求怎么平衡。

原因三 · 历史追溯

第三个原因:历史追溯能力不一样。常见追问:老板要看 3 个月前某个用户当时是什么等级,怎么查?只保留最新全量是不够的:它只能告诉你现在是什么,不能告诉你过去是什么。有每日快照表,直接查 3 月 25 日那个 dt 分区,就能还原当时的状态。只有增量表也不是不能查,但要从初始状态开始,把 3 个月的变化按顺序回放,逻辑复杂得多。还有个前提:快照分区得还在。生命周期只设了 30 天,3 个月前的分区早就被回收了。打个比方:最新全量像今天的体检报告,增量像每天的指标变化记录,快照像每月固定存档的体检档案。

延伸一 · CDC

延伸一:CDC。全称 Change Data Capture,就是变化数据捕获。它通常解析 MySQL 的 Binlog、PostgreSQL 的 WAL 这类数据库日志,拿到每一条 insert、update、delete。链路是这样的:订单表的每次变化写进 Binlog,Canal 或 Debezium 解析出来,发到 Kafka Topic。再按 dt 落成增量分区 ods_order_inc,最后合并到订单快照,保留当前状态。所以 CDC 是拿增量的方式,增量表是落到数仓后的结果。CDC 负责告诉你发生了什么变化,增量表负责把这些变化沉淀下来。

延伸二 · 拉链表

延伸二:拉链表,一种比每日全量快照更省空间的历史状态表,前面专题讲过,这里简单带过。用户表 5000 万行,每天只变 20 万行,每天存一份全量快照,很浪费。拉链表不每天复制所有用户,而是给每条状态记录加上 start_date 和 end_date。比如用户 1001:1 月 1 日到 3 月 10 日是普通;3 月 11 日升了 VIP,end_date 写 9999-12-31。要查 2 月 1 日的状态,就看这个日期落在哪个起止区间里,答案是普通。快照像每天拍照;拉链像只在造型变化时拍照,并记下这套造型从哪天穿到哪天。变化少的时候,拉链表非常省。

全量表 = 快照表?

再回到那个追问:全量表和快照表是不是一回事?不完全是,但会有重叠。全量表偏同步方式:这次从源系统搬了全部数据。快照表偏建模用途:保存某个时间点的状态,用来还原历史、分析状态。每天全量同步到 dt 分区,并且保留历史分区,它既是全量同步的结果,也能当每日快照来用。如果全量表只留最新一份,覆盖掉旧数据,它就承担不了历史快照的职责。看四个场景:每天全量覆盖旧表,算全量,不算快照;每天全量并保留 dt 分区,两个都算。每小时保存账户余额,不一定是全量,但算快照,重点是时间点状态。Binlog 每条变化落表,两个都不算,这是增量变化明细。这是加分点:全量和增量,更多是采集同步视角;快照,更多是数据建模和分析视角。

选型决策树

把选型串成一棵决策树。拿到一张业务表,先看数据量大不大、变化频率高不高。小表或变化很少,比如地区、配置、字典,优先全量表:每天全量覆盖或保留分区,适合维表和配置分析。大表且每天只变一小部分,比如订单、用户、商品,再问:需不需要记录每次变化的过程?需要,比如审计、行为、订单流转,就落增量表,保留 insert、update、delete。有了增量表以后,如果变化少、又要保留历史状态,考虑拉链表;否则进入 DWD 明细层,供过程分析和指标加工。不需要每次过程、只看每天结束状态,再问要不要还原历史某天:要,就建快照表,按天或按小时保存状态。不要,就维护一张当前状态表,用增量合并成最新全量,适合当前看板查询。

面试回答模板

面试可以这样答:我会从回答的问题来区分。全量表回答当前全貌,每次同步源表完整数据,适合小表、配置表、维表。增量表回答发生了什么变化,只存一段时间内的新增、修改、删除,适合订单、日志这类大表。快照表回答某个时间点是什么状态,比如每天的库存、账户余额、用户等级,适合历史状态还原。选型看三点。第一看数据量:表小变化少可以全量,表大变化少优先增量。第二看查询诉求:要看变化过程用增量,要看历史截面用快照。第三看成本:每日快照太大,可以用增量合并当前表,或者用拉链表保存历史。我不会拿增量表直接统计当前总量,它只有变化数据,容易漏掉没变化的老数据。判断标准很简单:问现在有多少,查全量或快照;问今天变了多少,查增量;问那天是什么样,查快照或拉链表。

高频追问

追问一:增量表能不能直接当快照表用?一般不能。增量表记的是变化事件,快照表记的是时间点状态。用户今天没有任何变化,增量表里就没有他,但快照表里必须有他当天的状态。除非把历史增量按主键合并,生成某天的完整状态,否则直接查增量表会漏数据。生产里常见做法是:ODS 保留增量明细,DWD 或 DIM 层再合并成每日快照或拉链表。追问二:删除数据在增量同步里怎么处理?删除是增量里最容易漏的坑。只靠 update_time 抽增量,物理删除的行可能根本抽不到,因为源表里已经没有这一行了。更稳的方式是用 CDC 捕获 delete 事件,增量表里记 op_type 等于 delete,或 is_deleted 等于 1。合并快照时遇到删除标记:要么不写进当天有效快照,要么写进去、但标记为无效,取决于下游要不要审计。追问三:什么时候用拉链表,什么时候用每日快照?表很小,或者业务经常按天直接取状态,用每日快照更简单,SQL 也更好写。表很大、每天变化比例很低,比如 1 亿用户每天只变 50 万,更适合拉链表。它只在状态变化时新增版本,比每日全量快照省很多存储。代价是查历史状态时,要写 start_date 小于等于 dt,并且 end_date 大于等于 dt,理解成本稍高。追问四:全量同步是不是一定比增量同步差?不是。全量的优点是简单、稳定、容易重跑。特别适合数据量小、变化少、源系统又没有可靠更新时间字段的表,比如地区表、枚举字典表、商品类目表。增量虽然省资源,但对主键、更新时间、删除捕获、幂等合并的要求都更高。所以别把全量说成低级方案:小表全量更划算,大表增量更划算,状态分析再补快照或拉链。

回到开头

回到开头那个 3200:问现在有多少,就别去查只存变化的增量表。全量看全貌,增量看变化,快照看那一天。

看完去做题