ETL 学习 （转）

from : http://blog.csdn.net/tiger119/article/details/1482555

对于数据仓库以及ETL的知识，我基本上是个门外汉。一切都得从头开始，记个笔记，方便自已了解学习进度。
首先，我们来了解最基本的定义：
嗯，也有人将ETL简单称为数据抽取。至少在未学习之前，领导告诉我的是，你需要做一个数据抽取的工具。
其实呢，抽取是ETL中的关键环节，顾名思义，也就将数据从不同的数据源中抓取(复制)出来。
太简单了！
上面的解释无首无尾，有点象能让你吃饱的第七个烧饼，
仔细一想，抽取是不可能单独存在，我们需要将与之关联的一些其它环节拿出来。

于是，得到ETL的定义：
将数据抽取（Extract）、转换（Transform）、清洗（Cleansing）、装载（Load）的过程。
好的，既然到了这一个层次，我们完全会进一步展开联想，引出上面这个抽象事件的前因后果，

抽取的源在哪里？
装载的目的又是什么呢？

抽取源：大多数情况下，可以认为是关系数据库，专业一点，就是事务处理系统(OLTP)。当然，广义一点，可能会是其它数据库或者是文件系统。
目的地：OK，我们希望是数据仓库。数据仓库是啥？在学习之前，它对我来说是个抽象的怪物，看过一些简单的资料之后，才了解这个怪物一点都不怪。堆积用来分析的数据的仓库。是了，是用来分析的，于是，它区别于OLTP中的数据存储。

然后，我们来看看为什么要ETL？
在我看来，有两个原因。
一：性能 将需要分析的数据从OLTP中抽离出来，使分析和事务处理不冲突。咦？这不是数据仓库的效果吗？是了，
数据仓库，大多数情况下，也就是通过ETL工具来生成地。
二：控制 用户可以完全控制从OLTP中抽离出来的数据，拥有了数据，也就拥有了一切。
嗯，OLAP分析，数据挖掘等等等……。

最后，总结一下，
从资料上看，ETL是一门大学问，对于大学问，实在有些怕怕，所以，我觉得应该停下来想一想，下一步我该干点啥？
嗯，时不我待，我没有办法一切从头开始，
是了，从应用出发，看看现在工作中，最急需的是什么？

鸭子要变成一盘菜，并不是举手将之置于油锅之劳。
OK，要将生米变为熟饭，鸭子放上大盘，一堆废话之后，我得先看看厨房里都有了一些啥？

 

ETL为数据仓库服务，数据仓库用于数据分析，数据分析属于BI系统的要干的事儿。

一般中/小型ERP系统都会有不成熟的BI系统，为啥叫做不成熟？
因为它们或者有报表分析功能，但不具有OLAP(在线分析)，或者有OLAP，但却没有数据挖掘和深度分析。或者干脆，来个大集成，直接利用第三方工具来达到相应的目的。
为什么会这样，究其原因，很多情况是因为没有自主的数据仓库，没有数据仓库，其它的做起来也就有些四不象了。而要建立数据仓库，首要的是：ETL。
于是，需求就应运而生了。

对了，BI是什么？OLAP是啥？什么又是数据挖掘？鉴于我只能解释其表面含义，我就不多说了。各位不妨找本数据仓库的书，翻翻前几页，一般就明白了。或者Google一把。

我们捡当下最流行的BI应用：OLAP来说说它与ETL的关系。
了解OLAP的人都知道，它的分析模型由事实表和维表组成。但往往OLTP系统中的数据库是为事务而建，而并不为分析而建，而为了BI去改动OLTP数据库是不现实，并且，很多情况下也基本上是不可能的(当然，有些公司把不可能的任务变成可能的，但这显然是一种很僵硬的做法)。
这时候，ETL的作用就显出来了，它可以为OLAP服务，按业务主题提取分析模型进行数据抽取。
(OLAP分析需要什么样的数据支持？可以参看一下OLAP的星型模型)。

