大数据技术之 Kettle(PDI)

Kettle

第一章 Kettle概述

1.1、ETL简介

Extract-Transform-Load(ETL)是一种常见的数据集成过程,用于从一个或多个数据源中提取数据,对数据进行转换和清洗,然后加载到目标数据存储中。

ETL 过程通常包括以下三个步骤:

  1. 提取(Extract):从一个或多个数据源中提取数据。数据源可以是关系型数据库、文件、API、日志文件等。在提取阶段,需要确定要提取的数据范围和数据格式。

  2. 转换(Transform):对提取的数据进行转换和清洗。这包括数据格式转换、数据清洗、数据合并、数据计算等操作。转换的目的是将数据转换为目标数据存储的结构和格式,并确保数据的准确性和一致性。

  3. 加载(Load):将转换后的数据加载到目标数据存储中。目标数据存储可以是关系型数据库、数据仓库、数据湖等。在加载阶段,需要确定数据的存储位置和加载方式,例如全量加载或增量加载。

ETL 过程的目标是将数据从源系统中提取出来,并经过转换和清洗后加载到目标系统中,以支持数据分析、报表生成、业务决策等需求。ETL 过程通常由专门的ETL工具或编程语言来实现,例如Talend、Informatica、Apache Spark等。

ETL 过程在数据集成和数据处理中起着重要的作用,可以帮助组织将分散的数据整合起来,并提供高质量、一致性的数据供业务使用。

1.2、Kettle简介

Kettle(也称为Pentaho Data Integration (简称:PDI))是一种开源的ETL(Extract-Transform-Load)工具,用于数据集成和数据处理。它是Pentaho Suite的一部分,由Pentaho公司开发和维护。

Kettle提供了一个可视化的图形化界面,使用户能够通过拖放和连接组件来设计和配置ETL流程。它支持多种数据源和目标,包括关系型数据库、文件、API、Hadoop等。Kettle提供了丰富的转换和清洗功能,可以对数据进行格式转换、数据过滤、数据合并、数据计算等操作。它还支持数据质量检查、错误处理和数据校验等功能。

Kettle具有以下特点和优势:

  1. 简单易用:Kettle提供了直观的图形化界面,使用户能够轻松地设计和配置ETL流程,无需编写复杂的代码。

  2. 强大的转换和清洗功能:Kettle提供了丰富的转换和清洗组件,可以满足各种数据处理需求,包括数据格式转换、数据过滤、数据合并、数据计算等。

  3. 多种数据源和目标支持:Kettle支持多种数据源和目标,包括关系型数据库、文件、API、Hadoop等,可以方便地集成不同类型的数据。

  4. 可扩展性:Kettle提供了丰富的插件和扩展机制,可以根据需要添加自定义组件和功能。

  5. 平台无关性:Kettle是基于Java开发的,可以在多个操作系统上运行,包括Windows、Linux和Mac OS等。

总之,Kettle是一种功能强大、易于使用的ETL工具,可以帮助用户实现数据集成和数据处理需求,提供高质量、一致性的数据供业务使用。

1.3、作业 和 转换 概念

作业(Job)和转换(Transformation)是两个核心概念,用于实现数据集成和处理的任务。

  1. 转换(Transformation):转换是Kettle中的基本单位,用于定义数据的抽取、转换和加载过程。一个转换由一系列的步骤(Step)组成,每个步骤执行特定的数据处理操作。例如,一个转换可以包括从数据库中抽取数据、对数据进行清洗和转换,然后将数据加载到目标数据库中。转换可以通过图形化界面进行设计和配置,也可以通过代码生成方式进行定义。

  2. 作业(Job):作业是由一个或多个转换组成的高级任务。作业用于定义和控制转换的执行顺序和条件。一个作业可以包含多个转换,并且可以定义转换之间的依赖关系和触发条件。例如,一个作业可以定义在每天凌晨执行一个转换,然后根据转换的执行结果决定是否执行下一个转换。作业可以通过图形化界面进行设计和配置,也可以通过代码生成方式进行定义。

作业和转换的关系是,一个作业可以包含一个或多个转换,作业用于控制转换的执行顺序和条件。转换是实际执行数据处理操作的单元,而作业是对转换进行组织和管理的容器。通过作业和转换的组合,可以实现复杂的数据集成和处理任务,并且可以灵活地控制任务的执行流程和条件。

