大数据ETL开发之图解Kettle工具（入门到精通）

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

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

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

• 并发运行? ：选择并发，可提高读写速度

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

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

3.1.2 文本文件输入

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

任务：熟悉文本文件输入控件，并新建转换，将txt日志文件转换为Excel文件

使用文本文件输入控件步骤：

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

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

3）获取下字段，并给字段设置合适的格式（数字类型的数据尽量选Integer，因为number类型有两位小数点）

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

3.1.3 Excel文件输入

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

任务：两张sheet表合二为一

使用Excel输入控件步骤如下：

原始数据：

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

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

3）获取excel的sheet工作表

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

5）预览数据

3.1.4 XML输入

1）XML简介

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

2）XPath简介

XPath即为XML路径语言(XML Path Language)，它是一种用来确定XML文档中某部分位置的语言。XPath基于XML的树状结构，提供在数据结构树中找寻节点的能力。XPath使用路径表达式在XML文档中选取节点。下面列出了最有用的路径表达式

3）XML输入控件

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

任务：熟悉XML输入控件，将XML文件的学生数据写到excel文件中

1.浏览获取xml文件，将xml文件添加到kettle中

2.获取 xml文档的所有路径，设置合适的循环读取路径

3.获取字段，获得自己想要读取的所有字段，并且设置适当的格式

4.预览数据，看看能否读取到自己想要的数据

3.1.5 JSON输入

1）JSON介绍

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

JSON核心概念：

• 数组：[]

• 对象：{}

• 属性：key:value

2）JSON Path

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

格式：

• 点记法：$.[0]title

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

3）JSON 输入控件

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

任务：获取到JSON文件里面的id，field，value字段，写到excel文件中

原始数据：

1.浏览获取JSON文件（注意文件路径不能有中文），将json文件获取到kettle中

2.根据JSON Path点记法，获取到需要的字段，并且设置合适格式

3）新建JSON输入控件2

第二步的数据内容为：

3.1.6 表输入

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

1）创建数据库连接

MySQL驱动下载（一定要下载对应数据库版本）：官网下载地址

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

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

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

2）表输入

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

3.2 Kettle输出控件

---

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

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

3.2.1 Excel输出

Kettle中自带了两个Excel输出，一个Excel输出，另一个是Microsoft Excel输出。Excel输出只能输出xls文件（适合Excel2003），Microsoft Excel输出可以输出xls和xlsx文件（适合Excel2007及以后）

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

1）选择合适的扩展名

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

3.2.2 文本文件输出

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

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

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

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

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

3.2.3 SQL文件输出

SQL文件输出一般跟表输入做连接，然后将数据库表的表结构和数据以sql文件的形式导出，然后做数据库备份的这么一个工作。（Kettle里面没varchar类型尽量少用）

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

2．选择目标表

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

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

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

3.2.4 表输出

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

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

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

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

3.2.5 更新&插入/更新

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

两者区别：

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

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

步骤：

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

2．选择目标表

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

4．输入要更新的字段

3.2.6 删除

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

1．选择数据库连接

2．选择目标表

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

3.3 Kettle转换控件

---

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

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

3.3.1 Concat fields

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

任务：将staff表的firstname和lastname拼接起来，形成name字段，然后再将数据插入到新表emp中

原始数据：

3.3.2 值映射

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

任务：将staff表的sex字段，映射成男or女，然后再插入到emp表中

原始数据：

1．选择映射的字段

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

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

4．设置映射的值

3.3.3 增加常量&增加序列

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

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

任务：给表staff的数据加一列固定值slary和一个递增的number序列，在控制台预览下数据即可，不用输出

3.3.4 字段选择

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

任务：在上一章节的转换之后，添加字段选择控件，移除掉firstname字段，并且将lastname重命名为name，将slary重命名为money，然后再次预览数据，查看数据的变化

3.3.5 计算器

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

任务：在上一节的任务基础之上，添加计算器控件对money和number字段进行相乘，得出新字段acount，然后预览数据

执行结果：

