ETL 数据加载机制概述

　

数据加载

ETL 是数据抽取（extract）、转换（Transform）、加载（Load）的简写，它的功能是从数据源抽取出所需的数据，经过数据清洗和转换，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去，是构建数据仓库最重要的一步。

在数据加载到数据库的过程中，分为全量加载（更新）和增量加载（更新）。

• 全量加载：全表删除后再进行数据加载的方式。
• 增量加载：目标表仅更新源表变化的数据。

全量加载从技术角度上说，比增量加载要简单很多。一般只要在数据加载之前，清空目标表，再全量导入源表数据即可。但是由于数据量，系统资源和数据的实时性的要求，很多情况下我们都需要使用增量加载机制。

增量加载难度在于必须设计正确有效的方法从数据源中抽取变化的数据以及虽然没有变化，但受到变化数据影响的源数据，同时将这些变化的和未变化但受影响的数据在完成相应的逻辑转换后更新到数据仓库中。优秀的增量抽取机制不但要求 ETL 能够将业务系统中的变化数据按一定的频率准确地捕获到，同时不能对业务系统造成太大的压力，影响现有业务，而且要满足数据转换过程中的逻辑要求和加载后目标表的数据正确性，同时数据加载的性能和作业失败后的可恢复重启的易维护性也是非常重要的考量方面。

增量抽取机制比较适用于以下特点的数据表：

• 数据量巨大的目标表。
• 源表变化数据比较规律，例如按时间序列增长或减少。
• 源表变化数据相对数据总量较小。
• 目标表需要记录过期信息或者冗余信息
• 业务系统能直接提供增量（delta）数据

如果每次抽取都有超过 1/4 的业务源数据需要更新，就应该考虑更改 ETL 的加载方法，由增量抽取改为全量抽取，另外全量抽取对于数据量较小，更新频率较低的系统也比较适用。

ETL 数据增量加载机制：

ETL 增量加载在方式上主要包括：

• 系统日志分析方式
• 触发器方式
• 时间戳方式
• 全表比对方式
• 源系统增量（delta）数据直接或者转换后加载

下面我们根据几个常见的数据抽取加载场景，以 DB2 数据库和 Datastage 为例，分别介绍上述几种增量更新的设计与实现。

源表与目标表一对一增量更新

这类更新主要常见于生产库与备份库之间，或者是面对不同数据集市的数据分发业务。即源表和目标表的数据对应关系十分简单，数据完全相同或者是源表仅作部分数据过滤。

对于这种类型的增量更新，可以采用系统日志分析方式。

系统日志分析方式

该方式通过分析数据库自身的日志来判断变化的数据。关系型数据库系统都会将所有的 DML 操作存储在日志文件中，以实现数据库的备份和还原功能。ETL 增量抽取进程通过对数据库的日志进行分析，提取对相关源表在特定时间后发生的 DML 操作信息，就可以得知自上次抽取时刻以来该表的数据变化情况，从而指导增量抽取动作。

在 DB2 中，这种方式的具体实现形式主要为数据库 Replication 功能。

Replication 功能可以快速实时的同步两个数据表的数据，不管是 insert，update 或者是 delete 操作，都可以及时捕获并更新。

图 1. DB2 Replication 原理图

利用 Replication 进行表复制与同步时，需要前期进行一些简单的配置，需要开启数据库的 Capture 程序。并且如果后期源表和目标表有列变动时，需要进行一些简单的配置以保证 Replication 能正常进行。

Replication 的主要步骤：

• create capture control table
• register source table
• create APPly control table
• create subscription
• start capture program
• start apply program

在所有这些之前，应该先 catalog database and log retain 源表和目标表.

整个 replication 实际上就是由 capture 和 apply 程序完成的。capture 负责捕获源表的改变，apply 负责将改变应用到目标表。subscription 就是将源表目标表对应起来的一个映射。

系统日志分析方式优缺点

优点：实现方式简单。隔离性好，如果发生加载失败，不会影响源表及其事务的级联失败。 缺点：

• 由于需要存储附属的 Capture 信息，大概需要 3 倍的包含在复制表中的日志空间，列越多所需日志空间越大。所以如果源表有大批量数据变化时，容易造成 CD 空间满而引起 Capture 程序终止，并且失败后无法直接重启和恢复数据，只能全表复制源数据。
• 使用 Replication 的源表不可以在工作时使用 no logging 模式，另外源表在被重新全表加载时，目标表也需要进行整表重新加载。