1.4、核心组件

Kettle(Pentaho Data Integration)有几个核心组件,用于实现数据集成和处理的功能。以下是一些主要的核心组件:

转换(Transformation):转换是Kettle中的基本单位,用于定义数据的抽取、转换和加载过程。一个转换由一系列的步骤(Step)组成,每个步骤执行特定的数据处理操作。例如,一个转换可以包括从数据库中抽取数据、对数据进行清洗和转换,然后将数据加载到目标数据库中。

步骤(Step):步骤是转换中的基本操作单元,用于执行具体的数据处理操作。Kettle提供了许多内置的步骤,如数据库输入、文本文件输出、字段计算等。用户还可以自定义步骤来满足特定的数据处理需求。

作业(Job):作业是由一个或多个转换组成的高级任务。作业用于定义和控制转换的执行顺序和条件。一个作业可以包含多个转换,并且可以定义转换之间的依赖关系和触发条件。

连接(Connection):连接用于定义数据源和目标的连接信息。Kettle支持多种类型的连接,如数据库连接、文件连接等。连接包括连接的URL、用户名、密码等信息,用于在转换中访问和处理数据。

参数(Parameter):参数用于在转换和作业中传递和共享数据。参数可以在转换和作业的配置中定义,并且可以在运行时动态地设置和获取。参数的使用可以增加转换和作业的灵活性和可重用性。

调度器(Scheduler):调度器用于定时执行作业和转换。Kettle提供了一个内置的调度器,可以根据预定义的时间表或触发条件自动触发作业和转换的执行。调度器还可以监控作业和转换的执行状态,并提供日志和报告。

这些核心组件共同构成了Kettle的功能框架,可以实现灵活、可靠的数据集成和处理任务。用户可以通过图形化界面或代码生成方式来配置和管理这些组件,以满足不同的数据处理需求。

1.5、下载安装

链接: https://www.hitachivantara.com/en-us/products/pentaho-plus-platform/data-integration-analytics/pentaho-community-edition.html

在这里插入图片描述
往下划,找到pdi
在这里插入图片描述

下载后目录解析:

在这里插入图片描述在这里插入图片描述

启动:有可能闪退,按我下面方法启动流程

使用管理员启动Spoon.bat

不行的按下面网上的流程试试
在这里插入图片描述

打开界面
在这里插入图片描述

第二章 控件使用

2.1、初体验:csv 转换 excel 示例

1、新建一个转换
2、拉一个 输入 下的 csv文件输入,双击编辑,选择一个csv文件,再点击获取字段

在这里插入图片描述
3、再拉一个输出 下的excel文件输出,双击编辑,新建一个文件名
4、将csv文件关联上excel文件(shift间按住 或者 鼠标移动到图标下方有一个按钮),选择主输出文件,excel组件再次打开,选择字段,获取字段,就能获取出csv的字段

在这里插入图片描述

在这里插入图片描述

5、点击运行,可选日志级别

在这里插入图片描述
在这里插入图片描述

2.2、转换

在这里插入图片描述

2.2.1、输入控件

在Kettle中,转换的输入是指从数据源中读取数据并将其提供给转换进行处理的过程。Kettle提供了多种输入步骤,可以从不同类型的数据源中读取数据。以下是一些常用的输入步骤:

  1. 文本文件输入(Text File Input):用于从文本文件中读取数据,可以指定文件路径、字段分隔符、文本编码等参数。

  2. 数据库输入(Table Input):用于从关系型数据库中读取数据,可以指定数据库连接信息、SQL查询语句等参数。

  3. Excel文件输入(Excel Input):用于从Excel文件中读取数据,可以指定文件路径、工作表名称、起始行等参数。

  4. XML输入(XML Input):用于从XML文件中读取数据,可以指定文件路径、XPath表达式等参数。

  5. Web服务输入(HTTP Input):用于从Web服务接口中读取数据,可以指定URL、请求方法、请求头等参数。

这些输入步骤可以根据具体的需求进行配置,以读取不同类型的数据源中的数据,并将其传递给转换中的其他步骤进行处理。