再说说数据挖掘：
这个课题实在太大，相关的书藉有很多很多，我还得花时间慢慢去学习。简单的说，这涉及ERP业务和统计学的知识。现在我暂时还没开始相关学习，但它与ETL的关系却很明显。因为数据挖掘所要求的数据大都是高聚合的已处理的数据，所以，不管从获取难度和效率上来说，都不适合直接从OLTP中获取。
同样，需要ETL来帮忙。

因此，按本人粗浅的理解：
ETL实在是： BI系统 设计开发，项目实施 之必备良药！

有句名言讲得好：成为巨人不如站在巨人的肩膀上。
如果想对ETL有详尽的了解，不妨先了解一下现有流行的ETL工具。

 

对于MS SQL-Server的DTS，在使用MS SQL-Server2000时，有最基本的接触，但仅限时简单的数据导入/导出。当开始了解ETL时，才发现DTS原来就是ETL的应用之一。
先不谈论DTS的好坏，但它至少MS产品的特点，易学，易用。所以，要了解ETL的应用，从DTS开始，在我看来，是个不错的选择。我只是学习，所以我没有能耐去评价它，下面，只能讲DTS的功能一一列举出来，可能这些与SQL-Server的联机帮助有些重复，但对我来说，是一个加深记忆的方法。

一：概念
自完全不同源的数据析取、转换并合并到单个或多个目的。主要应用于企业数据仓库的数据抽取过程，完成从源数据库/文件到数据仓库的抽取和转换过程。
看看，这不就是ETL要做的事儿。
二：特征
2.1：基于OLE DB，很方便从不同的数据源复制和转换数据。
是了，MS总是首要支持自已的其它产品，所以，它选择了最通用的Windows的标准。
2.2：有图形化设计器，也可通过编程二次开发。
这也是MS的长项，并且能把其它的东西一股脑儿的集成进来。
2.3：执行效率高于普通ADO(SQL)操作
据说是这样地，我做过简单的比较，确实不是一个数量级，原理呢？估计可能有较多特殊处理，比如批量插入bcp，传输前的数据压缩等技术……，当然，这只是我的猜测。
2.4：可调度。
这对于Windows系统，那很容易了，可以利用现成的系统级调度。

