ETL数据抽取实施方案简介

ETL数据抽取方案简介
目录
ETL简介3
ETL抽取方案4
1. .............................................................. 数据抽取 4
2. .............................................................. 数据变换和加
工8
3. .............................................................. 数据装载9
4. .............................................................. 时间戳方式
错误!不决义书签。

5. .............................................................. 全量删除插入
方式错误!不决义书签。

6. .............................................................. 全量比对方式
错误!不决义书签。

ETL简介
数据集成是把不相同本源、格式和特点的数据在逻辑上或物理上有机
地集中，从而供给全面的数据共享，是企业商务智能、数据库房系统的
重要组成部分。

ETL是企业数据集成的主要解决方案。

ETL中三个字母分别代表的是Extract、Transform、Load，即抽取、
变换、加载。

（1）数据抽取：从源数据源系统抽取目的数据源系统需要的数据；
（2）数据变换：将从源数据源获得的数据依照业务需求，变换成目
的数据源要求的形式，并对错误、不一致的数据进行冲刷和加工；
（3）数据加载：将变换后的数据装载到目的数据源。

ETL作为成立数据库房的一个环节，负责将分布的、异构数据源中
的数据如关系数据、平面数据文件等抽取来临时中间层后进行冲刷、转
换、集成，最后加载到数据库房或数据集市中，成为联机解析办理、数
据挖掘的基础。

现在越来越多地将ETL应用于一般信息系统数据的迁
移、交换和同步。

一个简单ETL流程如图1所示。

数据
文件目
抽转加
其他
临时
图1
ETL抽取方案
ETL过程中的主要环节就是数据抽取、数据变换和加工、数据装载。

为了实现这些功能，ETL工具会进行一些功能上的扩大，比方工作流、
调换引擎、规则引擎、脚本支持、统计信息等。

数据抽取
数据抽取是从数据源中抽取数据的过程。

本质应用中，数据源很多
采用的是关系数据库。

从数据库中抽取数据一般有以下几种方式：
全量抽取
全量抽取近似于数据迁移或数据复制，它将数据源中的表或视图的
数据纹丝没动的从数
据库中抽取出来，并变换成自己的ETL工具能够识其他格式。

全量抽取比较简单。

增量抽取
增量抽取只抽取自前一次抽取以来数据库中要抽取的表中新增或更正
的数据。

在ETL使用过程中，增量抽取较全量抽取应用更广。

如何捕捉
变化的数据是增量抽取的要点。

对捕捉方法一般有两点要求：正确性，
能够将业务系统中的变化数据按必然的频率正确地捕捉到；性能，不能够
对业务系统造成太大的压力，影响现有业务。

目前增量数据抽取中常用
的捕捉变化数据的方法有：
触发器方式（又称快照式）
在要抽取的表上成立需要的触发器，一般要成立插入、更正、删除
三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的
数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过
的数据被标记或删除。

优点：数据抽取的性能高，ETL加载规则简单，速度快，不需要修
改业务系统表结构，能够实现数据的递加添载。

缺点：要求业务表成立触发器，对业务系统有必然的影响，简单对
源数据库组成威胁。

时间戳方式
它是一种基于快照比较的变化数据捕捉方式，在源表上增加一个时
间戳字段，系统中更新更正表数据的时候，同时更正时间戳字段的值。

当进行数据抽取时，经过比较前一次抽取时间与时间戳字段的值来决定抽
取哪些数据。

有的数据库的时间戳支持自动更新，即表的其他字段的数
据发生改变时，自动更新时间戳字段的值。

有的数据库不支持时间戳的
自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。

优点：同触发器方式相同，时间戳方式的性能也比较好，ETL系统
设计清楚，源数据抽取相对清楚简单，能够实现数据的递加添载。

缺点：时间戳保护需要由业务系统完成，对业务系统也有很大的倾
入性（加入额外的时间戳字段），特别是对不支持时间戳的自动更新的数
据库，还要求业务系统进行额外的更新时间戳操作；别的，无法捕捉对
时间戳以前数据的delete和update操作，在数据正确性上碰到了必然
的限制。

全表删除插入方式
每次ETL操作均删除目标表数据，由ETL崭新加载数据。

优点：ETL加载规则简单，速度快。

缺点：对于维表加外键不适应，当业务系统产生删除数据操作时，
综合数据库将不会记录到所删除的历史数据，不能够够实现数据的递加添
载；同时对于目标表所成立的关系关系，需要重新进行创办。

全表比对方式
全表比对的方式是ETL工具早先为要抽取的表成立一个结构近似
的临时表，该临时表记录源表主键以及依照所有字段的数据计算出来，
每次进行数据抽取时，对源表和临时表进行的比对，如有不相同，进行
Update操作，如目标表没有存在该主键值，表示该记录还没有，即进
行Insert操作。