示例:其他控件都大同小异,需要用什么自己测试一下即可,主要理解概念,知道输入是做什么用的。

2.2.1.1、表输入

在这里插入图片描述

在Kettle中,表输入控件(Table Input)用于从关系型数据库表中读取数据。它是Kettle中常用的输入步骤之一。表输入控件提供了以下配置选项:

  1. 连接:选择要连接的数据库类型,如MySQL、Oracle、SQL Server等,并配置数据库连接信息,包括主机名、端口号、数据库名称、用户名和密码。

  2. SQL查询:编写SQL查询语句,用于从目标表中读取数据。可以直接输入SQL语句,也可以使用变量或参数。

  3. 字段:指定要读取的字段列表。可以手动指定字段,也可以使用"*"通配符读取所有字段。

  4. 查询参数:如果SQL查询中使用了变量或参数,可以在此处配置参数值。

  5. 数据类型:指定每个字段的数据类型。Kettle会根据数据类型进行数据转换和格式化。

  6. 执行结果:选择是否将查询结果输出到转换的下一个步骤。

通过配置表输入控件,可以从关系型数据库表中读取数据,并将其作为输入流传递给转换的下一个步骤进行后续处理。可以根据需要编写SQL查询语句,选择要读取的字段,并进行数据类型的配置。

步骤:

1、拖入一个表输入控件
在这里插入图片描述2、数据库连接新建,选择mysql,会报错说缺少 MySQL 相关的 JDBC 驱动。需自行从 MySQL 官网下载驱动包,放在 lib 目录下
在这里插入图片描述驱动下载地址链接: https://downloads.mysql.com/archives/c-j/

在这里插入图片描述

在这里插入图片描述

3、建立好连接转换后,可右键共享,以后新建表输入,有的选之前的连接
在这里插入图片描述
在这里插入图片描述4、建立连接,输出到excel,excel输出控件要记得获取字段
在这里插入图片描述
这一步很关键,连接连好后,需再次点击excel控件获取字段
在这里插入图片描述

2.2.2、输出控件

在Kettle中,转换的输出是指将处理后的数据写入到目标数据源或文件中的过程。Kettle提供了多种输出步骤,可以将数据写入到不同类型的目标中。以下是一些常用的输出步骤:

  1. 文本文件输出(Text File Output):将数据写入到文本文件中。 文件路径,字段分隔符,文本编码,是否追加写入,是否包含头部。

  2. 数据库输出(Table Output):将数据写入到关系型数据库中。数据库连接信息,目标表名,字段映射关系,批量插入大小。

  3. Excel文件输出(Excel Output):将数据写入到Excel文件中。文件路径,工作表名称,起始行,起始列,字段映射关系。

  4. XML输出(XML Output):将数据写入到XML文件中。文件路径,根节点名称,字段映射关系,是否包含头部。

  5. Web服务输出(HTTP Post):将数据通过HTTP请求发送到Web服务接口。URL,请求方法,请求头,请求参数。

  6. JSON输出(JSON Output):将数据写入到JSON文件或API中。文件路径,JSON路径表达式,字段映射关系

  7. 数据流输出(Stream Lookup):将数据写入到另一个转换中的输入流中。转换名称,步骤名称,输入流名称

这些输出步骤可以根据具体的需求进行配置,以将处理后的数据写入到不同类型的目标中。

2.2.2.1、表输出

在这里插入图片描述

在Kettle中,表输出控件(Table Output)用于将数据写入关系型数据库表中。它是Kettle中常用的输出步骤之一。表输出控件提供了以下配置选项:

  1. 连接:选择要连接的数据库类型,如MySQL、Oracle、SQL Server等,并配置数据库连接信息,包括主机名、端口号、数据库名称、用户名和密码。

  2. 目标表:指定要写入数据的目标表名。

  3. 字段映射:将输入流中的字段映射到目标表的字段。可以手动指定字段映射关系,也可以使用自动映射功能。

  4. 批量插入大小:指定每次批量插入的记录数。较大的批量插入大小可以提高写入性能,但也会增加内存消耗。

  5. 执行结果:选择是否将执行结果输出到转换的下一个步骤。

  6. 高级选项:提供了一些高级配置选项,如事务处理、批量更新、使用预编译语句等。

