点击上方蓝色字体，选择“设为星标”

回复”资源“获取更多资源

大数据技术与架构

点击右侧关注，大数据开发领域最强公众号！

大数据真好玩

点击右侧关注，大数据真好玩！

‍‍‍‍‍‍‍‍‍‍‍‍‍目录

一、ETL基础

1. 数据仓库架构中的ETL

2. 数据抽取

（1）逻辑抽取

（2）物理抽取

（3）变化数据捕获

3. 数据转换

4. 数据装载

5. 开发ETL系统的方法

二、ETL工具

1. ETL工具的产生

2. ETL工具的功能

（1）连接

（2）平台独立

（3）数据规模

（4）设计灵活性

（5）复用性

（6）扩展性

（7）数据转换

（8）测试和调试

（9）血统和影响分析

（10）日志和审计

三、Kettle简介

1. Kettle设计原则

2. 转换

（1）步骤

（2）转换的跳

（3）并行

（4）数据行

（5）数据类型转换

3. 作业

（1）作业项

（2）作业的跳

（3）多路径和回溯

（4）并行执行

（5）作业项结果

4. 数据库连接

（1）一般选项

（2）特殊选项

5. 连接与事务

6. 元数据与资源库

7. 工具

（1）Spoon

（2）Kitchen和Pan

（3）Carte

8. 虚拟文件系统

四、小结 ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍

我在2017年写了一本名为《Hadoop构建数据仓库实践》的书。在这本书中，较为详细地讲解了如何利用Hadoop（Cloudera's Distribution Including Apache Hadoop，CDH）生态圈组件构建传统数据仓库。例如，使用Sqoop从关系数据库全量或增量抽取数据到Hadoop系统，使用Hive进行数据转换和装载处理等等。作为进阶，书中还说明了数据仓库技术中的渐变维、代理键、角色扮演维度、层次维度、退化维度、无事实事实表、迟到事实、累计度量等常见问题在Hadoop上的处理。它们都是通过Hive SQL来实现的，其中有些SQL语句逻辑复杂，可读性也不是很好。

对于那些不太熟悉SQL语言的大数据开发人员来说，有没有可能使用一种GUI（Graphical User Interface，图形用户界面）工具实现上述所有功能呢？下面我就准备开辟一个专题，通过一系列文章，介绍并演示如何用Kettle完成这一工作。主要内容包括：
（一）ETL与Kettle的基本概念
（二）Kettle及其使用环境的安装与配置
（三）Kettle对Hadoop的支持
（四）建立ETL示例模型
（五）数据抽取
（六）数据转换与装载
（七）定期自动执行ETL作业
（八）维度表技术（增加列、维度子集、角色扮演维度、层次维度、退化维度、杂项维度、维度合并、分段维度） 
（九）事实表技术（周期快照、累计维度、无事实的事实表、迟到的事实、累积度量）
（十）Kettle数据分发与多线程
（十一）Kettle集群与数据分片
（十二）Kettle元数据管理

对于每一种技术，先要理解相关的概念和它之所以出现的原因，这对于我们继续深入学习其技术细节大有裨益。源于一贯的学习方法和习惯，让我们还是先从基本概念开始吧。

一、ETL基础

ETL一词是Extract、Transform、Load三个英文单词的首字母缩写，中文意为抽取、转换、装载。

• 抽取——从操作型数据源获取数据。

• 转换——转换数据，使之转变为适用于查询和分析的形式和结构。

• 装载——将转换后的数据导入到最终的目标数据仓库。

ETL是建立数据仓库最重要的处理过程，也是最体现工作量的环节，一般会占到整个数据仓库项目工作量的一半以上。建立一个数据仓库，就是要把来自于多个异构的源系统的数据整合在一起，放置于一个集中的位置用于数据分析。如果一开始这些源系统数据就是兼容的当然最好，但实际情况往往不是这样。ETL系统的工作就是要把异构的数据转换成同构的。如果没有ETL，很难对异构数据进行程序化的分析。

1. 数据仓库架构中的ETL

可以把数据仓库架构理解成构成数据仓库的组件及其之间的关系，那么就有了下面的数据仓库架构图。

图1-1 数据仓库架构

图1-1中显示的整个数据仓库环境包括操作型系统和数据仓库系统两大部分。操作型系统的数据由各种形式的业务数据组成，这其中可能包含关系数据库、TXT或CSV文件、HTML或XML文档，还可能存在外部系统的数据，比如网络爬虫抓取来的互联网数据等。数据可能是结构化、半结构化或非结构化的。这些数据经过ETL过程进入数据仓库系统。

这里把ETL分成了抽取和转换装载两个部分。抽取过程负责从操作型系统获取数据，该过程一般不做数据聚合和汇总，但是会按照主题进行集成，物理上是将操作型系统的数据全量或增量复制到数据仓库系统的RDS中。Hadoop生态圈中的主要数据抽取工具是Sqoop。Sqoop被设计成支持在关系数据库和Hadoop之间传输数据。

转换装载过程将数据进行清洗、过滤、汇总、统一格式化等一系列转换操作，使数据转为适合查询的格式，然后装载进数据仓库系统的TDS中。传统数据仓库的基本模式是用一些过程将操作型系统的数据抽取到文件，然后另一些过程将这些文件转化成MySQL或Oracle这样的关系数据库的记录。最后，第三部分过程负责把数据导入进数据仓库。

RDS（Raw Data Stores）是原始数据存储的意思。将原始数据保存到数据仓库里是个不错的想法。ETL过程的bug或系统中的其它错误是不可避免的，保留原始数据使得追踪并修改这些错误成为可能。有时数据仓库的用户会有查询细节数据的需求，这些细节数据的粒度与操作型系统的相同。有了RDS，这种需求就很容易实现，用户可以查询RDS里的数据而不必影响业务系统的正常运行。这里的RDS实际上是起到了操作型数据存储（Operational Data Store，ODS）的作用。

TDS（Transformed Data Stores）意为转换后的数据存储，是真正的数据仓库中的数据。大量用户会在经过转换的数据集上处理他们的日常查询。如果前面的工作做得好，这些数据的构建方式将保证最重要的和最频繁的查询能够快速执行。

这里的原始数据存储和转换后的数据存储是逻辑概念，它们可能物理存储在一起，也可能分开。当原始数据存储和转换后的数据存储物理上分开时，它们不必使用同样的软硬件。传统数据仓库中，原始数据存储通常是本地文件系统，数据被组织进相应的目录中，这些目录是基于数据从哪里抽取或何时抽取建立（例如以日期作为文件或目录名称的一部分）。转换后的数据存储一般使用某种关系数据库。在Hadoop生中，可以这两类数据逻辑上分开，物理上通过在Hive中建立两个不同的数据库来实现，最终所有数据都被分布存储到HDFS上。

自动化调度组件的作用是自动定期重复执行ETL过程。不同角色的数据仓库用户对数据的更新频率要求也会有所不同，例如财务主管需要每月的营收汇总报告，而销售人员想看到每天的产品销售数据。作为通用需求，所有数据仓库系统都应该能够建立周期性自动执行的工作流作业。ETL过程自动化是数据仓库成功的重要衡量标准。传统数据仓库一般利用操作系统自带的调度功能（如Linux的cron或Windows的计划任务）实现作业自动执行。Hadoop生态圈中有一个叫做Oozie的工具，它是一个Hadoop的工作流调度系统，可以使用它将ETL过程封装进工作流自动执行。

数据目录有时也被称为元数据存储，它可以提供一份数据仓库中数据的清单。用户通过它应该可以快速解决这些问题：什么类型的数据被存储在哪里，数据集的构建有何区别，数据最后的访问或更新时间等。此外还可以通过数据目录感知数据是如何被操作和转换的。一个好的数据目录是让用户体验到系统易用性的关键。Hadoop生态圈中主要的数据目录工具是HCatalog，它是Hadoop上的一个表和存储管理层。

查询引擎组件负责实际执行用户查询。传统数据仓库中，它可能是存储转换后数据的Oracle、MySQL等关系数据库系统内置的查询引擎，还可能是以固定时间间隔向其导入数据的OLAP立方体，如Essbase cube。Hadoop生态圈中的主要SQL查询引擎有基于MapReduce的Hive、基于RDD的SparkSQL和基于MPP的Impala等。

