面向数据集成的ETL技术研究

详细介绍了数据抽取的几种主要办法

面向数据集成的ETL技术研究

时间:2009-12-18 13:52来源: 作者: 点击:

34次

数据集成是把不同来源、格式和特点的数据在逻辑上或物理上有机地集中，从而为企业提供全面的数据共享，是企业商务智能、数据仓库系统的重要组成部分。 随着单位information化建设的扩展，巨大的投入资金为单位建立了众多的informationwindows，以帮助单位进行内外部业务的处理和管理工作。但是随着informationwindows的增加，各自孤立工作的informationwindows将会造成大量的冗余数据和业务人员的重复劳动。单位应用集成(EAI Picture EXP操作系统lorer，Enterprise Application Integration)应运而生。EAI Picture EXP操作系统lorer通过建立底层数据交换平台来联系横贯整个单位的异构

windows、应用、数据源等，完成在单位内部的ERP、CRM、SCM、数据库、数据仓库，以及其它重要的内部windows之间无缝地共享和交换数据的需要。数据集成是单位应用集成的重要环节，单位实现数据集成，可以使更多的人更充分地试用已有数据资源，减少资料收集、数据采集等重复劳动和相应费用。

但是，在实施数据集成的过程中，由于不同客户提供的数据可能来自不同的途径，其数据内容、数据格式和数据质量千差万别，有时甚至会遇到数据格式不能转换或数据转换格式后丢掉information等棘手问题，严重阻碍了数据在各部门和各应用windows中的流动与共享。因此，如何对数据进行有效的集成管理已成了增强单位商业竞争力的必然选自。ETL是实现数据集成的重要技术。 ETL中三个字母分别代表的是Extract、Transform、Load，即抽取、转换、加载。(1)数据抽取：从源数据源windows抽取目的数据源windows需要的数据;(2)数据转换：将从源数据源获取的数据按照业务需求，转换成目的数据源要求的形式，并对错误、不一致的数据进行清洗和加工。(3)数据加载：将转换后的数据装载到目的数据源。

ETL原本是操作为构建数据仓库的一个环节，负责将分布的、异构数据源中的数据如关系数据、平面数据文档等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成了联机分析处理、数据挖掘的入门。现在也越来越多地将ETL应用于一般informationwindows中数据的迁移、交换和同步。一个简单的ETL体系结构如图1.1所示。

2.ETL中的关键技术

ETL过程中的重要环节就是数据抽取、数据转换和加工、数据装载。为了实现这些性能，各个ETL工具一般会进行一部分性能上的扩充，例如工作流、调度引擎、规则引擎、脚本支持、统计information等。

2.1 数据抽取

数据抽取是从数据源中抽取数据的过程。实际应用中，数据源较多采用的是

详细介绍了数据抽取的几种主要办法

关系数据库。从数据库中抽取数据一般有以下几种办法。

(1)全量抽取

全量抽取类似于数据迁移或数据拷贝，它将数据源中的表或视图的数据原封不动的从数据库中抽取出来，并转换成自己的ETL工具可以辨别的格式。全量抽取比较简单。

(2)增量抽取

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

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

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

d.日志比较：通过分析数据库自身的日志来判断变化的数据。Oracle

Database的改变数据捕获(CDC，Changed Data Capture)技术是这方面的代表。CDC 特性是在Oracle Database9i数据库中引入的。CDC能够帮助你辨别从上次抽取之后发生变化的数据。利用CDC，在对源表进行insert、update或 delete

详细介绍了数据抽取的几种主要办法

等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的办法提供给目标windows。CDC体系结构基于发布者/订阅者模型。发布者捕捉变化数据并提供给订阅者。订阅者试用从发布者那里获得的变化数据。通常，CDCwindows拥有一个发布者和多个订阅者。发布者首先需要辨别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对发布者发布的所有数据都感兴趣。订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。CDC分为同步形式和异步形式，同步形式实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中。异步形式则是基于Oracle Database的流拷贝技术。

ETL处理的数据源除了关系数据库外，还可能是文档，例如txt文档、excel文档、xml文档等。对文档数据的抽取一般是进行全量抽取，一次抽取前可保存文档的时间戳或计算文档的MD5校验码，下次抽取时进行比对，如果相同则可忽略本次抽取。

2.2 数据转换和加工

从数据源中抽取的数据不一定完全满足目的库的要求，例如数据格式的不一致、数据输入错误、数据不完整等等，因此有必要对抽取出的数据进行数据转换和加工。

数据的转换和加工可以在ETL引擎中进行，也可以在数据抽取过程中利用关系数据库的特性同时进行。

(1)ETL引擎中的数据转换和加工

ETL引擎中一般以组件化的办法实现数据转换。常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等。这些组件如同一条流水线上的一道道工序，它们是可插拔的，且可以任意组装，各组件之间通过数据总线共享数据。

有些ETL工具还提供了脚本支持，使得客户可以以一种编程的办法定制数据的转换和加工行为。

(2)在数据库中进行数据加工

关系数据库本身已经提供了强大的SQL、函数来支持数据的加工，如在SQL查询语句中添加where条件进行过滤，查询中重命名字段名与目的表进行映射，substr函数，case条件判断等等。下面是一个SQL查询的例子。 select ID as USERID, substr(TITLE, 1, 20) as TITLE, case when REMARK is null then ' ' else REMARK end as 内容 from

详细介绍了数据抽取的几种主要办法

TB_REMARK where ID gt; 100;

相比在ETL引擎中进行数据转换和加工，直接在SQL语句中进行转换和加工更加简单清晰，性能更高。对于SQL语句无法处理的可以交由ETL引擎处理。

2.3 数据装载

将转换和加工后的数据装载到目的库中通常是ETL过程的最后步骤。装载数据的最佳办法取决于所执行操作的类型以及需要装入多少数据。当目的库是关系数据库时，一般来说有两种装载办法：

(1)直接SQL语句进行insert、update、delete操作。

(2)采用批量装载办法，如bcp、bulk、关系数据库特有的批量装载工具或api。

大多数情况下会试用第一种办法，因为它们进行了日志记录并且是可还原的。但是，批量装载操作易于试用，并且在装入大量数据时效率较高。试用哪种数据装载办法取决于业务windows的需要。

3.主流ETL工具

ETL工具从厂商来看分为两种,一种是数据库厂商自带的ETL工具,如Oracle Database warehouse builder、Oracle Database Data Integrator。另外一种是第三方工具提供商，如Kettle。Oschina世界也有很多的ETL工具，性能各异，强弱不一。

(1)Oracle Database Data Integrator(ODI)

ODI前身是Sunopsis Active Integration Platform，在2006年底被Oracle Database购买，重新命名为Oracle Database Data Integrator，重要定位于在ETL和数据集成的场景里试用。ODI和Oracle Database原来的ETL工具OWB相比有一部分显著的特点，比如和OWB一样是ELT架构，但是比OWB支持更多的异构的数据源，ODI提供了call web service的机制，并且ODI的端口也可以暴露为web service，从而可以和SOA环境进行交互。ODI能够检测事件，一个事件可以触发ODI的一个端口流程，从而完成近乎实时的数据集成。

ODI的重要性能特点有：

a.试用CDC操作为变更数据捕获的捕获办法。

b.proxy server支持并行处理和负载均衡。

c.完善的权限控制、版本管理性能。

详细介绍了数据抽取的几种主要办法

d.支持数据质量检查，清洗和回收脏数据。

e.支持与JMS消息中间件集成。

f.支持Web Service。

(2)SQL Server Integration Services(SSIS)

SSIS是SQL Server 2005的新成员，在SQL Server的早期版本中，其实就已经有了它的雏形，那时的名称叫做数据转换服务(DTS)。在SQL Server 2005的前两个版本SQL Server 7.0和SQL Server 2000中，DTS重要集中于提取和加载。通过试用DTS，可以从任何数据源中提取数据以及将数据加载到任何数据源中。在SQL Server 2005中，对DTS进行了重新设计和改进形成了SSIS。SSIS提供了数据关于的控制流、数据流、日志、变量、event、连结管理等入门设施。控制流也称为工作流或者任务流，它更像工作流，在工作流中每个组件都是一个任务。这些任务是按预定义的顺序执行的。在任务流中可能有分支。当前任务的执行结果决定沿哪条分支前进。数据流是新的概念。数据流也称为流水线，重要处理数据转换的问题。数据流由一组预定义的转换操作组成。数据流的起点通常是数据源(源表);数据流的终点通常是数据的目的地(目标表)。可以将数据流的执行认为是一个流水线的过程，在该过程中，每一行数据都是装配线中需要处理的零件，而每一个转换都是装配线中的处理单元。SSIS的体系结构如图3.1所示。

详细介绍了数据抽取的几种主要办法

图3.1 SSIS体系结构图

4.ETL工具的选自

在数据集成中该如何选自ETL工具呢?一般来说需要策划以下几个方面：

(1)对平台的支持程度。

(2)对数据源的支持程度。

(3)抽取和装载的性能是不是较高，且对业务windows的性能影响大不大，倾入性高不高。

(4)数据转换和加工的性能强不强。

(5)是否具有管理和调度性能。

详细介绍了数据抽取的几种主要办法

(6)是否具有良好的集成性和开放性。 (责任编辑：admin)

本文来源：https://www.bwwdw.com/article/o2b1.html