ETL概述(原创)

标签: etl 原创 | 发表时间:2013-03-25 10:00 | 作者:
出处:http://www.iteye.com

ETL概述
ETL,Extraction- Transformation-Loading的缩写,即数据抽取(Extract)、转换(Transform)、装载(Load)的过程,它是构建数 据仓库的重要环节。ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为 企业的决策提供分析依据。ETL是BI项目重要的一个环节。通过ETL,我们可以基于源系统中的数据来生成数据仓库。ETL为我们搭建了OLTP系统和 OLAP系统之间的桥梁,是数据从源系统流入数据仓库的通道。通常情况下,在BI项目中ETL会花掉整个项目的1/3的时间,ETL设计的好坏直接关接到 BI项目的成败。

ETL架构

创建ETL系统的时候,头脑里应并存的两条主线:规划&设计主线和数据流主线
规划&设计主线:需求和实现===〉架构===〉系统实施===〉测试和发布
数据流主线:抽取===〉清洗====〉规格化====〉提交
架构的时候,我们必须作出关于创建ETL系统创建方法的主要的决定,其中包括:

手工编码还是使用ETL工具ETL 实现方式一般有以下3种:一种是借助ETL工具(如Oracle的 OWB、SQLServer2000的DTS、SQLServer2005的SSIS服务、Informatic等)实现,一种是SQL方式实现,另外一 种是ETL工具和SQL相结合。前两种方法各有各的优缺点,借助工具可以快速的建立起ETL工程,屏蔽了复杂的编码任务,提高了速度,降低了难度,但是缺 少灵活性。SQL的方法优点是灵活,提高ETL运行效率,但是编码复杂,对技术要求比较高。第三种是综合了前面二种的优点,会极大地提高ETL的开发速度 和效率。

批处理式数据流还是流式数据流
ETL 系统的标准架构是从数据源中周期性的以批处理的方式进行抽去数据,流经整个系统,最后以批处理的方式对最终用户表进行批量更新。大部分数据仓库主要基于此 种架构方式。如果数据仓库的加载的实时性变得很急迫,批处理的方式就会被打破,替代的方法就是流式数据流,记录级数据从原系统不停的流向最终用户数据库和 屏幕。
批处理到流处理方式的转变会改变所有的一切,尽管必须也有抽取,清晰,转换和提交步骤,但是这些步骤必须经过修改,以便适应实时性记录处理需求,尤其是对于快速流方式而言,很多关于数据到达甚至参照完整性方面的常规假设都必须进行修改。

水平任务依赖还是垂直任务依赖水平方式组织任务流是指每个最终的数据库加载相互独立运行,因此,如果有订货和配送两项任务,这两项数据库加载任务会相互独立运行,这通常意味着抽取,清洗,转换和提交的步骤在两个工作流之间是非同步的。
垂直方式任务流会对多个离散的作业进行同步,这样最终的数据库加载会同步进行。尤其是多个系统使用共同的维表的时候,比如客户或者供应商等,之前的步骤一定要同步,这样,如果之前的步骤没有完全执行完,后面的步骤,比如转换或者提交就不会往下进行。

恢复和重启

从 一开始建立ETL系统的时候,就应当考虑如何保证系统从非正常结束状态下的恢复和重 启能力。比如有一个ETL作业是从全部的产品种类中抽取某个品牌产品的销售业绩,这样的任务不允许执行两次。在设计每一个ETL作业时都需要这样来考虑问 题,因为每个作业迟早都会出现非正常终止或者错误地执行多次的情况。无论如何,必须想办法防止发生这种情况。 

元数据

来 自于关系型数据库表和数据模型设计工具的元数据比较容易获取,但这些元数据可能只占系统全部元数据的25%。还有25%的元数据会在数据清洗过程中产生。 对于ETL小组而言,最大的元数据难题是在哪里以及以何种方式存储流程信息。ETL工具的一个重要的优势在于它们能够自动维护流程元数据。如果是使用手写 编写ETL系统,用户则必须构建流程元数据的中央资料库。

初次之外,自动调度、异常处理、质量控制和安全也是ETL架构设计中不可忽视的环节。
ETL过程

抽取

这 一部分需要在调研阶段做大量的工作,首先要搞清楚数据是从几个业务系统中来,各个业务系统的数据库服务器运行什么DBMS,是否存在手工数据,手工数据量 有多大,是否存在非结构化的数据等等,当收集完这些信息之后才可以进行数据抽取的设计。源系统的原始数据在进行大的转换之前通常直接写入到磁盘。来自于结 构化源系统的数据(比如IMS 数据库,或者XML数据集)在这一步中经常写入到文本文件或者关系型数据库表中。这使得最初的抽取尽可能简单和快速,
对于源数据的不同来源,有以下不同的实现方法
1、对于与存放DW的数据库系统相同的数据源处理方法
这一类数据源在设计上比较容易。一般情况下,DBMS(SQLServer、Oracle)都会提供数据库链接功能,在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select语句直接访问。
2、对于与DW数据库系统不同的数据源的处理方法
对 于这一类数据源,一般情况下也可以通过ODBC的方式建立数据库链接——如 SQLServer和Oracle之间。如果不能建立数据库链接,可以有两种方式完成,一种是通过工具将源数据导出成.txt或者是.xls文件,然后再 将这些源系统文件导入到ODS(Operating Data Source)中。另外一种方法是通过程序接口来完成。
3、对于文件类型数据源(.txt,.xls),可以利用数据库工具将这些数据导入到指定的数据库,然后从指定的数据库中抽取。或者还可以借助工具实现,如SQLServer2005的SSIS服务的平面数据源和平面目标等组件导入ODS中去。
4、增量更新的问题