用户界面指的是最终用户所使用的接口程序。可能是一个GUI软件，如BI套件的中的客户端软件，也可能就只是一个浏览器。Hadoop生态圈中比较知名的数据可视化工具是Hue和Zeppelin。

本专题的（三）Kettle对Hadoop的支持 将详细介绍如何在Kettle中使用Hadoop相关组件。

2. 数据抽取

抽取操作从源系统获取数据给后续的数据仓库环境使用。这是ETL处理的第一步，也是最重要的一步。数据被成功抽取后，才可以进行转换并装载到数据仓库中。能否正确地获取数据直接关系到后面步骤的成败。数据仓库典型的源系统是事务处理应用，例如，一个销售分析数据仓库的源系统之一，可能是一个订单录入系统，其中包含当前销售订单相关操作的全部记录。

设计和建立数据抽取过程，在ETL处理乃至整个数据仓库处理过程中，一般是较为耗时的任务。源系统很可能非常复杂并且缺少相应的文档，因此只是决定需要抽取哪些数据可能就已经非常困难了。通常数据都不是只抽取一次，而是需要以一定的时间间隔反复抽取，通过这样的方式把数据的所有变化提供给数据仓库，并保持数据的及时性。除此之外，源系统一般不允许外部系统对它进行修改，也不允许外部系统对它的性能和可用性产生影响，数据仓库的抽取过程要能适应这样的需求。如果已经明确了需要抽取的数据，下一步就该考虑从源系统抽取数据的方法了。

对抽取方法的选择高度依赖于源系统和目标数据仓库环境的业务需要。一般情况下，不可能因为需要提升数据抽取的性能，而在源系统中添加额外的逻辑，也不能增加这些源系统的工作负载。有时，用户甚至都不允许增加任何“开箱即用”的外部应用系统，这被认为是对源系统具有侵入性。下面分别从逻辑和物理两方面介绍数据抽取方法。

（1）逻辑抽取

有两种逻辑抽取类型：全量抽取和增量抽取。

全量抽取
源系统的数据全部被抽取。因为这种抽取类型影响源系统上当前所有有效的数据，所以不需要跟踪自上次成功抽取以来的数据变化。源系统只需要原样提供现有的数据而不需要附加的逻辑信息（比如时间戳等）。一个全表导出的数据文件或者一个查询源表所有数据的SQL语句，都是全量抽取的例子。

增量抽取
只抽取某个事件发生的特定时间点之后的数据。通过该事件发生的时间顺序能够反映数据的历史变化，它可能是最后一次成功抽取，也可能是一个复杂的业务事件，如最后一次财务结算等。必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由源系统自身来提供，例如能够反映数据最后发生变化的时间戳列，或者是一个原始事务处理之外的，只用于跟踪数据变化的变更日志表。大多数情况下，使用后者意味着需要在源系统上增加数据抽取逻辑。

在许多数据仓库中，抽取过程不含任何变化数据捕获技术。取而代之的是，把源系统中的整个表抽取到数据仓库过渡区（Staging Area），然后用这个表的数据和上次从源系统抽取得到的表数据作比对，从而找出发生变化的数据。虽然这种方法不会对源系统造成很大的影响，但显然需要考虑给数据仓库处理增加的负担，尤其是当数据量很大的时候。

（2）物理抽取

依赖于选择的逻辑抽取方法，还有能够对源系统所做的操作和所受的限制，存在两种物理数据抽取机制：直接从源系统联机抽取或者间接从一个脱机结构抽取数据。这个脱机结构有可能已经存在，也可能得需要由抽取程序生成。

联机抽取
数据直接从源系统抽取。抽取进程或者直连源系统数据库访问它们的数据表，或者连接到一个存储快照日志或变更记录的中间层系统（如MySQL数据库的binlog）。注意这个中间层系统并不需要必须和源系统物理分离。

脱机抽取
数据不从源系统直接抽取，而是从一个源系统以外的过渡区抽取。过渡区可能已经存在（例如数据库备份文件、关系数据库系统的重做日志、归档日志等），或者抽取程序自己建立。应该考虑以下的存储结构：

• 数据库备份文件。一般需要数据还原操作才能使用。

• 备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。

• 平面文件。数据定义成普通格式，关于源对象的附加信息（列名、数据类型等等）需要另外处理。

• 导出文件。关系数据库大都自带数据导出功能，如Oracle的exp/expdp程序和MySQL的mysqldump程序，都可以用于生成数据文件。

• 重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。

（3）变化数据捕获

抽取处理需要重点考虑增量抽取，也被称为变化数据捕获（Change Data Capture，CDC）。假设一个数据仓库系统，在每天夜里的业务低峰时间从操作型源系统抽取数据，那么增量抽取只需要过去24小时内发生变化的数据。变化数据捕获也是建立准实时数据仓库的关键技术。

当能够识别并获得最近发生变化的数据时，抽取及其后面的转换、装载操作显然都会变得更高效，因为要处理的数据量会小很多。遗憾的是，很多源系统很难识别出最近变化的数据，或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。

常用的变化数据捕获方法有时间戳、快照、触发器和日志四种。相信熟悉数据库的用户对这些方法都不会陌生。时间戳方法需要源系统有相应的数据列表示最后的数据变化。快照方法可以使用数据库系统自带的机制实现，如Oracle的物化视图技术，也可以自己实现相关逻辑，但会比较复杂。触发器是关系数据库系统具有的特性，源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发，触发器中的逻辑用于捕获数据的变化。日志可以使用应用日志或系统日志，这种方式对源系统不具有侵入性，但需要额外的日志解析工作。关于这四种方案的特点，将会在本专题（五）数据抽取 中具体说明。

3. 数据转换

数据从操作型源系统获取后，需要进行多种转换操作。如统一数据类型、处理拼写错误、消除数据歧义、解析为标准格式等等。数据转换通常是最复杂的部分，也是ETL开发中用时最长的一步。数据转换的范围极广，从单纯的数据类型转化到极为复杂的数据清洗技术。

在数据转换阶段，为了能够最终将数据装载到数据仓库中，需要在已经抽取来的数据上应用一系列的规则和函数。有些数据可能不需要转换就能直接导入到数据仓库。

数据转换一个最重要的功能是清洗数据，目的是只有“合规”的数据才能进入目标数据仓库。这步操作在不同系统间交互和通信时尤其必要，例如，一个系统的字符集在另一个系统中可能是无效的。另一方面，由于某些业务和技术的需要，也需要进行多种数据转换，例如下面的情况：

• 只装载特定的数据列。例如，某列为空的数据不装载。

• 统一数据编码。例如，性别字段，有些系统使用的是1和0，有些是‘M’和‘F’，有些是‘男’和‘女’，统一成‘M’和‘F’。

• 自由值编码。例如，将‘Male’改成‘M’。

• 预计算。例如，产品单价 * 购买数量 = 金额。

• 基于某些规则重新排序以提高查询性能。

• 合并多个数据源的数据并去重。

• 预聚合。例如，汇总销售数据。

• 行列转置。

• 将一列转为多列。例如，某列存储的数据是以逗号作为分隔符的字符串，将其分割成多列的单个值。

• 合并重复列。

• 预连接。例如，查询多个关联表的数据。

• 数据验证。针对验证的结果采取不同的处理，通过验证的数据交给装载步骤，验证失败的数据或直接丢弃，或记录下来做进一步检查。

4. 数据装载

ETL的最后步骤是把转换后的数据装载进目标数据仓库。这步操作需要重点考虑两个问题，一是数据装载的效率，二是一旦装载过程中途失败了，如何再次重复执行装载过程。

即使经过了转换、过滤和清洗，去掉了部分噪声数据，但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时甚至更长时间，同时需要占用大量的系统资源。要提高装载的效率，加快装载速度，可以从以下几方面入手。首先保证足够的系统资源。数据仓库存储的都是海量数据，所以要配置高性能的服务器，并且要独占资源，不要与别的系统共用。在进行数据装载时，可以禁用数据库约束（唯一性、非空性，检查约束等）和索引，当装载过程完全结束后，再启用这些约束，重建索引。这种方法会大幅提高装载速度。在数据仓库环境中，一般不使用数据库来保证数据的参考完整性，即不使用数据库的外键约束，它应该由ETL工具或程序来维护。