3.3.6 字符串剪切&替换&操作

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

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

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

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

执行结果：

3.3.7 排序记录&去除重复记录

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

任务：利用excel输入控件读取input目录下的06_去除重复记录.xlsx，然后对里面重复的数据进行按照id排序并去重

原始数据：

执行结果：

3.3.8 唯一行（哈希值）

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

任务：利用唯一行（哈希值）控件对06_去除重复记录.xlsx去重，并且查看最后输出的数据跟上个任务有何区别

执行结果：

3.3.9 拆分字段

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

任务：将拆分字段.xlsx里面的NBA球星的姓名，拆分成姓跟名

文件内容：

执行结果：

3.3.10 列拆分为多行

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

任务：对08_列拆分为多行.xlsx的数据按照hobby字段进行拆分为多行，然后将新数据输出到excel文件中，查看数据

原始数据：

1．选择要拆分的字段

2．设置合适的分割符

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

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

执行结果：

3.3.11 行扁平化

行扁平化就是把同一组的多行数据合并成为一行，可以理解为列拆分为多行的逆向操作

但是需要注意的是行扁平化控件使用有两个条件：

1)使用之前需要对数据进行排序

2)每个分组的数据条数要保证一致，否则数据会有错乱

任务：将09_行扁平化.xlsx的数据按照hobby字段进行扁平化

原始数据：

1．选择扁平化的字段

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

3.3.12 列转行

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

：

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

任务：将input目录下的10_列转行.xlsx的数据进行列转行，熟悉列转行控件的使用

原始数据：

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

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

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

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

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

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

执行结果：

3.3.13 行转列

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

任务：将行转列.xlsx用excel控件输入，然后行转列，熟悉行转列控件的使用。

原始数据：

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

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

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

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

执行结果：

3.4 Kettle应用控件

---

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

3.4.1 替换NULL值

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

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

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

任务：替换excel数据12_替换NULL值.xlsx的bonus列的null值为0

原始数据：

执行结果：

3.4.2 写日志

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

1．选择日志级别

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

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

任务：在上个任务的基础之上，添加写日志控件，在控制台输出查看数据

执行结果：

3.5 Kettle流程控件

---

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

3.5.1 Switch/case

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

任务：将excel：13_Switch的数据按照部门字段进行分类，将同一个部门的数据输出到一个excel中

原始数据：

1．选择需要判断的字段

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

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

执行结果：

3 .5.2 过滤记录

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

任务：将数据按照工资字段进行判断，将工资在20000及以上的数据输出到一个excel中，将工资小于20000的输出到另外一个excel中

原始数据：

1.在下面先填写数据的判断条件

2.然后再上面选择下判断条件为true或者false的输出步骤

执行结果：

3.5.3 空操作

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

任务：修改上节的转换任务，将工资大于等于20000的数据输出，小于20000的数据直接丢弃，熟悉空操作控件的使用。

3.5.3 中止

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

任务：使用中止控件判断上节任务中是否有人的工资低于20000，如果发现有人的工资低于20000的话，中止程序，并在控制台输出信息。

3.6 Kettle查询控件

---

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

3.6.1 数据库查询

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

任务：利用表输入控件获取到staff表的数据，然后利用数据库查询控件查询到department表的数据，然后对两个表按照dept_id字段进行左连接，并预览数据

原始数据：

1.选择合适的数据库链接

2.输入要去数据库里面查询的表名

3.输入两个表进行左连接的连接条件

4.获取返回字段，得到查询表返回的值

执行结果：

3.6.2 流查询

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

任务：用流查询控件，将staff和department的数据按照dept_id字段进行关联起来

1.输入查询的数据流

2.输入两个流进行匹配的字段（等值匹配）

3.输入查询出的字段

执行结果：

3.7 Kettle连接控件

---

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

3.7.1 合并记录

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

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

任务：利用合并记录控件比较合并记录-新旧excel的数据，并预览数据，查看标志字段的内容

原始数据：

1.旧数据源：选择旧数据来源的步骤

2.新数据源：选择新数据来源的步骤