对于数据量大的系统,必须考虑增量抽取。典型的做法是通过在ODS上建立时间戳以作为抽取记录,那么下次抽取时只要对比时间戳的抽取记录即可判断出增量数据。
数据的清洗转换
数据清洗

数据清洗的任务是过滤那些不符合要求的数据,将过滤的结果交给业务主管部门,确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。

大 多数情况下,源系统可接受的数据质量程度依据数据仓库要求的质量而不同。数据质量的处理可能包括几个独立的步骤,包括有效值检测(如是否是已有的邮政编 码?是否在有效值范围内?)、一致性检测(如邮政编码与城市代码是否一致?)、删除重复记录(如是否有同一个客户出现两次而相关的属性略有不同?)、检测 是否有复杂的业务规则和过程需要增强(如白金客户是否有相关的信用状态?)等等。数据的清洗转换可能需要人为的干预和判断。数据清洗步骤的结果往往半永久 保存,因为需要的转换往往难度非常大,并且是不可逆的。另外,清洗过的数据是否需要返回到源系统以提高数据质量,从而减少抽取时可能发生的问题呢?这是个 很有趣的问题。即使清洗过的数据不能物理返回到源系统,也应当具备数据异常报告机制以提高源系统的质量。这些数据的问题在最终的商务智能应用中也是非常重 要的。
数据转换
数据转换的任务主要进行不一致的数据转换、数据粒度的转换,以及一些商务规则的计算。
1、不一致数据转换:这个过程是一个整合的过程,将不同业务系统的相同类型的数据统一,比如同一个供应商在结算系统的编码是XX0001,而在CRM中编码是YY0001,这样在抽取过来之后统一转换成一个编码。
2、数据粒度的转换:业务系统一般存储非常明细的数据,而数据仓库中数据是用来分析的,不需要非常明细的数据。一般情况下,会将业务系统数据按照数据仓库粒度进行聚合。
3、商务规则的计算:不同的企业有不同的业务规则、不同的数据指标,这些指标有的时候不是简单的加加减减就能完成,这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中,以供分析使用。
数据装载
数 据装载也叫提交。后台任务的终点就是准备好数据以方便查询。这一步骤中至关重要的是将数据物理地组织成简单、对称的框架模型,我们称之为维度模型,或者星 型模型。这种框架大大地降低了查询时间,简化了开发过程。许多查询工具都需要维度框架,也是构建OLAP立方体的必要的基础。
ETL日志和警告发生
ETL 日志分为三类。一类是执行过程日志,这一部分日志是在ETL执行过程中每执行一步的记录,记录每次运行每一步骤的起始时间,影响了多少行数据,流水账形 式。一类是错误日志,当某个模块出错的时候写错误日志,记录每次出错的时间、出错的模块以及出错的信息等。第三类日志是总体日志,只记录ETL开始时间、 结束时间是否成功信息。如果使用ETL工具,ETL工具会自动产生一些日志,这一类日志也可以作为ETL日志的一部分。记录日志的目的是随时可以知道 ETL运行情况,如果出错了,可以知道哪里出错。
如果ETL出错了,不仅要形成ETL出错日志,而且要向系统管理员发送警告。发送警告的方式多种,一般常用的就是给系统管理员发送邮件,并附上出错的信息,方便管理员排查错误。
ETL数据加载模式:
1、 完全刷新:数据仓库数据表中只包括最新的数据,每次加载均删除原有数据,然后完全加载最新的源数据。这种模式下,数据抽取程序抽取源数据中的所有记 录,在加载前,将目标数据表清空,然后加载所有记录。为提高删除数据的速度,一般是采用Truncate清空数据表。如本系统中的入库当前信息表采用此种 模式。
2、镜像增量:源数据中的记录定期更新,但记录中包括记录时间字段,源数据中保存了数据历史的记录,ETL可以通过记录时间将增量数据从源数据抽取出来以附加的方式加载到数据仓库中,数据的历史记录也会被保留在数据仓库中
3、事件增量:每一个记录是一个新的事件,相互之间没有必然的联系,新记录不是对原有记录数值的变更,记录包括时间字段,可以通过时间字段将新增数据抽取出来加载到数据库中。
4、 镜像比较:数据仓库数据具有生效日期字段以保存数据的历史信息,而源数据不保留历史并且每天都可能被更新。因此,只能将新的镜像数据与上次加载的数据 的镜像进行比较,找出变更部分,更新历史数据被更新记录的生效终止日期,并添加变更后的数据。大多数源数据中需保存历史信息的维表。

 