三：详细
来说点详细的东东，当然，这里的详细并不指全部，实际上，我也只能挑出比较有代表性的功能点列举一下，要是全部，那还不如抄袭联机帮助了。
3.1：包——最小的可调度单元
包是基本任务，由工作流连接而成。包可以嵌套(包中的单个任务可以是执行另一个已存在的包)。
包是调度的直接管理对象，嵌套包保证包任务的重用。
3.2：任务——最小的工作流连接单元
分为以下几种任务：
导出和导入数据任务、转换任务、复制数据库对象、邮件/消息发送、Transact-SQL 语句或 Microsoft ActiveX® 脚本执行包、采用COM自定义任务。
其中，最常用的应该是普通转换任务。这等同于ETL中的T。
3.3： 数据连接——定义数据源和目的地
分为以下几种：
标准数据库（如 Microsoft SQL Server 2000、Microsoft Access 2000、Oracle、dBase、Paradox）；与 ODBC 数据源的 OLE DB 连接；Microsoft Excel 2000 电子表格数据；HTML 源；以及其它 OLE DB 提供程序
文件连接数据链接连接(比较特殊)
注意：SQL-Server作为Windows应用产品，DTS自然也不支持Java。
对于标准数据库是最基本的应用，易于实现。文件连接涉及格式定义，还不算复杂。
3.4：转换——数据转换
数据转换，指列进行，有多种方式：
3.4.1：简单复制方式。
3.4.2：简单转换(如，数据格式化，取字符串子串等……)
3.4.3：ActiveX脚本(实际上是vbScript和JavaScript)
3.4.4：调用自定的COM对象进行转换。
通常会采用一二方式。如果要将DTS集成到你的应用中，可以第三种方式。
3.5：工作流
工作流包括成功，失败，完成。工作流支持脚本控制。可编程控制。
工作流方式对于纯数据抽取意义不大，但是考虑到任务包含收发邮件，信息，上/下传文件，那就有必要了。当然，你也可以将这些任务单独提出到应用端执行，而不采用DTS中的任务。
3.6：元数据
元数据方式更多的留备扩展，最基础的应用是用来预定义数据表。
3.7：数据驱动的查询任务——(一种特珠的转换方式)
一种很特殊的转换方式，支持参数。这里，单独把它提出来，因为它不是那么容易理解，我花了一些时间，才将其弄明白。
3.7.1：根据源数据的特征决定之后的查询方式(指增，删，改，查询操作)。
3.7.2：支持参数，参数通过定义绑定表来关联，通过定义转换脚本，可以计算出绑定列参数。
3.7.3：转换时可以定义批量处理。
3.7.4：转换支持多阶段抽取，可以定义阶段代码，阶段分为：行转换，转换失败，插入成功，插入失败，批处理完成，抽取完成。
此种应用异常复杂，应属高级应用。简单的看，当转换任务需要根据数据源来确定动作，这是非常有用的。比如你的应用存在多表存储，就可能根据表内的时间来确定之后的动作，达到数据分多表存储的目的。
3.8： 查找查询
作为转换数据任务和数据驱动的查询任务的一种功能。
查找查询使您得以对源和目的之外的其它连接运行查询和存储过程。例如，通过使用查找查询，可以在查询过程中进行单独的连接，并在目的表中包括来自连接的数据。
作为一种扩充功能，实现更复杂的查询操作。
3.9： 多任务支持事务处理
除了顺序执行，可以进行并行执行步骤，且支持检查点回滚。
当然，任务的事务仍然依赖于数据源所在所据库是否支持事务。一般情况下，顺序执行已经可以满足抽取需求。

好了，不管对错，还是说说我的看法：
首先：DTS中抽取流程定义比较清晰，数据转换功能丰富，可视的设计工具强大，有一定的可扩展性(COM定制，脚本支持)。
但是：但学习下来，隐隐约约觉得它有MS产品的通病，就是大而全，如果进行小型应用的集成，它是很方便和够用的，但如果要在大型项目中集成DTS，它提供的二次开发方式，就显得有些散乱，缺少一个总体的框架指导。当然，这可能与它的定位有关。
对了，我看的SQL-Sever2000的DTS，对于2005，我不知有哪些方面的改进。

 

现在是一个Google的时代，而对于开发者，开源已成为最重要的参考书。对于某课题，不管你是深入研究还是初窥门径。估且google一把，勾一勾同行的成就，你必会获益良多。

说到ETL开源项目，Kettle当属翘首，因此，偶决定花点时间了解一下。
项目名称很有意思，水壶。按项目负责人Matt的说法：把各种数据放到一个壶里，然后呢，以一种你希望的格式流出。呵呵，外国人都很有联想力。
看了提供的文档，然后对发布程序的简单试用后，可以很清楚得看到Kettle的四大块：
Chef——工作(job)设计工具 (GUI方式)
Kitchen——工作(job)执行器 (命令行方式)
Spoon——转换(transform)设计工具 (GUI方式)
Span——转换(trasform)执行器 (命令行方式)
嗯，厨师已经在厨房里，勺子和盘子一应俱全，且看能做出如何的大餐？