数据装载过程可能由于多种原因而失败，比如装载过程中某些源表和目标表的结构不一致而导致失败，而这时已经有部分表装载成功了。在数据量很大的情况下，如何能在重新执行装载过程时只装载失败的部分是一个不小的挑战。对于这种情况，实现可重复装载的关键是要记录下失败点，并在装载程序中处理相关的逻辑。还有一种情况，就是装载成功后，数据又发生了改变（比如有些滞后的数据在ETL执行完才进入系统，就会带来数据的更新或新增），这时需要重新再执行一遍装载过程，已经正确装载的数据可以被覆盖，但相同数据不能重复新增。简单的实现方式是先删除在插入，或者用replace into、merge into等类似功能的操作。

装载到数据仓库里的数据，经过汇总、聚合等处理后交付给多维立方体或数据可视化、仪表盘等报表工具、BI工具做进一步的数据分析。

5. 开发ETL系统的方法

ETL系统一般都会从多个应用系统整合数据，典型的情况是这些应用系统运行在不同的软硬件平台上，由不同的厂商所支持，各个系统的开发团队也是彼此独立的，随之而来的数据多样性增加了ETL系统的复杂性。

开发一个ETL系统，常用的方式是使用数据库标准的SQL及其程序化语言，如Oracle的PL/SQL和MySQL的存储过程、用户自定义函数（UDF）等。还可以使用Kettle这样的ETL工具，这些工具都提供多种数据库连接器和多种文件格式的处理能力，并且对ETL处理进行了优化。使用工具的最大好处是减少编程工作量，提高工作效率。如果遇到特殊需求或特别复杂的情况，可能还是需要使用Shell、Java、Python等编程语言开发自己的应用程序。

ETL过程要面对大量的数据，因此需要较长的处理时间。为提高ETL效率，通常这三步操作会并行执行。当数据被抽取时，转换进程同时处理已经收到的数据。一旦某些数据被转换过程处理完，装载进程就会将这些数据导入目标数据仓库，而不会等到前一步工作执行完才开始。

二、ETL工具

1. ETL工具的产生

ETL工具出现之前，人们使用手工编写程序的方式来完成不同数据源的数据整合工作，常见的程序语言如COBOL、Perl或PL/SQL等。尽管这种数据整合方案由来已久，但直至今天仍有ETL工作使用这种手工编程/脚本的方式来完成。在还没有太多开源ETL工具的年代，相对价格昂贵的ETL工具而言，手工编程还有一定意义。手工编程的主要缺点在于：

• 容易出错

• 开发周期长

• 不易于维护

• 缺少元数据

• 缺乏一致性的日志和错误处理

最初的ETL工具为克服这些问题而被开发，方法是依据设计好的ETL工作流来自动生成所需代码。随之出现了Prism、Carlton、ETI等产品。代码生成最大的弊端是大多数代码生成仅能用于有限的特定数据库。不久之后，就在代码生成技术广泛应用之时，新的基于引擎架构的ETL工具出现了。新一代ETL工具可以执行几乎所有的数据处理流程，还可以将数据库连接和转换规则作为元数据存储起来。因为引擎有标准的工作方式，所有的转换在逻辑上是独立的，无论是相对于数据源还是数据目标。基于引擎的ETL工具通常比代码生成的方式更具通用性。Kettle就是一个基于引擎ETL工具的典型例子。在这个领域，还有一些其它熟悉的名字，比如Informatica Powercenter以及SQL Server Information Services等。

无论是代码生成器还是基于引擎的工具，都能帮助我们发现数据源的底层架构，以及这些架构之间的关系。但它们都需要开发目标数据模型，或者先行开发，或者在设计数据转换步骤时开发。设计阶段过后，还必须进行目标数据模型与源数据模型的映射，而整个过程是相当耗时的。所以后来还随之出现了模型驱动的数据仓库工具。模型驱动架构（Model-Driven Architecture，MDA）工具试图自动化实现数据仓库的设计过程，读取源数据模型，生成目标数据模型与需求数据之间的映射，以便向目标表填充数据，但市场上的相关工具并不多。当然MDA工具也不可能解决所有的数据集成问题，并且仍然需要具备一定技能的数据仓库开发人员才能发挥其作用。

2. ETL工具的功能

下面描述一般ETL工具必备的通用功能，以及Kettle如何提供这些功能。

（1）连接

任何ETL工具都应该有能力连接到类型广泛的数据源和数据格式。对于最常用的关系型数据库系统，还要提供本地的连接方式（如Oracle的OCI），ETL应该能够提供下面最基本的功能：

• 连接到普通关系型数据库并获取数据，如常见的Oracle、MS SQL Server、IBM DB/2、Ingres、MySQL和PostgreSQL等。

• 从有分隔符或固定格式的ASCII文件中获取数据。

• 从XML文件中获取数据。

• 从流行的办公软件中获取数据，如Access数据库和Excel电子表格。

• 使用FTP、SFTP、SSH方式获取数据（最好不用脚本）。

除了上述这些功能，还要能从Web Services或RSS中获取数据。如果还需要一些ERP系统里的数据，如Oracle E-Business Suite、SAP/R3、PeopleSoft或JD/Edwards，ETL工具也应该提供到这些系统的连接。

除了将通用的关系数据库和文本格式的文件作为数据源，Kettle也提供Salesforce.com和SAP/R3的输入步骤，但不是套件内，需要额外安装。对于其它ERP和财务系统的数据抽取还需要其它解决方法。

（2）平台独立

一个ETL工具应该能在任何平台上甚至是不同平台的组合上运行。例如，一个32位的操作系统可能在开发的初始阶段运行很好，但是当数据量越来越大时，就需要一个更强大的64位操作系统。再比如，开发一般是在Windows或Mac机上进行的，而生产环境一般是Linux系统或集群，ETL解决方案应该可以无缝地在这些系统间切换。Kettle是用Java开发的，可以运行在任何安装了Java虚拟机的计算机上。

（3）数据规模

ETL解决方案应该能处理逐年增长的数据。一般ETL能通过下面三种方式处理大数据。

• 并发：ETL过程能够同时处理多个数据流，以便利用现代多核的硬件架构。

• 分区：ETL能够使用特定的分区模式，将数据分发到并发的数据流中。

• 集群：ETL过程能够分配在多台机器上联合完成。

Kettle转换里的每个步骤都是以并发的方式来执行，并且可以多线程并行，这样加快了处理速度。Kettle在运行转换时，根据用户的设置，可以将数据以分发和复制两种方式发送到多个数据流中。分发是以轮流的方式将每行数据只发给一个数据流，复制是将一行数据发给所有数据流。本专题的（十）Kettle数据分发与多线程 将论述Kettle并行机制。

为了更精确控制数据，Kettle还使用了分区模式，通过分区可以将同一特征的数据发送到同一个数据流。这里的分区只是概念上类似于数据库的分区，Kettle并没有针对数据库分区有什么功能，一般认为数据库应该比ETL更适合完成数据分区。集群是有效的规模扩展方式，可以使Kettle将工作负载按需分配到多台机器上。本专题的（十一）Kettle集群与数据分片 部分深入讲解这两种规模扩展方式。

（4）设计灵活性

一个ETL工具应该留给开发人员足够的自由度来使用，而不能通过一种固定的方式限制用户的创造力和设计的需求。ETL工具可以分为基于过程的和基于映射的。基于映射的工具只在源和目的数据之间提供一组固定的步骤，严重限制了设计工作的自由度。基于映射的工具一般易于使用，可快速上手，但是对于更复杂的任务，基于过程的工具才是最好的选择。使用像Kettle这样基于过程的工具，根据实际的数据和业务需求，可以创建自定义的步骤和转换。

（5）复用性

设计完的ETL转换应该可以被复用，这也是ETL工具的一个不可或缺的特征。复制和粘贴已存在的转换步骤是最常见的一种复用，但这还不是真正意义上的复用。复用一词是指定义了一个转换或步骤，从其它地方可以调用这些转换或步骤。Kettle里有一个“映射(子转换)”步骤，可以完成转换的复用，该步骤可以将一个转换作为其它转换的子转换。另外转换还可以在多个作业里多次使用，同样作业也可以作为其它作业的子作业。

