Kettle

一、Kettle简介

Kettle（又名：Pentaho Data Integration）是一款国外开源的ETL工具。

• Kettle 中文名称叫水壶，该项目的主程序员MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。
• kettle 是纯 java 开发，开源的 ETL工具，用于数据库间的数据迁移 。可以在 Linux、Windows、Unix 中运行。有图形界面，也有命令脚本，还可以二次开发。

二、环境搭建

2.1.安装 java jdk 环境

• 环境变量：JAVA_HOME
• 环境变量：PATH
• 验证：打开 cmd，执行 java –version

参考网址：

2.2.解压 Kettle 软件

• windows：spoon.bat（双击）
• mac/linux：spoon.sh

• cd 解压目录下
• ./spoon.sh

三、Kettle数据转换

3.1.Kettle 的基本开发步骤

基本步骤：

①.新建转换

②.构建Kettle的数据流图

• 数据从哪来（Extract）？
• 经过什么处理（Transform）？
• 最终数据到哪去（Load）？

③.配置数据流图中的各个组件

• Kettle内置各种组件

④.保存并启动执行

3.2.Txt转Excel

3.2.1.需求说明

需求：将 user.txt 文件的数据抽取转换输出到 user_by_kettle.xls 文件。

在Kettle中将 user.txt 文件的数据抽取转换输出到 user_by_kettle.xls 文件中：

关键要点：

①.文本文件输入组件

• 配置输入的 user.txt 文件：注意分隔符的设置

②.Excel输出组件

• 配置输出的 user_by_kettle.xls 文件

3.2.2.Kettle实现

创建Kettle数据流图：

• 输入：找到文本文件输入
• 输出：Excel输出
• 连接：shift 键 + 鼠标拖动

文本文件输入组件配置

①.添加需要转换的文件

②.指定多列之间的分隔符和编码格式

 ③.点击获取字段，即可查看数据

Excel输出组件配置

指定Excel数据组件文件

获取指定，可以指定输出的字段类型

保存当前转换，并启动执行

3.3.Excel转MySQL

3.3.1.需求

需求：将 users.xls 文件中的数据，抽取转换输出到 MySQL 的 tb_user 表中。

 

3.3.2.Kettle实现

①.先需要创建mysql数据库kettle_demo用于存储表格：

drop database if exists kettle_demo;
create database if not exists kettle_demo charset=utf8;

②.kettle中的lib目录中加入mysql的jar文件

• 将资料中的 MySQL jdbc 驱动包mysql-connector-java-5.1.41-bin.jar、mysql-connector-java-8.0.16.jar 放置到 data-integration/lib 中
• 找到 data-integration\simple-jndi\jdbc.properties 文件编辑，在末尾加上连接信息（见PPT备注信息）
• 注意：配置之后，kettle软件一定要重新启动！！！

③.创建Excel输入和输出

④.配置输入组件

选择需要转换的sheet

获取字段信息，调整字段类型和格式

⑤.配置表输出

创建数据库连接：

保存后，点击SQL修改字段类型，然后执行SQL语句创建表

⑤.保存，执行转换操作

执行会发现，报错了，这是因为id字段设置为int类型，长度只能为11，而数据超出字段长度：

要解决这个问题，只需要修改表中id字段的类型和长度

在此执行转换，发现已经成功：

3.4.MySQL表间转换

需求：将 tb_user 表中的数据，抽取转换输出到 tb_user1 表中

①.共享数据连接，共享数据连接后，就不用每次都创建新的数据连接了

②.创建数据流图

③.配置表输入组件

④.预览数据如下：

⑤.配置表输出组件

⑥.执行SQL创建表

⑦.保存配置，并执行：

四、常见组件使用

Kettle中有很多常见组件，本小节主要介绍几个常用的组件：

• 插入-更新组件
• switch-case组件
• SQL脚本组件

4.1.插入-更新组件

在 Kettle（Pentaho Data Integration，PDI）中，插入-更新组件（Insert/Update）用于实现对数据库的插入或更新操作，具体是根据特定的条件来判断是向数据库表中插入新记录，还是更新已有的记录（保证两种表的数据一致性）。这个组件通常用于在ETL（Extract, Transform, Load）流程中处理数据库的数据同步。

4.1.1.需求说明

示例需求：将 tb_user 表中的更新/新增的数据，抽取更新/新增到 tb_user1 表中

4.1.2.插入更新和表到表的区别

插入更新和表到表区别

• t_user_to_t_user1 : 只进行全量追加.
• 插入更新 : 对比关键字段，更新所有数据. (不会删除)

