ETL 概念解析

实现方法

常见的 ETL 实现方法

借助 ETL 工具（如 Oracle 的 OWB、Informatic、Kettle 等）实现
SQL 方式实现
ETL 工具和 SQL 相结合。

借助工具可以快速的建立起 ETL 工程，屏蔽了复杂的编码任务，提高了速度，降低了难度，但是缺少灵活性。SQL 的方法优点是灵活，提高 ETL 运行效率，但是编码复杂，对技术要求比较高。往往需要结合使用。

抽取

数据抽取主要是针对各个业务系统及不同数据源的分散数据，充分理解数据定义后，规划需要的数据源及数据定义，制定可操作的数据源抽取（增量抽取）方案。

数据抽取过程中需要关注的要点：

确认每个数据源的系统和数据结构。
定义抽取方式，是人工抽取还是基于工具抽取。
对于每个数据源，确定数据抽取的频度，是每天、每星期、还是每个季度等。
决定抽取任务中各项任务的先后顺序，某项工作是否必须等到前面的工作成功完成后，才可以开始。
决定如何处理无法抽取的输入记录。

增量抽取

1、触发器方式

触发器方式是普遍采取的一种增量抽取机制。该方式是根据抽取要求，在要被抽取的源表上建立插入、修改、删除 3 个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个增量日志表，ETL 的增量抽取则是从增量日志表中而不是直接在源表中抽取数据，同时增量日志表中抽取过的数据要及时被标记或删除。为了简单起见，增量日志表一般不存储增量数据的所有字段信息，而只是存储源表名称、更新的关键字值和更新操作类型（KNSEN、UPDATE 或 DELETE），ETL 增量抽取进程首先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对目标表进行相应的处理。

例如，对于源表为 ORACLE 类型的数据库，采用触发器方式进行增量数据捕获的过程如下：

SQL 代码

-- 1. 创建增量日志表 DML_LOG：
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；

这样，对表 T 的所有 DML 操作就记录在增量日志表 DML_LOG 中，注意增量日志表中并没有完全记录增量数据本身，只是记录了增量数据的来源。进行增量 ETL 时，只需要根据增量日志表中的记录情况，反查源表得到真正的增量数据。

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

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

2、时间戳方式

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

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

有的数据库（例如 SQL SERVER）的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。在这种情下，进行 ETL 实施时就只需要在源表加上时间戳字段就可以了。

对于不支持时间戳自动更新的数据库，这就要求业务系统在更新业务数据时，通过编程的方式手工更新时间戳字段。使用时间戳方式可以正常捕获源表的插入和更新操作，但对于删除操作则无能为力，需要结合其它机制才能完成。

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

优点：

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

缺点：

使用时间戳方式可以正常捕获源表的插入和更新操作，但对于删除操作则无能为力，需要结合其它机制才能完成。这时可以设计一张和源表相同的数据表记录源表中删除的数据，同时记录删除时的时间戳，在对目标表更新时同时读取这张表的记录，进行删除操作。或者在目标表通过打标记的方式（update active_flag=1）进行逻辑删除。
应用起来有部分局限性。即源表都需要有时间戳字段。如果部分源表（或参考表）无时间戳字段，且源表有部分字段更新时（常见于维度表的定义更新），则面临历史数据的更新问题。这时一般需要采用 SQL 语句或者下文介绍的全表对比方式进行历史数据更新。或者调整时间戳范围，做全表数据的刷新。这种情况需要对目标表的实时性要求不高，可以在系统空闲时进行处理。

3、全表删除插入方式

全表删除插入方式是指每次抽取前先删除目标表数据，抽取时全新加载数据。该方式实际上将增量抽取等同于全量抽取。对于数据量不大，全量抽取的时间代价小于执行增量抽取的算法和条件代价时，可以采用该方式。

4、全表比对方式

全表比对即在增量抽取时，ETL 进程逐条比较源表和目标表的记录，将新增和修改的记录读取出来。优化之后的全部比对方式是采用 MD5 校验码，需要事先为要抽取的表建立一个结构类似的临时表，该临时表记录源表的主键值以及根据源表所有字段的数据计算出来的 MD5 校验码，每次进行数据抽取时，对源表和 MD5 临时表进行 MD5 校验码的比对，