参考至:《The Data Warehouse ETL Toolkit》Ralph Kimball著

                  http://www.cnblogs.com/elock/archive/2009/09/04/1560565.html

                  http://www.cnblogs.com/honkcal/archive/2012/09/11/2678631.html

本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [etl 原创] 推荐:

ETL概述(原创)

- - ITeye博客
ETL,Extraction- Transformation-Loading的缩写,即数据抽取(Extract)、转换(Transform)、装载(Load)的过程,它是构建数 据仓库的重要环节. ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为 企业的决策提供分析依据.

美图离线ETL实践

- - SegmentFault 最新的文章
美图收集的日志需要通过 ETL 程序清洗、规整,并持久化地落地于 HDFS / Hive,便于后续的统一分析处理. ETL 即 Extract-Transform-Load,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程. ETL 一词较常用在数据仓库,但其对象并不限于数据仓库.

CloverETL 3.5.0 发布,开源的 ETL 框架

- - 开源中国社区最新新闻
CloverETL 3.5.0 发布,此版本包括了之前两个里程碑版本的特性,更新内容如下:. 在社区版添加了 Hadoop 连接. ETL 是一切数据仓库应用的基础. CloverETL 是一个基于 Java 的开源的 ETL 框架,同时还包含了一个 ETL设计器——.

Kettle环境变量在ETL工程上的应用

- - 行业应用 - ITeye博客
    好久没更新博文,最近临近离职,事情少了,有空停下来总结总结这段时间的东西.     今天先记录下Kettle环境变量的应用.     一个好的环境变量的使用,在业务变更或者环境变动后,只需要很少工作量的修改即可完成. 我们把变量分成系统环境变量和业务变量,系统环境变量是指那些在ETL中指定的系统级资源配置,如数据库信息、日志文件路径等等,这些变量一旦设定,不会经常变动;业务变量指跟当前ETL执行处理的内容有关,如日期、产品ID等等,跟运行时有关.

【转】ETL中用到的一些基本技术概论

- - 行业应用 - ITeye博客
在公司做了几年的数据,跟数据打了不少的交道. 对软件的理解,也慢慢的偏向于对数据的流转上,虽然用户体验UI也占据着很大的地位. 之前答应过坛子里的朋友,准备写关于写金蝶的系列文章. 时间恍惚,匆匆几年就这样过去了. 如今,笔者历经几年,从一个大男孩,变成了美丽女孩的丈夫. 曾今,朋友说到:有些人写代码是为了一辈子都去写代码,也有些人写代码是为了不去写代码.

ETL工具Pentaho Kettle的transformation和job集成

- - CSDN博客推荐文章
Kettle是一款国外开源的etl工具,纯java编写,数据抽取高效稳定(数据迁移工具). Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制. 2.1.       transformation实现解析.

基于Hadoop生态圈的数据仓库实践 —— ETL(一)

- - CSDN博客推荐文章
一、使用Sqoop抽取数据.         Sqoop是一个在Hadoop与结构化数据存储(如关系数据库)之间高效传输大批量数据的工具. 它在2012年3月被成功孵化,现在已是Apache的顶级项目. Sqoop有Sqoop1和Sqoop2两代,Sqoop1最后的稳定版本是1.4.6,Sqoop2最后版本是1.99.6.

批处理ETL已经消亡,Apache Kafka才是数据处理的未来吗?

- -
最近的一些数据发展趋势推动传统的批处理抽取-转换-加载(ETL)架构发生了巨大的变化:数据平台要在整个企业范围内运行;数据源的类型变得更多;流数据得到了普遍性增长. 在实时ETL方面,早期采用的方式是企业应用集成(EAI),但是这里所用的技术通常是不可扩展的. 这给传统的数据集成带来了两难的选择:实时但不可扩展,或者可扩展但采用的是批处理方案.

PL/SQL动态SQL(原创)

- - ITeye博客
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一. 很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成. 再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页.

将原创进行到底

- - 杨晖的博客-TV2.0的实验田
——来源《投资者报》 记者 占昕.     “六年前的某一天,杨晖来到我当时工作的外滩中心楼下的咖啡厅,她对我讲她要办一个叫《波士堂》的电视节目. 俺当时心里咕嘟着,‘她也太想当boss了吧’. ”这是唯众六周年晚宴,睿狮广告传播的首席战略官沈柏屏在留言卡片上写下的一段话. 或许你尚来不及知道她的名字,但你一定知道《波士堂》、《谁来一起午餐》、《开讲啦》、《这会儿不上班》等风靡全国的电视节目.