通过配置表输出控件,可以将数据按照指定的字段映射关系写入到关系型数据库表中,实现数据的持久化存储。

步骤:
1、拖入一个表输出控件
2、双击编辑,数据库连接选择刚刚那个,选择目标表,会自动填充目标模式
在这里插入图片描述3、可将数据库的某个查询的sql数据,传输到另一个表中

在这里插入图片描述

2.2.2.2、更新&插入/更新

1、更新:
在这里插入图片描述

更新控件(Update)用于更新关系型数据库表中的数据。它是Kettle中常用的输出步骤之一。

在这里插入图片描述
根据上面更新配置,不同两张表,表输入的id = 目标表的id的时候更新name字段。
在这里插入图片描述

2、插入/更新 :

在这里插入图片描述

插入/更新控件(Insert/Update)用于根据输入流中的数据,向关系型数据库表中插入新记录或更新已存在的记录。

在这里插入图片描述

根据上面更新配置,不同两张表,表输入的id = 目标表的id的时候更新name字段;目标表没有表输入里的id的时候,会插入一条新的数据

在这里插入图片描述

2.2.2.3、删除

在这里插入图片描述

在Kettle中,删除控件(Delete)用于从关系型数据库表中删除记录。

在这里插入图片描述
根据上面控件的配置,可实现表输入的字段id = 目标表里的id的时候,执行删除

在这里插入图片描述

2.2.2.4、JSON 输出

可将数据转换为JSON格式并输出。

在这里插入图片描述

1、获取表输入字段
2、可将json转成一个字段存放,也写到文件中

如下配置,会新增一个outputValue字段,里面是放着json,值为所选字段

在这里插入图片描述

在这里插入图片描述

2.2.2.5、JSON 输入与解析

在这里插入图片描述

在这里插入图片描述
如将下面复杂json解析

在这里插入图片描述

在这里插入图片描述
$ 代表的是当前传入整个数据的根对象

.小数点一个,代表是你想要的是基于当前对象{}下的字段名

..小数点两个,代表你想要的是基于当前对象[{}]下的字段名

2.2.3、转换控件

在Kettle中,转换控件(Transformation)用于在当前转换中执行另一个独立的Kettle转换。

转换控件提供了以下配置选项:

  1. 转换文件:指定要执行的Kettle转换文件的路径。

  2. 转换参数:可以为要执行的转换传递参数。参数可以是静态的值,也可以是从输入流中获取的字段值。

  3. 执行结果:选择是否将执行结果输出到转换的下一个步骤。

通过配置转换控件,可以在当前转换中执行另一个独立的Kettle转换。可以指定要执行的转换文件的路径,并可以为要执行的转换传递参数。转换控件会执行指定的转换,并将执行结果输出到转换的下一个步骤进行后续处理。这样可以实现转换之间的逻辑控制和数据流转。

2.2.3.1、Concat fields拼接字段

Concat Fields控件用于将多个字段的值合并为一个字段

在这里插入图片描述

拉一个Concat Fields控件,target field name (目标字段名),下方是要拼接的字段

在这里插入图片描述

实现了表输入的数据传输到表输出,其中name字段为表输入的name和sex字段的拼接

在这里插入图片描述

2.2.3.2、Value mapper 值映射

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

在这里插入图片描述

在这里插入图片描述
实现将表输入的sex字段的值为1和2的转换成中文性别,传输给表输出。

在这里插入图片描述

2.2.3.3、增加常量&增加序列

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

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

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述实现了在字段里增加了常量字段值为1,增加了序列num字段,自增
在这里插入图片描述

2.2.3.4、字段选择

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

在这里插入图片描述以下将name字段改成name2,且可移除某些字段,元数据跟修改一样,但有更多选择,可以改类型

在这里插入图片描述

2.2.3.5、计算机

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

在这里插入图片描述

在这里插入图片描述多种计算可供选择
在这里插入图片描述

2.2.3.6、字符串剪切&替换&操作

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

  1. 剪切字符串是指定输入流字段裁剪的位置剪切出新的字段。
  2. 字符串替换是指定搜索内容和替换内容,如果输入流的字段匹配上搜索内容就进行替换生成新字段。
  3. 字符串操作是去除字符串两端的空格和大小写切换,并生成新的字段。

