ETL - 数据抽取的应用

所谓数据抽取，就是从源数据系统抽取目标数据系统需要的数据，拿到数据数据以后再根据业务需求去对数据进项处理。之前我们介绍了数据的全量抽取、增量抽取的概念。今天我们来讲讲全量抽取与增量抽取的应用。

全量抽取应用较为广泛，因为我们在做业务处理分析过程中往往是需要对以前的历史数据结合分析的。但是全量抽取也不意味着就是把数据库里的所有历史数据都抽取过来，这样的方式太过于浪费资源。增量抽取也会分多种方式，具体的要和自己公司的的业务结合。比如我们可能把全量抽取分为全量覆盖和全量切片。全量覆盖不用再多说，全量分片的话，可以是截至某个时间点的所有数据，这样是为了把时间太久价值较低的数据或者是业务不需要的某段数据排除在外。

增量抽取机制相对于全量抽取较为复杂。我们介绍过增量抽取机制主要有这么几种：触发器方式、时间戳方式、全表删除插入方式、全表比对方式、日志表方式。其中触发器方式、时间戳方式应用的相对较多。

下面我们来介绍这两种增量抽取的应用：

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

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

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

1.创建增量日志表DML_LOG：

CREATE TABLE 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 Itstar BEFORE INSERT OR UPDATE OR DELETE ON itstar FOR EACH ROWDECLARE 1 DML TYPE VARCHAR2(1);
BEGINIF INSERTING THEN L_DML TYPE：＝'I';
ELSEIF UPDATING THEN L_DML TYPE：＝'U';
ELSEIF DELETING THEN L_DML TYPE：＝'D';
END IF;
IF DELETING thenINSERT INTO DML_LOG(ID,TABLE_NAME,RECORD_ID,EXECUTE_DATE,DML_TYPE)
VALUES(SEQ_DML_LOG.NEXTVAL,'TEST',:OLD.ID,SYSDATE,L_DML_TYPE);
ELSEINSERT　INTO DML_LOG　(ID , TABLE_NAME , RECORD_ID , EXECUTE_DATE , DML_TYPE)(SEQ_DML_LOG.NEXTVAL,’TEST’,:NEW.ID,SYSDATE,L_DML_TYPE);
END IF;
END;

时间戳方式:指增量抽取时，抽取进程通过比较系统时间与抽取源表的时间戳字段的值来决定抽取哪些数据。这种方式需要在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。有的数据库(例如SQL SERVER)的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。在这种情下，进行ETL实施时就只需要在源表加上时间戳字段就可以了。对于不支持时间戳自动更新的数据库，这就要求业务系统在更新业务数据时，通过编程的方式手工更新时间戳字段。使用时间戳方式可以正常捕获源表的插入和更新操作，但对于删除操作则无能为力，需要结合其它机制才能完成。