kettle

 

http://blog.csdn.net/cissyring/archive/2008/05/29/2493865.aspx
文章一：ETL和Kettle简介

ETL 即数据抽取（ Extract ）、转换（ Transform ）、装载（ Load ）的过程。它是构建数据仓库的重要环节。数据仓库是面向主题的、集成的、稳定的且随时间不断变化的数据集合，用以支持经营管理中的决策制定过程。数据仓库系统中有可能存在着大量的噪声数据 , 引起的主要原因有：滥用缩写词、惯用语、数据输入错误、重复记录、丢失值、拼写变化等。即便是一个设计和规划良好的数据库系统，如果其中存在着大量的噪声数据，那么这个系统也是没有任何意义的，因为 “ 垃圾进，垃圾出 ” （ garbage in, garbage out ），系统根本就不可能为决策分析系统提供任何支持。为了清除噪声数据，必须在数据库系统中进行数据清洗。目前有不少数据清洗研究和 ETL 研究，但是如何在 ETL 过程中进行有效的数据清洗并使这个过程可视化，此方面研究不多。本文主要从两个方面阐述 ETL 和数据清洗的实现过程： ETL 的处理方式和数据清洗的实现方法。

（1）       ETL 的处理方式

本文所采用的 ETL 方法是数据库段区域中的 ETL 处理方式，它不使用外部引擎而是使用数据库作为唯一的控制点。由于源系统 SQLserver2000 是关系数据库，它的段表也是典型的关系型表。成功地将外部未修改数据载入数据库后，再在数据库内部进行转换。数据库段区域中的 ETL 处理方式执行的步骤是提取、装载、转换，即通常所说的 ELT 。这种方式的优点是为抽取出的数据首先提供一个缓冲以便于进行复杂的转换，减轻了 ETL 进程的复杂度。

（2）       ETL 过程中实现数据清洗的实现方法

首先，在理解源数据 的基础上实现数据表属性一致化。为解决源数据的同义异名和同名异义的问题，可通过元数据管理子系统，在理解源数据的同时，对不同表的属性名根据其含义重新 定义其在数据挖掘库中的名字，并以转换规则的形式存放在元数据库中，在数据集成的时候，系统自动根据这些转换规则将源数据中的字段名转换成新定义的字段 名，从而实现数据挖掘库中的同名同义。

其次，通过数据缩减，大幅度缩小数据量。由于源数据量很大，处理起来非常耗时，所以可以优先进行数据缩减，以提高后续数据处理分析效率。

最后，通过预先设定数据处理的可视化功能节点，达到可视化的进行数据清洗和数据转换的目的。针对缩减并集成后的数据，通过组合预处理子系统提供各种数据处理功能节点，能够以可视化的方式快速有效完成数据清洗和数据转换过程。

2.   KETTLE 简介

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

    说到 ETL 开源项目， Kettle 当属翘首，项目名称很有意思，水壶。按项目负责人 Matt 的说法：把各种数据放到一个壶里，然后呢，以一种你希望的格式流出。呵呵，外国人都很有联想力。

    看了提供的文档，然后对发布程序的简单试用后，可以很清楚得看到 Kettle 的四大块：

Chef ——工作 (job) 设计工具 (GUI 方式 )

Kitchen ——工作 (job) 执行器 ( 命令行方式 )

Spoon ——转换 (transform) 设计工具 (GUI 方式 )

Span ——转换 (trasform) 执行器 ( 命令行方式 )

2.1. Chef ——工作(job) 设计器

这是一个 GUI 工具，操作方式主要通过拖拖拉拉，勿庸多言，一看就会。

何谓工作？ 多个作业项，按特定的工作流串联起来，开成一项工作。正如：我的工作是软件开发。我的作业项是：设计、编码、测试！先设计，如果成功，则编码，否则继续设计，编码完成则开始设计，周而复始，作业完成。

2.1.1.    Chef 中的作业项包括：

转换 ：指定更细的转换任务，通过 Spoon 生成。通过 Field 来输入参数；

SQL ： sql 语句执行；

FTP ：下载 ftp 文件；

邮件 ：发送邮件；

检查表是否存在 ；

检查文件是否存在 ；

执行 shell 脚本 ：如 dos 命令。

批处理 ： ( 注意： windows 批处理不能有输出到控制台 ) 。

Job 包 ：作为嵌套作业使用。

JavaScript 执行 ：这个比较有意思，我看了一下源码，如果你有自已的 Script 引擎，可以很方便的替换成自定义 Script ，来扩充其功能；

SFTP ：安全的 Ftp 协议传输；

HTTP 方式的上 / 下传 。

