转换控件2w字图文详解》惠阳保姆13825404095

文章正文

发布时间：2025-02-12 11:11

一、Kettle介绍

Kettle 是一个开源的 ETL（Extract, Transform, Load，提取、转换、加载）工具，全称为KDE Extraction, Transportation, Transformation and Loading Environment，广泛应用于数据集成、数据清洗、数据迁移等领域。它的名字“Kettle”源自其“装置”功能的含义，即“把各种数据源像水一样提取出来并进行处理”。在2006年被Pentaho公式收购后，重命名为 Pentaho Data Integration（PDI），这个工具能够帮助用户在不同的数据源和目标系统之间进行数据转换、清洗和加载。Kettle 被广泛用于构建数据仓库、数据集成平台等。

二、转换（Transformation）

转换是Kettle中的核心组件之一，负责定义数据从源到目标的转换过程。转换由多个步骤（Step）组成，每个步骤都有特定的功能，如数据抽取、数据清洗、数据转换等。这些步骤通过跳（Hop）来连接，形成一个完整的数据转换流程。跳定义了一个单向通道，允许数据从一个步骤流动到另一个步骤。

在这里插入图片描述

步骤（Step）：步骤是转换中的基本组成部分，每个步骤都会读、写数据行（唯一例外是“生成记录”步骤，该步骤只写数据）。步骤将数据写到与之相连的一个或多个输出跳，再传送到跳的另一端的步骤。大多数步骤都可以有多个输出跳，且一个步骤的数据发送可以被设置为分发和复制。

分发是目标步骤轮流接收记录

复制是所有的记录被同时发送到所有的目标步骤

跳（Hop）：跳是步骤之间带箭头的连线，定义了步骤之间的数据通路。跳实际上是两个步骤之间的被称之为行集的数据行缓存。当行集满了，向行集写数据的步骤将停止写入；当行集空了，从行集读取数据的步骤停止读取。

三、转换控件详解 1、输入控件

输入是转换里面的第一个分类，输入控件也是转换中的第一大控件, 用来抽取数据或者生成数据。输入是ETL里面的E (Extract)，主要做数据提取的工作，本文挑选其中的主流输入控件进行介绍。

在这里插入图片描述

1.1 CSV文件输入

CSV文件是一种文本文件，其中数据的每个部分都由逗号分隔。这种格式使得数据能够以简单的文本形式存储，同时保持表格数据的结构。

在这里插入图片描述

步骤名称：自定义步骤名称，确保在同一个转换内命名唯一

文件名：点击浏览选择对应的CSV文件

列分隔符：默认是CSV的分隔符逗号

封闭符：结束行数据的读写（不用改）

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

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

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

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

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

文件编码：如果预览数据的名称出现乱码，可更换文件编码为UTF-8

1.2 XML文件输入

XML（eXtensible Markup Language，可扩展标记语言）是一种标记语言，它被设计用来传输和存储数据。与HTML（HyperText Markup Language，超文本标记语言）主要用于显示数据不同，XML更侧重于数据本身的结构和内容。

1、点击浏览选择XML文件

2、点击增加后，添加到选中的文件和目录列表中

在这里插入图片描述

3、点击获取XML文档的所有路径后选择循环读取路径

在这里插入图片描述

4、点击获取字段

在这里插入图片描述

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