（6）扩展性

ETL工具必须要有扩展功能的方法。几乎所有的ETL工具都提供了脚本，以编程的方式来解决工具本身不能解决的问题。另外有些ETL工具可以通过API或其它方式来为工具增加组件。第三种方法是使用脚本语言写函数，函数可以被其它转换或脚本调用。

Kettle提供了上述所有功能。“JavaScript代码”步骤可以用来开发Java脚本，把这个脚本保存为一个转换，再通过映射(子转换)步骤，又可以变为一个标准的可以复用的函数。实际上并不限于脚本，每个转换都可以通过这种映射(子转换)方式来复用，如同创建了一个组件。Kettle在设计上就是可扩展的，它提供了一个插件平台。这种插件架构允许第三方为Kettle平台开发插件。Kettle里的所有组件都是插件，即使是默认提供的组件。

（7）数据转换

ETL项目很大一部分工作都是在做数据转换。在输入和输出之间，数据要经过检验、连接、分割、合并、转置、排序、归并、克隆、排重、删除、替换或者其它操作。常用的ETL工具（包括Kettle）都提供了下面一些最基本的转换功能：

• 缓慢变更维度（Slowly Changing Dimension，SCD）

• 查询值

• 行列转置

• 条件分割

• 排序、合并、连接

• 聚集

（8）测试和调试

测试和调试的重要性不言而喻。ETL的设计过程和直接用开发语言写程序很相似，也就是说在写程序时用到的一些步骤或过程同样也适用于ETL设计。测试也是ETL设计的一部分。为了完成测试工作，我们通常需要假设下面几种失败场景，并要给出相应的处理方法：

• 如果ETL过程没有按时完成数据转换的任务怎么办？

• 如果转换过程异常终止怎么办？

• 目标是非空列的数据抽取到的数据为空怎么办？

• 转换后的行数和抽取到的数据行数不一致怎么办（数据丢失）？

• 转换后计算的数值和另一个系统的数值不一致怎么办（逻辑错误）？

测试可分为黑盒测试（也叫功能测试）和白盒测试（也叫结构测试）。对于前者，ETL转换就被认为是一个黑盒子，测试者并不了解黑盒子内的功能，只知道输入和期望的输出。白盒测试要求测试者知道转换内部的工作机制并依此设计测试用例来检查特定的转换是否有特定的结果。

调试实际是白盒测试中的一部分，通过调试可以让开发者或测试者一步一步地运行一个转换，并找出问题的所在。Kettle为作业和转换都提供了单步逐行调试功能特性。

（9）血统和影响分析

任何ETL工具都应该有一个重要的功能：读取转换的元数据，抽取由不同转换构成的数据流的信息。血统分析和影响分析是基于元数据的两个相关的特性。血统是一种回溯性的机制，它可以查看到数据的来源。例如，“价格”和“数量”字段作为输入字段，在转换中根据这两个字段计算出“收入”字段。即使在后面的处理流程里过滤了“价格”个“数量”字段，血统分析也能分析出“收入”字段是基于“价格”和“数量”字段的。

影响分析是基于元数据的另种分析方法，该方法可以分析源数据字段对随后的转换以及目标表的影响。在本专题的（十二）Kettle元数据管理 中将详细讲述这一主题。

（10）日志和审计

数据仓库的目的就是要提供一个准确的信息源，因此数据仓库里的数据应该是可靠和可信的。为了保证这种可靠性，同时保证可以记录下所有的数据转换操作，ETL工具应该提供日志和审计功能。日志可以记录下在转换过程中执行了哪些步骤，包括每个步骤开始和结束时间时间戳。审计可以追踪到对数据做的所有操作，包括读行数、转换行数、写行数。在这方面Kettle在ETL工具市场处于领先地位。

传统大的软件厂商一般都提供ETL工具软件，如Oracle的OWB和ODI、微软的SQL Server Integration Services、SAP的Data Integrator、IBM的InfoSphere DataStage、Informatica等。下面介绍本专题的主角，开源的ETL工具中的佼佼者——Kettle。

三、Kettle简介

Kettle是Pentaho公司的数据整合产品，它可能是现在世界上最流行的开源ETL工具，经常被用于数据仓库环境，并可用来操作Hadoop上的数据。Kettle的使用场景包括：不同数据源之间迁移数据、把数据库中的数据导出成平面文件、向数据库大批量导入数据、数据转换和清洗、应用整合等。

Kettle是使用Java语言开发的。它最初的作者Matt Casters原是一名C语言程序员，在着手开发Kettle时还是一名Java小白，但是他仅用了一年时间就开发出了Kettle的第一个版本。虽然有很多不足，但这版毕竟是可用的。使用自己并不熟悉的语言，仅凭一己之力在很短的时间里就开发出了复杂的ETL系统工具，作者的开发能力和实践精神令人十分佩服。后来Pentaho公司获得了Kettle源代码的版权，Kettle也随之更名为Pentaho Data Integration，简称PDI。

1. Kettle设计原则

Kettle工具在设计之初就考虑到了一些设计原则，这些原则也借鉴了以前使用过的其它一些ETL工具积累下的经验和教训。

• 易于开发

Kettle认为，作为ETL开发者，应该把时间用在创建应用解决方案上。任何用于软件安装、配置的时间都是一种浪费。例如，为了创建数据库连接，很多和Kettle类似的Java工具都要求用户手工输入数据驱动类名和JDBC URL连接串，这明显把用户的注意力转移到了技术方面而非业务方面。Kettle尽量避免这类问题的发生。

• 避免自定义开发

一般ETL工具提供了标准化的构建组件来实现ETL开发人员不断重复的需求。当然可以通过手工编写Java代码或Java脚本来实现一些功能，但增加的每一行代码都给项目增加了复杂度和维护成本。所以Kettle尽量避免手工开发，而是提供组件及其各种组合来完成任务。

• 所有功能都通过用户界面完成

Kettle直接把所有功能通过界面的方式提供给用户，节约开发人员或用户的时间。当然专家级的ETL用户还是要去学习隐藏在界面后的一些特性。在Kettle里，ETL元数据可以通过XML格式表现，或通过资源库，或通过使用Java API。无论ETL元数据以哪种形式提供，都可以百分之百通过图形用户界面来编辑。

• 没有命名限制

ETL转换里有各种各样的名称，如数据库连接、转换、步骤、数据字段、作业等都要有一个名称。如果还要在命名时考虑一些如长度或字符限制，就会给工作带来一定麻烦。Kettle具备足够的智能化来处理ETL开发人员设置的各种名称。最终ETL解决方案应该可以尽可能地自描述，这样可以部分减少文档的需求，进而减少项目维护成本。

• 透明

Kettle不需要用户了解转换中某一部分工作是如何完成的，但允许用户看到ETL过程中各部分的运行状态。这样可以加快开发速度、降低维护成本。

• 灵活的数据通道

Kettle从设计之初就在数据的发送、接收方式上尽可能灵活。Kettle可以在文本文件、关系数据库等不同目标之间复制和分发数据，从不同数据源合并数据也是内核引擎的一部分，同样很简单。

• 只映射需要的字段

在一些ETL工具里经常可以看到数百行的输入和输出映射，对于维护人员来说这是一个噩梦。在ETL开发过程中，字段要经常变动，这样的大量映射也会增加维护成本。Kettle的一个重要核心原则就是，在ETL流程中所有未指定的字段都自动被传递到下一个组件。也就是说输入中的字段会自动出现在输出中，除非中间过程特别设置了终止某个字段的传递。

• 可视化编程

Kettle可以被归类为可视化编程语言（Visual Programming Languages，VPL），因为Kettle可以使用图形化的方式定义复杂的ETL程序和工作流。Kettle里的图就是转换和作业。可视化编程一直是Kettle里的核心概念，它可以让用户快速构建复杂的ETL作业，并降低维护工作量。Kettle中的设计开发工作几乎都可以通过简单的拖拽来完成。它通过隐藏很多技术细节，使IT领域更接近于业务领域。

2. 转换

转换（transformation）是Kettle ETL解决方案中最主要的部分，它处理抽取、转换、装载各阶段各种对数据行的操作。转换包括一个或多个步骤（step），如读取文件、过滤输出行、数据清洗或将数据装载到数据库等等。

