Bi的ETL中怎么做增量处理



增量抽取 增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据。在ETL使用过程中。增量抽取较全量抽取应用更广。如何捕获变化的数据是增量抽取的关键。对捕获方法一般有两点要求：准确性，能够将业务系统中的变化数据按一定的频率准确地捕获到；性能，不能对业务系统造成太大的压力，影响现有业务。目前增量数据抽取中常用的捕获变化数据的方法有：
a.触发器：在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除。触发器方式的优点是数据抽取的性能较高，缺点是要求业务表建立触发器，对业务系统有一定的影响。

b.时间戳：它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，即表的其它字段的数据发生改变时，自动更新时间戳字段的值。有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单，但对业务系统也有很大的倾入性（加入额外的时间戳字段），特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作。另外，无法捕获对时间戳以前数据的delete操作,在数据准确性上受到了一定的限制。

c.全表比对：典型的全表比对的方式是采用MD5校验码。ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MD5校验码。MD5方式的优点是对源系统的倾入性较小（仅需要建立一个MD5临时表），但缺点也是显而易见的，与触发器和时间戳方式中的主动通知不同，MD5方式是被动的进行全表数据的比对，性能较差。当表中没有主键或唯一列且含有重复记录时，MD5方式的准确性较差。

d.日志对比：通过分析数据库自身的日志来判断变化的数据。Oracle的改变数据捕获（CDC，ChangedDataCapture）技术是这方面的代表。CDC特性是在Oracle9i数据库中引入的。CDC能够帮助你识别从上次抽取之后发生变化的数据。利用CDC，在对源表进行insert、update或delete等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。CDC体系结构基于发布者/订阅者模型。发布者捕捉变化数据并提供给订阅者。订阅者使用从发布者那里获得的变化数据。通常，CDC系统拥有一个发布者和多个订阅者。发布者首先需要识别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对发布者发布的所有数据都感兴趣。订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。CDC分为同步模式和异步模式，同步模式实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中。异步模式则是基于Oracle的流复制技术。

============================================================================================================

ETL之增量抽取方式

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

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

这样，对表T的所有DML操作就记录在增量日志表DML_LOG中，注意增量日志表中并没有完全记录增量数据本身，只是记录了增量数据的来源。进行增量ETL时，只需要根据增量日志表中的记录情况，反查源表得到真正的增量数据。
SQL代码 
(1)创建增量日志表DML_LOG： 
CREATE TABLE DML_LOG( 
ID NUMBER PRIMARY KEY, ／／自增主键 
TABLE NAME VARCHAR2(200)． ／／源表名称 
RECORD ID NUMBER, ／／源表增量记录的主键值 
DML TYPE CH根(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 1 DML TYPE VARCHAR2(1)； 
BEGIN 
IF INSERTING THEN L_DML TYPE：= I’； 
ELSIF UPDATING THEN I_DML_TYPE：=。TY； 
ELSIF DELETING THEN L_DML_TYPE：= D’； 
ENDIF； 

IF DELETING THEN 
INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD— 
ID，EXECUTE_DATE，DMLJYPE) 
VALUES(SEQ_DML_LOG．NEXTVAL，’TEST ，：OLD．ID，SYSDATE， 
L_DML_TYPE)； 
ELSE 
INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD_ 
ID，EXECUTE_DATE，DMLJYPE) 
VALUES(SEQ_DML_LOG．NEXTVAL，。TEST ，：NEW．ID，SYSDATE，L 
TIROL_TYPE)； 
ENDIF； 
END；

2、时间戳方式

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

更新时间戳：

3、全表删除插入方式

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

4、全表比对方式

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

MD5校验码，每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，如有不同，进行UPDATE操作：如目标表没有存在该主键值，表示该记录还没有，则进行INSERT操作。

然后，还需要对在源表中已不存在而目标表仍保留的主键值，执行DELETE操作。

5、日志表方式

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

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

6、系统日志分析方式

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