一：Chef——工作(job)设计器
这是一个GUI工具，操作方式主要通过拖拖拉拉，勿庸多言，一看就会。
何谓工作？ 多个作业项，按特定的工作流串联起来，开成一项工作。正如：我的工作是软件开发。我的作业项是：设计、编码、测试！先设计，如果成功，则编码，否则继续设计，编码完成则开始设计，周而复始，作业完成。
来，看看Chef中的作业项：
1.1： 转换：指定更细的转换任务，通过Spoon生成。通过Field来输入参数。
1.2： SQL：sql语句执行，
1.3： FTP：下载ftp文件。
1.4： 邮件：发送邮件。
1.5： 检查表是否存在，
1.6： 检查文件是否存在，
1.7： 执行shell脚本。如：dos命令。
1.8： 批处理。(注意：windows批处理不能有输出到控制台)。
1.9： Job包。作为嵌套作业使用。
1.10：JavaScript执行。这个比较有意思，我看了一下源码，如果你有自已的Script引擎，可以很方便的替换成自定义Script，来扩充其功能。
1.11：SFTP：安全的Ftp协议传输。
1.12：HTTP方式的上/下传。

好了，看看工作流：
如上文所述，工作流是作业项的连接方式。分为三种：无条件，成功，失败。这个没啥好说的，从名字就能知道它的意图。
嗯，为了方便工作流使用，提供了几个辅助结点单元(你也可将其作为简单的作业项)：
1：Start单元，任务必须由此开始。设计作业时，以此为起点。
2：OK单元：可以编制做为中间任务单元，且进行脚本编制，用来控制流程。
3：ERROR单元：用途同上。
4：DUMMY单元：啥都不做，主要是用来支持多分支的情况。文档中有例子，不再多说。

存储方式：
支持XML存储，或存储到指定数据库中。
一些默认的配置(如数据库存储位置……)，在系统的用户目录下，单独建立了一个.Kettle目录，用来保存用户的这些设置。
LogView：可查看执行日志。