转换里的步骤通过跳（hop）来连接，跳定义了一个单向通道，允许数据从一个步骤向另一个步骤步骤流动。在Kettle里，数据的单位是行，数据流就是数据行从一个步骤到另一个步骤的移动。

图1-2所示的转换从数据库读取数据并写入文本文件。除了步骤和跳，转换还包括了注释（note）。注释是一个文本框，可以放在转换流程图的任何位置。注释的主要目的是使转换文档化。

（1）步骤

步骤是转换的基本组成部分，它以图标的方式图形化地展现，图1-2中显示了两个步骤，“表输入”和“文本文件输出”。一个步骤有几个关键特性：

• 步骤需要有一个名字，这个名字在转换范围内唯一。

• 每个步骤都会读写数据行。唯一例外是“生成记录”步骤，该步骤只写数据。在本专题的（四）建立ETL示例模型 中将看到如何使用“生成记录”步骤生成日期维度数据。

• 步骤将数据写到与之相连的一个或多个输出跳（outgoing hops），再传送到跳的另一端的步骤。对另一端的步骤来说，这个跳就是一个输入跳（incoming hops），步骤通过输入跳接收数据。

• 大多数步骤可以有多个输出跳。一个步骤的数据发送可以被设置为轮流发送或复制发送。轮流发送是将数据行依次发给每个输出跳，复制发送是将全部数据行发送给所有输出跳。

• 在运行转换时，一个线程运行一个步骤或步骤的一份拷贝，如图1-2中“表输入”步骤左上角的X4，表示4个线程执行该步骤，数据行将复制4份。所有步骤的线程几乎同时运行，数据行连续地流过步骤之间的跳。

（2）转换的跳

跳（hop）就是步骤间带箭头的连线，跳定义了步骤之间的数据通路。跳实际上是两个步骤之间的被称为行级（row set）的数据行缓存。行集的大小可以在转换的设置里定义，Kettle 8.3 默认为10000行。当行集满了，向行集写数据的步骤将停止写入，直到行集里又有了空间。当行集空了，从行集读取数据的步骤停止读取，直到行集里又有可读的数据行。注意，跳在转换里不能循环，因为在转换里每个步骤都依赖于前一个步骤获取字段。

（3）并行

跳的这种基于行集缓存的规则允许每个步骤都由一个独立的线程运行，这样并发程度最高。这一规则也允许以最小消耗内存的数据流的方式来处理。在数据分析中，我们经常要处理大量数据，所以这种并发低耗内存的方式也是ETL工具的核心需求。

对于Kettle转换，不可能定义一个步骤在另一个步骤之后执行，因为所有步骤都以并发方式执行：当转换启动后，所有步骤都同时开始，从它们的输入跳中读取数据，并把处理过的数据写到输出跳，直到输入跳不再有数据，就中止步骤的运行。当所有的步骤都中止了，整个转换就中止了。从功能的角度看，转换具有明确的起点和终点。例如，图1-2里显示的转换起点是“表输入”步骤，因为这个步骤生成数据行。终点是“文本文件输出”步骤，因为这个步骤将数据写到文件，而且后面不再有其它节点。

前面关于步骤并发执行与起点、终点的描述看似自相矛盾，实际上只是看问题的角度不同。一方面，可以想象数据沿着转换里的步骤移动，形成一条行头到尾的数据通路。另一方面，转换里的步骤几乎是同时启动的，所以不可能判断出哪个步骤是第一个启动的步骤。如果想要一个任务沿着指定的顺序执行，就要使用后面介绍的“作业”了。

（4）数据行

数据以数据行的形式沿着步骤移动。一个数据行是零到多个字段的集合，字段包括这里所列的几种数据类型。

• String：字符类型数据。

• Number：双精度浮点数。

• Integer：带符号64位长整型。

• BigNumber：任意精度数值。

• Date：毫秒精度的日期时间值。

• Boolean：取值为true或false的布尔值。

• Binary：二进制类型，可以包括图形、音视频或其它类型的二进制数据。

每个步骤在输出数据行时都有对字段的描述，这种描述就是数据行的元数据，通常包括下面一些信息：

• 名称：行里的字段名应该是唯一的。

• 数据类型：字段的数据类型。

• 长度：字符串的长度或BigNumber类型的长度。

• 精度：BigNumber数据类型的十进制精度。

• 掩码：数据显示的格式（转换掩码）。如果要把数值型（Number、Integer、BigNumber）或日期类型转换成字符串类型就需要用到掩码，例如在图形界面中预览数值型、日期型数据，或者把这些数据保存成文本或XML格式时。

• 小数点：十进制数据的小数点格式。不同文化背景下小数点符号是不同的，一般是点（.）或逗号（,）。

• 分组符号（数字里的分割符号）：数值类型数据的分组符号，不同文化背景下数字里的分组符号也是不同的，一般是逗号（,）或点（.）或单引号（'）。

• 初始步骤：Kettle在元数据里还记录了字段是由哪个步骤创建的，可以让用户快速定位字段是由转换里的哪个步骤最后一次修改或创建。

当设计转换时有几个数据类型的规则需要注意：

• 行集里的所有行都应该有同样的数据结构。当从多个步骤向一个步骤里写数据时，多个步骤输出的数据行应该有相同的结构，即字段名、数据类型、字段顺序都相同。

• 字段元数据不会在转换中发生变化。字符串不会自动截去长度以适应指定的长度，浮点数也不会自动取整以适应指定的精度。这些功能必须通过一些指定的步骤来完成。

• 默认情况下，空字符串被认为与NULL相等，但可以通过kettle.properties文件中的kettle_empty_string_differs_from_null参数来设置。

（5）数据类型转换

既可以显式地转换数据类型，如在“字段选择”步骤中直接选择要转换的数据类型，也可以隐式地转换数据类型，如将数值数据写入数据库的varchar类型字段。这两种形式的数据转换实际上是完全一样的，都是使用了数据和对数据的描述。

• Date和String的转换

Kettle内部的Date类型里包含了足够的信息，可以用这些信息来表现任何毫秒精度的日期、时间值。如果要在String和Date类型之间转换，唯一要指定的就是日期格式掩码。表1-1显示的是几个日期转换例子。

表1-1 日期转换例子

• Numeric和String的转换

Numeric数据（包括Number、Integer、BigNumber）和String类型之间的转换用到的几个字段元数据是：转换掩码、小数点符号、分组符号和货币符号。这些转换掩码只是决定了一个文本格式的字符串如何转换为一个数值，而与数值本身的实际精度和舍入无关。表1-2显示了几个常用的例子。

表1-2 数值转换掩码的例子

• 其它转换

表1-3提供了Boolean和String之间、整型与日期类型之间数据类型转换的列表。

表1-3 其它数据类型转换

3. 作业

大多数ETL项目都需要完成各种各样的维护任务。例如，当运行中发生错误，要做哪些操作；如何传送文件；验证数据库表是否存在等等。这些操作要按照一定顺序完成，就需要一个可以串行执行的作业来处理。

一个作业包括一个或多个作业项，这些作业项以某种顺序来执行。作业执行顺序由作业项之间的跳（job hop）和每个作业项的执行结果来决定。图1-3显示了一个典型的装载数据仓库的作业。

（1）作业项

作业项是作业的基本构成部分。如同转换的步骤，作业项也可以使用图标的方式图形化展示。但是作业项有一些地方不同于步骤：

• 步骤的名字在转换中是唯一的，但作业项可以有影子拷贝（shadow copies），如图1-3中的“错误邮件”。这样可以把一个作业项放在多个不同的位置。这些影子拷贝里的信息都是相同的，编辑了一份拷贝，其它拷贝也会随之修改。

• 在作业项之间可以传递一个结果对象（result object）。这个结果对象里包含了数据行，它们不是以流的方式传递的，而是等一个作业项执行完了，再传递给下一个作业项。

• 默认情况下，所有的作业项都以串行方式执行，只是在特殊的情况下以并行方式执行。

因为作业顺序执行作业项，所以必须定义一个起点，如图中的“start”作业项，就定义了一个起点。一个作业只能定义一个开始作业项。