在这里插入图片描述
剪切:

在这里插入图片描述
替换:

在这里插入图片描述
操作:

在这里插入图片描述

2.2.3.7、排序记录&去除重复记录

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

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

2.2.3.8、其他常用构件
  1. 唯一行(哈希值):

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

  1. 拆分字段

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

  1. 列拆分为多行

列拆分为多行就是把指定字段按指定分隔符进行拆分为多行,然后其他字段直接复制。

  1. 行扁平化

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

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

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

  1. 列转行

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

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

  1. 行转列

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

2.2.4、应用控件

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

在这里插入图片描述

2.2.4.1、If field value is null 替换null值

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

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

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

在这里插入图片描述

可选择某些字段为空时,替换成我们设置的值,例如下面key_word为null,替换成123

在这里插入图片描述

在这里插入图片描述

2.2.4.2、写日志

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

1. 选择日志级别

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

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

在这里插入图片描述

在这里插入图片描述
实现了,打印每一行的上方的字段的值,方便调试

在这里插入图片描述

2.2.5、流程控件

在Kettle转换中,流程控件用于控制和管理转换中的流程和执行顺序。它可以根据条件进行分支处理、循环执行、跳过步骤等操作,以实现复杂的数据处理逻辑。

在这里插入图片描述

2.2.5.1、Switch/case

Switch/Case控件用于根据指定的条件进行分支处理,根据条件的结果选择不同的路径进行处理。它类似于编程语言中的switch/case语句,可以根据不同的条件值执行不同的操作。

在这里插入图片描述

以下配置的age字段,值为18的时候执行了表输出,值为28的时候执行表输出2

在这里插入图片描述
在这里插入图片描述

2.2.5.2、过滤记录(if else)

过滤记录控件(Filter Rows)用于根据指定的条件过滤数据记录。它可以根据条件的结果,选择保留符合条件的记录或者删除不符合条件的记录。

在这里插入图片描述

以下配置了,age字段大于等于18是,true执行excel,false时执行了excel2

在这里插入图片描述

在这里插入图片描述

2.2.5.3、空操作

在Kettle转换中,空操作控件(Dummy)用于在转换中插入一个空的步骤,不对数据进行任何处理。它通常用于以下情况:

  1. 流程控制:空操作控件可以作为流程控制的占位符,用于标记某个位置或执行某个操作的起始点。可以在空操作控件之后添加其他步骤,以实现特定的流程逻辑。

  2. 调试和测试:空操作控件可以用于调试和测试转换,可以在转换中插入空操作控件来暂停转换的执行,以便查看中间结果或进行调试操作。

  3. 文档说明:空操作控件可以用于文档说明和注释,可以在转换中插入空操作控件来添加注释或说明,以便其他开发人员理解转换的逻辑和目的。

使用空操作控件非常简单,只需将其拖放到转换中的适当位置即可。空操作控件不需要任何配置或设置,它不会对数据进行任何处理,只是作为一个占位符存在。

空操作控件在Kettle转换中的作用是提高转换的可读性和可维护性,可以帮助开发人员更好地理解和管理转换的逻辑。它在转换中的应用非常灵活,可以根据实际需求进行使用。

在这里插入图片描述

实现了满足条件执行输出excel,不满足时什么也不做
在这里插入图片描述

2.2.5.4、中止

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

在这里插入图片描述

在Kettle转换的中止控件中,有三个选项可以配置,它们分别是:

  1. Abort the running transformation(中止正在运行的转换):选择此选项后,当中止控件被执行时,整个转换会立即停止执行,并且不会继续执行后续的步骤。此选项会直接中止转换的执行,不会记录任何错误信息。

  2. Abort and log as an error(中止并记录为错误):选择此选项后,当中止控件被执行时,整个转换会立即停止执行,并且不会继续执行后续的步骤。此选项会将中止控件的执行记录为一个错误,并将错误信息记录到日志中。

  3. Stop input processing(停止输入处理):选择此选项后,当中止控件被执行时,只会中止当前步骤的输入处理,而不会中止整个转换的执行。后续的步骤仍然会继续执行。此选项通常用于在处理大量数据时,当某个条件满足时,可以中止当前步骤的输入处理,以提高转换的执行效率。