如有不同，进行 UPDATE 操作
如目标表没有存在该主键值，表示该记录还没有，则进行 INSERT 操作。
对在源表中已不存在而目标表仍保留的主键值，执行 DELETE 操作。

一般全表比对更新可以采用一些常用的 ETL 工具协助进行。

优点：

适用于涉及多张源表的抽取与转换，业务逻辑复杂的增量更新。
适用于源表无时间戳字段，无法采用时间戳方式进行增量更新。

缺点：

对于大数据量数据表，效率不高。

5、日志表方式

对于建立了业务系统的生产数据库，可以在数据库中创建业务日志表，当特定需要监控的业务数据发生变化时，由相应的业务系统程序模块来更新维护日志表内容。增量抽取时，

通过读日志表数据决定加载哪些数据及如何加载。日志表的维护需要由业务系统程序用代码来完成。

6、系统日志分析方式

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

7、特定数据库方式（ORACLE）

以下介绍常见的针对特有数据库系统的增景抽取方式。

ORACLE 改变数据捕获（CHANGEDDATACAPTURE，CDC）方式：

ORACLECDC 特性是在 ORAELE9I 数据库中引入的。CDC 能够帮助识别从上次抽取之后发生变化的数据。利用 CDC，在对源表进行 INSERT、UPCLATE 或 DELETE 等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给 ETL 抽取进程，作为增量抽取的依据。CDC 方式对源表数据变化情况的捕获有两种方式：同步 CDC 和异步 CDC。同步 CDC 使用源数据库触发器来捕获变更的数据。这种方式是实时的，没有任何延迟。当 DML 操作提交后，变更表中就产生了变更数据。异步 CDC 使用数据库重做日志（REDOLOG）文件，在源数据库发生变更以后，才进行数据捕获。

ORACLE 闪回查询方式

ORACLE9I 以上版本的数据库系统提供了闪回查询机制，允许用户查询过去某个时刻的数据库状态。这样，抽取进程可以将源数据库的当前状态和上次抽取时刻的状态进行对比，快速得出源表数据记录的变化情况。

8、比较和分析

可见，ETL 在进行增量抽取操作时，有以上各种机制可以选择。现从兼容性、完备性、性能和侵入性 3 个方面对这些机制的优劣进行比较分析。

兼容性

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

完备性

时间戳方式不能捕获 DELETE 操作，需要结合其它方式一起使用。

性能

增量抽取的性能因素表现在两个方面，一是抽取进程本身的性能，二是对源系统性能的负面影响。

触发器方式、日志表方式以及系统日志分析方式由于不需要在抽取过程中执行比对步骤，所以增量抽取的性能较佳。全表比对方式需要经过复杂的比对过程才能识别出更改的记录，抽取性能最差。

在对源系统的性能影响方面，触发器方式由于是直接在源系统业务表上建立触发器，同时写临时表，对于频繁操作的业务系统可能会有一定的性能损失，尤其是当业务表上执行批量操作时，行级触发器将会对性能产生严重的影响；同步 CDC 方式内部采用触发器的方式实现，也同样存在性能影响的问题；全表比对方式和日志表方式对数据源系统数据库的性能没有任何影响，只是它们需要业务系统进行额外的运算和数据库操作，会有少许的时间损耗；时间戳方式、系统日志分析方式以及基于系统日志分析的方式（异步 CDC 和闪回查询）对数据库性能的影响也是非常小的。

侵入性

指业务系统是否要为实现增抽取机制做功能修改和额外操作，在这一点上，时间戳方式值得特别关注该方式除了要修改数据源系统表结构外，对于不支持时间戳字段自动更新的关系型数据库产品，还必须要修改业务系统的功能，让它在源表 T 执行每次操作时都要显式的更新表的时间戳字段，这在 ETL 实施过程中必须得到数据源系统高度的配合才能达到，并且在多数情况下这种要求在数据源系统看来是比较“过分”的，这也是时间戳方式无法得到广泛运用的主要原因。另外，触发器方式需要在源表上建立触发器，这种在某些场合中也遭到拒绝。还有一些需要建立临时表的方式，例如全表比对和日志表方式。可能因为开放给 ETL 进程的数据库权限的限制而无法实施。同样的情况也可能发生在基于系统日志分析的方式上，因为大多数的数据库产品只允许特定组的用户甚至只有 DBA 才能执行日志分析。闪回查询在侵入性方面的影响是最小的。