XPath表达式说明与用法示例
nodename 选择指定节点名称的所有节点 //p：选择所有<p>节点
/ 从根节点开始选择，相当于绝对路径 /html/head/title：从根节点<html>开始，选择<head>下的<title>节点
// 从当前节点开始选择后代节点，相当于相对路径 //div：选择文档中所有的<div>节点
. 选择当前节点 .//p：选择当前节点下的所有<p>节点（包括子孙节点）
.. 选择当前节点的父节点 ../title：选择当前节点的父节点下的<title>节点
@ 选择属性 //a[@href]：选择所有具有href属性的<a>节点
* 匹配任何元素节点 //div/*：选择所有<div>下的直接子元素
@* 匹配任何属性节点 //a/@*：选择所有<a>节点的所有属性
node() 匹配任何类型的节点 //node()：选择文档中的所有节点
[谓语] 用于筛选节点，谓语可以是整数、属性、函数等 //div[@class="footer"]：选择class属性为footer的<div>节点
position() 返回当前被处理的节点的位置 //li[position()=1]：选择第一个<li>节点
last() 返回当前节点集中的最后一个节点 //li[position()=last()]：选择最后一个<li>节点
contains(string1,string2) 判断string1是否包含string2 //*[contains(@class,'menu')]：选择class属性中包含menu的节点
text() 获取节点的文本内容 //title/text()：获取<title>节点的文本内容
1.3 JSON文件输入

JSON（JavaScript Object Notation）文件是一种轻量级的数据交换格式文件，它基于ECMAScript (欧洲计算机协会制定的js规范)的一个子集，采用完全独立于语言的文本格式来存储和表示数据。

1、点击浏览选择JSON文件

2、点击增加后，添加到选中的文件和目录列表中

在这里插入图片描述

3、使用JSON Path或者点击Select fields，获取到需要的字段，并且设置合适格式

在这里插入图片描述

4、针对数据嵌套情况，新增一个json输入步骤用于解析
在这里插入图片描述

在这里插入图片描述

JSON Path是一种查询JSON对象的语言，它允许用户通过一种简洁明了的语法来定位和提取JSON对象中的特定数据。这种语言类似于XML中的XPath，但专门为JSON数据格式设计。

表达式示例 JSON用法说明
$ { "name": "John", "age": 30 } 获取整个JSON对象
$.name { "name": "John", "age": 30 } 获取name属性的值，即"John"
$['age'] { "name": "John", "age": 30 } 获取age属性的值，即30
$.* { "name": "John", "age": 30, "city": "New York" } 获取所有属性的值，即["John", 30, "New York"]
$.. { "name": "John", "details": { "age": 30, "city": "New York" } } 递归获取所有元素的值
$.details[0] { "details": [{ "age": 30 }, { "city": "New York" }] } 获取details数组的第一个元素，即{ "age": 30 }
$.details[*].age { "details": [{ "age": 30 }, { "age": 25 }] } 获取details数组中所有元素的age属性值，即[30, 25]
$..[?(@.age > 25)] { "details": [{ "age": 30 }, { "age": 20 }] } 筛选age大于25的元素，即[{ "age": 30 }]
$..@ { "name": "John", "age": 30 } 获取当前元素的值，即整个JSON对象的内容
1.4 Excel文件输入

1、根据表格类型选择引擎（xls或者xlsx）

在这里插入图片描述

2、点击浏览选择Excel文件

3、点击增加后，添加到选中的文件列表中

在这里插入图片描述

4、获取工作表并添加

在这里插入图片描述

5、选择字段并预览

在这里插入图片描述

1.5 文本文件输入

1、点击浏览选择Excel文件

2、点击增加后，添加到选中的文件列表中

在这里插入图片描述

3、选择分隔符，编码方式

在这里插入图片描述

4、点击获取字段并预览

在这里插入图片描述

1.6 表输入

表输入是Kettle中极为常用的一种输入组件，鉴于企业数据多存储于数据库中，这一功能显得尤为重要。Kettle支持连接多种主流数据库，如Oracle、MySQL、SQL Server等。然而，在建立与这些数据库的连接之前，配置相应的数据库驱动是不可或缺的步骤。接下来，我们将以MySQL为例，详细阐述Kettle如何连接MySQL数据库。

1、下载mysql连接jar包放入安装目录下的lib目录中

MySQL :: Download MySQL Connector/J (Archived Versions)

在这里插入图片描述

1、在DB连接中配置数据源，右键点击共享后其他转换也能使用该数据源

在这里插入图片描述

2、预览

在这里插入图片描述

2、输出控件

