Kettle 连接组件

目录

合并记录

组件说明

合并记录组件能对两个输入流中的数据（即两个不同来源的数据）进行合并，这两个来源的数据分别为旧数据和新数据，该组件将旧数据和新数据按照指定的关键字匹配、比较、合并。合并之后的数据会比原始的数据在结构多一个标志字段( 默认名为：flagfield)。

组件预览

合并组件

组件详解

  1. 旧数据来源：旧数据来源的步骤，一般设置为要被同步覆盖的数据源，也可以理解为被比较的旧数据

  2. 新数据来源：新数据来源的步骤，一般设置为要同步的数据，也可以理解为新数据

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

flagfield值说明
identical旧数据和新数据一样
changed旧数据数据发生了变化
new新数据中有而旧数据中没有的记录
deleted旧数据中有而新数据中没有的记录
  1. 比较字段： 对于两个数据源中的同一条记录中，指定需要比较的字段。合并后的数据将包括旧数据来源和新数据来源里的所有数据，对于变化的数据，使用新数据代替旧数据，同时在结果里用一个标示字段，来指定新旧数据的比较结果。

注意：

  1. 旧数据和新数据需要事先按照关键字段排序。
  2. 旧数据和新数据要有相同的字段名称，即传入合并记录的两个输入数据流的字段名、字段类型、字段位置等必须完全一致，否则会报错。

合并组件示例

本案例模拟使用excel去更新数据库表的场景。

  1. 准备一个测试表 test 和 一个记录表 test1
CREATE TABLE if not exists `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE if not exists `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `flagfield` varchar(20) default null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. 初始化 test 数据
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (3, 'lily', '女', 18);

查看表数据

mysql> select * from test 
    -> ;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | mars | 男  |  30 |
|  2 | lucy | 女  |  18 |
|  3 | lily | 女  |  18 |
+----+------+-----+-----+
3 rows in set (0.11 sec)
  1. 准备一个 excel ，文件名称：合并记录test，sheet 页名：test，数据内容如下
idnamesexage
1mars男30
3lily女17
4李磊男20
  1. 转换

excel2mysql

  • 4.1 获取 test 表数据

test数据

  • 4.2 获取 excel 数据

excel1

excel2

excel3

  • 4.3 排序

order

order1

  • 4.4 合并记录

detail

  • 4.5 合并结果并保存，通过“数据同步”组件，自动根据“合并记录”组件生成的标记字段值进行数据库操作

sync1sync2

  • 4.6 记录合并过程

loglog1

  1. 运行转换

首先查看 test1 表，可以发现 flagfield 标记的数据状态达到预期。

mysql> select * from test1 ;
+----+-------+-----+-----+-----------+
| id | name  | sex | age | flagfield |
+----+-------+-----+-----+-----------+
|  1 | mars  | 男  |  30 | identical |
|  2 | lucy  | 女  |  18 | deleted   |
|  3 | lily  | 女  |  17 | changed   |
|  4 | lilei | 男  |  20 | new       |
+----+-------+-----+-----+-----------+
4 rows in set (0.13 sec)

其次查看 test 表，可以发现数据已经根据 excel 中的内容把 mysql 中 test 表内容更新。

mysql> select * from test ;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | mars  | 男  |  30 |
|  3 | lily  | 女  |  17 |
|  4 | lilei | 男  |  20 |
+----+-------+-----+-----+
3 rows in set (0.12 sec)

排序合并

组件说明

排序合并组件可以将两组数据按照指定字段排序并合并。需要注意一点，合并前的数据必须已经是排序好的数据，且排序方式必须和“排序合并”中指定的排序方式相同，否则“排序合并”没有效果。

组件预览

排序组件

组件详解

  • 字段：用来排序的字段。
  • 升序：是否是升序字段

注意： 组件使用有个要求：必须有一个字段是升序。

排序合并组件示例

本案例将模拟将excel和mysql中test表数据合并到一起。注意：只是单纯的何在一起~~~~

  1. 准备一个测试表 test 和 一个记录表 test1
CREATE TABLE if not exists `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE if not exists `test2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. 初始化 test 数据
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (3, 'lily', '女', 18);

查看表数据

mysql> select * from test 
    -> ;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | mars | 男  |  30 |
|  2 | lucy | 女  |  18 |
|  3 | lily | 女  |  18 |
+----+------+-----+-----+
3 rows in set (0.11 sec)
  1. 准备一个 excel ，文件名称：排序合并test，sheet 页名：test，数据内容如下
idnamesexage
1mars男30
3lily女17
4李磊男20
  1. 转换

排序组件ktr

excel 输入组件配置方式同合并组件示例，这里不再赘述。

  1. 运行转换

查看 test2 表，结果如下，可以明显发现 excel 中的数据和 test 表中的数据已经按照id字段的升序排序并合并在了一起。

mysql> select * from test2 ; 
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | mars  | 男  |  30 |
|  1 | mars  | 男  |  30 |
|  2 | lucy  | 女  |  18 |
|  3 | lily  | 女  |  18 |
|  3 | lily  | 女  |  17 |
|  4 | lilei | 男  |  20 |
+----+-------+-----+-----+
6 rows in set (0.12 sec)
  1. 如果两个数据没有统一排序，结果如何？

排序组件ktr1

运行转换，结果如下。不难发现，排序合并的排序效果并没有实现。

mysql> select * from test2 ; 
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | mars  | 男  |  30 |
|  4 | lilei | 男  |  20 |
|  2 | lucy  | 女  |  18 |
|  3 | lily  | 女  |  18 |
|  3 | lily  | 女  |  17 |
|  1 | mars  | 男  |  30 |
+----+-------+-----+-----+
6 rows in set (0.12 sec)

记录关联 (笛卡尔输出)

组件说明

记录关联 (笛卡尔输出)组件，就是对两个数据流做笛卡尔积的拼接。注意，这里的拼接是每一行数据做笛卡尔拼接，即每行数据的字段会增加，变为两个2里面的所有字段。

记录关联 (笛卡尔输出)

组件预览

记录关联 (笛卡尔输出)预览

组件详解

  • Main step to read from : 主步骤
  • 条件：过滤拼接的条件，没有条件默认全部都进行拼接
  • +：添加条件

记录关联 (笛卡尔输出) 示例

本案例将模拟将excel和mysql中test表数据合并到一起。

  1. 准备一个测试表 test
CREATE TABLE if not exists `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. 初始化 test 数据
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (3, 'lily', '女', 18);

查看表数据

mysql> select * from test 
    -> ;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | mars | 男  |  30 |
|  2 | lucy | 女  |  18 |
|  3 | lily | 女  |  18 |
+----+------+-----+-----+
3 rows in set (0.11 sec)
  1. 准备一个 excel ，文件名称：记录关联 (笛卡尔输出)test，sheet 页名：test，数据内容如下
id1name1sex1age1
1mars男30
3lily女17
4李磊男20

excel 输入组件配置方式同合并组件示例，这里不再赘述。

  1. 转换

记录关联 (笛卡尔输出)组件ktr

  1. 运行转换

运行转换并在 Preview data 中查看数据

记录关联 (笛卡尔输出)组件结果

记录集连接

组件说明

记录集组件可以当作JOIN 条件使用，特别是当两个数据来源不在同一个数据库中或者不能通过同一种方式获取时，记录集组件能够使数据流拼接起来。它跟数据库的LEFT JOIN、RIGHT JOIN、INNER JOIN、FULL JOIN 很像。

记录集连接

组件预览

记录集连接预览

组件详解

  • 第一个步骤：数据一
  • 第二个步骤：数据二
  • 连接类型：INNER、LEFT OUTER、INNER OUTER、FULL OUTER（内链接，左外连接，右外连接，全外连接）。
  • 连接字段：数据一和数据二关联的值

注意：

  1. 第一个步骤为连接的主表。
  2. 数据一和二必须是有序的，并且是根据需要join的字段排序，如果不能确保排序一致，通过 kettle 的“排序记录”组件实现排序。

记录集连接示例

本案例将模拟将excel和mysql中test表数据合并到一起。

  1. 准备一个测试表 test
CREATE TABLE if not exists `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. 初始化 test 数据
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (3, 'lily', '女', 18);

查看表数据

mysql> select * from test 
    -> ;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | mars | 男  |  30 |
|  2 | lucy | 女  |  18 |
|  3 | lily | 女  |  18 |
+----+------+-----+-----+
3 rows in set (0.11 sec)
  1. 准备一个 excel ，文件名称：记录集连接test，sheet 页名：test，数据内容如下
id1name1sex1age1
1mars男30
3lily女17
4李磊男20

excel 输入组件配置方式同合并组件示例，这里不再赘述。

  1. 转换

记录集合并

  1. 运行转换
  • 连接类型为INNER是

记录集合并inner

  • 连接类型为LEFT OUTER是

记录集合并inner

  • 连接类型为RIGHT OUTER是

记录集合并inner

  • 连接类型为FULL OUTER是

记录集合并inner

Multiway merge join

组件说明

记录集连接是两个记录集的合并，而3个及以上的记录集，采用多路数据合并连接的方式。多路数据合并连接英文名称为Multiway merge join，是对多个记录集的合并，效率更高，速度更快。

组件预览

多路合并

组件详解

  • join type: 连接类型。支持内关联和外关联两种
  • input step[步骤号]：连接的步骤
  • join keys: 用于连接关联的字段

组件示例

本案例将模拟将excel和mysql中test表、test1表的数据通过inner和full outer方式关联在一起。

  1. 转换

多路组合

  1. SQL 脚本内容
CREATE TABLE if not exists `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE if not exists `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `flagfield` varchar(20) default null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

truncate table test ; 
truncate table test1 ;

insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test` (`id`, `name`, `sex`, `age`) VALUES (3, 'lily', '女', 18);

insert INTO `demo`.`test1` (`id`, `name`, `sex`, `age`) VALUES (1, 'mars', '男', 30);
insert INTO `demo`.`test1` (`id`, `name`, `sex`, `age`) VALUES (2, 'lucy', '女', 18);
insert INTO `demo`.`test1` (`id`, `name`, `sex`, `age`) VALUES (5, 'lilei', '男', 18);
  1. excel 内容
id1name1sex1age1
1mars男30
3lily女17
4李磊男20

excel 输入组件配置方式同合并组件示例，这里不再赘述。

  1. 运行转换
  • Multiway merge join - inner 结果

inner

  • Multiway merge join - full outer 结果

outer