这类方法适用于一对一小数据量的增量更新。

另外面对源表与目标表一对一增量更新时，也可以采用触发器方式。

触发器方式

触发器增量抽取主要有 2 种方式：

• 直接进行数据加载
• 利用增量日志表进行增量加载

直接进行数据加载方式是创建一个与源表结构类似的临时表，然后创建一个三种类型的触发器，分别对应 insert , update , delete 操作。每当源表有数据变动的时候，利用触发器将变化的数据填入此临时表表中。最后通过维护这个临时表，在进行 ETL 过程的时候，将目标表中相应的数据进行修改。ETL 过程结束后，清空此临时表。

利用增量日志表进行增量加载则是不直接抽取源表数据，仅仅是将操作内容写入一张增量日志表里（同时增量日志表中抽取过的数据要及时被标记或删除）。增量日志表一般不存储增量数据的所有字段信息，而只是存储源表名称、更新的关键字值和更新操作类型 (insert、update 或 delete)，ETL 增量抽取进程首先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对目标表进行相应的处理。

由于增量日志表中并没有完全记录增量数据本身，只是记录了增量数据的来源。进行增量 ETL 时，只需要根据增量日志表中的记录情况，反查源表得到真正的增量数据。

采用增量日志表进行触发器增量抽取的具体实现思路为：根据抽取要求，在要被抽取的源表上建立插入、修改、删除 3 个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个增量日志表，其数据捕获的过程如下：

表 T 的所有 DML 操作记录在增量日志表 DML_LOG 中（注意增量日志表中并没有完全记录增量数据本身，只是记录了增量数据的来源）。

清单 1. 使用触发器记录增量数据变化代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