（2）作业的跳

如同转换中各步骤之间的跳，作业的跳是作业项之间的连接线，它定义了作业的执行路径。作业里每个作业项的不同运行结果决定了作业的不同执行路径。对作业项的运行结果的判断如下：

• 无条件执行：不论上一个作业项执行成功还是失败，下一个作业项都会执行。这是一种黑色的连接线，上面有一个锁的图标，如图1-3中 “start”到“传送数据”作业项之间的连线。

• 当运行结果为真时执行：当上一个作业项的执行结果为真时，执行下一个作业项，通常在需要无错误执行的情况下使用。这是一种绿色连接线，上面有一个对钩号图标，如图1-3中横向的三个连线。

• 当运行结果为假时执行：当上一个作业项的执行结果为假或没有成功时，执行下一个作业项。这是一种红色的连接线，上面有一个红色的叉子图标。

在作业跳的右键菜单上可以设置以上这三种判断方式。

（3）多路径和回溯

Kettle使用一种回溯算法来执行作业里的所有作业项，而且作业项的运行结果（真或假）也决定执行路径。回溯算法是一种深度遍历：假设执行到了图里的一条路径的某个节点时，要依次执行这个节点的所有子路径，直到没有再可以执行的子路径，就返回该节点的上一节点，再反复这个过程。

图1-4 使用回溯算法串行执行多个路径

例如，图1-4里的A、B、C三个作业项的执行顺序为：

1. 首先“Start”作业项搜索所有下一个节点作业项，找到了“A”和“C”。

2. 执行“A”

3. 搜索“A”后面的作业项，发现了“B”。

4. 执行“B”。

5. 搜索“B”后面的作业项，没有找到任何作业项。

6. 回到“A”，也没有发现其它作业项。

7. 回到“Start”，发现另一个要执行的作业项“C”。

8. 执行“C”。

9. 搜索“C”后面的作业项，没有找到任何作业项。

10. 回到“Start”，没有找到任何作业项。

11. 作业结束。

因为没有定义执行顺序，所以这个例子的执行顺序除了ABC，还可以是CAB。这种回溯算法有两个重要特征：

• 因为作业可以是嵌套的，除了作业项有运行结果，作业也需要一个运行结果，因为一个作业可以是另一个作业的作业项。一个作业的运行结果，来自于它最后一个执行的作业项。这个例子里作业的执行顺序可能是ABC，也可能是CAB，所以不能保证作业项C的结果就是作业的结果。

• 作业里允许循环。当在作业里创建了一个循环，一个作业项就会被执行多次，作业项的多次运行结果会保存在内存里，便于以后使用。

（4）并行执行

一个作业项能以并发的方式执行它后面的作业项，如图1-5中的作业所示。在这个例子里，作业项A和C几乎同时启动。

图1-5 并行执行的作业项

需要注意的是，如果A和C是顺序执行的多个作业项，那么这两组作业项也是并行执行的，如图1-6所示。

图1-6 两组同时执行的作业项

在这个例子中，作业项[A、B、写日志]和[C、D、清空表]是在两个线程里并行执行的。通常设计者也是希望以这样的方式执行。但有时候，设计者希望一部分作业项并行执行，然后再串行执行其它作业项。这就需要把并行的作业项放到一个新的作业里，然后作为另一个作业的作业项，如图1-7所示。

图1-7 并行加载作业作为另一个作业的作业项

（5）作业项结果

作业执行结果不仅决定了作业的执行路径，而且还向下个作业项传递了一个结果对象。结果对象包括了这里所示的一些信息。

• 一组数据行：在转换里使用“复制记录到结果”步骤可以设置这组数据行。与之对应，使用“从结果获取记录”步骤可以获取这组数据行。在一些作业项里，如“Shell”、“转换”、“作业”的设置里有一个选项可以循环执行这组数据行，这样可以通过参数化来控制转换和作业。

• 一组文件名：在作业项的执行过程中可以获得一些文件名。这组文件名是所有与作业项发生过交互的文件的名称。例如，一个转换读取和处理了10个XML文件，这些文件名就会保留在结果对象里。使用转换里的“从结果获取文件”步骤可以获取到这些文件名，除了文件名还能获取到文件类型。“一般”类型是指所有的输入输出文件，“日志”类型是指Kettle日志文件。

• 读、写、输入、输出、更新、删除、拒绝的行数和转换里的错误数。

• 脚本作业项的退出状态：根据脚本执行后的状态码，判断脚本的执行状态，再执行不同的作业流程。

4. 数据库连接

Kettle里的转换和作业使用数据库连接来连接到关系型数据库。Kettle数据库连接实际是数据库连接的描述，也就是建立实际连接需要的参数。实际连接只是在运行时才建立，定义一个Kettle的数据库连接并不真正打开一个数据库的连接。各种数据库的行为彼此不同，图1-8所示的数据库连接窗口里有很多种数据库。

图1-8 数据库连接窗口

（1）一般选项

在数据库连接窗口中主要设置三个选项：

• 连接名称：设定一个在作业或转换范围内唯一的名称。

• 连接类型：从数据库列表中选择要连接的数据库类型。根据选中数据库的类型不同，要设置的访问方式和连接参数也不同，某些Kettle步骤或作业项生成SQL语句时使用的方言也不同。

• 访问方式：在列表里可以选择可用的访问方式，一般都使用JDBC连接，不过也可以使用ODBC数据源、JNDI数据源、Oracle的OCI连接（使用Oracle命名服务）等。

根据选择的数据库不同，右侧面板的连接参数设置也不同。例如图1-8中，只有Oracle数据库可以设置表空间选项。一般常用的连接参数为：

• 主机名：数据库服务器的主机名或IP地址。

• 数据库名：要访问的数据库名。

• 端口号：默认是选中的数据库服务器的默认端口号。

• 用户名和密码：连接数据库服务器的用户名和密码。

（2）特殊选项

对于大多数用户来说，使用数据库连接窗口的“一般”标签就足够了。但偶尔也可能需要设置对话框里的“高级”标签的内容，如图1-9所示。

图1-9 数据库连接窗口中的“高级”标签

• 支持Boolean数据类型：对Boolean（bit）数据类型，大多数数据库的处理方式都不相同，即使同一个数据库的不同版本也可能不同。许多数据库根本不支持Boolean数据类型，如Oracle和MySQL，所以默认情况下，Kettle使用一个char(1)字段的不同值（如Y或N）来代替Boolean字段。如果选中了这个选项，Kettle就会为支持Boolean类型的数据库生成正确的SQL方言。

• 双引号分割标识符：强迫SQL语句里的所有标识符（如列名、表名）加双引号，一般用于区分大小写的数据库，或者Kettle里定义的关键字列表和实际数据库不一致的情况。

• 强制转为小写：将所有表名和列名转为小写。

• 强制转为大写：将所有表名和列名转为大写。

• 默认模式名：当不明确指定模式名时默认的模式名。

• 连接后要执行的SQL语句：一般用于建立连接后，修改某些数据库参数，如session级的变量或调试信息等。

除了这些高级选项，在连接对话框的 “选项”标签下，还可以设置数据库特定的参数，如一些连接参数。为了便于使用，对于某些数据库（如MySQL），Kettle提供了一些默认的连接参数和值。有几种数据库类型，Kettle还提供了连接参数的帮助文档，通过单击“选项”标签中的“帮助”按钮可以打开对应数据库的帮助页面。

还可以选择Apache的通用数据库连接池选项。如果运行了很多小的转换或作业，这些转换或作业里又定义了生命期短的数据库连接，连接池选项就显得有意义了。连接池选项不会限制并发数据库连接的数量。

当一个大数据库不能再满足需求时，就会考虑用很多小的数据库来处理数据。通常可以使用数据分区技术（注意不是数据库系统本身自带分区特性）来分散数据装载。这种方法可以将一个大数据集分为几个数据分区，每个分区都保存在独立的数据库实例中。这种方法的优点显而易见，能够大幅减少每个表或每个数据库实例的行数。可以在数据库连接对话框的“集群”标签下设置分区，详见本专题的（十一）Kettle集群与数据分片。