2.1.2.    工作流

如上文所述，工作流是作业项的连接方式。分为三种：无条件，成功，失败，为了方便工作流使用， KETTLE 提供了几个辅助结点单元 ( 也可将其作为简单的作业项 ) ：

Start 单元 ：任务必须由此开始。设计作业时，以此为起点。

OK 单元 ：可以编制做为中间任务单元，且进行脚本编制，用来控制流程。

ERROR 单元 ：用途同上。

DUMMY 单元 ：什么都不做，主要是用来支持多分支的情况，文档中有例子。

2.1.3.    存储方式

支持 XML 存储，或存储到指定数据库中。

一些默认的配置 ( 如数据库存储位置…… ) ，在系统的用户目录下，单独建立了一个 .Kettle 目录，用来保存用户的这些设置。

2.1.4.    LogView ：

可查看执行日志。

2.2. 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 可以使用它的任务计划工具。

2.3. Spoon ——转换过程设计器

    GUI 工作，用来设计数据转换过程，创建的转换可以由 Pan 来执行，也可以被 Chef 所包含，作为作业中的一个作业项。

    下面简单列举一下所有的转换过程。 ( 简单描述，详细的可见 Spoon 文档 )

2.3.1.    Input-Steps ：输入步骤

l         Text file input ：文本文件输入

可以支持多文件合并，有不少参数，基本一看参数名就能明白其意图。

l         Table input ：数据表输入

实际上是视图方式输入，因为输入的是 sql 语句。当然，需要指定数据源 ( 数据源的定制方式在后面讲一下 )

l         Get system info ：取系统信息

就是取一些固定的系统环境值，如本月最后一天的时间，本机的 IP 地址之类。

l         Generate Rows ：生成多行。

这个需要匹配使用，主要用于生成多行的数据输入，比如配合 Add sequence 可以生成一个指定序号的数据列。

l         XBase Input

l         Excel Input

l         XML Input

这三个没啥可讲的，看看参数就明了。

2.3.2.    Output-Steps ： 输出步聚

l         Text file output ：文本文件输出。这个用来作测试蛮好，呵呵。很方便的看到转换的输出。

l         Table output ：输出到目的表。

l         Insert/Update ：目的表和输入数据行进行比较，然后有选择的执行增加，更新操作。

l         Update ：同上，只是不支持增加操作。

l         XML Output ：

2.3.3.    Look-up ：查找操作

l         Data Base

l         Stream

l         Procedure

l         Database join

2.3.4.    Transform  转换

l         Select values

对输入的行记录数据 的字段进行更改 ( 更改数据类型，更改字段名或删除 ) 数据类型变更时，数据的转换有固定规则，可简单定制参数。可用来进行数据表的改装。

l         Filter rows

  对输入的行记录进行 指定复杂条件 的过滤。 用途可扩充 sql 语句现有的过滤功能。但现有提供逻辑功能超出标准 sql 的不多。

l         Sort rows

对指定的列以升序或降序排序，当排序的行数超过 5000 时需要临时表。

l         Add sequence

为数据流增加一个序列，这个配合其它 Step(Generate rows, rows join) ，可以生成序列表，如日期维度表 ( 年、月、日 ) 。

l         Dummy

不做任何处理，主要用来作为分支节点。

l         Join Rows

对所有输入流做笛卡儿乘积。

l         Aggregate

聚合，分组处理

l         Group by

分组，用途可扩充 sql 语句现有的分组，聚合函数。但我想可能会有其它方式的 sql 语句能实现。

l         Java Script value

使用 mozilla 的 rhino 作为脚本语言，并提供了很多函数，用户可以在脚本中使用这些函数。

l         Row Normaliser

该步骤可以从透视表中还原数据到事实表，通过指定维度字段及其分类值，度量字段，最终还原出事实表数据。

l         Unique rows

去掉输入流中的重复行，在使用该节点前要先排序，否则只能删除连续的重复行。  

l         Calculator

提供了一组函数对列值进行运算，用该方式比用户自定义 JAVA SCRIPT 脚本速度更快。

l         Merge Rows

用于比较两组输入数据，一般用于更新后的数据重新导入到数据仓库中。

l         Add constants ：

增加常量值。

l         Row denormaliser

同 Normaliser 过程相反。

l         Row flattener

表扁平化处理，指定需处理的字段和扃平化后的新字段，将其它字段做为组合 Key 进行扃平化处理。

2.3.5.    除了上述基本节点类型外还定义了扩展节点类型  

l         SPLIT FIELDS

按指定分隔符拆分字段

l         EXECUTE SQL SCRIPT

执行 SQL 语句

l         CUBE INPUT

