增量抽取是数据仓库ETL(extraction，transformation，loading，数据的抽取、转换和装载)实施过程中需要重点考虑的问题。在ETL过程中，增量更新的效率和可⾏性是决定ETL实施成败的关键问题之⼀，ETL中的增量更新机制⽐较复杂，采⽤何种机制往往取决于源数据系统的类型以及对增量更新性能的要求。 1 ETL概述
ETL包括数据的抽取、转换、加载。①数据抽取：从源数据源系统抽取⽬的数据源系统需要的数据：②数据转换：将从源数据源获取的数据按照业务需求，转换成⽬的数据源要求的形式，并对错误、不⼀致的数据进⾏清洗和加⼯；③数据加载：将转换后的数据装载到⽬的数据源。
ETL作为构建数据仓库的⼀个环节，负责将分布的、异构数据源中的数据如关系数据、平⾯数据⽂件等抽取到临时中间层后进⾏清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。ETL原来主要⽤户构建数据仓库和商业智能项⽬，现在也越来越多地应⽤于⼀般信息系统数据的迁移、交换和同步。
在ETL的3个环节中，数据抽取直接⾯对各种分散、异构的数据源，如何保证稳定⾼效的从这些数据源中提取正确的数据，是ETL设计和实施过程中需要考虑的关键问题之⼀。
在集成端进⾏数据的初始化时，⼀般需要将数据源端的全部数据装载进来，这时需要进⾏全量抽取。全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据全部从数据库中抽取出来，再进⾏后续的转换和加载操作。全量抽取可以使⽤数据复制、导⼊或者备份的⽅式完成，实现机制⽐较简单。全量抽取完成后，后续的抽取操作只需抽取⾃上次抽取以来表中新增或修改的数据，这就是增量抽取。 在数据库仓库中，⽆论是全量抽取还是增量抽取，抽取⼯作⼀般由数据仓库⼯具来完成，如oracle的OWB，DataStage以及专业的ETL商业产品Informatica PowerCenter等。如果企业的预算有限，也可以考虑使⽤开源项⽬Kettle。这些⼯具都有⼀个特点，就是本⾝并没有实现特定的增量抽取机制，它们完成全量抽取后，⽤户可以通过定制计划任务的⽅式，实现按⼀定的周期从源系统中抽取当前周期内产⽣的增量数据，但⾄于这些增量数据如何产⽣，⼯具并没有提供⾃动⽣成增量数据的功能。所以，ETL过程中增量数据的产⽣机制是⼀个需要⽤户重点研究和选择的问题。
2 增量抽取机制
要实现增量抽取，关键是如何准确快速的捕获变化的数据。优秀的增量抽取机制要求ETL能够将业务系统中的变化数据按⼀定的频率准确地捕获到，同时不能对业务系统造成太⼤的压⼒，影响现有业务。相对全量抽取⽽⾔，增量抽取的设计更复杂，有⼀种将全量抽取过程⾃动转换为增量抽取过程的
ETL设计思路，前提是必须捕获变化的数据，增量数据抽取中常⽤的捕获变化数据的⽅法有以下⼏种：
2.1 触发器⽅式
触发器⽅式是普遍采取的⼀种增量抽取机制。该⽅式是根据抽取要求，在要被抽取的源表上建⽴插⼊、修改、删除3个触发器，每当源表中的数据发⽣变化，就被相应的触发器将变化的数据写⼊⼀个增量⽇志表，ETL的增量抽取则是从增量⽇志表中⽽不是直接在源表中抽取数据，同时增量⽇志表中抽取过的数据要及时被标记或删除。
为了简单起见，增量⽇志表⼀般不存储增量数据的所有字段信息，⽽只是存储源表名称、更新的关键字值和更新操作类型(insert、update或delete)，ETL增量抽取进程⾸先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对⽬标表进⾏相应的处理。
例如，对于源表为Oracle类型的数据库，采⽤触发器⽅式进⾏增量数据捕获的过程如下：
(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 T BEFORE INSERT OR UPDATE OR DELETE ON T for each row
declare I_dml_type varchar2(1);异形耐火砖
begin
if INSERTING then l_dml type：＝’I’;
elsif UPDATING then I_dml_type：＝’U’;
elsif DELETING then l_dml_type：＝’D’;
end if;
if DELETING then
insert into DML_LOG(ID,TABLE_NAME,RECORD_ID,EXECUTE_DATE,DML_TYPE)
values(seq_val,’Test’,:old.ID,sysdate,l_dml_type);
else
insert into DML_LOG(ID,TABLE_NAME,RECORD_ID,EXECUTE_DATE,DML_TYPE)
values(seq_val,’Test’,:new.ID,sysdate,l_dml_type);
end if;
end;
这样，对表T的所有DML操作就记录在增量⽇志表DML_LOG中，注意增量⽇志表中并没有完全记录增量数据本⾝，只是记录了增量数据的来源。进⾏增量ETL时，只需要根据增量⽇志表中的记录情况，反查源表得到真正的增量数据。
2.2 时间戳⽅式
时间戳⽅式是指增量抽取时，抽取进程通过⽐较系统时间与抽取源表的时间戳字段的值来决定抽取哪些数据。这种⽅式需要在源表上增加⼀个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。
有的数据库(例如Sql Server)的时间戳⽀持⾃动更新，即表的其它字段的数据发⽣改变时，时间戳字段的值会被⾃动更新为记录改变的时刻。在这种情况下，进⾏ETL实施时就只需要在源表加上时间戳字段就可以了。对于不⽀持时间戳⾃动更新的数据库，这就要求业务系统在更新业务数据时，通过编程的⽅式⼿⼯更新时间戳字段。
使⽤时间戳⽅式可以正常捕获源表的插⼊和更新操作，但对于删除操作则⽆能为⼒，需要结合其它机制才能完成。
2.3 全表删除插⼊⽅式
全表删除插⼊⽅式是指每次抽取前先删除⽬标表数据，抽取时全新加载数据。该⽅式实际上将增量抽取等同于全量抽取。对于数据量不⼤，全量抽取的时间代价⼩于执⾏增量抽取的算法和条件代价时，可以采⽤该⽅式。
2.4 全表⽐对⽅式
全表⽐对即在增量抽取时，ETL进程逐条⽐较源表和⽬标表的记录，将新增和修改的记录读取出来。
优化之后的全部⽐对⽅式是采⽤MD5校验码，需要事先为要抽取的表建⽴⼀个结构类似的MD5临时表，该临时表记录源表的主键值以及根据源表所有字段的数据计算出来的MD5校验码，每次进⾏数据抽取时，对源表和MD5临时表进⾏MD5校验码的⽐对，如有不同，进⾏update 操作：如⽬标表没有存在该主键值，表⽰该记录还没有，则进⾏insert操作。然后，还需要对在源表中已不存在⽽⽬标表仍保留的主键值，执⾏delete操作。
2.5 ⽇志表⽅式
对于建⽴了业务系统的⽣产数据库，可以在数据库中创建业务⽇志表，当特定需要监控的业务数据发⽣变化时，由相应的业务系统程序模块来更新维护⽇志表内容。增量抽取时，通过读⽇志表数据决定加载哪些数据及如何加载。⽇志表的维护需要由业务系统程序⽤代码来完成。
2.6 系统⽇志分析⽅式
该⽅式通过分析数据库⾃⾝的⽇志来判断变化的数据。关系犁数据库系统都会将所有的DML操作存储在⽇志⽂件中，以实现数据库的备份和还原功能。ETL增量抽取进程通过对数据库的⽇志进⾏分析，提取对相关源表在特定时间后发⽣的DML操作信息，就可以得知⾃上次抽取时刻以来该表的数据变化情况，从⽽指导增量抽取动作。
有些数据库系统提供了访问⽇志的专⽤的程序包(例如Oracle的LogMiner)，使数据库⽇志的分析⼯作得到⼤⼤简化。
2.7 特定数据库的⽅式
双层布
针对特有数据库系统的增量抽取⽅式：
2.7.1 Oracle改变数据捕获(changed data capture，CDC)⽅式
OracleCDC特性是在Oraele9i数据库中引⼊的。CDC能够帮助识别从上次抽取之后发⽣变化的数据。利⽤CDC，在对源表进⾏insert、update或delete等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发⽣变化的数据，然后利⽤数
据库视图以⼀种可控的⽅式提供给ETL抽取进程，作为增量抽取的依据。
风琴式导轨防护罩
CDC⽅式对源表数据变化情况的捕获有两种⽅式：同步CDC和异步CDC。同步CDC使⽤源数据库触发器来捕获变更的数据。这种⽅式是实时的，没有任何延迟。当DML操作提交后，变更表中就产⽣了变更数据。异步CDC使⽤数据库重做⽇志(redolog)⽂件，在源数据库发⽣变更以后，才进⾏数据捕获。
2.7.2 Oracle闪回查询⽅式
Oracle9i以上版本的数据库系统提供了闪回查询机制，允许⽤户查询过去某个时刻的数据库状态。这样，抽取进程可以将源数据库的当前状态和上次抽取时刻的状态进⾏对⽐，快速得出源表数据记录的变化情况。
3 ⽐较和分析
可见，ETL在进⾏增量抽取操作时，有以上各种机制可以选择。现从兼容性、完备性、性能和侵⼊性4个⽅⾯对这些机制的优劣进⾏⽐较分析。
兼容性cwmp
数据抽取需要⾯对的源系统，并不⼀定都是关系型数据库系统。某个ETL过程需要从若⼲年前的遗留系统中抽取Excel或者CSV⽂本数据的情形是经常发⽜的。这时，所有基于关系型数据库产品的增量机
制都⽆法⼯作，时间戳⽅式和全表⽐对⽅式可能有⼀定的利⽤价值，在最坏的情况下，只有放弃增量抽取的思路，转⽽采⽤全表删除插⼊⽅式。
完备性
完备性⽅⾯，时间戳⽅式不能捕获delete操作，需要结合其它⽅式⼀起使⽤。
发光细菌性能
增量抽取的性能因素表现在两个⽅⾯，⼀是抽取进程本⾝的性能，⼆是对源系统性能的负⾯影响。触发器⽅式、⽇志表⽅式以及系统⽇志分析⽅式由于不需要在抽取过程中执⾏⽐对步骤，所以增量抽取的性能较佳。全表⽐对⽅式需要经过复杂的⽐对过程才能识别出更改的记录，抽取性能最差。在对源系统的性能影响⽅⾯，触发器⽅式由于是直接在源系统业务表上建⽴触发器，同时写临时表，对于频繁操作的业务系统可能会有⼀定的性能损失，尤其是当业务表上执⾏批量操作时，⾏级触发器将会对性能产⽣严重的影响；同步CDC⽅式内部采⽤触发器的⽅式实现，也同样存在性能影响的问题；系统⽇志分析⽅式和⽇志表⽅式对数据源系统数据库的性能没有任何影响，只是它们需要业务系统进⾏额外的运算和数据库操作，会有少许的时间损耗；时间戳⽅式、系统⽇志分析⽅式以及基于系统⽇志分析的⽅式(异步CDC和闪回查询)对数据库性能的影响也是⾮常⼩的。
侵⼊性
对数据源系统的侵⼊性是指业务系统是否要为实现增量抽取机制做功能修改和额外操作，在这⼀点上，时间戳⽅式值得特别关注。该⽅式除了要修改数据源系统表结构外，对于不⽀持时间戳字段⾃动更新的关系型数据库产品，还必须要修改业务系统的功能，让它在源表t执⾏每次操作时都要显式的更新表的时间戳字段，这在ETL实施过程中必须得到数据源系统⾼度的配合才能达到，并且在多数情况下这种要求在数据源系统看来是⽐较“过分”的，这也是时间戳⽅式⽆法得到⼴泛运⽤的主要原因。另外，触发器⽅式需要在源表上建⽴触发器，这种在某些场合中也遭到拒绝。还有⼀些需要建⽴临时表的⽅式，例如全表⽐对和⽇志表⽅式。可能因为开放给ETL进程的数据库权限的限制⽽⽆法实施。同样的情况也可能发⽣在基于系统⽇志分析的⽅式上，因为⼤多数的数据库产品只允许特定组的⽤户甚⾄只有DBA才能执⾏⽇志分析。闪回杏询在侵⼊性⽅⾯的影响是最⼩的。
大数据广告综述:
通过对各种增量抽取机制的对⽐分析，我们发现，没有⼀种机制具有绝对的优势，不同机制在各种因素的表现⼤体上都是相对平衡的。兼容性较差的机制，像CDC和闪回查询机制，由于充分利⽤了数据源系统DBMS的特性，相对来说具有较好的整体优势；最容易实现以及兼容性最佳的全表删除插⼊机制，则是以牺牲抽取性能为代价的；系统⽇志分析⽅式对源业务系统的功能⽆需作任何改变，对源系统表也⽆需建⽴触发器，⽽抽取性能也不错，但有可能需要源系统开放DBA权限给ETL抽取进程，并且⾃⾏分析⽇志系统难度较⾼，不同数据库系统的⽇志格式不⼀致，这就在⼀定程度上限制了它的使
⽤范围。所以，ETL实施过程中究竞选择哪种增量抽取机制，要根据实际的数据源系统环境进⾏决策，需要综合考虑源系统数据库的类型、抽取的数据量(决定对性能要求的苛刻程度)、对源业务系统和数据库的控制能⼒以及实现难度等各种因素，甚⾄结合各种不同的增量机制以针对环境不同的数据源系统进⾏ETL实施。
4 结束语
为了实现数据仓库数据的⾼效更新，增量抽取是ETL数据抽取过程中⾮常重要的⼀环，其实现机制直接决定了ETL的整体实施效果。我们通过对⼏种常见的增量抽取机制进⾏了对⽐，总结了各种机制的特性并分析了它们的优劣。各种增量抽取机制都有它有存在的价值和固有的限制条件，在ETL的设计和实施⼯作过程中，只能依据项⽬的实际环境进⾏综合考虑，甚⾄需要对可采⽤的多种机制进⾏实际的测试，才能确定⼀个最优的增量抽取⽅法。
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议