ETL概述(原创)

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

本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]

评论

5 楼 czmmiao 2013-03-26  

songbin0201 写道

czmmiao 写道

songbin0201 写道

数据抽取，twitter刚开源的databus不是就在干这事？感觉比时间戳更高效简单吧，避免时间戳强制耦合进业务相关的记录表和流水表

databus我没用过，如果不用时间戳，那有什么办法实现增量更新？

据说是分析oracle和mysql的操作日志，其实自己也可以这样做，只是分析操作日志想较于时间戳，难度和成本教大

怎么感觉你自相矛盾了。。。。。。

4 楼 songbin0201 2013-03-25  

czmmiao 写道

songbin0201 写道

数据抽取，twitter刚开源的databus不是就在干这事？感觉比时间戳更高效简单吧，避免时间戳强制耦合进业务相关的记录表和流水表

databus我没用过，如果不用时间戳，那有什么办法实现增量更新？

据说是分析oracle和mysql的操作日志，其实自己也可以这样做，只是分析操作日志想较于时间戳，难度和成本教大

3 楼 dacoolbaby 2013-03-25  

清洗数据基本都是用SQL来完成的。。只不过有时候这些SQL跑在ETL工具上，有时候跑在数据库上面而已。

元数据的管理很多公司，甚至很大的公司都是用EXCEL来做的。
其实真的需要有个系统来管理一下。

2 楼 czmmiao 2013-03-25  

songbin0201 写道

数据抽取，twitter刚开源的databus不是就在干这事？感觉比时间戳更高效简单吧，避免时间戳强制耦合进业务相关的记录表和流水表

databus我没用过，如果不用时间戳，那有什么办法实现增量更新？

1 楼 songbin0201 2013-03-25  

数据抽取，twitter刚开源的databus不是就在干这事？感觉比时间戳更高效简单吧，避免时间戳强制耦合进业务相关的记录表和流水表