数据仓库ETL算法

1. ETL的定义： 是 数据抽取(Extract) 、 转换(Transform) 、 清洗(Cleansing) 、 装载(Load) 的过程。是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去 ；

2. 常用的ETL工具： 主要有三大主流工具，分别是Ascential公司的 Datastage 、Informatica公司的 Powercenter 、NCR Teradata公司的 ETL Automation .还有其他开源工具，如 PDI(Kettle) 等 。

3. ETL是DW系统的基础：

DW系统以事实发生数据为基础，自产数据较少。

一个企业往往包含多个业务系统，均可能成为DW数据源。

业务系统数据质量良莠不齐，必须学会去伪存真。

业务系统数据纷繁复杂，要整合进数据模型。

源数据之间关系也纷繁复杂，源数据在加工进DW系统时，有些必须遵照一定的先后次序关系；

4. 源数据的分类：

流水事件表 ：此类源表用于记录交易等动作的发生，在源系统中会新增、大部分不会修改和删除，少量表存在删除情况。如定期存款登记簿；

常规状态表 ：此类源表用于记录数据信息的状态。在源系统中会新增、修改，也存在删除的情况。如客户信息表；

代码参数表 ：此类源表用于记录源系统中使用到的数据代码和参数；

5. 数据文件的类型：

数据文件大多数以1天为固定的周期从源系统加载到数据仓库。数据文件包含增量，全量以及待删除的增量。

增量数据文件 ：数据文件的内容为数据表的增量信息，包含表内新增及修改的记录。

全量数据文件 ：数据文件的内容为数据表的全量信息，包含表内的所有数据。

带删除的增量 ：数据文件的内容为数据表的增量信息，包含表内新增、修改及删除的记录，通常删除的记录以字段DEL_IND='D'标识该记录。

6. ETL标准算法可划分为： 历史拉链算法 、 追加算法(事件表) 、 Upsert算法(主表) 及 全删全加算法(参数表) ；

7. ETL标准算法选择：

历史拉链 ：根据业务分析要求，对数据变化都要记录，需要基于日期的连续历史轨迹；

追加(事件表) ：根据业务分析要求，对数据变化都要记录，不需要基于日期的连续历史轨迹；

Upsert(主表) ：根据业务分析要求，对数据变化不需要都要记录，当前数据对历史数据有影响；

全删全加算法(参数表) ：根据业务分析要求，对数据变化不需要都要记录，当前数据对历史数据无影响；

8. 历史拉链法： 所谓拉链，就是记录历史，记录一个事务从开始，一直到当前状态的所有变化信息(参数新增开始结束日期) ；

9. 追加算法： 一般用于事件表，事件之间相对独立，不存在对历史信息进行更新 ；

10. Upsert算法： 时update和insert组合体，一般用于对历史信息变化不需要进行跟踪保留、只需其最新状态且数据量有一定规模的表，如客户资料表 ；

11. 全删全加算法： 一般用于数据量不大的参数表，把历史数据全部删除，然后重新全量加载 ；

12. 处理复杂度： 历史拉链，Upsert,Append,全删全加 ；加载性能： 全删全加，Append，Upsert，历史拉链 ；

13. 近源模型层主要算法： APPEND算法 ， 常规拉链算法 ， 全量带删除拉链算法 ；

14. 整合模型层算法： APPEND算法 ， MERGE算法 ， 常规拉链算法 ， 基于增量数据的删除拉链算法 ， 基于全量数据的删除拉链算法 ， 经济型常规拉链算法 ， 经济型基于增量数据的删除拉链算法 ， 经济型基于全量数据的删除拉链算法 ， PK_NOT_IN_APPEND算法 ， 源日期字段自拉链算法 ；

15. 技术缓冲到近源模型层的数据流算法----- APPEND算法 ：[0210]

此算法通常用于流水事件表，适合这类算法的源表在源系统中不会更新和删除，而只会发生一笔添加一笔，所以只需每天将交易日期为当日最新数据取过来直接附加到目标表即可，此类表在近源模型层的字段与技术缓冲层、源系统表基本上完全一致，不会额外增加物理化处理字段，使用时也与源系统表的查询方式相同；

16. 技术缓冲到近源模型层的数据流算法----- 常规拉链算法 ：[0211]

此算法通常用于无删除操作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不删除，所以需每天获取当日末最新数据(增量或全增量均可)，先找出真正的增量数据(新增和修改)，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操作(即END_DT关闭到当前业务日期)，然后再将最新的增量数据作为开链数据插入到目标表即可。

此类表再近源模型层比技术缓冲层、源系统的相应表额外增加两个物理化处理字段 START_DT(开始日期) 和 END_DT(结束日期) ，使用时需要先选定 视觉日期 ，通过 START_DT 和 END_DT 去卡 视觉日期 ，即 START_DT<='视觉日期'AND END_DT>'视觉日期' ；

17. 技术缓冲到近源模型层的数据流算法----- 全量带删除拉链算法 ：[0212]

此算法通常用于有删除操作的常规状态类表，并且要求全量的数据文件，用以对比出删除增量；适合这类算法的源表在源系统中会新增，修改，删除，每天将当日末最新全量数据取过来外，分别找出真正的增量数据(新增，修改)和删除增量数据，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操作(即END_DT关闭到当前业务日期)，然后再将最新增量数据中真正的增量及删除数据作为开链数据插入到目标表即可，注意删除记录的删除标志DEL_IND会设置为‘D’；

此类表在近源模型层比技术缓冲层，源系统的相应表额外增加三个物理化处理字段 START_DT(开始日期) ， ENT_DT(结束日期) ， DEL_IND(删除标准) 。使用方式分两类：一时一般查询使用，此时需要先选定视角日期，通过 START_DT 和 END_DT 去卡 视角日期 ，即 S TART_DT<='视角日期' AND END_DT>‘视角日期’ ，同时加上条件 DEL_IND <> 'D' ；另一种是下载或获取当日增量数据，此时就是需要 START_DT<='视角日期' AND END_DT>'视角日期' 一个条件即可，不需要加 DEL_IND <> 'D' 的条件。

18. 近源模型层到整合模型层的数据流算法---- APPEND算法 ：[0610]

此算法通常用于流水事件表，适合这类算法的源表在源系统中不会更新和删除，而只会发生一笔添加一笔，所以只需每天将交易日期为当日的最新数据取过来直接附加到目标表即可；

通常建一张名为 VT_NEW_编号 的临时表，用于将各组当日最新数据转换加到 VT_NEW_编号 后，再一次附加到最终目标表；

19. 近源模型层到整合模型层的数据流算法---- MERGE INTO算法 ：[0611]

此算法通常用于无删除操作的常规状态表，一般是无需保留历史而只保留当前最新状态的表，适合这类算法的源表在源系统中会新增，修改，但不删除，所以需获取当日末最新数据(增量或全量均可)，用于MERGE IN或UPSERT目标表；为了效率及识别真正增量的要求，通常先识别出真正的增量数据(新增及修改数据)，然后再用这些真正的增量数据向目标表进行MERGE INTO操作；

通常建两张临时表，一个名为 VT_NEW_编号 ，用于将各组当日最新数据转换加到 VT_NEW_编号 ；另一张名为 VT_INC_编号 ，将 VT_NEW_编号 与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入 VT_INC_编号 ，然后再用 VT_INC_编号 对最终目标表进行 MERGE INTO 或 UPSERT 。

20. 近源模型层到整合模型层的数据流算法---- 常规拉链算法 ：[0612]

此算法通常用于无删除操作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不删除，所以需每天获取当日末最新数据(增量或全增量均可)，先找出真正的增量数据(新增和修改)，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操作(即END_DT关闭到当前业务日期)，然后再将最新增量数据作为开链数据插入到目标表即可；

通常建两张临时表，一个名为 VT_NEW_编号 ，用于将各组当日最新数据转换加到 VT_NEW_编号 ；另一张名为 VT_INC_编号 ，将 VT_NEW_编号 与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入 VT_INC_编号 ，然后再将最终目标表的开链数据中的 PK 出现在 VT_INT_编号 中进行关链处理，然后将 VT_INC_编号 中的所有数据作为开链数据插入最终目标表即可。

21. 近源模型层到整合模型层的数据流算法-- 基于增量数据删除拉链算法 ：[0613]

此算法通常用于有删除操作的常规状态表，并且要求删除数据是以DEL_IND='D'删除增量的形式提供；适合这类算法的源表再源系统中会新增、修改、删除，除每天获取当日末最新数据(增量或全量均可)外，还要获取当日删除的数据，根据找出的真正增量数据(新增和修改)以及删除增量数据，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操作(即END_DT关闭到当前业务时间)，然后再将增量(不含删除数据)作为开链数据插入到目标表中即可；

通常建三张临时表，一个名为 VT_NEW_编号 ，用于将各组当日最新数据 (不含删除数据)转换加载到 VT_NEW_编号 ；第二张表名为 VT_INC_编号 ，用 VT_NEW_编号 与目标表中的昨日的数据进行对比后找出真正的增量数据放入 VT_INC_编号 ；第三张表名为 VT_DEL_编号 ，将删除增量数据转换加载到 VT_DEL_编号 ；最后再将最终目标表的开链数据中 PK 出现在 VT_INC_编号 或 VT_DEL_编号 中的进行关链处理，最后将 VT_INC_编号 中的所有数据作为开链数据插入最终目标表即可；

22. 近源模型层到整合模型层的数据流算法-- 基于全量数据删除拉链算法 ：[0614]

此算法通常用于有删除操作的常规状态表，并且要求提供全量数据，用以对比出删除增量；适合这类算法的源表在源系统中会新增、修改、每天将当日末的最新全量数据取过来外，分别找出真正的增量数据(新增、修改)和删除增量数据，用它们将目标表中属性发生修改的开链数据(有效记录)进行关链操作(即END_DT关闭到当前业务时间)，然后再将最新数据中真正的增量数据(不含删除数据)作为开链数据插入到目标表即可；

通常建两张临时表，一个名为 VT_NEW_编号 ，用于将各组当日最新全量数据转换到 VT_NEW_编号 ；另一张表名为 VT_INC_编号 ,将 VT_NEW_编号 与目标表中昨日的数据进行对比后找出真正的增量数据(新增、修改)和删除增量数据放入 VT_INC_编号 ，注意将其中的删除增量数据的 END_DT 置以最小日期(借用)；最后再将最终目标表的开链数据中 PK 出现再 VT_INC_编号 或 VT_DEL_编号 中的进行关链处理，然后将 VT_INC_编号 中所有的 END_DT 不等于最小日期数据(非删除数据)作为开链数据插入最终目标表即可；

23. 近源模型层到整合模型层的数据流算法-- 经济型常规拉链算法 ：[0615]

此算法基本等同与 常规拉算法 ，只是在最后一步只将属性非空即非0的记录才作为开链数据插入目标表；

24. 近源模型层到整合模型层的数据流算法-- 经济型基于增量数据删除拉链算法 ：[0616]

此算法基本等同于 基于增量数据删除拉链算法 ，只是在最后一步只将属性非空及非0的记录才作为开链数据插入目标表；

25. 近源模型层到整合模型层的数据流算法-- 经济型基于全量数据删除拉链算法 ：[0617]

此算法基本等同于 基于全量数据删除拉链算法 ，只是在最后一步只将属性非空及非0的记录才作为开链数据插入目标表；

26. 近源模型层到整合模型层的数据流算法-- PK_NOT_IN_APPEND算法 ：[0618]

此算法是对每一组只将PK在当前 VT_NEW_编号 表中未出现的数据再插入 VT_NEW_编号 表，最后再将 PK 未出现在目标表中的数据插入目标表，以保证只进那些PK未进过的数据；

27. 近源模型层到整合模型层的数据流算法-- 以源日期字段自拉链算法 ：[0619]

此算法是源表中有日期字段标识当前记录的生效日期，本算法通过对同主键记录按这个生效日期排序后，一次首尾相连行形成一条自然拉链的算法；