二：Kitchen——作业执行器
是一个作业执行引擎，用来执行作业。这是一个命令行执行工具，没啥可讲的，就把它的参数说明列一下。
-rep : Repository name 任务包所在存储名
-user : Repository username 执行人
-pass : Repository password 执行人密码
-job : The name of the job to launch 任务包名称
-dir : The directory (don't forget the leading / or /)
-file : The filename (Job XML) to launch
-level : The logging level (Basic, Detailed, Debug, Rowlevel, Error, Nothing) 指定日志级别
-log : The logging file to write to 指定日志文件
-listdir : List the directories in the repository 列出指定存储中的目录结构。
-listjobs : List the jobs in the specified directory 列出指定目录下的所有任务
-listrep : List the defined repositories 列出所有的存储
-norep : Don't log into the repository 不写日志
嗯，居然不支持调度。看了一下文档，建议使用操作系统提供的调度器来实现调度，比如：Windows可以使用它的任务计划工具。

三：Spoon——转换过程设计器
GUI工作，用来设计数据转换过程，创建的转换可以由Pan来执行，也可以被Chef所包含，作为作业中的一个作业项。
下面简单列举一下所有的转换过程。(简单描述，详细的可见Spoon文档)

3.1：Input-Steps：输入步骤
3.1.1：Text file input：文本文件输入，
可以支持多文件合并，有不少参数，基本一看参数名就能明白其意图。
3.1.2：Table input：数据表输入
实际上是视图方式输入，因为输入的是sql语句。当然，需要指定数据源(数据源的定制方式在后面讲一下)
3.1.3：Get system info：取系统信息
就是取一些固定的系统环境值，如本月最后一天的时间，本机的IP地址之类。
3.1.4：Generate Rows：生成多行。
这个需要匹配使用，主要用于生成多行的数据输入，比如配合Add sequence可以生成一个指定序号的数据列。
3.1.5：XBase Input：
3.1.6：Excel Input：
3.1.7：XML Input：
这三个没啥可讲的，看看参数就明了。

3.2：Output-Steps： 输出步聚
3.2.1：Text file output：文本文件输出。
这个用来作测试蛮好，呵呵。很方便的看到转换的输出。
3.2.2：Table output：输出到目的表。
3.2.3：Insert/Update：目的表和输入数据行进行比较，然后有选择的执行增加，更新操作。
3.2.4：Update：同上，只是不支持增加操作。
3.2.5：XML Output：

3.3：Look-up：查找操作
DataBase:
Stream：
Procedure：
Database join：
很简单，看看参数就明白了。

3.4：Transform 转换 (嗯，重点)
3.4.1：Select values： 对输入的行记录数据 的字段进行更改 (更改数据类型，更改字段名或删除)
数据类型变更时，数据的转换有固定规则，可简单定制参数。可用来进行数据表的改装。
3.4.2： Filter rows： 对输入的行记录进行 指定复杂条件 的过滤。
用途可扩充sql语句现有的过滤功能。但现有提供逻辑功能超出标准sql的不多。
3.4.3：Sort rows：对指定的列以升序或降序排序，当排序的行数超过5000时需要临时表。
3.4.4：Add sequence：为数据流增加一个序列，
这个配合其它Step(Generate rows, rows join)，可以生成序列表，如日期维度表(年、月、日)。
3.4.5：Dummy：不做任何处理，主要用来作为分支节点。
3.4.6：Join Rows：对所有输入流做笛卡儿乘积。
3.4.7：Aggregate：聚合，分组处理
3.4.8：Group by：分组
用途可扩充sql语句现有的分组，聚合函数。但我想可能会有其它方式的sql语句能实现。
3.4.9：Java Script value：使用mozilla的rhino作为脚本语言，并提供了很多函数，用户可以在脚本中使用这些函数。
3.4.10. Row Normaliser：该步骤可以从透视表中还原数据到事实表，
通过指定维度字段及其分类值，度量字段，最终还原出事实表数据。
3.4.11. Unique rows：去掉输入流中的重复行
在使用该节点前要先排序，否则只能删除连续的重复行。
3.4.12. Calculator：提供了一组函数对列值进行运算，
所介绍，使用该方式比用户自定义JAVA SCRIPT脚本速度更快。
3.4.13. Merge Rows：用于比较两组输入数据，一般用于更新后的数据重新导入到数据仓库中。
3.4.14. Add constants：增加常量值。
这个我没弄明白它的用法？？？
3.4.15. Row denormaliser：同Normaliser过程相反。
3.4.16. Row flattener：表扁平化处理
指定需处理的字段和扃平化后的新字段，将其它字段做为组合Key进行扃平化处理。

3.5：Extra：除了上述基本节点类型外还定义了扩展节点类型
3.5.1：SPLIT FIELDS， 按指定分隔符拆分字段
3.5.2：EXECUTE SQL SCRIPT，执行SQL语句
3.5.3：CUBE INPUT，
3.5.4：CUBE OUTPUT等。
这两个没明白是啥意思。
3.6：其它
存储方式： 与Chef相同。
数据源(Connection)；见后。
Hops：setp连接起来，形成Hops。
Plugin step types等节点：这个没仔细看，不知如何制作Plugin step。
LogView：可查看执行日志。

四：Pan——转换的执行工具
命令行执行方式，可以执行由Spoon生成的转换任务。
同样，不支持调度。
参数与Kitchen类似，可参见Pan的文档。

五：其它
Connection：
可以配置多个数据源，在Job或是Trans中使用，这意味着可以实现跨数据库的任务。
支持大多数市面上流行的数据库。

个人感觉：(本人不成熟的看法)
1：转换功能全，使用简洁。作业项丰富，流程合理。但缺少调度。
2：java代码，支持的数据源范围广，所以，跨平台性较好。
3：从实际项目的角度看，和其它开源项目类似，主要还是程序员的思维，缺少与实际应用项目(专业领域)的更多接轨，当然，项目实施者的专注点可能在于一个平台框架，而非实际应用(实际应用需要二次开发)。
4：看过了大多数源码，发现源码的可重用性不是太好(缺少大粒度封装)，有些关键部分好象有Bug。
比如：个别class过于臃肿，线程实现的同步有问题。
5：提供的工具有些小错，如参数的容错处理。
好，对Kettle有了浅浅的了解，其它的容后再说。