“输出”在数据转换中扮演着至关重要的角色，它既是转换流程中的一个关键分类，也是实现数据加载（Load）的核心环节。而“输出控件”则是确保这一流程顺利进行的重要工具，它负责数据的存储和管理。

在这里插入图片描述

2.1 Excel输出

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

1、配置文件名和扩展名

在这里插入图片描述

2、按需配置内容和格式（一般不调整）

在这里插入图片描述

3、获取字段

在这里插入图片描述

2.2 SQL文件输出

1、配置输出的文件名和扩展名

在这里插入图片描述

2、查看导出内容

在这里插入图片描述

2.3 表输出

1、选择目标表，或者自定义目标表

在这里插入图片描述

2、自定义目标表时，可以点击SQL，点击执行创建目标表

在这里插入图片描述

3、可以看到数据已输出到test_table表

在这里插入图片描述

2.4 更新

更新操作涉及将数据库表中的数据与数据流中的数据进行比对。如果发现两者之间存在差异，则进行更新。然而，如果数据流中的数据量超出了数据库表中的数据量，系统则会报错。

1、现在ywl表和test_table表里的数据量一致，我修改几条test_table表的数据

在这里插入图片描述

2、更新test_table表中数据，查询值选择时间（一般为唯一主键）

在这里插入图片描述

3、执行后test_table表数据重新和ywl表数据保持一致

在这里插入图片描述

4、特殊情况，用来查询的关键字对应的test_table表中数据有多条时，虽然两表数据量不一致，但是不会报错，test_table表中数据全部更新

执行前

在这里插入图片描述

执行后

在这里插入图片描述

4、这时候删除一条test_table表中数据再执行，这时候会报错，因为更新要求两表数据量一致

在这里插入图片描述

2.5 插入 / 更新

那有什么办法可以做到缺失的时候插入，其他时候更新呢，这时候就可以使用插入 / 更新步骤。

在这里插入图片描述

执行后发现test_table表被删除的数据又插入了。

2.6 删除

这个控件允许用户根据特定条件从数据库表中移除数据。

在这里插入图片描述

在test_table中新增两条ywl中不存在的数据

在这里插入图片描述

执行后test_table中数据除了新增的两条ywl中不存在的记录外，全部被删除

在这里插入图片描述

3、转换控件

ETL中的Transform阶段是一个至关重要的环节，它负责将Extract阶段从源系统中提取出来的数据进行清洗、转换和处理，以满足目标数据存储系统的要求，是ETL过程中最为复杂和耗时的部分之一。

在这里插入图片描述

3.1 Add constants增加常量

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

给表employee_employee的数据加一列固定值HeatSubsidy

在这里插入图片描述

可以看到已添加

在这里插入图片描述

3.2 增加序列

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

在这里插入图片描述

3.3 Concat fields

将两个或多个字段（或变量）的值连接在一起形成一个新的字符串。

在这里插入图片描述

Target Field Name:拼接后的字段名

Separator:拼接符

Fields:要拼接的字段

可以看到已拼接

在这里插入图片描述

3.4 Value mapper

值映射（Value Mapping）是一种数据转换技术，它用于将数据字段中的特定值转换为其他预定义的值。在数据质量管理和数据整合的场景中，值映射尤为重要，因为不同系统或数据源可能使用不同的编码或表示法来表示相同的概念。

在这里插入图片描述

使用的字段名：需要映射的字段

字段值：从源值映射为目标值

可以看到已映射

在这里插入图片描述

3.5 列拆分为多行

列拆分为多行就是把指定字段按指定分隔符拆分为多行

在这里插入图片描述

可以看到habby已拆分

在这里插入图片描述

3.6 行扁平化

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

1、目标字段的数量等于hobby的类型数量

在这里插入图片描述

3.7 列转行

将原始数据表格中的列数据转换为行数据，这种操作常用于将多个列的值合并为一列，并添加新的列名来标识原始列名。

在这里插入图片描述

1、配置关键字段、分组字段、目标字段

在这里插入图片描述

