Kettle转换

# 一、Kettle输入控件

输入是转换里面的第一个分类，输入控件也是转换中的第一大控件，用来抽取数据或者生成数据。输入是ETL里面的E（Extract），主要做数据提取的工作。

由于Kettle中自带的输入控件比较多，本文只挑出开发中经常使用的几个输入控件来进行讲解，详情如下图。

# 1.1、CSV文件输入

CSV文件是一个用逗号分隔的固定格式的文本文件，这种文件后缀名为.csv，可以用Excel或者文本编辑器打开。在企业里面一般最常见的ETL需求就是将csv文件转换为excel文件，如果用Kettle来做这个ETL工作，就需要用到本章节讲解的CSV文件输入控件。

① 步骤名称：可以修改，但是在同一个转换里面要保证唯一性，见名知意；

② 文件名：选择对应的csv文件；

③ 列分隔符：默认是逗号；

④ 封闭符：结束行数据的读写；

⑤ NIO缓存大小：文件如果行数过多，需要调整此参数；

⑥ 包含列头行：意思是文件中第一行是字段名称行，表头不进行读写；

⑦ 行号字段：如果文件第一行不是字段名称或者需要从某行开始读写，可在此输入行号。

⑧ 并发运行？：选择并发，可提高读写速度；

⑨ 字段中有回车换行？：不要选择，会将换行符做数据读出；

⑩ 文件编码：如果预览数据出现乱码，可更换文件编码；

# 1.2、文本文件输入

提取服务器上的日志信息是公司里ETL开发很常见的操作，日志信息基本上都是文本类型，因此文本文件输入控件是kettle中常用的一个输入控件。使用文本文件输入控件步骤：

1）添加需要转换的日志文件

2）按照日志文件格式，指定分隔符

3）获取下字段，并给字段设置合适的格式

4）最后点下预览记录，看看能否读到数据

# 1.3、Excel输入

Excel输入控件也是很常用的输入控件，一般企业里会用此控件对大量的Excel文件进行ETL操作。使用Excel输入控件步骤如下：

1）按照读取的源文件格式指定对应的表格类型为xls还是xlsx

2）选择并添加对应的excel文件

3）获取excel的sheet工作表

4）获取字段，并给每个字段设置合适的格式

5）预览数据

# 1.4、XML输入

# XML简介

XML-可扩展标记语言eXtensible Markup Language，由W3C组织发布，目前推荐遵守的是W3C组织于2000年发布的XML1.0规范。XML用来传输和存储数据，就是以一个统一的格式，组织有关系的数据，为不同平台下的应用程序服务。

# XPath简介

XPath即为XML路径语言（XML Path Language），它是一种用来确定XML文档中某部分位置的语言。XPath基于XML的树状结构，提供在数据结构树中找寻节点的能力。

XPath 使用路径表达式在 XML 文档中选取节点。下面列出了最有用的路径表达式：

# XML输入控件

了解XML和XPath概念以后，我们要开始学习Kettle的XML输入控件，企业里经常用此控件进行XML文件的ETL操作。

浏览获取xml文件，将xml文件添加到kettle中
获取xml文档的所有路径，设置合适的循环读取路径
获取字段，获得自己想要读取的所有字段，并且设置适当的格式
预览数据，看看能否读取到自己想要的数据

# 1.5、JSON输入

# JSON简介

JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。JSON对象本质上就是一个JS对象，但是这个对象比较特殊，它可以直接转换为字符串，在不同语言中进行传递，通过工具又可以转换为其他语言中的对象。

JSON核心概念：数组、对象、属性。

数组：[ ]

对象：{ }

属性：key:value

# JSON Path

JSONPath类似于XPath在xml文档中的定位，JsonPath表达式通常是用来路径检索或设置Json的。其表达式可以接受“dot–notation”（点记法）和“bracket–notation”（括号记法）格式

点记法：$.store.book[0].title

括号记法：$[‘store’][‘book’][0][‘title’]

# JSON输入控件

了解JSON格式和JSON Path以后，我们要学习使用JSON输入控件，JSON控件也是企业里做ETL常用的控件之一。

浏览获取JSON文件，将json文件获取到kettle中
根据JSON Path点记法，获取到需要的字段，并且设置合适格式