增量机制	兼容性	完备性	抽取性能	对源系统性能影响	对源系统侵入性	实现难度
触发器方式	关系型数据库	高	优	大	一般	较容易
时间戳方式	关系型数据库，具有“字段”结构的其它数据格式	低	较优	很小	大	较容易
全表删除插入方式	任何数据格式	高	极差	无	无	容易
全表比对方式	关系型数据库、文本格式	高	差	小	一般	一般
日志表方式	关系型数据库	高	优	小	较大	较容易
系统日志分析方式	关系型数据库	高	优	很小	较大	难
同步 CDC 方式	Oracle 数据库 9i 以上	高	优	大	一般	较难
异步 CDC 方式	Oracle 数据库 9i 以上	高	优	很小	一般	较难
闪回查询方式	Oracle 数据库 9i 以上	高	较优	很小	无	较容易

清洗

一般情况下，数据仓库分为 ODS、DW 两部分。通常的做法是从业务系统到 ODS 做清洗，将脏数据和不完整数据过滤掉，在从 ODS 到 DW 的过程中转换，进行一些业务规则的计算和聚合。

1、数据清洗

数据清洗的任务是过滤那些不符合要求的数据，将过滤的结果交给业务主管部门，确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。

不完整的数据：这一类数据主要是一些应该有的信息缺失。对于这一类数据过滤出来，按缺失的内容分别写入不同 Excel 文件向客户提交，要求在规定的时间内补全。补全后才写入数据仓库。
错误的数据：这一类错误产生的原因是业务系统不够健全，在接收输入后没有进行判断直接写入后台数据库造成的，比如数值数据输成全角数字字符、字符串数据后面有一个回车操作、日期格式不正确、日期越界等。这一类数据也要分类，对于类似于全角字符、数据前后有不可见字符的问题，只能通过写 SQL 语句的方式找出来，然后要求客户在业务系统修正之后抽取。日期格式不正确的或者是日期越界的这一类错误会导致 ETL 运行失败，这一类错误需要去业务系统数据库用 SQL 的方式挑出来，交给业务主管部门要求限期修正，修正之后再抽取。
重复的数据：对于这一类数据——特别是维表中会出现这种情况——将重复数据记录的所有字段导出来，让客户确认并整理。

数据清洗是一个反复的过程，不可能在几天内完成，只有不断的发现问题，解决问题。对于是否过滤，是否修正一般要求客户确认，对于过滤掉的数据，写入 Excel 文件或者将过滤数据写入数据表，在 ETL 开发的初期可以每天向业务单位发送过滤数据的邮件，促使他们尽快地修正错误，同时也可以做为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉，对于每个过滤规则认真进行验证，并要用户确认。

2、数据转换

数据转换的任务主要进行不一致的数据转换、数据粒度的转换，以及一些商务规则的计算。

不一致数据转换：这个过程是一个整合的过程，将不同业务系统的相同类型的数据统一，比如同一个供应商在结算系统的编码是 XX0001, 而在 CRM 中编码是 YY0001，这样在抽取过来之后统一转换成一个编码。
数据粒度的转换：业务系统一般存储非常明细的数据，而数据仓库中数据是用来分析的，不需要非常明细的数据。一般情况下，会将业务系统数据按照数据仓库粒度进行聚合。
商务规则的计算：不同的企业有不同的业务规则、不同的数据指标，这些指标有的时候不是简单的加加减减就能完成，这个时候需要在 ETL 中将这些数据指标计算好了之后存储在数据仓库中，以供分析使用。

ETL 日志

ETL 日志分为三类。

执行过程日志：这一部分日志是在 ETL 执行过程中每执行一步的记录，记录每次运行每一步骤的起始时间，影响了多少行数据，流水账形式。
错误日志：当某个模块出错的时候写错误日志，记录每次出错的时间、出错的模块以及出错的信息等。
总体日志：只记录 ETL 开始时间、结束时间是否成功信息。如果使用 ETL 工具，ETL 工具会自动产生一些日志，这一类日志也可以作为 ETL 日志的一部分。记录日志的目的是随时可以知道 ETL 运行情况，如果出错了，可以知道哪里出错。

参考文章: