ETL技术研究报告.docx
- 文档编号:7103086
- 上传时间:2023-01-17
- 格式:DOCX
- 页数:18
- 大小:150.22KB
ETL技术研究报告.docx
《ETL技术研究报告.docx》由会员分享,可在线阅读,更多相关《ETL技术研究报告.docx(18页珍藏版)》请在冰豆网上搜索。
ETL技术研究报告技术研究报告1.前言前言ETL,Extraction-Transformation-Loading的缩写,中文名称为数据提取、转换和加载。
ETL工具有:
OWB(OracleWarehouseBuilder)、ODI(OracleDataIntegrator)、InformaticPowerCenter、AICloudETL、DataStage、RepositoryExplorer、Beeload、Kettle、DataSpiderETL是数据仓库中的非常重要的一环。
1.1简述简述它是承前启后的必要的一步。
相对于关系数据库,数据仓库技术没有严格的数学理论基础,它更面向实际工程应用。
所以从工程应用的角度来考虑,按着物理数据模型的要求加载数据并对数据进行一些系列处理,处理过程与经验直接相关,同时这部分的工作直接关系数据仓库中数据的质量,从而影响到联机分析处理和数据挖掘的结果的质量。
1.2在数据仓库中扮演的角色在数据仓库中扮演的角色ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成,最后加载到数据仓库或数据集市中,成为联机分析处理、数据挖掘的基础。
ETL作为BI/DW(BusinessIntelligence)的核心和灵魂,能够按照统一的规则集成并提高数据的价值,是负责完成数据从数据源向目标数据仓库转化的过程,是实施数据仓库的重要步骤。
如果说数据仓库的模型设计是一座大厦的设计蓝图,数据是砖瓦的话,那么ETL就是建设大厦的过程。
在整个项目中最难部分是用户需求分析和模型设计,而ETL规则设计和实施则是工作量最大的,约占整个项目的60%80%,这是国内外从众多实践中得到的普遍共识。
1.3ETL体系结构体系结构ETL体系结构,它体现了主流ETL产品框架的主要组成部分。
ETL是指从源系统中提取数据,转换数据为一个标准的格式,并加载数据到目标数据存储区,通常是数据仓库。
2.发展趋势发展趋势ETL系统一般不会单独作为一个项目来做,大多与数据仓库、决策支持等系统一起作为支持系统完成。
1.发展背景发展背景随着信息化进程的推进,政府机构和企事业单位对数据资源整合的需求越来越明显,越来越多的单位将数据整合纳入到下一步的重点规划中。
但面对分散在不同地区、种类繁多的异构数据库进行数据整合并非易事,首先要解决冗余、歧义等脏数据的清洗问题,仅靠手工进行不但费时费力,质量也难以保证;其次,数据的定期更新也存在困难。
如何实现业务系统数据整合,是摆在政府机构、企事业单位面前进一步提升信息化程度的最大难题。
ETL数据转换系统为数据整合提供了令人满意的解决方案。
它可以批量完成数据抽取(Extract)、清洗(Cleaning)、转换(Transformation)、装载(Loading)等任务,不但满足了用户对种类繁多的异构数据库进行整合的需求,同时可以通过增量方式进行数据的后期更新,一体化的解决了数据整合过程中遇到的种种困难。
2.发展环境发展环境目前市场上主流的国外ETL工具可以分为两大类,一类是专业的ETL厂商的产品,如AscentialDataStageXE、SagentSolution、Informatica,这类产品一般都有较完善的体系结构和久经考验的产品,产品的功能之复杂和详尽往往能令初次接触的人膛目,但其高昂的价格也会使一般用户望而却步。
另一类是整体数据仓库方案供应商或数据库提供商,如OracleWarehouseBuilder、IBMWarehouseManager等,他们在提供数据仓库存储、设计、展现工具的同时也提供相应的ETL工具,这类产品一般对自己厂商的相关产品有很好的支持并能发挥出最大效率,但结构相对封闭对其他厂商产品的支持也有限。
3.国内具体情况国内具体情况国内同类软件开发相对落后,较成熟的异构数据转换产品非常少,软件的可操作性较差,在功能、性能上都不够理想。
国内企事业单位在进行BI项目建设的时候,往往雷声大雨点小或者只是有钱单位的游戏,难道中小企业就不能做BI来为自己的决策提供帮助吗?
正宗的BI产品或者方案实在是贵,搞得众老板却步不前,或者干脆暗骂一声掉头就走。
虽然这样,信息化还是要做,于是各种各样的小系统如雨后春笋般冒出来,各有所长、各不相同、各执一词,却又如同盲人摸象,各家的优点结合起来差不多能凑个完整的解决方案!
3.ETL技术现状技术现状随着企业信息化建设的发展,巨大的投资为企业建立了众多的信息系统,以帮助企业进行内外部业务的处理和管理工作。
但是随着信息系统的增加,各自孤立工作的信息系统将会造成大量的冗余数据和业务人员的重复劳动。
企业应用集成(EAI,EnterpriseApplicationIntegration)应运而生。
EAI通过建立底层数据交换平台来联系横贯整个企业的异构系统、应用、数据源等,完成在企业内部的ERP、CRM、SCM、数据库、数据仓库,以及其它重要的内部系统之间无缝地共享和交换数据的需要。
数据集成是企业应用集成的重要环节,企业实现数据集成,可以使更多的人更充分地使用已有数据资源,减少资料收集、数据采集等重复劳动和相应费用。
ETL原本是作为构建数据仓库的一个环节,负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成,最后加载到数据仓库或数据集市中,成为联机分析处理、数据挖掘的基础。
现在也越来越多地将ETL应用于一般信息系统中数据的迁移、交换和同步。
一个简单的ETL体系结构如下图所示。
3.1ETL分类及其关键技术ETL过程中的主要环节就是数据抽取、数据转换和加工、数据装载。
为了实现这些功能,各个ETL工具一般会进行一些功能上的扩充,例如工作流、调度引擎、规则引擎、脚本支持、统计信息等。
3.2数据抽取数据抽取数据抽取是从数据源中抽取数据的过程。
实际应用中,数据源较多采用的是关系数据库。
从数据库中抽取数据一般有以下几种方式。
3.2.1全量抽取全量抽取全量抽取类似于数据迁移或数据复制,它将数据源中的表或视图的数据原封不动的从数据库中抽取出来,并转换成自己的ETL工具可以识别的格式。
全量抽取比较简单。
3.2.2增量抽取增量抽取增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据。
在ETL使用过程中。
增量抽取较全量抽取应用更广。
如何捕获变化的数据是增量抽取的关键。
对捕获方法一般有两点要求:
准确性,能够将业务系统中的变化数据按一定的频率准确地捕获到;性能,不能对业务系统造成太大的压力,影响现有业务。
目前增量数据抽取中常用的捕获变化数据的方法有:
a.触发器:
在要抽取的表上建立需要的触发器,一般要建立插入、修改、删除三个触发器,每当源表中的数据发生变化,就被相应的触发器将变化的数据写入一个临时表,抽取线程从临时表中抽取数据,临时表中抽取过的数据被标记或删除。
触发器方式的优点是数据抽取的性能较高,缺点是要求业务表建立触发器,对业务系统有一定的影响。
b.时间戳:
它是一种基于快照比较的变化数据捕获方式,在源表上增加一个时间戳字段,系统中更新修改表数据的时候,同时修改时间戳字段的值。
当进行数据抽取时,通过比较系统时间与时间戳字段的值来决定抽取哪些数据。
有的数据库的时间戳支持自动更新,即表的其它字段的数据发生改变时,自动更新时间戳字段的值。
有的数据库不支持时间戳的自动更新,这就要求业务系统在更新业务数据时,手工更新时间戳字段。
同触发器方式一样,时间戳方式的性能也比较好,数据抽取相对清楚简单,但对业务系统也有很大的倾入性(加入额外的时间戳字段),特别是对不支持时间戳的自动更新的数据库,还要求业务系统进行额外的更新时间戳操作。
另外,无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。
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处理的数据源除了关系数据库外,还可能是文件,例如txt文件、excel文件、xml文件等。
对文件数据的抽取一般是进行全量抽取,一次抽取前可保存文件的时间戳或计算文件的MD5校验码,下次抽取时进行比对,如果相同则可忽略本次抽取。
3.3数据转换和加工数据转换和加工从数据源中抽取的数据不一定完全满足目的库的要求,例如数据格式的不一致、数据输入错误、数据不完整等等,因此有必要对抽取出的数据进行数据转换和加工。
数据的转换和加工可以在ETL引擎中进行,也可以在数据抽取过程中利用关系数据库的特性同时进行。
3.3.1ETL引擎中的数据转换和加工引擎中的数据转换和加工ETL引擎中一般以组件化的方式实现数据转换。
常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等。
这些组件如同一条流水线上的一道道工序,它们是可插拔的,且可以任意组装,各组件之间通过数据总线共享数据。
有些ETL工具还提供了脚本支持,使得用户可以以一种编程的方式定制数据的转换和加工行为。
3.3.2在数据库中进行数据加工在数据库中进行数据加工关系数据库本身已经提供了强大的SQL、函数来支持数据的加工,如在SQL查询语句中添加where条件进行过滤,查询中重命名字段名与目的表进行映射,substr函数,case条件判断等等。
下面是一个SQL查询的例子。
selectIDasUSERID,substr(TITLE,1,20)asTITLE,casewhenREMARKisnullthenelseREMARKendasCONTENTfromTB_REMARKwhereID100;相比在ETL引擎中进行数据转换和加工,直接在SQL语句中进行转换和加工更加简单清晰,性能更高。
对于SQL语句无法处理的可以交由ETL引擎处理。
3.4数据装载数据装载将转换和加工后的数据装载到目的库中通常是ETL过程的最后步骤。
装载数据的最佳方法取决于所执行操作的类型以及需要装入多少数据。
当目的库是关系数据库时,一般来说有两种装载方式。
3.4.1SQL装载装载直接SQL语句进行insert、update、delete操作。
3.4.2采用批量装载方法采用批量装载方法.如bcp、bulk、关系数据库特有的批量装载工具或api。
大多数情况下会使用第一种方法,因为它们进行了日志记录并且是可恢复的。
但是,批量装载操作易于使用,并且在装入大量数据时效率较高。
使用哪种数据装载方法取决于业务系统的需要。
4ETL产品分析产品分析ETL工具从厂商来看分为两种,一种是数据库厂商自带的ETL工具,如Oraclewarehousebuilder、OracleDataIntegrator。
另外一种是第三方工具提供商,如Kettle。
开源世界也有很多的ETL工具,功能各异,强弱不一。
4.1主流主流ETL工具介绍工具介绍ETL工具从厂商来看分为两种,一种是数据库厂商自带的ETL工具,如Oraclewarehousebuilder、OracleDataIntegrator。
另外一种是第三方工具提供商,如Kettle。
开源世界也有很多的ETL工具,功能各异,强弱不一。
4.1.1OracleDataIntegrator(ODI)ODI前身是SunopsisActiveIntegrationPlatform,在2006年底被Oracle收购,重新命名为OracleDataIntegrator,主要定位于在ETL和数据集成的场景里使用。
ODI和Oracle原来的ETL工具OWB相比有一些显著的特点,比如和OWB一样是ELT架构,但是比OWB支持更多的异构的数据源,ODI提供了callwebservice的机制,并且ODI的接口也可以暴露为webservice,从而可以和SOA环境进行交互。
ODI能够检测事件,一个事件可以触发ODI的一个接口流程,从而完成近乎实时的数据集成。
ODI的主要功能特点有:
a.使用CDC作为变更数据捕获的捕获方式。
b.代理支持并行处理和负载均衡。
c.完善的权限控制、版本管理功能。
d.支持数据质量检查,清洗和回收脏数据。
e.支持与JMS消息中间件集成。
f.支持WebService。
4.1.2SQLServerIntegrationServices(SSIS)SSIS是SQLServer2005的新成员,在SQLServer的早期版本中,其实就已经有了它的雏形,那时的名称叫做数据转换服务(DTS)。
在SQLServer2005的前两个版本SQLServer7.0和SQLServer2000中,DTS主要集中于提取和加载。
通过使用DTS,可以从任何数据源中提取数据以及将数据加载到任何数据源中。
在SQLServer2005中,对DTS进行了重新设计和改进形成了SSIS。
SSIS提供了数据相关的控制流、数据流、日志、变量、event、连接管理等基础设施。
控制流也称为工作流或者任务流,它更像工作流,在工作流中每个组件都是一个任务。
这些任务是按预定义的顺序执行的。
在任务流中可能有分支。
当前任务的执行结果决定沿哪条分支前进。
数据流是新的概念。
数据流也称为流水线,主要解决数据转换的问题。
数据流由一组预定义的转换操作组成。
数据流的起点通常是数据源(源表);数据流的终点通常是数据的目的地(目标表)。
可以将数据流的执行认为是一个流水线的过程,在该过程中,每一行数据都是装配线中需要处理的零件,而每一个转换都是装配线中的处理单元。
SSIS的体系结构如图3.1所示。
图3.1SSIS体系结构图4.2ETL工具的选择工具的选择在数据集成中该如何选择ETL工具呢?
一般来说需要考虑以下几个方面:
(1)对平台的支持程度。
(2)对数据源的支持程度。
(3)抽取和装载的性能是不是较高,且对业务系统的性能影响大不大,倾入性高不高。
(4)数据转换和加工的功能强不强。
(5)是否具有管理和调度功能。
(6)是否具有良好的集成性和开放性5.ETL的本质的本质做数据仓库系统,ETL是关键的一环。
说大了,ETL是数据整合解决方案,说小了,就是倒数据的工具。
回忆一下工作这么些年来,处理数据迁移、转换的工作倒还真的不少。
但是那些工作基本上是一次性工作或者很小数据量,使用access、DTS或是自己编个小程序搞定。
可是在数据仓库系统中,ETL上升到了一定的理论高度,和原来小打小闹的工具使用不同了。
究竟什么不同,从名字上就可以看到,人家已经将倒数据的过程分成3个步骤,E、T、L分别代表抽取、转换和装载。
5.1ETL本质一本质一(元数据元数据)ETL的过程就是数据流动的过程,从不同异构数据源流向统一的目标数据。
其间,数据的抽取、清洗、转换和装载形成串行或并行的过程。
ETL的核心还是在于T这个过程,也就是转换,而抽取和装载一般可以作为转换的输入和输出,或者,它们作为一个单独的部件,其复杂度没有转换部件高。
和OLTP系统中不同,那里充满这单条记录的insert、update和select等操作,ETL过程一般都是批量操作,例如它的装载多采用批量装载工具,一般都是DBMS系统自身附带的工具,例如OracleSQLLoader和DB2的autoloader等。
ETL本身有一些特点,在一些工具中都有体现,下面以datastage和powermart举例来说。
5.1.1、静态的、静态的ETL单元和动态的单元和动态的ETL单元实例单元实例一次转换指明了某种格式的数据如何格式化成另一种格式的数据,对于数据源的物理形式在设计时可以不用指定,它可以在运行时,当这个ETL单元创建一个实例时才指定。
对于静态和动态的ETL单元,Datastage没有严格区分,它的一个Job就是实现这个功能,在早期版本,一个Job同时不能运行两次,所以一个Job相当于一个实例,在后期版本,它支持multipleinstances,而且还不是默认选项。
Powermart中将这两个概念加以区分,静态的叫做Mapping,动态运行时叫做Session。
5.1.2、ETL元数据元数据元数据是描述数据的数据,他的含义非常广泛,这里仅指ETL的元数据。
主要包括每次转换前后的数据结构和转换的规则。
ETL元数据还包括形式参数的管理,形式参数的ETL单元定义的参数,相对还有实参,它是运行时指定的参数,实参不在元数据管理范围之内。
5.1.3、数据流程的控制、数据流程的控制要有可视化的流程编辑工具,提供流程定义和流程监控功能。
流程调度的最小单位是ETL单元实例,ETL单元是不能在细分的ETL过程,当然这由开发者来控制,例如可以将抽取、转换放在一个ETL单元中,那样这个抽取和转换只能同时运行,而如果将他们分作两个单元,可以分别运行,这有利于错误恢复操作。
当然,ETL单元究竟应该细分到什么程度应该依据具体应用来看,目前还没有找到很好的细分策略。
比如,我们可以规定将装载一个表的功能作为一个ETL单元,但是不可否认,这样的ETL单元之间会有很多共同的操作,例如两个单元共用一个Hash表,要将这个Hash表装入内存两次。
5.1.4、转换规则的定义方法、转换规则的定义方法提供函数集提供常用规则方法,提供规则定义语言描述规则。
5.1.5、对数据的快速索引、对数据的快速索引一般都是利用Hash技术,将参照关系表提前装入内存,在转换时查找这个hash表。
Datastage中有Hash文件技术,Powermart也有类似的Lookup功能。
5.2探求探求ETL本质二本质二(分类分类)昨在IT-Director上阅读一篇报告,关于ETL产品分类的。
一般来说,我们眼中的ETL工具都是价格昂贵,能够处理海量数据的家伙,但是这是其中的一种。
它可以分成4种,针对不同的需求,主要是从转换规则的复杂度和数据量大小来看。
5.2.1、交互式运行环境、交互式运行环境你可以指定数据源、目标数据,指定规则,立马ETL。
这种交互式的操作无疑非常方便,但是只能适合小数据量和复杂度不高的ETL过程,因为一旦规则复杂了,可能需要语言级的描述,不能简简单单拖拖拽拽就可以的。
还有数据量的问题,这种交互式必然建立在解释型语言基础上,另外他的灵活性必然要牺牲一定的性能为代价。
所以如果要处理海量数据的话,每次读取一条记录,每次对规则进行解释执行,每次在写入一条记录,这对性能影响是非常大的。
5.2.2、专门编码型的、专门编码型的它提供了一个基于某种语言的程序框架,你可以不必将编程精力放在一些周边的功能上,例如读文件功能、写数据库的功能,而将精力主要放在规则的实现上面。
这种近似手工代码的性能肯定是没话说,除非你的编程技巧不过关(这也是不可忽视的因素之一)。
对于处理大数据量,处理复杂转换逻辑,这种方式的ETL实现是非常直观的。
5.2.3、代码生成器型的、代码生成器型的它就像是一个ETL代码生成器,提供简单的图形化界面操作,让你拖拖拽拽将转换规则都设定好,其实他的后台都是生成基于某种语言的程序,要运行这个ETL过程,必须要编译才行。
Datastage就是类似这样的产品,设计好的job必须要编译,这避免了每次转换的解释执行,但是不知道它生成的中间语言是什么。
以前我设计的ETL工具大挪移其实也是归属于这一类,它提供了界面让用户编写规则,最后生成C+语言,编译后即可运行。
这类工具的特点就是要在界面上下狠功夫,必须让用户轻松定义一个ETL过程,提供丰富的插件来完成读、写和转换函数。
大挪移在这方面就太弱了,规则必须手写,而且要写成标准c+语法,这未免还是有点难为最终用户了,还不如做成一个专业编码型的产品呢。
另外一点,这类工具必须提供面向专家应用的功能,因为它不可能考虑到所有的转换规则和所有的读写,一方面提供插件接口来让第三方编写特定的插件,另一方面还有提供特定语言来实现高级功能。
例如Datastage提供一种类Basic的语言,不过他的Job的脚本化实现好像就做的不太好,只能手工绘制job,而不能编程实现Job。
5.2.4、最后还有一种类型叫做数据集线器、最后还有一种类型叫做数据集线器顾名思义,他就是像Hub一样地工作。
将这种类型分出来和上面几种分类在标准上有所差异,上面三种更多指ETL实现的方法,此类主要从数据处理角度。
目前有一些产品属于EAI(EnterpriseApplicationIntegration),它的数据集成主要是一种准实时性。
所以这类产品就像Hub一样,不断接收各种异构数据源来的数据,经过处理,在实施发送到不同的目标数据中去。
虽然,这些类看似各又千秋,特别在BI项目中,面对海量数据的ETL时,中间两种的选择就开始了,在选择过程中,必须要考虑到开发效率、维护方面、性能、学习曲线、人员技能等各方面因素,当然还有最重要也是最现实的因素就是客户的意象。
5.3探求探求ETL本质三本质三(转换转换)ETL探求之一中提到,ETL过程最复杂的部分就是T,这个转换过程,T过程究竟有哪些类型呢?
5.3.1、宏观输入输出、宏观输入输出从对数据源的整个宏观处理分,看看一个ETL过程的输入输出,可以分成下面几类:
1、大小交,这种处理在数据清洗过程是常见了,例如从数据源到ODS阶段,如果数据仓库采用维度建模,而且维度基本采用代理键的话,必然存在代码到此键值的转换。
如果用SQL实现,必然需要将一个大表和一堆小表都Join起来,当然如果使用ETL工具的话,一般都是先将小表读入内存中再处理。
这种情况,输出数据的粒度和大表一样。
2、大大交,大表和大表之间关联也是一个重要的课题,当然其中要有一个主表,在逻辑上,应当是主表LeftJoin辅表。
大表之间的关联存在最大的问题就是性能和稳定
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ETL 技术研究 报告
![提示](https://static.bdocx.com/images/bang_tan.gif)