# 1.6、表输入

# 创建数据库连接

表输入可以说是kettle中用到最多的一种输入控件，因为企业中大部分的数据都会存在数据库中。kettle可以连接市面上常见的各种数据库，比如Oracle，Mysql，SqlServer等。但是在连接各个数据库之前，我们需要先配置好对应的数据库驱动，本教程以mysql为例，给大家讲解kettle连接mysql数据库的过程。

首先我们要将对应版本的mysql连接驱动放到kettle安装目录下面的lib文件夹下，然后重启kettle的客户端Spoon

重启Spoon客户端以后，我们就可以创建对应的数据库连接了，在转换视图的主对象树目录下，有个DB连接，右键然后选择新建，在打开数据库连接框里，填写正确的数据库信息，然后测试，测试无误后，可以保存此数据库连接。

数据库连接默认只对本转换有效，换一个转换以后，这个连接就没法用了，还需要新建数据库连接，所以我们需要将建好的这个数据库连接进行共享下，共享以后，其他的转换也能用我们提前建好的这个数据库连接了。

# 表输入

创建好数据库连接以后，我们就可以使用表输入控件了，双击表输入控件，选择刚刚创建的数据库连接，然后在SQL框里输入合适的查询语句，然后点击预览按钮，看能否预览到我们期望的数据。

# 二、Kettle输出控件

输出是转换里面的第二个分类，输出控件也是转换中的第二大控件，用来存储数据。输出是ETL里面的L（Load），主要做数据加载的工作。

由于Kettle中自带的输出控件比较多，本文只挑出开发中经常使用的几个输出控件来进行讲解，详情如下图。

# 2.1、Excel输出

Kettle中自带了两个Excel输出，一个Excel输出，另一个是Microsoft Excel输出。

Excel输出只能输出xls文件（适合Excel2003），Microsoft Excel输出可以输出xls和xlsx文件（适合Excel2007及以后）

Excel输出大家已经很熟悉了，本章不再赘述，接下来给大家讲下Microsoft Excel输出。

1）选择合适的扩展名

2）点击浏览，补全输出文件的路径已经文件名

# 2.2、文本文件输出

文本文件输出控件，顾名思义，这是一个能将数据输出成文本的控件，比较简单，在企业里面也比较常用。

1．设置对应的目录和文件名

2．设置合适的扩展名，比如txt，csv等

3．在内容框里设置合适的分隔符，比如分号，逗号，TAB等

4．在字段框里获取字段，并且给每个字段设置合适的格式

# 2.3、SQL文件输出

SQL文件输出一般跟表输入做连接，然后将数据库表的表结构和数据以sql文件的形式导出，然后做数据库备份的这么一个工作。

1．选择合适的数据库连接

2．选择目标表

3．勾选增加创建表语句和每个语句另起一行

4．填写输出文件的路径和文件名

5．扩展名默认为sql，这个不需要更改

# 2.4、表输出

表输出控件可以将kettle数据行中的数据直接写入到数据库中的表中，企业里做ETL工作会经常用到此控件。

1．选择合适的数据库连接

2．选择目标表，目标表可以提前在数据库中手动创建好，也可以输入一个数据库不存在的表，然后点击下面的SQL按钮，利用kettle现场创建

3．如果目标表的表结构和输入的数据结构不一致，还可以自己指定数据库字段

****

# 2.5、更新&插入/更新

更新和插入/更新，这两个控件是kettle提供的将数据库已经存在的记录与数据流里面的记录进行对比的控件。企业级ETL经常会用到这两个控件来进行数据库更新的操作

两者区别：

更新是将数据库表中的数据和数据流中的数据做对比，如果不同就更新，如果数据流中的数据比数据库表中的数据多，那么就报错。

插入/更新的功能和更新一样，只不过优化了数据不存在就插入的功能，因此企业里更多的也是使用插入/更新。

1．选择正确的数据库连接

2．选择目标表

3．输入两个表来进行比较的字段，一般来说都是用主键来进行比较

4．输入要更新的字段

# 2.6、删除

删除控件可以删除数据库表中指定条件的数据，企业里一般用此控件做数据库表数据删除或者跟另外一个表数据做对比，然后进行去重的操作。

1．选择数据库连接

2．选择目标表