关系数据库在数据的连接、合并、排序等方面有着突出的优势。和基于流的数据处理引擎，如Kettle相比，它的一大优点是，数据库使用的数据都存储在磁盘中。当关系型数据库进行连接或排序操作时，直接使用这些数据即可，而不用把这些数据装载到内存里，这就体现出明显的性能方面的优势。但缺点也是很明显的，把数据装载到关系数据库里也可能会产生性能的瓶颈。

对ETL开发者而言，要尽可能利用数据库自身的性能优势，来完成连接或排序这样的操作。如果不能在数据库里进行连接这样的操作，如数据的来源不同，也应该在数据库里排序，以便在ETL里做连接操作。

5. 连接与事务

数据库连接只在执行作业或转换时使用。在作业里，每一个作业项都打开和关闭一个独立的数据库连接。转换也是如此，但是因为转换里的步骤是并行的，每个步骤都打开一个独立的数据库连接并开始一个事务。尽管这样在很多情况下会提高性能，但当不同步骤更新同一个表时，也会带来锁和参照完整性问题。

为了解决打开多个数据库连接而产生的问题，Kettle可以在一个事务中完成转换。在转换设置对话框的 “杂项”标签中，设置“使用唯一连接”，可以完成此功能。当选中了这个选项，所有步骤里的数据库连接都使用同一个数据库连接。只有所有步骤都正确，转换正确执行，才提交事务，否则回滚事务。

6. 元数据与资源库

转换和作业是Kettle的核心组成部分。在介绍Kettle设计原则时曾经讨论过，它们可以用XML格式来表示，可以保存在资料库里，也可以用Java API的形式来表示。它们的这些表示方式，都依赖于这里所列的元数据。

• 名字：转换或作业的名字。不论是在一个ETL工程内还是在多个ETL工程内，都应该尽可能使用唯一的名字，这样在远程执行时或多个ETL工程共用一个资源库时都会有帮助。

• 文件名：转换或作业所在的文件名或URL。只有当转换或作业是以XML文件的形式存储时，才需要设置这个属性。当从资源库加载时，不必设置这个属性。

• 目录：这个目录是指在Kettle资源库里的目录。当转换或作业保存在资源库里时设置，保存为XML文件时不用设置。

• 描述：这是一个可选属性，用来设置作业或转换的简短的描述信息。如果使用了资源库，这个描述属性也会出现在资源库浏览窗口的文件列表中。

• 扩展描述：也是一个可选属性，用来设置作业或转换的详细描述信息。

当ETL项目规模比较大，有很多ETL开发人员在一起工作，开发人员之间的合作就显得很重要。Kettle以插件的方式灵活定义不同种类的资源库，但不论是哪种资源库，它们的基本要素是相同的：它们都使用相同的用户界面、存储相同的元数据。目前有三种常见资源库：数据库资源库、Pentaho资源库和文件资源库。

• 数据库资源库：把所有的ETL信息保存在关系数据库中。这种资源库比较容易创建，详见本专题的（十二）Kettle元数据管理。

• 文件资源库：在一个文件目录下定义一个资源库。因为Kettle使用的是Apache VFS虚拟文件系统，所以这里的文件目录是一个广泛的概念，包括zip文件、Web服务、FTP服务等。

• Pentaho资源库：包含在Kettle企业版中的一个插件。这种资源库实际是一个内容管理系统（Content Manage System，CMS），它具备一个理想资源库的所有特性，包括版本控制和依赖完整性检查。

无论哪种资源库都应该具有下面的特性：

• 中央存储：在一个中心位置存储所有的转换和作业。ETL用户可以访问到工程的最新视图。

• 文件加锁：防止多个用户同时修改同一文件。

• 修订管理：一个理想的资源库可以存储一个转换或作业的所有历史版本，以便将来参考。可以打开历史版本，并查看变更日志。

• 依赖完整性检查：检查资源库转换或作业之间的相互依赖关系，可以确保资源库里没有丢失任何链接，没有丢失任何转换、作业或数据库连接。

• 安全性：防止未授权的用户修改或执行ETL作业。

• 引用：重新组织转换、作业，或简单重命名，都是ETL开发人员的常见工作。要做好这些工作，需要完整的转换或作业的引用。

7. 工具

Kettle是一个独立的产品，但它包括了ETL开发和部署阶段用到的多个工具程序，主要包括：

• Spoon：图形化工具，用于快速设计和维护复杂的ETL工作流。

• Kitchen：运行作业的命令行工具。

• Pan：运行转换的命令行工具。

• Carte：轻量级（大概1MB）Web服务器，用来远程执行转换或作业。一个运行有Carte进程的机器可以作为从服务器，从服务器是Kettle集群的一部分。

每个工具都有独立的功能，也多少依赖于其它程序。Kettle的主体框架如图1-10所示。

图1-10 Kettle工具程序框架

（1）Spoon

Spoon是Kettle的集成开发环境（IDE）。它基于Java SWT提供了图形化的用户接口，主要用于ETL的设计。在Kettle安装目录下，有启动Spoon的脚本，如Windows下的Spoon.bat，类UNIX下的spoon.sh。Windows用户还可以通过执行Kettle.exe启动Spoon。Spoon的屏幕截图如图1-11所示。

图1-11 Spoon

图1-11里可以清楚地看到Spoon的主窗口。主窗口上方有一个菜单条，下方是一个左右分隔的应用窗口。右方面板里有多个标签面板，每个标签面板都是一个当前打开的转换或作业。左方面板是一个树状结构步骤或作业项视图。右方的工作区又可以分为上下两个部分：上部的画布和下部的结果面板。

图1-11的当前选中的画布标签里显示了一个设计好的转换。设计作业或转换的过程实际就是往画布里添加作业项或转换步骤的图标这么简单，向画布添加图标的方式为，从左侧的树中拖拽。这些作业项和转换步骤通过跳来连接。跳就是从一个作业项/步骤的中心连接到另一个作业项/步骤的一条线。在作业里跳定义的是控制流，在转换里跳定义的是数据流。工作区下方的面板是运行结果面板，其中除了显示运行结果还显示运行时日志和运行监控。

工作区左侧的树有“主对象树”和“核心对象”两个标签。主对象树将当前打开的作业或转换里的所有作业项或步骤以树状结构展现。设计者可以在这里快速地找到某个画布上的步骤、跳或数据库连接等资源。核心对象中包含Kettle中所有可用的作业项或步骤，可以在搜索框中输入文本查找名称模糊匹配的作业项或步骤。一些调试作业/转换的工具也集成到了Spoon的图形界面里，设计者可以在IDE里直接调试作业/转换。这些调试功能按钮在画布上方的工具栏里。

（2）Kitchen和Pan

作业和转换可以在图形界面里执行，但这只是在开发、测试和调试阶段。在开发完成后，需要部署到实际运行环境中，在部署阶段Spoon就很少用到了。部署阶段一般需要通过命令行执行，并把命令行放到Shell脚本中，并定时调度这个脚本。Kitchen和Pan命令行工具就是用于这个阶段，在实际的生产环境使用。

Kitchen和Pan工具是Kettle的命令行执行程序。实际上，Kitchen和Pan只是在Kettle执行引擎上的封装。它们只是解释命令行参数，调用并把这些参数传递给Kettle引擎。Kitchen和Pan在概念和用法上都非常相近，这两个命令的参数也基本是一样的。唯一不同的是Kitchen用于执行作业，Pan用于执行转换。在使用命令行执行作业或转换时，需要重点考虑网络传输的性能。Kettle数据流将数据作为本地行集缓存。如果数据源和目标之间需要通过网络传输大量数据，将Kettle部署于源或目标服务器上会极大提升性能。

Kitchen和Pan都通过脚本的方式启动，在Windows系统下，脚本名称是Kitchen.bat和Pan.bat，在类UNIX系统下，脚本名称是Kitchen.sh和Pan.sh。在执行这些脚本以及Kettle自带的其它脚本时，要把Kettle目录切换为控制台的当前目录。类UNIX系统的脚本默认情况下是不能执行的，必须使用chmod命令使脚本可执行。

Kettle用Java语言开发，因此在使用Kettle命令行时需要注意匹配Java版本。例如Kettle8.2.0版本需要JDK 1.8的支持。这样就能在Spoon的图形界面下进行设计开发调试，然后用命令行执行保存在本地文件或资源库中的转换或作业，秉承Java程序一次编译到处运行的理念。下面是一些命令行的例子。