、特定数据库方式（ORACLE）
以下介绍常见的针对特有数据库系统的增景抽取方式。
7.1 ORACLE改变数据捕获(CHANGEDDATACAPTURE，CDC)方式：ORACLECDC特性是在ORAELE9I数据库中引入的。CDC能够帮助识别从上次抽取之后发生变化的数据。
利用CDC，在对源表进行INSERT、UPCLATE或DELETE等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给ETL抽取进程，作为增量抽取的依据。CDC方式对源表数据变化情况的捕获有两种方式：同步CDC和异步CDC。同步CDC使用源数据库触发器来捕获变更的数据。这种方式是实时的，没有任何延迟。当DML操作提交后，变更表中就产生了变更数据。异步CDC使用数据库重做日志(REDOLOG)文件，在源数据库发生变更以后，才进行数据捕获。
7.2 ORACLE闪回查询方式：ORACLE9I以上版本的数据库系统提供了闪回查询机制，允许用户查询过去某个时刻的数据库状态。这样，抽取进程可以将源数据库的(BI)
当前状态和上次抽取时刻的状态进行对比，快速得出源表数据记录的变化情况。

8、比较和分析

可见，ETL在进行增量抽取操作时，有以上各种机制可以选择。现从兼容性、完备性、性能和侵入性3个方面对这些机制的优劣进行比较分析。数据抽取需要面对的源系统，并不一定都是关系型数据库系统。某个ETL过程需要从若干年前的遗留系统中抽取EXCEL或者CSV文本数据的情形是经常发牛的。这时，所有基于关系型数据库产品的增量机制都无法工作，时间戳方式和全表比对方式可能有一定的利用价值，在最坏的情况下，只有放弃增量抽取的思路，转而采用全表删除插入方式。完备性方面，时间戳方式不能捕获DELETE操作，需要结合其它方式一起使用。增量抽取的性能因素表现在两个方面，一是抽取进程本身的性能，二是对源系统性能的负面影响。触发器方式、日志表方式以及系统日志分析方式由于不需要在抽取过程中执行比对步骤，所以增量抽取的性能较佳。全表比对方式需要经过复杂的比对过程才能识别出更改的记录，抽取性能最差。在对源系统的性能影响方面，触发器方式由于是直接在源系统业务表上建立触发器，同时写临时表，对于频繁操作的业务系统可能会有一定的性能损失，尤其是当业务表上执行批量操作时，行级触发器将会对性能产生严重的影响；同步CDC方式内部采用触发器的方式实现，也同样存在性能影响的问题；全表比对方式和日志表方式对数据源系统数据库的性能没有任何影响，只是它们需要业务系统进行额外的运算和数据库操作，会有少许的时间损耗；时间戳方式、系统日志分析方式以及基于系统日志分析的方式(异步CDC和闪回查询)对数据库性能的影响也是非常小的。对数据源系统的侵入性是指业务系统是否要为实现增抽取机制做功能修改和额外操作，在这一点上，时间戳方式值得特别关注该方式除了要修改数据源系统表结构外，对于不支持时间戳字段自动更新的关系型数据库产品，还必须要修改业务系统的功能，让它在源表T执行每次操作时都要显式的更新表的时间戳字段，这在ETL实施过程中必须得到数据源系统高度的配合才能达到，并且在多数情况下这种要求在数据源系统看来是比较“过分”的，这也是时间戳方式无法得到广泛运用的主要原因。另外，触发器方式需要在源表上建立触发器，这种在某些场合中也遭到拒绝。还有一些需要建立临时表的方式，例如全表比对和日志表方式。可能因为开放给ETL进程的数据库权限的限制而无法实施。同样的情况也可能发生在基于系统日志分析的方式上，因为大多数的数据库产品只允许特定组的用户甚至只有DBA才能执行日志分析。闪回查询在侵入性方面的影响是最小的.

 

在我从事的ETL工作中，大部分都是采用时间戳方式进行增量抽取，如银行业务，VT新开户，使用时间戳方式，可以在固定时间内，组织人员进行数据抽取，进行整合后，加载到目标系统。而触发器方式，虽然可以自动进行抽取，但是执行频率过多，影响效率！第三种方式对于大数据量来说是非常不可取的，尤其是对于一些银行、电信行业，因为数据全量比较大，所以进行增量校对是比较耗时的，总起来说，个人趋向使用时间戳方式进行增量抽取，当然具体情况要看工作的使用环境！

==============================================================================================================

时间戳方式的增量：

这个实验主要思想是在创建数据库表的时候，

通过增加一个额外的字段，也就是时间戳字段，

例如在同步表 tt1 和表 tt2 的时候，

通过检查那个表是最新更新的，那个表就作为新表，而另外的表最为旧表被新表中的数据进行更新。

实验数据如下：

mysql database 5.1

test.tt1( id int primary key , name varchar(50) );

mysql.tt2( id int primary key, name varchar(50) );

 

快照表，可以将其存放在test数据库中，

同样可以为了简便，可以将其创建为temporary 表类型。

 