3．设置数据流跟目标表要删除数据的对应字段

# 三、Kettle转换控件

转换控件是转换里面的第四个分类，转换控件也是转换中的第三大控件，用来转换数据。转换是ETL里面的T（Transform），主要做数据转换，数据清洗的工作。ETL整个过程中，Transform的工作量最大，耗费的时间也比较久，大概可以占到整个ETL的三分之二。

由于Kettle中自带的转换控件比较多，本文只挑出开发中经常使用的几个转换控件来进行讲解，详情如下图。

# 3.1、Concat fields

转换控件Concat fields，顾名思义，就是将多个字段连接起来形成一个新的字段。

# 3.2、值映射

值映射就是把字段的一个值映射成其他的值。在数据质量规范上使用非常多，比如很多系统对应性别sex字段的定义不同。所以我们需要利用此控件，将同一个字段的不同的值，映射转换成我们需要的值。

1．选择映射的字段

2．还可以自定义映射完以后的新字段名

3．可以设置不匹配时的默认值

4．设置映射的值

# 3.3、增加常量&增加序列

增加常量就是在本身的数据流里面添加一列数据，该列的数据都是相同的值。

增加序列是给数据流添加一个序列字段，可以自定义该序列字段的递增步长。

# 3.4、字段选择

字段选择是从数据流中选择字段、改变名称、修改数据类型。

# 3.5、计算器

计算器是一个函数集合来创建新的字段，还可以设置字段是否移除（临时字段）。我们可以通过计算器里面的多个计算函数对已有字段进行计算，得出新字段。

# 3.6、字符串剪切&替换&操作

转换控件中有三个关于字符串的控件，分别是剪切字符串，字符串操作，字符串替换

剪切字符串是指定输入流字段裁剪的位置剪切出新的字段。

字符串替换是指定搜索内容和替换内容，如果输入流的字段匹配上搜索内容就进行替换生成新字段。

字符串操作是去除字符串两端的空格和大小写切换，并生成新的字段。

# 3.7、排序记录&去除重复记录

去除重复记录是去除数据流里面相同的数据行。但是此控件使用之前要求必须先对数据进行排序，对数据排序用的控件是排序记录，排序记录控件可以按照指定字段的升序或者降序对数据流进行排序。因此排序记录+去除重复记录控件常常配合组队使用。

****

# 3.8、唯一行（哈希值）

唯一行（哈希值）就是删除数据流重复的行。此控件的效果和（排序记录+去除重复记录）的效果是一样的，但是实现的原理不同。排序记录+去除重复记录对比的是每两行之间的数据，而唯一行（哈希值）是给每一行的数据建立哈希值，通过哈希值来比较数据是否重复，因此唯一行（哈希值）去重效率比较高，也更建议大家使用。

# 3.9、拆分字段

拆分字段是把字段按照分隔符拆分成两个或多个字段。需要注意的是，字段拆分以后，原字段就会从数据流中消失。

# 3.10、列拆分为多行

列拆分为多行就是把指定字段按指定分隔符进行拆分为多行，然后其他字段直接复制。具体效果如下图

1．选择要拆分的字段

2．设置合适的分割符

3．设置分割以后的新字段名

4．选择是否输出新数据的排列行号，行号是否重置

# 3.11、行扁平化

行扁平化就是把同一组的多行数据合并成为一行，可以理解为列拆分为多行的逆向操作。但是需要注意的是行扁平化控件使用有两个条件：

使用之前需要对数据进行排序
每个分组的数据条数要保证一致，否则数据会有错乱

1．选择扁平化的字段

2．填写目标字段，字段个数跟每个分组的数据一致

# 3.12、列转行

列转行，顾名思义多列转一行，就是如果数据一列有相同的值，按照指定的字段，将其中一列的字段内容变成不同的列，然后把多行数据转换为一行数据的过程。具体效果如下图：

注意：列转行之前数据流必须按照分组字段进行排序，否则数据会错乱！

1．关键字段：从数据内容变成列名的字段

2．分组字段：列转行，转变以后的分组字段

3．目标字段：增加的列的列名字段

4．数据字段：目标字段的数据字段

5．关键字值：数据字段查询时的关键字，也可以理解为key

6．类型：要给目标字段设置合适的类型，否则会报错

