如何基于Hive每日运行的事实表构建可留存历史数据的事实表

核心逻辑是基于快照分区做增量追加存储，不改动原有日表的调度逻辑，在上层单独建历史表存储每日的全量切片，既不影响现有日表的使用，也能完整留存所有历史数据。

具体实现步骤 #

• 第一步：新建上层历史事实表
  字段和你现有仅存当日数据的日事实表完全一致，额外新增一个dt字段标记数据所属的快照日期，表本身按dt做分区，提升查询效率。以常用的大数据数仓引擎为例，建表参考代码如下：

  CREATE TABLE IF NOT EXISTS 上层历史事实表名 (
    -- 这里把原日事实表的所有字段按顺序、类型完整抄过来，不要漏字段、改类型
    字段1 类型 COMMENT '字段1注释',
    字段2 类型 COMMENT '字段2注释',
    ...
  ) PARTITIONED BY (
    dt STRING COMMENT '快照日期，格式统一为yyyy-MM-dd'
  )
  -- 存储格式按团队规范选择，常用parquet、orc格式即可
  STORED AS PARQUET;
  

• 第二步：调整每日调度依赖和写入逻辑
  原有日事实表的调度逻辑完全不动，避免影响现有下游任务。新增一个调度任务，强依赖当日日事实表的产出任务——也就是等当日日表数据跑完、校验通过之后，再触发这个历史表写入任务，把当日日表的全量数据写入历史表对应dt的分区，注意这里只写当日分区，绝对不能覆盖之前的历史分区。
  写入逻辑参考：

  -- 调度参数里的${bizdate}取当前运行的业务日期，比如跑2024-05-20的数，这个参数就自动赋值为2024-05-20
  INSERT OVERWRITE TABLE 上层历史事实表名 PARTITION(dt = '${bizdate}')
  SELECT * FROM 现有仅存当日数据的日事实表;
  

  这里用INSERT OVERWRITE写单个当日分区是安全的，只会替换当前日期分区的数据，不会碰之前的历史分区，后续如果要重跑某天的历史数据，单独重跑对应日期的任务就行，不会污染其他时间的数据。
• 第三步：加基础校验规则
  每次写完当日分区之后，自动跑两个校验：一是比对当日分区的数据总行数和原日表的行数是否一致，二是比对核心指标（比如总金额、总用户数这类核心汇总值）和原日表是否一致，出现偏差立刻告警，避免漏数、错数进历史表。

使用说明 #

• 要查当日最新数据：直接过滤dt为最新业务日期即可，和你原来查日表的逻辑完全一致，性能和原日表几乎没有差别。
• 要查任意历史日期的全量数据：过滤dt为对应历史日期即可，不需要做多表关联、拉链回溯，查询逻辑简单，出错概率低。

补充说明：如果单日数据量特别大，长期存全量快照存储成本过高，可以根据业务需求优化：比如超过3个月的历史数据只保留核心字段、或者把不变更的冷数据做压缩合并，但是从实现成本、维护难度、查询效率三个维度综合看，每日分区快照是最稳妥的方案，90%以上的业务场景都适用。

内容的提问来源于stack exchange，提问作者Santosh Kumar