2、转换完成

在这里插入图片描述

3.8 行转列

行转列是指将数据库或数据表格中的行数据转换为列数据。这种操作通常用于数据的汇总、报表制作或数据透视。

在这里插入图片描述

1、配置Key字段、字段

在这里插入图片描述

2、转换完成

在这里插入图片描述

3.9 计算器

可以通过计算器里面的多个计算函数对已有字段进行计算，得出新字段。

在这里插入图片描述

已转换

在这里插入图片描述

3.10 唯一行（哈希值）

唯一行（哈希值）就是删除数据流重复的行，给每一行的数据建立哈希值，通过哈希值来比较数据是否重复，唯一行（哈希值）去重效率比排序记录+去除重复记录高。

在这里插入图片描述

1、输入数据

在这里插入图片描述

2、转换后重复数据已删除

在这里插入图片描述

3.11 剪切字符串

在这里插入图片描述

1、输入数据

在这里插入图片描述

2、转换后数据

在这里插入图片描述

3.12 Replace in string替换字符串

在这里插入图片描述

1、输入数据

在这里插入图片描述

2、转换后数据

在这里插入图片描述

3.13 字符串操作

在这里插入图片描述

Trim type：去除左右两端空格

Lower/Upper：大小写

Padding：填充

Pad cahr：填充的字符

Pad Length：填充的长度

InitCap：首字母转换为大写，其余字母转换为小写

Escape：转义字符处理，如Use CDATA可以将字符串转换为CDATA格式的字符串

Digits：数字处理，none：不进行数字处理，only：只保留数字字符，去除其他字符，remove：去除数字字符，保留其他字符。

Remove Special Character：去除特殊字符，可以根据需求选择需要删除的字符，如空格（space）、换行符（line feed）等

1、输入数据

在这里插入图片描述

2、输出数据

4、应用控件

在这里插入图片描述

4.1 If field value is null替换NULL值

缺失值处理

在这里插入图片描述

1、输入数据

在这里插入图片描述

2、输出数据

在这里插入图片描述

4.2 发送邮件

在这里插入图片描述

1、自定义常量数据配置

在这里插入图片描述

2、发送邮件配置

在这里插入图片描述

3、执行后，已收到邮件

在这里插入图片描述

4.3 写日志

日志控件可以将数据流的每行数据打印到控制台。

1、配置打印的内容

在这里插入图片描述

2、执行后日志已打印

在这里插入图片描述

5、流程控件 5.1 Switch/case

Switch/case可以利用某一个字段的值的不同，让数据流向不同的步骤。

在这里插入图片描述

1、配置Switch / case

在这里插入图片描述

3、可以看到数据已分流

在这里插入图片描述

5.2 过滤记录

在这里插入图片描述

1、配置过滤字段和过滤条件

在这里插入图片描述

2、可以看到数据数据已分流

在这里插入图片描述

5.3 中止

在转换过程中，有时需要处理异常情况或手动中止流程。这时，中止控件（Stop Control）就显得尤为重要。

在这里插入图片描述

Abort the running transformation：这个选项会立即停止整个转换的执行。所有正在进行的步骤都会被中止，不管它们是否已经完成当前的数据处理。

Abort and log as an error：这个选项不仅会立即停止整个转换的执行，还会将中止原因记录为错误日志。这对于调试和后续分析错误原因非常有帮助。

Stop input processing：这个选项的作用是停止当前步骤（Step）的输入数据处理。当选择这个选项时，转换不会立即停止，而是会停止当前步骤接收和处理新的输入数据。已经处理的数据不会受到影响，后续步骤仍然会继续处理它们。

5.4 空操作（什么也不做）

空操作是一个特殊的步骤（Step），它实际上不会对数据流执行任何操作。这个控件的主要作用是作为数据流的终点或占位符，在特定的转换逻辑中可能需要一个不执行任何动作的步骤。

在这里插入图片描述

6、脚本控件 6.1 执行SQL脚本