l         CUBE OUTPUT

2.3.6.    其它

l         存储方式： 与 Chef 相同。

l         数据源 (Connection) ；见后。

l         Hops ： setp 连接起来，形成 Hops 。

l         Plugin step types 等节点：这个没仔细看，不知如何制作 Plugin step 。

l         LogView ：可查看执行日志。

2.4. Pan ——转换的执行工具

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

2.5. 其它

Connection

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

2.6. 个人感觉：( 本人不成熟的看法)

1、  转换功能全，使用简洁。作业项丰富，流程合理。但缺少调度。

2、  java 代码，支持的数据源范围广，所以，跨平台性较好。

3、  从实际项目的角度看，和其它开源项目类似，主要还是程序员的思维，缺少与实际应用项目 ( 专业领域 ) 的更多接轨，当然，项目实施者的专注点可能在于一个平台框架，而非实际应用 ( 实际应用需要二次开发 ) 。

4、  看过了大多数源码，发现源码的可重用性不是太好 ( 缺少大粒度封装 ) ，有些关键部分好像有 Bug 。比如：个别 class 过于臃肿，线程实现的同步有问题。

5、  提供的工具有些小错，如参数的容错处理。

3.   ETL 小结

做数据仓库系统，ETL是关键的一环。说大了，ETL是数据整合解决方案，说小了，就 是倒数据的工具。回忆一下工作这么些年来，处理数据迁移、转换的工作倒还真的不少。但是那些工作基本上是一次性工作或者很小数据量，使用access、 DTS或是自己编个小程序搞定。可是在数据仓库系统中，ETL上升到了一定的理论高度，和原来小打小闹的工具使用不同了。究竟什么不同，从名字上就可以看 到，人家已经将倒数据的过程分成3个步骤，E、T、L分别代表抽取、转换和装载。

其实ETL过程就是数据流动的过程，从不同的数据源流向不同的目标数据。但在数据仓库 中，ETL有几个特点，一是数据同步，它不是一次性倒完数据就拉到，它是经常性的活动，按照固定周期运行的，甚至现在还有人提出了实时ETL的概念。二是 数据量，一般都是巨大的，值得你将数据流动的过程拆分成E、T和L。

现在有很多成熟的工具提供ETL功能，例如datastage、powermart 等，且不说他们的好坏。从应用角度来说，ETL的过程其实不是非常复杂，这些工具给数据仓库工程带来和很大的便利性，特别是开发的便利和维护的便利。但另 一方面，开发人员容易迷失在这些工具中。举个例子，VB是一种非常简单的语言并且也是非常易用的编程工具，上手特别快，但是真正VB的高手有多少？微软设 计的产品通常有个原则是“将使用者当作傻瓜”，在这个原则下，微软的东西确实非常好用，但是对于开发者，如果你自己也将自己当作傻瓜，那就真的傻了。 ETL工具也是一样，这些工具为我们提供图形化界面，让我们将主要的精力放在规则上，以期提高开发效率。从使用效果来说，确实使用这些工具能够非常快速地 构建一个job来处理某个数据，不过从整体来看，并不见得他的整体效率会高多少。问题主要不是出在工具上，而是在设计、开发人员上。他们迷失在工具中，没 有去探求ETL的本质。

可以说这些工具应用了这么长时间，在这么多项目、环境中应用，它必然有它成功之处，它 必定体现了ETL的本质。如果我们不透过表面这些工具的简单使用去看它背后蕴涵的思想，最终我们作出来的东西也就是一个个独立的job，将他们整合起来仍 然有巨大的工作量。大家都知道“理论与实践相结合”，如果在一个领域有所超越，必须要在理论水平上达到一定的高度

4.   ETL 本质

4.1. ETL 特点

ETL的过程就是数据流动的过程，从不同异构数据源流向统一的目标数据。其间，数据的 抽取、清洗、转换和装载形成串行或并行的过程。ETL的核心还是在于T这个过程，也就是转换，而抽取和装载一般可以作为转换的输入和输出，或者，它们作为 一个单独的部件，其复杂度没有转换部件高。和OLTP系统中不同，那里充满这单条记录的insert、update和select等操作，ETL过程一般 都是批量操作，例如它的装载多采用批量装载工具，一般都是DBMS系统自身附带的工具，例如Oracle SQLLoader和DB2的autoloader等。

ETL本身有一些特点，在一些工具中都有体现，下面以datastage和powermart举例来说。