4.1.3.案例实现

①.创建数据流图

②.配置表输入组件

③.配置插入更新组件，需要指定目标表（需要先创建）

④.执行SQL创建表

⑤.保存并运行，会发现能同步数据到tb_user1中了，但是由于t_user表中中张三数据有两条，ID相同，所以只显示了一条

修改t_user表中李四的age字段修改为26，然后执行转换，发现数据t_user1中李四的age被修改为了26

4.2.switch/case组件

4.2.1.需求说明

需求：将 tb_user 表中的数据，抽取并按照性别输入到 3 个不同的 Excel 文件中

效果如下：

4.2.2.实现

①.创建数据流图

②.新建后，男和女Excel输出选择，create a new target case for this step（为此步骤创建一个新的目标案例），而其他Excel则输出选择The default target step

③.配置表输入组件

④.配置switch组件

⑤.配置excel输出组件

• Excel输出-男

• Excel输出-女

• Excel输出-其他

⑥.取消数据库连接设置中取消勾选Supports the timestamp data type，用于支持timestamp 类型

⑦.保存并执行转换，查看生成的Excel

4.3.SQL脚本组件

SQL 脚本组件 是一种用于执行 SQL 查询的工具，通常用于以下用途：

• 从多个数据源中提取数据。
• 在数据转换前清理或预处理数据库中的数据。
• 将转换后的数据加载回数据库。
• 对数据表执行批量更新或删除操作。

4.3.1.需求说明

需求：使用Kettle执行SQL脚本，将 t_user1 表中的数据清空

4.3.2.Kettle的SQL脚本

①.创建数据流图

②.配置SQL组件

③.保存并执行，会发现t_user1表中的数据已经被清除掉了

五、转换参数设置

5.1.需求

需求：使用Kettle执行SQL脚本删除 tb_user1 表中指定省份的用户，启动时可以指定具体省份

关键要点：

• 配置 Kettle 转换参数

• 双击转换文件空白处，在弹出的窗口选择命名参数，进行设置即可，参数在这个转换的组件中可以被使用。
• 组件中使用参数的语法：${参数名}

• 执行SQL脚本组件

• 配置执行的 SQL 语句，使用转换参数

5.2.详细设置

①.配置转换参数

②.配置sql组件: 转换参数使用格式 **${参数名}**

③.保存并执行(执行时需要设置参数值)

④.执行会发现北京市的数据被删除了

六、作业(job)开发

"作业（Job）" 是一种控制流程的机制，主要用于管理和调度一系列步骤和任务的执行顺序。它与"转换（Transformation）"不同，后者专注于具体的数据处理，而"作业"则更加侧重于流程控制和任务调度。

6.1.现实问题

问题：如果kettle的转换，需要定期执行该怎么办？

注意：利用之前创建的Excel输出到表的转换设置定时执行

作业(job)的作用，就是可以把多个转换组合起来，并且可以根据需要设置定时周期性执行。

6.2.实现

创建数据流图，配置转换（加载之前的的Excel输出到表的转换）

设置定时策略

保存并启动，会发现这个定时任务一直在执行，每隔5s钟就会执行一个转换

表中的数据在不断地增加

七、email邮箱服务

在我们上个章节中通过通过“作业”实现了定时任务将Excel表中数据添加到表中，可以在之前的基础上，添加任务成功或者失败的时候发送邮件通知

7.1.创建数据流图

在之前的基础添加发送邮件

7.2.设置发送邮件服务器

这里以126邮箱设置为例进行说明

下面对于126邮箱进行配置，后面座位Jenkins发送邮件的服务邮箱使用

7.2.1.登录网页版邮箱（https://mail.126.com/)，进入126邮箱首页。

7.2.2.点击上方“设置”，选择“POP3/SMTP/IMAP”选项。

7.2.3.在新弹出的弹窗中，点击“继续开启”。

扫码页面您可以选择扫码发送短信，或者点击下方“手动发送短信”。

7.2.5.点击“我已发送”后，如果系统检测到用户成功发送短信，即获取到授权码。

这个授权码你需要复制保存下来，以供后续使用

7.3.设置发送邮件

7.3.1.设置成功邮件

对于成功邮件进行设置：添加收件人和发件人邮箱

设置邮箱服务器和发件人信息，注意密码哪里需要使用授权码

设置发送的邮件内容

7.3.2.设置失败邮件

跟成功邮件只有发送的内容不一致，故而未在列出

7.4.保存执行

这里修改定时任务，设置每天17:20定时执行任务

然后运行，会发现任务执行后，收件人能收到邮件