在这里插入图片描述

1、配置sql，Alice的工资+300

在这里插入图片描述

执行每一行？： 勾选“执行每一行”后，就可以在UPDATE语句中使用?来代替变量名了。且“Bind parmaters”变为可用

Execute as a single statement：（作为单条语句执行）是一个在执行SQL脚本组件中的选项。这个选项决定了SQL脚本是应该被当作一个整体发送到数据库执行，还是应该按照分号（;）分隔成多个单独的语句来执行。

Quotes String： 该选项与“Bind parmaters”选项只能选一个，勾选该选项的作用是，在替换的变量前后两端加上单引号，并且对于特殊字符进行转义，比如条件值中，有单引号时，kettle会自动在单引号前再加一个单引号（在两端单引号的基础上）

Bind parmaters： 勾选时字段为字符类型时，问号两端不能加单引号

UPDATE test_orm.employee_employee SET salary=salary WHERE name =?

不勾选时需要在问号两端加单引号，如果是数值类型，则不需要

UPDATE test_orm.employee_employee SET salary=salary WHERE name =‘’?’

变量替换： 勾选后where条件可以使用动态参数替换

用法1：如果仅仅勾选“变量替换”，则sql语句中，只能使用变量来替换，而不能用传的参数，即只能用${V_TABLE_NAME}，而且，如果字段为字符型，还必须加单引号，即UPDATE语句需要写成如下：

UPDATE test_orm.employee_employee SET salary=salary WHERE name =‘${name}’

用法2：先勾选了“执行每一行”，然后编辑左下角的参数，此时可以从上一个步骤的数据流中或者数据作为条件：

UPDATE test_orm.employee_employee SET salary=salary WHERE name =?

2、执行转换后sql已执行

在这里插入图片描述

6.2 执行SQL脚本（字段流替换）

读取字段中的sql脚本并执行

在这里插入图片描述

1、输入表的s字段中存放了insertsql

在这里插入图片描述

2、配置读取s字段

在这里插入图片描述

3、执行后可以看到成功执行了insert语句

在这里插入图片描述

7、查询控件 7.1 Column exists

用于确认输入的表名和列名是否存在

在这里插入图片描述

1、配置测试数据

在这里插入图片描述

2、输出结果，与实际情况一致，3个字段存在

在这里插入图片描述

7.2 Database lookup数据库查询

数据库查询控件可以从数据库里面查询出数据，然后跟数据流中的数据进行左连接。

在这里插入图片描述

1、配置Database lookup

在这里插入图片描述

2、执行后输出left join后的数据

在这里插入图片描述

7.3 检查表是否存在

在这里插入图片描述

1、配置测试数据

在这里插入图片描述

2、执行结果与实际一致，表存在

在这里插入图片描述

7.4 流查询

流查询控件可以将两条数据流中的数据加载到内存中，然后按照指定的字段做等值匹配后输出。

在这里插入图片描述

1、流查询配置

在这里插入图片描述

2、执行后结果

在这里插入图片描述

8、连接控件 8.1Merge join合并连接

合并连接可以实现两个数据流的inner、left、right、full类型的join,注意输入的数据流需要事先排好序，并且排序的字段还一定要选两个表关联的字段，否则数据错乱，出现null值。

在这里插入图片描述

1、步骤配置

在这里插入图片描述

2、执行结果

在这里插入图片描述

8.2 合并记录

合并上下数据流，等同于sql中的union，即合并去重,注意输入的数据流需要事先排好序，并且排序的字段还一定要选两个表关联的字段，否则数据错乱，出现null值。

在这里插入图片描述

1、步骤配置

在这里插入图片描述

2、执行结果，已合并

在这里插入图片描述

9、统计控件 9.1 分组

可以实现类似sql中的group by聚合功能

在这里插入图片描述

1、步骤配置

在这里插入图片描述

2、执行结果

在这里插入图片描述

9.2 在内存中分组

功能等同于分组控件，在内存中进行

在这里插入图片描述