优点：对已有系统表结构不产生影响，不需要更正业务操作程序，所有抽取规则由ETL完成，管理保护一致，能够实现数据的递加添载，
没有风险。

缺点：ETL比对较复杂，设计较为复杂，速度较慢。

与触发器和时
间戳方式中的主动通知不相同，全表比对方式是被动的进行全表数据的比
对，性能较差。

当表中没有主键或唯一列且含有重复记录时，全表比对
方式的正确性较差。

日志表方式
在业务系统中增加系统日志表，当业务数据发生变化时，更新保护
日志表内容，看作ETL加载时，经过读日志表数据决定加载那些数据及
如何加载。

优点：不需要更正业务系统表结构，源数据抽取清楚，速度较快。

能够实现数据的递加添载。

缺点：日志表保护需要由业务系统完成，需要对业务系统业务操作
程序作更正，记录日志信息。

日志表保护较为麻烦，对原有系统有较大
影响。

工作量较大，改动较大，有必然风险。

Oracle变化数据捕捉（CDC方式）
经过解析数据库自己的日志来判断变化的数据。

Oracle的改变数据
捕捉（CDC，ChangedDataCapture）技术是这方面的代表。

CDC特
性是在Oracle9i数据库中引入的。

CDC能够帮助你鉴别从前一次抽取之
后发生变化的数据。

利用CDC，在对源表进行insert、update或delete
等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化
表中。

这样就可以捕捉发生变化的数据，尔后利用数据库视图以一种可
控的方式供给给目标系统。

CDC系统结构基于宣布/订阅模型。

宣布者
捕捉变化数据并供给给订阅者。

订阅者使用从宣布者那处获得的变化数
据。

平时，CDC系统拥有一个宣布者和多个订阅者。

宣布者第一需要
鉴别捕捉变化数据所需的源表。

尔后，它捕捉变化的数据并将其保存在
特别创办的变化表中。

它还使订阅者能够控制对变化数据的接见。

订阅
者需要清楚自己感兴趣的是哪些变化数据。

一个订阅者可能不会对宣布
者宣布的所有数据都感兴趣。

订阅者需要创办一个订阅者视图来接见经
宣布者授权能够接见的变化数据。

CDC分为同步模式和异步模式，同
步模式实时的捕捉变化数据并储藏到变化表中，宣布者与订阅都位于同
一数据库中；异步模式则是基于Oracle的流复制技术。

优点：供给了易于使用的API来设置CDC环境，缩短ETL的时
间。

不需要更正业务系统表结构，能够实现数据的递加添载。

缺点：业务系统数据库版本与产品不一致，难以一致实现，实现过
程相对复杂，并且需深入研究方能实现。

也许经过第三方工具实现，价
格昂贵。

数据变换和加工
从数据源中抽取的数据不用然完满满足目的库的要求，比方数据格
式的不一致、数据输入错误、数据不完满等等，因此有必要对抽取出的
数据进行数据变换和加工。

数据的变换和加工能够在ETL引擎中进行，也能够在数据抽取过程
中利用关系数据库的特点同时进行。

a)
b)ETL引擎中的数据变换和加工
ETL引擎中一般以组件化的方式实现数据变换。

常用的数据变换组
件有字段照射、数据过滤、数据冲刷、数据代替、数据计算、数据考据、
数据加解密、数据合并、数据拆分等。

这些组件仿佛一条流水线上的一
道道工序，它们是可插拔的，且能够任意组装，各组件之间经过数据总
线共享数据。

同时ETL工具还供给了脚本支持，使得用户能够以一种编
程的方式定制数据的变换和加工行为。

对照在数据库中加工，性能较高，但不简单进行更正和清楚辨别。

在数据库中进行数据加工
关系数据库自己已经供给了富强的SQL、函数来支持数据的加工，
如在SQL盘问语句中增加where条件进行过滤，盘问中重命名字段名
与目的表进行照射，substr函数，case条件判断等等。

对照在ETL引擎中进行数据变换和加工，直接在SQL语句中进行
变换和加工更加简单清楚；但依赖SQL语句，有些数据加工经过SQL
语句可能无法实现，对于SQL语句无法办理的能够交由ETL引擎办理。

数据装载
将变换和加工后的数据装载到目的库中平时是ETL过程的最后步
骤。

装载数据的最正确方法取决于所执行操作的种类以及需要装入多少许
据。

当目的库是关系数据库时，一般来说有两种装载方式：
（1）直接SQL语句进行insert、update、delete操作。

（2）采用批量装载方法，sqlldr等。

大多数情况下使用第一种方法，因为它们进行了日志记录并且是可
恢复的。

但是，批量装载操作易于使用，并且在装入大量数据时效率较高。

使用哪一种数据装载方法取决于业务系统的需要。