1、静态的ETL单元和动态的ETL单元实例；一次转换指明了某种格式的数据如何格式 化成另一种格式的数据，对于数据源的物理形式在设计时可以不用指定，它可以在运行时，当这个ETL单元创建一个实例时才指定。对于静态和动态的ETL单 元，Datastage没有严格区分，它的一个Job就是实现这个功能，在早期版本，一个Job同时不能运行两次，所以一个Job相当于一个实例，在后期 版本，它支持multiple instances，而且还不是默认选项。Powermart中将这两个概念加以区分，静态的叫做Mapping，动态运行时叫做Session。

2、ETL元数据；元数据是描述数据的数据，他的含义非常广泛，这里仅指ETL的元数 据。主要包括每次转换前后的数据结构和转换的规则。ETL元数据还包括形式参数的管理，形式参数的ETL单元定义的参数，相对还有实参，它是运行时指定的 参数，实参不在元数据管理范围之内。

3、数据流程的控制；要有可视化的流程编辑工具，提供流程定义和流程监控功能。流程调 度的最小单位是ETL单元实例，ETL单元是不能在细分的ETL过程，当然这由开发者来控制，例如可以将抽取、转换放在一个ETL单元中，那样这个抽取和 转换只能同时运行，而如果将他们分作两个单元，可以分别运行，这有利于错误恢复操作。当然，ETL单元究竟应该细分到什么程度应该依据具体应用来看，目前 还没有找到很好的细分策略。比如，我们可以规定将装载一个表的功能作为一个ETL单元，但是不可否认，这样的ETL单元之间会有很多共同的操作，例如两个 单元共用一个Hash表，要将这个Hash表装入内存两次。

4、转换规则的定义方法；提供函数集提供常用规则方法，提供规则定义语言描述规则。

5、对数据的快速索引；一般都是利用Hash技术，将参照关系表提前装入内存，在转换时查找这个hash表。Datastage中有Hash文件技术，Powermart也有类似的Lookup功能。

4.2. ETL 类型

昨在IT-Director上阅读一篇报告，关于ETL产品分类的。一般来说，我们眼中的ETL工具都是价格昂贵，能够处理海量数据的家伙，但是这是其中的一种。它可以分成4种，针对不同的需求，主要是从转换规则的复杂度和数据量大小来看。它们包括：

1、交互式运行环境，你可以指定数据源、目标数据，指定规则，立马ETL。这种交互式 的操作无疑非常方便，但是只能适合小数据量和复杂度不高的ETL过程，因为一旦规则复杂了，可能需要语言级的描述，不能简简单单拖拖拽拽就可以的。还有数 据量的问题，这种交互式必然建立在解释型语言基础上，另外他的灵活性必然要牺牲一定的性能为代价。所以如果要处理海量数据的话，每次读取一条记录，每次对 规则进行解释执行，每次在写入一条记录，这对性能影响是非常大的。

2、专门编码型的，它提供了一个基于某种语言的程序框架，你可以不必将编程精力放在一 些周边的功能上，例如读文件功能、写数据库的功能，而将精力主要放在规则的实现上面。这种近似手工代码的性能肯定是没话说，除非你的编程技巧不过关（这也 是不可忽视的因素之一）。对于处理大数据量，处理复杂转换逻辑，这种方式的ETL实现是非常直观的。

3、代码生成器型的，它就像是一个ETL代码生成器，提供简单的图形化界面操作，让你 拖拖拽拽将转换规则都设定好，其实他的后台都是生成基于某种语言的程序，要运行这个ETL过程，必须要编译才行。Datastage就是类似这样的产品， 设计好的job必须要编译，这避免了每次转换的解释执行，但是不知道它生成的中间语言是什么。以前我设计的ETL工具大挪移其实也是归属于这一类，它提供 了界面让用户编写规则，最后生成C++语言，编译后即可运行。这类工具的特点就是要在界面上下狠功夫，必须让用户轻松定义一个ETL过程，提供丰富的插件 来完成读、写和转换函数。大挪移在这方面就太弱了，规则必须手写，而且要写成标准c++语法，这未免还是有点难为最终用户了，还不如做成一个专业编码型的 产品呢。另外一点，这类工具必须提供面向专家应用的功能，因为它不可能考虑到所有的转换规则和所有的读写，一方面提供插件接口来让第三方编写特定的插件， 另一方面还有提供特定语言来实现高级功能。例如Datastage提供一种类Basic的语言，不过他的Job的脚本化实现好像就做的不太好，只能手工绘 制job，而不能编程实现Job。

4、最后还有一种类型叫做数据集线器，顾名思义，他就是像Hub一样地工作。将这种类 型分出来和上面几种分类在标准上有所差异，上面三种更多指ETL实现的方法，此类主要从数据处理角度。目前有一些产品属于EAI（Enterprise Application Integration），它的数据集成主要是一种准实时性。所以这类产品就像Hub一样，不断接收各种异构数据源来的数据，经过处理，在实施发送到不同 的目标数据中去。

