ETL 数据加载机制概述

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

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

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

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

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

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

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

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

ETL 数据增量加载机制：

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

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

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

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

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

系统日志分析方式

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

系统日志分析方式优缺点

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

缺点：日志表维护需要由OLTP系统完成，需要对OLTP系统业务操作程序作修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响。

触发器方式

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

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

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

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

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

触发器方式优缺点

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

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

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

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

时间戳方式

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

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

时间戳方式优缺点

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

缺点：

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

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

b. 如果系统自动更新或手工更新时间戳字段时可能会出现数据延迟现象产生。

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 操作。

全表比对方式优缺点

优点是适用于：

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

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

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

总结与分析

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

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

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

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

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

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

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

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

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

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

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