这三个选项提供了不同的中止方式和处理方式,可以根据实际需求选择适合的选项。如果需要完全中止转换的执行,并且不记录错误信息,可以选择"Abort the running transformation"选项;如果需要中止转换的执行,并将中止操作记录为一个错误,可以选择"Abort and log as an error"选项;如果只需要中止当前步骤的输入处理,可以选择"Stop input processing"选项。

在这里插入图片描述

执行空操作的时候,中止,打印错误日志

在这里插入图片描述

2.2.6、查询控件

查询控件(Table Input)用于从数据库表中读取数据。它可以执行SQL查询语句,并将查询结果作为输出流传递给后续的步骤进行处理。

在这里插入图片描述

2.2.6.1、Database lookup 数据库查询

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

在这里插入图片描述

实现了关联demo2表,左连接的效果

在这里插入图片描述

在这里插入图片描述

2.2.6.2、流查询

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

在这里插入图片描述

以下配置实现了,将表数据1和表数据2根据id关联了起来

在这里插入图片描述
在这里插入图片描述

2.2.7、连接控件

连接(Join)控件用于将两个或多个输入流连接在一起,根据指定的连接条件将它们的记录进行关联。

在这里插入图片描述

2.2.7.1、合并记录

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

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

在这里插入图片描述

利用合并记录控件比较合并记录-新旧excel的数据,并预览数据,查看标志字段的内容,可看是新增还是删除或更新 还是不变的

在这里插入图片描述

在这里插入图片描述

2.2.7.2、Merge join记录集连接

这个控件相比于上面的那些连接关联会用的更多一些

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

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

可实现两个表的数据进行左连接,右连接,内连接,外连接
在这里插入图片描述

2.2.8、统计控件

统计控件可以帮助我们对输入流中的数据进行聚合和汇总,以便进行更深入的数据分析和处理。可以根据实际需求选择不同的统计操作,并根据需要对分组字段进行设置。

在这里插入图片描述

2.2.8.1、分组

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

在这里插入图片描述
在这里插入图片描述

2.2.9、脚本控件

脚本(Script)控件用于执行自定义的脚本代码,以实现一些特定的数据处理逻辑。脚本控件可以使用不同的脚本语言,如JavaScript、Java,Sql等,来编写脚本代码。

在这里插入图片描述

2.2.9.1、执行SQL脚本

该控件可以连接到数据库,并执行指定的SQL语句,以实现数据的查询、插入、更新、删除等操作。

在这里插入图片描述

执行了sql脚本,将age为空的置为18

在这里插入图片描述

在这里插入图片描述

1、动态传参,执行sql脚本 示例:

1、新建一个表输入,编写sql
在这里插入图片描述

2、拉入一个sql脚本,编写参数,连接表输入,可实现将表输入的值插入到sql脚本中

在这里插入图片描述
连接sql脚本

在这里插入图片描述

2.2.10、作业控件

使用这些作业控件,你可以在转换中执行作业,并在作业之间传递数据,实现更灵活和复杂的数据处理逻辑

在这里插入图片描述

2.2.10.1、Set Variables 设置变量

该步骤可以在转换运行时动态地设置变量的值,以便在后续的步骤中使用。

在这里插入图片描述

变量活动类型:

  1. “Valid in the root job”:勾选此选项表示该变量在整个根作业中都是有效的。这意味着在根作业的其他步骤中也可以使用该变量。

  2. “Valid in the Java Virtual Machine”:勾选此选项表示该变量在Java虚拟机(JVM)中是有效的。这意味着在整个转换运行期间,无论是在根作业还是子作业中,都可以使用该变量。

  3. “Valid in the parent job”:勾选此选项表示该变量在父作业中是有效的。这意味着在父作业的其他步骤中也可以使用该变量。

  4. “Valid in the grand-parent job”:勾选此选项表示该变量在父作业的父作业(即祖父作业)中是有效的。这意味着在祖父作业的其他步骤中也可以使用该变量。

示例:获取系统时间,将获取的字段选择出来,设置成变量。设置变量配置与上方截图通样。

1、获取系统时间,设置变量
在这里插入图片描述

在这里插入图片描述2、设置完变量即可再第二个转换中,获取到这个变量