`# 列出所有有效参数`


`Kettle-home> ./kitchen.sh`


`# 运行一个存储在文件中的作业`


`Kettle-home> ./kitchen.sh /file:/home/foo/daily_load.kjb`


`# 运行一个资源库里的作业`


`Kettle-home> ./kitchen.sh /rep:pdirepo /user:admin /pass:admin /dir:/ /job:daily_load.kjb`


`# 运行一个存储在文件中的转换`


`Kettle-home> ./pan.sh -file:/home/mysql/MongoDB_to_MySQL.ktr`

Kitchen和Pan的命令行包含了很多参数，在不使用任何参数的情况下，直接运行Kitchen和Pan会列出所有参数的帮助信息。参数的语法规范为：

`[/-]name [[:=]value]`

参数以斜线（/）或横线（-）开头，后面跟参数名。大部分参数名后面都要有参数值。参数名和参数值之间可以是冒号（:）或等号（=），参数值里如果包含空格，参数值必须用单引号（'）或双引号（"）引起来。

作业和转换的命令行参数非常相似，这两个命令的参数可以分为下面几类：

• 指定作业或转换

• 控制日志

• 指定资源库

• 列出可用资源库和资源库内容

表1-4列出了Kitchen和Pan共有的命令行参数。

表1-4 Kitchen和Pan共有的命令行参数

尽管Kitchen和Pan命令的参数名基本相同，但这两个命令里的dir参数和listdir参数的含义有一些区别。对Kitchen而言，dir和listdir参数列出的是作业的路径，Pan命令里的这两个参数列出的是转换路径。除了共有的命令行参数外，Kitchen和Pan自己特有的命令行参数分别见表1-5、表1-6。

表1-5 Kitchen特有的命令行参数

表1-6 Pan特有的命令行参数

（3）Carte

Carte服务用于执行一个作业，就像Kitchen一样。但和Kitchen不同的是，Carte是一个服务，一直在后台运行，而Kitchen只是运行完一个作业就退出。当Carte在运行时，一直在某个端口监听HTTP请求。远程机器客户端给Carte发出一个请求，在请求里包含了作业的定义。当Carte接到了这样的请求后，它验证请求并执行请求里的作业。Carte也支持其它几种类型的请求，这些请求用于获取Carte的执行进度、监控信息等。

Carte是Kettle集群中一个重要的构建模块。集群可将单个工作或转换分成几部分，在Carte服务器所在的多个计算机上并行执行，因此可以分散工作负载。关于Carte以及Kettle集群的配置和使用，详见本专题的（十一）Kettle集群与数据分片。

8. 虚拟文件系统

灵活而统一的文件处理方式对ETL工具来说非常重要，所以Kettle支持URL形式的文件名。Kettle使用Apache的通用VFS作为文件处理接口，替用户解决各种文件处理方面的复杂情况。例如，使用Apache VFS可以选中.zip压缩包内的多个文件，和在一个本地目录下选择多个文件一样方便。表1-7里显示的是VFS的一些典型的例子。

表1-7 VFS文件规范的例子

四、小结

编程和使用工具是常用的开发ETL应用的方法，而ETL工具又有基于映射和基于引擎之分。面对各种各样的ETL开发工具，之所以选择Kettle主要归结为下面几点原因。

• 最小化编码工作

开发ETL系统通常是一个非常复杂的工程，造成这种复杂性的原因很多。数据仓库的数据来源可能分布在不同的数据库，不同的地理位置，不同的应用系统之中，而且由于数据形式的多样性，数据转换的规则大都极为复杂。如果手工编写程序抽取数据并做转换，不可避免地需要大量的设计、编码、测试、维护等工作。这还不包括熟练掌握编程语言的学习成本。另一方面，Kettle非常容易使用，其所有的功能都通过用户界面完成，不需要任何编码工作。用户只需要告诉它做什么，而不用指示它怎么做，这大大提高了ETL过程的开发效率。在Spoon界面中，用户通过简单拖拽就能完成绝大部分ETL设计工作。

• 极简的多线程与并发执行

显然多线程并行可以极大提高程序执行效率，然而从编程角度讲，多线程比单线程要考虑的问题多得多。在Kettle中设置多线程方式执行非常简单，只要在步骤的右键菜单中选择“改变开始复制的数量”，然后指定线程数即可，其它工作都交给Kettle处理，实现细节对用户完全透明。另外再次强调，Kettle转换中的各个步骤本身就是以数据流的形式并行的。

• 完备的转换步骤与作业项

Kettle 8.3版本中，转换的核心对象包含输入、输出、应用、转换、脚本等23个分类，每个分类中又包含大量的步骤。作业的核心对象包含14个分类，同样每个分类中包含大量作业项。数据库连接更是支持53种数据库之多。可以说当前Kettle原生已经几乎支持所有常见数据源和ETL功能需求，而且步骤、作业项、数据库种类还会随着Kettle的版本更新而不断增加。

• 完全跨平台

Kettle是基于Java的解决方案，因此天然继承了Java跨平台性。用户可以在自己熟悉的环境中（如Windows、Mac等），通过图形界面进行ETL设计开发，然后将调试好的转换或作业保存为外部XML文件，或将元数据存储在资源库中。这样只要有合适的JVM存在，转换或作业就能运行在任何环境和平台之上，真正做到与平台无关。

以我个人的经验而言，只要是和关系数据库打交道，很多情况ETL通过SQL就能搞定。但有时面对看似普通的需求，用SQL解决却相当麻烦。在本篇最后举一个实际工作中遇到的简单例子，说明Kettle比SQL更适合的使用场景，同时加深一点对Kettle的直观印象。下一篇文章从安装配置开始进入使用Kettle的实操阶段。

收到的需求是这样的：有几百个文本文件，每个文件内容的格式相同，都是有固定分隔符的两列，每个文件有数千行记录。现在需要把这些文件的内容导入一个表中，除了文件内容中的两列，还要存一列记录数据对应的文件名。

向数据库表中导入数据，本来是一件轻而易举的事。可有几百个文件，还要将文件名连同对应的数据一起存入到表中，要手工逐个处理每个文件未免太麻烦了。现在是Kettle一显身手的时候了。Kettle的转换处理数据流，其中有一个“获取文件名”的输入步骤，可以使用它在导入文件数据时添加上文件名字段，而且支持正则表达式同时获取多个文件名，正好适用此场景。下面为在Kettle 8.3中的实现步骤。

1. 新建一个转换，包含“获取文件名”、“文本文件输入”、“表输出”三个步骤，如图1-12所示。

图1-12 多文件数据导入

2. 设置“获取文件名”步骤，如图1-13所示。

图1-13 “获取文件名”步骤设置

文件所在目录为Kettle所在服务器本地的/tmp/data/，通配符采用正则表达式写法。注意*前面要加一个“.”，否则报错。这一步骤会将包括文件名在内的文件的13个属性作为输出字段传递给后面的步骤。

3. 设置“文本文件输入”步骤，“文件”、“内容”、“字段”标签分别如图1-14到图1-16所示。

图1-14 “文本文件输入”步骤的“文件”设置

“选中文件”使用与“获取文件名”步骤相同的正则表达式。“在输入里的字段被当做文件名”中需要填写上一步骤中作为文件名的字段（默认为filename）。

图1-15 “文本文件输入”步骤的“内容”设置

字段分隔符为逗号，格式选择“Unix”。

图1-16 “文本文件输入”步骤的“字段”设置

通过点击“获取字段”按钮，可以自动获得文本文件中的字段。

4. 设置“表输出”步骤，如图1-17所示

图1-17 “表输出”步骤设置

将表字段与前面步骤输出的字段做映射。

该转换执行后，会将/tmp/data/目录下所有txt文件的内容，及其对应的文件名同时导入表中。

版权声明：

本文为大数据技术与架构整理，原作者独家授权。未经原作者允许转载追究侵权责任。

编辑｜冷眼丶

微信公众号｜import_bigdata

欢迎点赞+收藏+转发朋友圈素质三连

文章不错？点个【在看】吧！** 👇**

本文分享自微信公众号 - 大数据技术与架构（import_bigdata）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。