数据如图 kettle-1

kettle-1

============================================================

 

主流程如图 kettle-2

 kettle-2

 

在prepare中，向 tt1，tt2 表中增加 时间戳字段，

由于tt1，tt2所在的数据库是不同的，所以分别创建两个数据库的连接。

prepare

 

kettle-3

 

 在执行这个job之后，就会在数据库查询的时候看到下面的字段：

 

kettle-4

然后， 我们来对tt1表做一个 insert 操作 一个update操作吧~

kettle-5

在原表上无论是insert操作还是update操作，对应的updateTime都会发生变更。

如果tt1 表 和 tt2 表中 updateTime 字段为最新时间的话，则说明该表是新表 。

 

下面只要是对应main_thread的截图：

kettle-6

 

在这里介绍一下Main的层次：

Main

START

Main.prepare

Main.main_thread

{

　　START

　　main_thread.create_tempTable

　　main_thread.insert_tempTable

　　main_thread.tt1_tt2_syn

　　SUCCESS

}

Main.finish

SUCCESS

 

在main_thread中的过程是这样的：

作为一个局部的整体，使它每隔200s内进行一次循环，

这样的话，如果在其中有指定的表 tt1 或是 tt2 对应被更新或是插入的话，

该表中的updateTime字段就会被捕捉到，并且进行同步。

如果没有更新出现，则会走switch的 default 路线对应的是write to log.

继续循环。

 

首先创建一个快照表，然后将tt1，tt2表中的最大(最新)时间戳的值插入到快照表中。

然后，通过一个transformation来判断那个表的updateTime值最新，

来选择对应是 tt1表来更新 tt2 还是 tt2 表来更新 tt1 表；

 

main_thread.create_tempTable.JOB:

 

 

main_thread.insert_tempTable.Job:

PS： 对于第二个SQL 应该改成(不修改会出错的)

set @var1 = ( select MAX(updatetime) from tt2);

insert into test.temp values ( 2 , @var1 ) ;

因为conn对应的是连接mysql（数据库实例名称），

但是我们把快照表和tt1 表都存到了test（数据库实例名称）里面。

 

在上面这个图中对应的语句是想实现，在temp表中插入两行记录元组。

其中id为1 的元组对应的temp.lastTime 字段 是 从tt1 表中选出的 updateTime 值为最新的，

id 为2的元组对应的 temp.lastTime 字段 是 从 tt2 表中选出的 updateTime 值为最新的 字段。

当然 ， id 是用来给后续 switch 操作提供参考的，用于标示最新 updateTime 是来自 tt1 还是 tt2，

同样也可以使用 tableName varchar(50) 这种字段 来存放 最新updateTime 对应的 数据库.数据表的名称也可以的。

 

main_thread.tt1_tt2_syn.Transformation:

首先，创建连接 test 数据库的 temp 表的连接，

选择 temp表中 对应 lastTime 值最新的所在的记录

所对应的 id 号码。

首先将temp中 lastTime 字段进行 降序排列，

然后选择id ， 并且将选择记录仅限定成一行。

然后根据id的值进行 switch选择。

在这里LZ很想使用，SQL Executor，

但是它无法返回对应的id值。

但是表输入可以返回对应的id值，

并被switch接收到。

 

 

下图是对应 switch id = 1 的时候：即 tt1 更新 tt2

注意合并行比较 的新旧数据源 的选择

和Insert/Update 中的Target table的选择

 

下图是对应 switch id = 2 的时候：即 tt2 更新 tt1

注意合并行比较 的新旧数据源 的选择

和Insert/Update 中的Target table的选择 

 

 

 

但是考虑到增加一个 column 会浪费很多的空间，

所以咋最终结束同步之后使用 finish操作步骤来将该 updateTime这个字段进行删除操作即可。

这个与Main中的prepare的操作是相对应的。

Main.finish

 

 

 

 

这样的话，实验环境已经搭建好了，

接下来进行，实验的数据测试了，写到下一个博客中。

 当然，触发器也是一种同步的好方法，写到后续博客中吧~

 

时间戳的方式相比于触发器，较为简单并且通用，

但是 数据库表中的时间戳字段，很费空间，并且无法对应删除操作，

也就是说 表中删除一行记录， 该表应该作为新表来更新其余表，但是由于记录删除 时间戳无所依附所以无法记录到。

posted @   2014-10-26 00:12   Bobby0322  阅读( 476) 评论( 1)   编辑   收藏