在这里插入图片描述3、在第二个转换中,写死${变量名} ,即可获取值,要切记勾上变量替换

在这里插入图片描述

2.2.10.2、Get Variables 获取变量

获取已定义的变量的值。该步骤可以将变量的值存储到转换中的字段中,以供后续步骤使用

在这里插入图片描述
例如:获取变量 + 常量 拼接起来

在这里插入图片描述

2.2.10.3、复制记录到结果 / 从结果获取记录

将记录复制到结果集中。该步骤可以将输入流中的记录复制到输出流中,以供后续步骤使用或输出到目标。

在这里插入图片描述

例如:将列表复制到结果中,另一个转换结果,更详细可看下方记录
在这里插入图片描述
在这里插入图片描述

2.3、作业

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

一个作业包含一个或者多个作业项,这些作业项以某种顺序来执行。作业执行顺序由作业项之间的跳(job hop)和每个作业项的执行结果来决定。

作业(Job)是由一系列的转换(Transformation)和其他任务(Task)组成的工作流程。作业可以用于实现复杂的数据处理和调度任务。

2.3.1、作业项

作业项是作业的基本构成部分。如同转换的步骤,作业项也可以使用图标的方式图形化展示。但是,作业项和转换步骤有下面几点不同:

  1. 转换步骤与步骤之间是数据流,作业项之间是步骤流。

  2. 转换启动以后,所有步骤一起并行启动等待数据行的输入,而作业项是严格按照执行顺序启动,一个作业项执行完以后,再执行下一个作业项。

  3. 在作业项之间可以传递一个结果对象(result object)。这个结果对象里面包含了数据行,它们不是以数据流的方式来传递的。而是等待一个作业项执行完了,再传递个下一个作业项。

  4. 因为作业顺序执行作业项,所以必须定义一个起点。有一个叫“开始”的作业项就定义了这个点。一个作业只能定一个开始作业项。

2.3.2、作业跳

作业的跳是作业项之间的连接线,他定义了作业的执行路径。作业里每个作业项的不同运行结果决定了做作业的不同执行路径。作业跳一共分为下面三种情况:

①无条件执行:不论上一个作业项执行成功还是失败,下一个作业项都会执行。这是一种蓝色的连接线,上面有一个锁的图标。

在这里插入图片描述

②当运行结果为真时执行:当上一个作业项的执行结果为真时,执行下一个作业项。通常在需要无错误执行的情况下使用。这是一种绿色的连接线,上面有一个对钩号的图标。

在这里插入图片描述

③当运行结果为假时执行:当上一个作业项的执行结果为假或者没有成功执行是,执行下一个作业项。这是一种红色的连接线,上面有一个红色的停止图标。

在这里插入图片描述

在图标上单击就可以对跳进行设置。

2.3.3、控件

在这里插入图片描述

在Kettle作业中,可以使用作业项来实现不同的功能和操作。下面是关于"Start"、“Dummy”、“成功”、设置变量和转换的说明:

  1. Start作业项:作业的起始点,表示作业的开始。通常将"Start"作业项作为作业的第一个作业项,用于标识作业的起始位置。可设置定时执行

  2. Dummy作业项:虚拟作业项,用于连接作业中的不同作业项。在作业中,可以使用"Dummy"作业项来创建分支、合并分支或者连接不同的作业项。

  3. 成功作业项:用于判断作业的执行结果是否成功。可以将"成功"作业项与其他作业项连接,根据前面的作业项的执行结果来判断作业的成功或失败。通常会在作业的最后面添加一个"成功"作业项,用于标识作业的结束。这是因为作业的执行结果需要有一个明确的标识,以便后续的处理或者监控。

  4. 设置变量:在Kettle作业中,可以使用变量来存储和传递数据。可以通过"设置变量"作业项来设置变量的值。在作业中的其他作业项中,可以使用变量来引用和操作数据。

  5. 转换:转换作业项用于执行一个独立的转换,可以是已经定义好的转换文件(.ktr)或者是动态生成的转换。转换作业项可以用于实现数据抽取、转换和加载(ETL)等任务。

通过将这些作业项连接在一起,可以构建一个完整的Kettle作业。
例如:
可以使用"Start"作业项作为作业的起始点,然后连接到"设置变量"作业项,设置一个变量的值。接下来,可以连接到转换作业项,执行数据转换操作。最后,可以使用"成功"作业项来标识作业的结束。

在这里插入图片描述

第三章 资源库

3.1 数据库资源库

数据库资源库是将作业和转换相关的信息存储在数据库中,执行的时候直接去数据库读取信息,很容易跨平台使用

  1. 点击右上角connect,新增资源库,选择database repositories
    在这里插入图片描述
    在这里插入图片描述

点击保存后会创建很多表

在这里插入图片描述

连接资源库,默认账号密码为admin
在这里插入图片描述

后面保存的信息都会存储到数据库中,方便下次使用
在这里插入图片描述

第四章 示例(持续更新)

1、循环分页抽取数据(适用大数据一次抽不完,分页多次抽取)

在这里插入图片描述
第一步骤:获取表页数,先查询表能分多少页,将页数按1-最终页成一个列表
在这里插入图片描述

1、表输入:使用sql获取源表页数
在这里插入图片描述2、字段选择:将字段类型改成number类型
在这里插入图片描述3、表输入:将分页行号使用sql转成行号列表,注意需要从步骤插入数据
在这里插入图片描述

select a.rownum from  
(SELECT 
  @rownum:= @rownum + 1 AS rownum,
m.*
FROM
  (SELECT @rownum := 0) t,`tb_user_location` m) a
where a.rownum <= ?

4、选择字段:将字段改名并设置number类型

在这里插入图片描述5、复制记录到结果
在这里插入图片描述

第二步骤:是一个作业,获取变量循环抽数

在这里插入图片描述
1、转换1:设置分页变量,设置一个新的变量名

在这里插入图片描述
2、转换2:获取变量加抽数,根据传过来的页数,每页5000条,获取数据,注意勾选替换变量,后将数据输出到表里

在这里插入图片描述

SELECT
	P.*
FROM
	(
		SELECT
			@rowno :=@rowno + 1 AS rownum,
			O.*
		FROM
			(SELECT * FROM tb_user_location) O,
			(SELECT @rowno := 0) t
	) P
WHERE
	rownum BETWEEN ((${VAR_P_PAGE} - 1) * 5000 + 1)
AND (${VAR_P_PAGE} * 5000);

第三步骤:执行job,勾选执行每一输入行
在这里插入图片描述

2、获取当前时间、昨天、前几天时间,并设置变量

1、获取今天、昨天 时间,昨天就是将类型选择昨天,其他跟今天一致
在这里插入图片描述
2、获取前几天时间

在这里插入图片描述

javascript脚本:

Date.prototype.Format = function (fmt) { //author: meizz 
    var o = {
        "M+": this.getMonth() + 1, //月份 
        "d+": this.getDate(), //日 
        "h+": this.getHours(), //小时 
        "m+": this.getMinutes(), //分 
        "s+": this.getSeconds(), //秒 
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度 
        "S": this.getMilliseconds() //毫秒 
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
    if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}
 
var dtNew=new Date(new Date().getTime()-7*24*60*60*1000).Format("yyyyMMdd"); //new 当前时间对象 2代表2天 24*60*60*1000代表2天时间的ms数
// 即当天减去2天时间ms数 得到前天的时间

3、根据动态表名查询,插入到动态表里(一天一张表,表名按日期)

在这里插入图片描述
步骤一:转换1:获取系统时间,这个就是上面示例获取今天日期

步骤二:转换2:表不存在则创建表,使用变量设置动态表名创建表,并设置新的表名变量

在这里插入图片描述

1、执行sql脚本,如果表不存在则创建表
在这里插入图片描述
2、 获取时间变量 ,增加常量,两个字段拼接成一个新的字段tableName,并设置变量,注意顺序跟上方流程一致,我这边减少截图放一起

在这里插入图片描述步骤三:获取动态表名查询表,并插入数据到动态表上

在这里插入图片描述

使用变量:${TODAY} 和 ${TABLENAME} 实现动态抽取表,动态插入到动态表名里
在这里插入图片描述
在这里插入图片描述
避坑注意:有时候转换改的多,要在作业那边点击转换重新保存一下,不然拿的还是旧的转换

  • 26
    点赞
  • 31
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值