# 3.13、行转列

行转列，一行转多列，就是把数据字段的字段名转换为一列，把数据行变为数据列。我们也可以简单理解为行转列控件是列转行控件的逆向操作。具体如下图：

1． Key字段：行转列，生成的列名字段名

2．字段名称：原本数据流中的字段名

3． Key值：Key字段的值，这个是自己自定义的，一般都跟前面的字段名称一样

4． Value字段：对应的Key值的数据列的列名

# 四、Kettle应用控件

应用是转换控件里面的第五个分类，这个分类下是Kettle给我们自带的一些工具类

# 4.1、替换NULL值

替换NULL值，顾名思义就是将数据里面的null值替换成其他的值，此控件比较简单，但是在企业里面也会经常用到。

1．可以选择替换数据流中所有字段的null值

2．也可以选择字段，在下面的字段框里面，根据不同的字段，将null值替换成不同的值

****

# 4.2、写日志

写日志控件主要是调试的时候使用，此控件可以将数据流的每行数据打印到控制台，方便我们调试整个程序。

1．选择日志级别

2．可以输入自定义输出的语句

3．选择要输出打印的字段

# 五、Kettle流程控件

流程是转换里面的第六个分类，流程分类下的控件主要用来控制数据流程和数据流向

# 5.1、Switch/case

Switch/case控件，最典型的数据分类控件，可以利用某一个字段的数据的不同的值，让数据流从一路到多路。

1．选择需要判断的字段

2．选择判断字段的值的类型

3．填写分类数据的判断条件和目标步骤

# 5.2、过滤记录

和Switch/case做对比的话，过滤记录相当于if-else，可以自定义输入一个判断条件，然后将数据流中的数据一路分为两路。

在下面先填写数据的判断条件
然后再上面选择下判断条件为true或者false的输出步骤

# 5.3、空操作

空操作，顾名思义就是什么也不做，此控件一般作为数据流的终点。

# 5.4、中止

中止是数据流的终点，如果有数据流到此控件处，整个转换程序将中止，并且在控制台输出报错信息。此控件一般用来校验数据，或者调试程序。

# 六、Kettle查询控件

查询是转换里面的第九个分类，查询控件是用来查询数据源里面的数据，并合并到主数据流中。

# 6.1、数据库查询

数据库查询就是从数据库里面查询出数据，然后跟数据流中的数据进行左连接的一个过程。左连接的意思是数据流中原本的数据全部有，但是数据库查询控件查询出来的数据不一定全部会列出，只能按照输入的匹配条件来进行关联。

选择合适的数据库链接
输入要去数据库里面查询的表名
输入两个表进行左连接的连接条件
获取返回字段，得到查询表返回的值

# 6.2、流查询

流查询控件就是查询两条数据流中的数据，然后按照指定的字段做等值匹配。注意：流查询在查询前把数据都加载到内存中，并且只能进行等值查询。

输入查询的数据流
输入两个流进行匹配的字段（等值匹配）
输入查询出的字段

# 七、Kettle连接控件

连接是转换里面的第十个分类，连接分类下的控件一般都是将多个数据集通过关键字进行连接起来，形成一个数据集的过程。

# 7.1、合并记录

合并记录是用于将两个不同来源的数据合并，这两个来源的数据分别为旧数据和新数据，该步骤将旧数据和新数据按照指定的关键字匹配、比较、合并。注意旧数据和新数据需要事先按照关键字段排序，并且旧数据和新数据要有相同的字段名称。

合并后的数据将包括旧数据来源和新数据来源里的所有数据，对于变化的数据，使用新数据代替旧数据，同时在结果里用一个标示字段，来指定新旧数据的比较结果。

旧数据源：选择旧数据来源的步骤
新数据源：选择新数据来源的步骤
标志字段：设置标志字段的名称，标志字段用于保存比较的结果，比较结果有下列几种

① “identical” – 旧数据和新数据一样

② “changed” – 数据发生了变化;

③ “new” – 新数据中有而旧数据中没有的记录

④ “deleted” –旧数据中有而新数据中没有的记录
关键字段：用于定位判断两个数据源中的同一条记录的字段。
比较字段：对于两个数据源中的同一条记录，指定需要比较的字段