CREATE TABLE DML_LOG( //新建 DML_LOG 表记录增量数据变化  ID NUMBER PRIMARY KEY, //自增主键  TABLE_NAME VARCHAR2(200), //源表名称  RECORD_ID NUMBER, //源表增量记录的主键值 DML_TYPE CHAR(1), //增量类型，I 表示新增：U 表示更新；D 表示删除  EXECUTE_DATE DATE //发生时间  )；  (2) 为 DML_LOG 创建一个序列 SEQ_DML_LOG 上，以便触发器写增量日志表时生成 ID 值。  (3) 针对要监听的每一张表，创建一个触发器，例如对表 TEST 创建触发器如下：  CREATE OR REPLACE TRIGGER T BEFORE INSERT OR UPDATE  OR DELETE ON T FOR EACH ROW DECLARE L_DML_TYPE VARCHAR2(1)；  BEGIN  IF INSERTING THEN L_DML_TYPE：=’I’；// 源表有新增数据  ELSIF UPDATING THEN L_DML_TYPE：=’U’； // 源表有更新数据  ELSIF DELETING THEN L_DML_TYPE：=’D’； // 源表有删除数据  ENDIF； IF DELETING THEN // 源表有删除数据  INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD_ID，EXECUTE_DATE，DML_TYPE)  VALUES(SEQ_DML_LOG.nextval,’TEST’ ,OLD.ID,SYSDATE,L_DML_TYPE)；  ELSE  INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD_ID，EXECUTE_DATE，DML_TYPE)  VALUES(SEQ_DML_LOG.NEXTVAL,’TEST’ ,NEW.ID,SYSDATE,L_DML_TYPE)；  ENDIF； END；

触发器方式优缺点

优点：数据抽取的性能较高。

缺点：要求业务表建立触发器，对业务系统有影响，需要对用户数据库进行修改，不能对多表和视图进行操作，如果目标表发生错误会造成级联事务失败，这在生产系统无法忍受，另外一个缺点是如果触发器运行过程中产生问题，有时需要重新加载整个表来恢复加载作业的运行。 这类方法适用于一对一且业务逻辑不复杂的表的增量更新。

源表与目标表一对一增量更新，但需要进行一些数据转换操作

有些源表与目标表之间不是简单的数据对应关系，可能两者之间有不同的数据结构，需要进行一些数据转换，例如汇总，行转列等操作后才能进行更新。上文提到的 Replication 和触发器方式就不太适用，这时采用时间戳的方法比较合适。

时间戳方式

实现原理是指增量抽取时，抽取进程通过比较系统时间或者源表上次抽取时的最大时间戳与抽取源表的时间戳字段的值来决定抽取哪些数据。这种方式需要在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。

采用时间戳进行增量更新时需要源表有相应的时间戳字段，所以对于没有时间戳的源表需要进行相应业务需要改造，增加必要的时间戳字段。

时间戳字段的添加可以通过方式：

• 系统自动添加系统时间。有的数据库 (例如 DB2) 的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。

其语法为：

1	Add column COLUMN_NAME TIMESTAMP NOT NULL generated ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

在这种情况下，进行 ETL 实施时就只需要在源表加上时间戳字段就可以了。

• 对于不支持时间戳自动更新的数据库，这就要求业务系统在更新业务数据时，通过存储过程或者 ETL 工具的方式手工更新时间戳字段。

利用时间戳在进行 ETL 增量更新的时候，可以通过 Where 语句对源表的时间戳字段进行筛选，从而选取出增量数据对目标表进行更新。这时采用的策略多分为：

a. 需要一个日志表对 ETL 的更新操作进行记录，将最后一次成功更新的时间戳记录下来，以方便进行时间筛选。每次进行 ETL 增量更新时，读取日志表里的上次更新时间戳记录，作为本次增量更新的时间窗口起始时间。

清单 2. 利用日志表进行时间戳选择的代码示例

1 2 3 4 5 6 7 8 9 10 11 12

eg1. INSERT INTO table_target (A, B, C) SELECT A1, B1, C1 FROM table_source WHERE modify_date>=’last_load_date’//即为在日志表中记录的上次更新时间 and modify_date< ’current_load_date’ eg2. INSERT INTO table_target (A, B, C) SELECT s1.A1, s1.B1+s2.B2, s1.C1-s2.C2 FROM table_source s1, table_source s2 WHERE s1.modify_date>=’last_load_date’ //即为在日志表中记录的上次更新时间 and s1. modify_date< ’current_load_date’ and s1.pk=s2.pk

每次 ETL 更新结束后，务必要更新日志表里相应的时间戳字段，以保证下次增量更新数据的准确性和及时性。

采用此方式，需要维护一张日志表。优点是当增量程序运行失败或者长时间 ETL 程序未正常运行，需要进行数据补录时，不需要人工手工输入时间参数。ETL 可以根据日志表自动捕获上次数据加载成功的时间点，进行数据补录。

b. 直接选取源表中时间戳字段进行时间窗口筛选，利用系统时间进行计算。

此类方法不需要记录上次加载时间，实现简单，可以不需要日志表进行数据加载记录。但是如果出现加载失败，或者时间窗口失效时，补录数据较为麻烦。需要查询上次运行成功时间，手工输入相应的时间参数，进行数据重新加载。 例如实现代码如下：

清单 3. 利用系统时间进行时间戳选择的代码示例

1 2 3 4 5

Eg. INSERT INTO table_target (A, B, C) SELECT A1, B1, C1 FROM table_source WHERE modify_date>=’current timestamp-n day’// current timestamp 则为系统时间计算 and modify_date< current timestamp

时间戳方式优缺点

优点：实现逻辑简单，可以大批量更新数据。不仅可以对一张源表进行数据捕获，也可以对多张源表的增量数据进行捕获。

缺点：

a. 使用时间戳方式可以正常捕获源表的插入和更新操作，但对于删除操作则无能为力，需要结合其它机制才能完成。这时可以设计一张和源表相同的数据表记录源表中删除的数据，同时记录删除时的时间戳，在对目标表更新时同时读取这张表的记录，进行删除操作。

或者在目标表通过打标记的方式（update active_flag=1）进行逻辑删除。

b. 如果系统自动更新或手工更新时间戳字段时可能会出现数据延迟现象产生。比如在上述的 eg1 中，数据修改时间是：23:59:56 2000-1-1，而数据戳 modify_date 延迟到 00:00:07 2000-1-2，且 last_load_date 显示为: 00:00:00 2000-1-2, 则这条数据就不会被捕获。此时可以采用重叠时间窗口的方式避免这类情况发生，修改代码如下：

清单 4. 重叠时间窗口代码示例

1 2 3 4 5 6 7

Eg. DELETE FROM table_target WHERE add_date>=’last_load_date’-1 day; INSERT INTO table_target (A, B, C) SELECT A1, B1, C1 FROM table_source WHERE modify_date>=’last_load_date’-1 day //-1 即为重叠时间窗口的关键参数 AND modify_date< current timestamp;

c. 如果采用系统自动更新时间戳的方式，需要特别注意在 Load 整个表的操作时，要保持此字段数值不变，否则时间戳将会自动加载为 Load 的最新时间，影响整个表的增量更新逻辑。

d. 应用起来有部分局限性。即源表都需要有时间戳字段。如果部分源表（或参考表）无时间戳字段，且源表有部分字段更新时（常见于维度表的定义更新，如地区维度，产品维度等），则面临历史数据的更新问题。此时采用时间戳方法只能更新新增数据的维度定义，而无法更新历史数据。这时一般需要采用 sql 语句或者下文介绍的全表对比方式进行历史数据更新。或者调整时间戳范围，做全表数据的刷新。这种情况需要对目标表的实时性要求不高，可以在系统空闲时进行处理。

e. 由于时间戳增量更新经常应用于业务逻辑复杂的 ETL 过程，在面对源表里有多条记录汇总至目标表一条记录情况时，例如源表里记录 R1，R2，R3（主键均相同），根据主键汇总至目标表生成记录 T1，则需要注意如果时间窗口内有源表一条数据发生了数据变动（R2），则需要利用 R2 的主键将源表中的 R1，R3 也同时捕获出来（虽然 R1，R3 在时间窗口内没有数据变动），重新进行汇总，更新目标表中的 T1 记录，以避免数据丢失或者数据逻辑不正确问题。

源表与目标表多对一增量更新

日常的 ETL 更新中，还会遇到目标表的数据来源来自于多张源表，通过关键字段的拼接进行更新操作。如果多张源表都有时间戳字段，可以利用时间戳进行增量更新，另外还可以采用全表比对的方式进行增量更新。

全表比对方式

全表比对即在增量抽取时，ETL 进程逐条比较源表和目标表的记录，将新增和修改的记录读取出来。优化之后的全部比对方式是采用 md5 校验码，需要事先为要抽取的表建立一个结构类似的临时表，该临时表记录源表的主键值以及根据源表所有字段的数据计算出来的 MD5 校验码，每次进行数据抽取时，对源表和 MD5 临时表进行 MD5 校验码的比对，如有不同，进行 UPDATE 操作：如目标表没有存在该主键值，表示该记录还没有，则进行 INSERT 操作。然后，还需要对在源表中已不存在而目标表仍保留的主键值，执行 DELETE 操作。

一般全表比对更新可以采用一些常用的 ETL 工具协助进行，以 DataStage 为例，可以采用 Compare, Difference 和 Change Capture Stage 这几个组件进行数据的比对。以 Change Capture 组件为例，Capture Change Stage 输出的是以 after 输入流为基础，外加组件生成的 change code 字段可以区分出，before 中需要新增，更新或删除的数据。

图 2. 全表比对的 DataStage Job 示例-比对表生成

点击查看大图

多个源表进行拼接，组合出和目标表相同数据结构的数据流。如图 3 所示。

图 3. 全表比对的 DataStage Job 示例-比对及后续处理图

点击查看大图

利用 Capture Change Stage 进行全表比对，对临时表（数据流）进行比对，如有不同，进行 update 操作（change_code=3）：如目标表没有存在该主键值，表示该记录还没有，则进行 insert 操作 (change_code=1)。然后，还需要对在源表中已不存在而目标表仍保留的主键值，执行 delete 操作 (change_code=2)。如果目标表和临时表的记录相同，一般选择忽略。

全表比对方式优缺点

优点是适用于：

a. 涉及多张源表的抽取与转换，业务逻辑复杂的增量更新。

b. 源表无时间戳字段，无法采用时间戳方式进行增量更新。

缺点是采用全表比对，对于大数据量数据表，效率不高。

总结与分析

表 1. 各类增量抽取方式比较表

	系统日志分析方式	触发器方式	时间戳方式	全表比对方式
对目标表新增数据	可	可	可	可
对目标表更新数据	可	可	可	可
对目标表删除数据	可	可	无法捕获	可
目标表数据量	小	小	大	适中
目标表类型	所有	除视图以外均可	所有	所有
源表数量	1	1	多	多
源表处理逻辑	简单	简单	复杂	复杂
系统资源占用	较多	较少	较少	较多
数据抽取性能	优	优	较优	差
源表是否需要有时间戳字段	不需要	不需要	需要	不需要
容灾能力	较差	普通	普通	优

在选择合适的增量加载机制时，需要注意的方面包括：

1. 增量抽取的容灾能力。主要是指增量加载更新方式如果运行失败或者数据库宕机重启后，是否可以重新加载增量数据或者是否需要手工补录数据的能力，一定程度上也影响着增量加载的可维护性。

其中系统日志分析方式 Replication 在失败或者未运行状况下，将无法捕获源表的增量数据，无法恢复历史增量数据的加载，所以容灾能力最差。触发器方式和时间戳方式，如果有相应的日志表存在，或者增量日志未被清除，则可以在再次启动后重新加载历史增量数据，容灾性一般。而全表比对方式，根据它的实现原理则在重新启动时不受任何影响，可以全面捕获增量数据，容灾性最好。

2. 增量抽取的性能因素。表现在两个方面，一是抽取进程本身的性能，二是对源系统性能的负面影响。触发器方式、日志表方式以及系统日志分析方式由于不需要在抽取过程中执行比对步骤，所以增量抽取的性能较佳。全表比对方式需要经过复杂的比对过程才能识别出更改的记录，抽取性能最差。

如果增量更新的业务逻辑比较复杂，对机器性能要求较高，在进行更新时可能会有影响现有业务逻辑表的性能，则可以评估其增量更新的重要性，如果对现有业务影响不大，且源表比较稳定，客户可以容忍暂时性的一定程度的数据不一致。则可以考虑定时进行增量更新。例如更新程序每日凌晨运行一次，甚至每周空闲时间运行一次。

另外业界普遍常用的方式是源系统在它的闲时抽取增量 delta 数据文件，数据仓库系统得到 delta 文件后再加载到仓库系统。在数据仓库架构设计中，生产系统层和 ODS 层的直接数据交互即为此类型。

3. 触发器方式需要在源表上建立触发器，这种在某些应用场合中遭到拒绝。还有一些需要建立临时表的方式，例如全表比对和日志表方式。可能因为开放给 ETL 进程的数据库权限的限制而无法实施。同样的情况也可能发生在基于系统日志分析的方式上，因为大多数的数据库产品只允许特定组的用户甚至只有 DBA 才能执行日志分析。例如 DB2 的 Replication 功能只能由 DBA 维护与修改，普通用户无法操作。

4. 为了保证增量更新的数据准确性，建议建立一系列的核查脚本，以保证源表和数据表的数据一致性问题。核查脚本可以根据轻重缓急设定其运行频率。

5. 数据抽取需要面对的源系统，并不一定都是关系型数据库系统。某个 ETL 过程需要从若干年前的遗留系统中抽取 excel 或者 CSV 文本数据的情形是经常发生的。这时，所有基于关系型数据库产品的增量机制都无法工作，时间戳方式和全表比对方式可能有一定的利用价值，在最坏的情况下，只有放弃增量抽取的思路，转而采用全表删除插入方式（或者要求源系统提供增量 delta 数据文件）。

相关阅读

怎么让网站的数据更有利用价值

　网站数据分析是通过观察、调查、实验、测量等结果，通过数据的显示形式把网站各方面情况反映出来，使运营者更加了解网站的运营情况

数据库学习的一些基础知识及常用命令

数据库 “数据库”是以一定方式储存在一起、能够多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。 【基本

数据结构之二叉排序树（C语言实现）

一、基本概念 1.二叉排序树 二叉排序树（Binary sort tree，BST），又称为二叉查找树，或者是一棵空树；或者是具有下列性质的二叉树： (1)若

数据仓库

操作型数据（如：某超市昨天卖了一箱啤酒，收银员实际收到100，找零20元）。 特点：细节化，分散化。 决策型数据（如：该超市上个月一共卖了多少瓶

数据结构与算法（一）---重点复习知识

吐槽 国庆假期第二天，去实验室开门，给猫猫铲丑丑，然后给她换猫粮，换水，喂这货吃的emmmmmm，然后今天就把之前在极客时间上买的数据结构与