3.标志字段：设置标志字段的名称，标志字段用于保存比较的结果，比较结果有下列几种：

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

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

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

• ④“deleted” –旧数据中有而新数据中没有的记录

4.关键字段：用于定位判断两个数据源中的同一条记录的字段。

5.比较字段：对于两个数据源中的同一条记录，指定需要比较的字段

执行结果：

3.7.2 记录集连接

记录集连接可以对两个步骤中的数据流进行左连接，右连接，内连接，外连接。此控件功能比较强大，企业做ETL开发会经常用到此控件，但是需要注意在进行记录集连接之前，需要对记录集的数据进行排序，并且排序的字段还一定要选两个表关联的字段，否则数据错乱，出现null值。

任务：使用记录集连接控件对数据库表satff和department按照部门id分别进行内连接，左连接，右连接，外连接，查看数据的不同

原始数据：

注意：两个表进行排序记录的时候，排序的字段一定要选择部门id，否则数据会不正确

1.选择需要连接的两个数据流的步骤

2.选择连接类型，一共有四个：INNER，LEFT OUTER，RIGHT OUTER，FULL OUTER

3.从两个数据流步骤里面选出连接字段

执行结果：

3.8Kettle统计控件

---

统计是转换里面的第十三个分类，统计控件可以提供数据的采样和统计功能。

3.8.1 分组

分组控件的功能类似于GROUP BY，可以按照指定的一个或者几个字段进行分组，然后其余字段可以按照聚合函数进行合并计算。注意，在进行分组之前，数据最好先进行排序。

任务：给表staff的数据按照部门进行分组，求出各部门人数以及各部门员工的平均年龄。

原始数据：

1.选择分组字段

2.给其余字段选择合适的聚合函数进行计算

执行结果：

3.9 Kettle映射控件

映射是转换里面的第十八个分类，映射可以用来定义子转换，方便代码封装和重用。

3.9.1 映射

---

映射（子转换）是用来配置子转换，对子转换进行调用的一个步骤。

映射输入规范是输入字段，由调用的转换输入。

映射输出规范是向调用的转换输出所有列，不做任何处理

任务：封装一个子转换能够通过dept_id求出dept_name，然后使用另外一个转换调用此子转换，求出数据库staff表id=3的员工的姓名，年龄，部门id，部门姓名，并输出到控制台。

执行结果：

3.10 Kettle脚本控件

---

脚本是转换的第七个分类，脚本就是直接通过写程序代码完成一些复杂的操作。

3.10.1执行SQL脚本

执行sql脚本控件就是连接到数据库里面，然后执行自己写的一些sql语句

任务：利用执行sql脚本控件将student表数据的atguigu的年龄更新为18

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

2.填入要执行的sql语句

第4章 Kettle作业

==============================================================================

4.1 作业简介

---

大多数ETL项目都需要完成各种各样的维护工作。例如，如何传送文件；验证数据库表是否存在等等。而这些操作都是按照一定顺序完成。因为转换以并行方式执行，就需要一个可以串行执行的作业来处理这些操作。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

rocess=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5aSn5pWw5o2uX-Wwj-iigQ==,size_15,color_FFFFFF,t_70,g_se,x_16)

执行结果：

3.10 Kettle脚本控件

---

脚本是转换的第七个分类，脚本就是直接通过写程序代码完成一些复杂的操作。

3.10.1执行SQL脚本

执行sql脚本控件就是连接到数据库里面，然后执行自己写的一些sql语句

任务：利用执行sql脚本控件将student表数据的atguigu的年龄更新为18

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

2.填入要执行的sql语句

第4章 Kettle作业

==============================================================================

4.1 作业简介

---

大多数ETL项目都需要完成各种各样的维护工作。例如，如何传送文件；验证数据库表是否存在等等。而这些操作都是按照一定顺序完成。因为转换以并行方式执行，就需要一个可以串行执行的作业来处理这些操作。

[外链图片转存中…(img-l6R6oOry-1714501224210)]
[外链图片转存中…(img-enX1dNaz-1714501224211)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！