虽然，这些类看似各又千秋，特别在BI项目中，面对海量数据的ETL时，中间两种的选择就开始了，在选择过程中，必须要考虑到开发效率、维护方面、性能、学习曲线、人员技能等各方面因素，当然还有最重要也是最现实的因素就是客户的意象。

4.3. ETL 中的转换-Transication

ETL探求之一中提到，ETL过程最复杂的部分就是T，这个转换过程，T过程究竟有哪些类型呢？

4.3.1.    宏观输入输出方面

从对数据源的整个宏观处理分，看看一个ETL过程的输入输出，可以分成下面几类：

1、大小交，这种处理在数据清洗过程是常见了，例如从数据源到ODS阶段，如果数据仓 库采用维度建模，而且维度基本采用代理键的话，必然存在代码到此键值的转换。如果用SQL实现，必然需要将一个大表和一堆小表都Join起来，当然如果使 用ETL工具的话，一般都是先将小表读入内存中再处理。这种情况，输出数据的粒度和大表一样。

2、大大交，大表和大表之间关联也是一个重要的课题，当然其中要有一个主表，在逻辑 上，应当是主表Left Join辅表。大表之间的关联存在最大的问题就是性能和稳定性，对于海量数据来说，必须有优化的方法来处理他们的关联，另外，对于大数据的处理无疑会占用 太多的系统资源，出错的几率非常大，如何做到有效错误恢复也是个问题。对于这种情况，我们建议还是尽量将大表拆分成适度的稍小一点的表，形成大小交的类 型。这类情况的输出数据粒度和主表一样。

3、站着进来，躺着出去。事务系统中为了提高系统灵活性和扩展性，很多信息放在代码表 中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。大家对 Decode肯定不陌生，这是进行宽表化常见的手段之一。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽 表的粒度必定要比窄表粗一些，就粗在那个代码字段上。

4、聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒 度的过程。聚集本身其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，聚集算法的优化仍是探究的一个课题。例如是直接使用SQL的 Group by，还是先排序，在处理。

4.3.2.    微观规则

从数据的转换的微观细节分，可以分成下面的几个基本类型，当然还有一些复杂的组合情况，例如先运算，在参照转换的规则，这种基于基本类型组合的情况就不在此列了。ETL的规则是依赖目标数据的，目标数据有多少字段，就有多少条规则。

1、直接映射，原来是什么就是什么，原封不动照搬过来，对这样的规则，如果数据源字段和目标字段长度或精度不符，需要特别注意看是否真的可以直接映射还是需要做一些简单运算；

2、字段运算，数据源的一个或多个字段进行数学运算得到的目标字段，这种规则一般对数值型字段而言；

3、参照转换，在转换中通常要用数据源的一个或多个字段作为Key。

4.4. ETL 中数据质量

“不要绝对的数据准确，但要知道为什么不准确。”这是我们在构建BI系统是对数据准确 性的要求。确实，对绝对的数据准确谁也没有把握，不仅是系统集成商，包括客户也是无法确定。准确的东西需要一个标准，但首先要保证这个标准是准确的，至少 现在还没有这样一个标准。客户会提出一个相对标准，例如将你的OLAP数据结果和报表结果对比。虽然这是一种不太公平的比较，你也只好认了吧。

首先在数据源那里，已经很难保证数据质量了，这一点也是事实。在这一层有哪些可能原因导致数据质量问题？可以分为下面几类：

1、数据格式错误，例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样 处理大数据量的数据源系统，他们通常会舍弃一些数据库自身的检查机制，例如字段约束等。他们尽可能将数据检查在入库前保证，但是这一点是很难确保的。这类 情况诸如身份证号码、手机号、非日期类型的日期字段等。

2、数据一致性，同样，数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID，在例如有些代码在代码表中找不到等。

3、业务逻辑的合理性，这一点很难说对与错。通常，数据源系统的设计并不是非常严谨，例如让用户开户日期晚于用户销户日期都是有可能发生的，一个用户表中存在多个用户ID也是有可能发生的。对这种情况，有什么办法吗？

评论

1 楼 apache_tom 2011-11-19  

引用

[*]

[url][/url][flash=200,200][/flash][img][/img]

[*]

引用

[/u][b][/b][i][/i][u]

引用

[*]

[url][/url]

引用

[/u][u]

引用

引用

引用

引用

引用

引用

引用

引用

[*]

[img][/img][url][/url][flash=200,200][/flash]