转载声明：文章来源https://blog.csdn.net/Yongkang_Chen/article/details/121968040? 

大数据平台数据来源于企业内部、外部各个源系统，包括企业内部的前端人工输入、智能设备采集、app端埋点日志、外部数据采购等等等等，可以说是千奇百怪五花八门，从以前的CDS到数据仓库，再到数据湖、数据中台，整个企业内部数据流向就如同一条条毛细血管中的血液，由全身各处流向心脏，在做有氧交换，处理、加工、汇总、分析再分发到需要他的地方。这个比喻可能有失偏颇，但是大致就是这么个意思吧。

在整个ETL的过程中就会面临一个问题，那就是历史数据存储问题，怎么将历史数据存好、用好、好用是一个值得思考的问题，今天就简单的总结一下大数据平台对于历史数据储存的ETL算法，适用于数据仓、数据中台的湖仓一体架构（本文总结基于teradata的SDM-ETL POLICY算法）。

F1(DELETE/INSERT)：

1 删除历史表中所有数据；

2 将最新增量加载日表数据全部插入历史表中。

适用场景：

一般多用于全量数据，数据量较小，但对于历史情况不许过多关注，只关注最新有效数据情况，比如全国省份信息，企业内部全国机构信息。

F2(UPSERT)：

1 根据业务逻辑确定对主键比字段（Upsert by）；

2 生成当前临时表，将当日加载日表数据插入当前临时表；

3 根据当前临时表中数据要素，update历史表（T_PFS_CUS_ACC）中相对应的数据，where条件根据（upsert by）；

4 插入数据，将当前临时表中的数据在历史表中不存在即新增数据（以upsert by为条件）的数据插入到历史表。

逻辑图如下：

适用场景：

一般多用于全量数据，数据量相大，但对于历史情况不许过多关注，只关注最新有效数据情况，比如企业内部所所有客户信息情况。

F3(History Chain)：

1 恢复时间戳，即避免同一天多次运行造成数据重复

1）将历史表中开始日期等于要要加载的日期的数据删除 DELETE FROM histable WHERE 开始日期=当前输入日期(start_dt=$tx_date);

2）将结束日期等于加载日期的数据，将结束日期更改为最大日期 UPDATE histable SET 结束日期 Sttm_Dt= $MAXDATE WHERE Sttm_Dt= ${TX_DATE}‘; 根据业务逻辑确定对主键比字段（Upsert by）。

2 根据业务逻辑确定对主键比字段（Upsert by）；

3 生成当前临时表，将当日加载日表数据插入当前临时表；

4 比较今日数据（即“当前数据临时表”中的数据）和当前有效数据（当前有效数据指历史表中结束日期等于最大日期），比较条件：除去开始日期和结束日期所有字段，当前数据不在历史表中的数据插入“增改临时表”（INS表）中；

5 根据Upsert by的条件更新历史表中数据，对更新数据进行封口，置结束日期为$tx_date;

6 将临时表中数据置开始日期(rep_dt)为$tx_date和结束日期(sttm_dt)为$MAXDATE,其他数据不变，“增改临时表”数据插入历史表中。

逻辑图如下：

适用场景：

一般用于增量数据，数据量较大的，变动频率不高的，反应历史情况的数据，比如协议的一些情况信息，如人行的征信信息，一些企业的风控系统对客户的评级数据等

F4(Economy History Chain)：

1 F4算法同F3算法基本相同，是F3的一种特殊情况，即对新增临时表数据中指定某些特殊字段不为空，为空直接不进历史表，其他逻辑算法与F3完全一致。

适用场景：

一般用于增量数据，数据量较大的，变动频率不高的，反应历史情况的数据，存在特殊情况，按需选取。

F5(FullData History Chain)：

1 F5算法与F3的数据生成相同，区别在于多生成2个临时表，分别是PRE(以前)UPD(更改)；

2 PRE中数据是以sttm_dt(结算日期)＝$maxdate从历史表中取出的数据；

3 INS的生成数据是将“当前临时表”（cur_i)中数据在pre表中数据比较，不存在的数据插入到INS中；

4 UPD的生成数据是将pre表中数据在“当前临时表”(cur_i)中的数据比较，不存在数据插入到UPD中；

5 删除历史表中已UPD为条件的数据后将UPD数据插入历史表中；

6 将INS数据插入目标表中。

逻辑图如下：

适用场景：

一般用于增量数据，数据量较大的，变动频率不高的，反应历史情况的数据，存在特殊情况，按需选取。

F6(Two-PK History Chain)：

1 F6算法也是F4算法的另一种特殊情况（个人理解），即判断原先拉链具有可存在性，2个或者多个拉链都是合理存在

2 如果判断为多条主键存在为合理，建立“当前数据临时表”，将新增加载数据插入到“当前临时表”；

3 建立“增改临时表”，“当前临时表”与历史表比较，不存在的放入比较临时表中，比较的条件是“当前临时表”中的数据除去开始日期和结束日期的所有列与历史表中结束日期为$maxdate的所有数据相对应的列比较；

4 将增改临时表中的数据插入到目标表中，开始日期为输入日期，结束日期为$maxdate，历史表原拉链不变更；

5 如果判断主键唯一性时，更新历史表中数据，对更新数据封口，置结束日期为输入日期。条件为结束日期为$maxdate并且其他的列（除开始日期）的数据不在“当前临时表”中。

适用场景：

一般用于增量数据，数据量较大的，变动频率不高的，反应历史情况的数据，而且历史与现在的状态同时生效，感觉还是需要原子性拆分把，就是不同属性唯一依赖主键，实际中几乎没用到，平时建模都是三范建模，除非降范才能出现这种情况（个人理解，不喜勿喷哦）。

F7(Self History Chain)：

1 F7算法是满足当传来数据为历史数据时，重新建立新的历史时间链；

2 建立“当前数据临时表”，将新增加载数据插入到“当前临时表”；

3 将历史表中数据导入“当前临时表”中，条件为 历史表中的user by 在 “当前临时表”中不存在的数据；

4 删除历史表中数据，将“当前临时表”用LEFT JOIN的方式重新生成新的历史拉链插入历史表中。

适用场景：

一般用于增量数据，数据量较大的，变动频率不高的，反应历史情况的数据，实际工作中几乎没碰到，感觉像是用于补历史拉链的一种算法，怎么都感觉像是修数算法

I(append)：

1 Append 算法只是对历史表仅仅插入新的数据；

2 建立“当前数据临时表”，将新增加载数据插入到“当前临时表”；

3 删除历史表中开始日期为输入日期；

4 将当前临时表中数据插入到历史表中

适用场景：

一般用于增量数据，数据量较大的，变动频率较高的，反应历史情况的数据，这个没啥好说的啦，就是快照、切片算法啦，空间消耗极大，但是也没别的办法啦。

一口气说了这么多，总而言之，言而总之，算法没有最好之说，只有最合适的，没必要苛求去追求那种算法，有的人就是觉得越复杂，越高级，个人觉得合适就好，一个合适的算法能够帮助你优化数据存储结构，并降低数据综合存储成本，提升平台执行效率。

本文都是总结的Teradata的SDM-ETL POLICY算法，三十多年来，teradata积累了大量的资产与方法支持这套算法的最佳实践，本人有幸加入但目前为止也只学到了点皮毛，但是受益匪浅，有感兴趣的童鞋欢迎一起交流、学习，文中有什么不对的地方也欢迎指正、批评。

版权声明：本文为CSDN博主「Yongkang_Chen」的原创文章，遵循CC 4.0 BY-SA版权协议