ETL_Kettle

• 简介
• • 介绍
  • 目录文件
  • 核心概率
  • 文件类型
• 操作 - 转换
• • 多文件合并
  • 输入
  • 输出
  • 转换
  • 转换
  • 流程
  • 查询
  • 连接
  • 统计
  • 映射
  • 脚本
• 操作 - 作业
• • 参数
  • 设置变量/获取变量

简介

介绍

1、ETL是数据抽取、转换、加载

2、Spoon是图形界面接口

3、Kettle包含job和transformation两种脚本

4、Kettle依赖java环境运行

目录文件

Carte.bat/sh ：启动集群命令

Encr.bat/sh ：加密算法

Import.bat/sh ：导入命令

Kitchen.bat/sh ：运行job的命令

Pan.bat/sh ：运行transformation的命令

set-pentaho-en.bat/sh ：设置环境变量脚本

Spoon.bat/sh ：启动Kettle UI操作界面

核心概率

1、转换处理抽取、转换、加载各种对数据行的操作

2、转换包含一个或多个步骤（step）命名需唯一，如读取文件、过滤数据行、数据清洗或将数据加载到数据库

3、转换里的步骤通过跳（hop）来连接，行集的数据行缓存

4、数据行-数据类型，String/Number/Integer/BigNumber(任意精度)/Date/Boolean/Binary(二进制文件)

5、数据行-元数据，每个步骤输出数据行有对字段的描述也就是元数据

文件类型

1、XML-XPath

XPath-路径表达式
books  选取books元素的所有子节点
/books	选取根元素books
books/book	选取books的子元素的所有book元素
//book	选取book子元素，而不管它们在文档中的位置
books//book	 选取属于books元素的后代的所有book元素，而不管它们位于books之下的什么位置
//@lang  选取名为lang 的所有属性
           

2、JSON-JSONPath

方法：点记法；括号记法

点记法（常用）示例：$.store.book[0].title --查询store、book数组下key为title的所有值

$	查询的根节点对象，用于表示一个json数据，可以是数组或对象
@	过滤器断言处理的当前节点对象
*	通配符，可以表示一个名字或数字
..  递归搜索
.<name>   表示一个子节点
['<name>'(,'<name>')]   表示一个或多个子节点
[<name>(,<name>)]   表示一个或多个数组下标
[start:end]    数组片段，区间为[start,end]，不包含end
[?(<expression>)]    过滤器表达式，表达式结果必须为boolean
           

操作 - 转换

多文件合并

1、分析：

输入：Excel输入；输出：Excel输出

2、步骤：

Excel输入–选择文件目录（D:\input\）–通配符号（测试文件.*.xlsx）–包含子目录（是）

备注：

多个文件需要满足相同格式

输入

文本、表格、自定义常量（放在开始供后面节点使用）等输入

输出

表格、SQL等输出

转换

1、Concat fields

将多个字段连接起来形成一个新的字段

2、值映射

把字段的一个值映射成其他的值

3、增加常量

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

4、增加序列

给数据流添加一个序列字段

5、字段选择

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

6、计算器

一个函数集合来创建新的字段，还可以设置字段是否移除（临时字段）

7、剪切字符串

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

8、字符串替换

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

9、字符串操作

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

10、排序记录

按照指定的字段的升序或降序对数据流排序

11、去除重复记录

去除数据流里面相同的数据行，通过指定用来比较的字段（注意：必须对数据流进行排序）

12、唯一行（哈希值）

唯一行就是删除数据流重复的行（效率高）（注意：其实现的原理跟去除重复记录不同）

13、拆分字段

把字段按照分隔符拆分成两个或多个字段（注意：拆分字段后，原字段就不存在于数据流中）

14、列拆分为多行

把指定分隔符的 进行拆分为多行

15、列转行

如果数据一列有相同的值，按照指定的字段，把多行数据转换为一行数据（注意：必须对数据流进行排序）

指定关键字段（原数据流列转换的字段）、分组字段（相同值的字段），目标字段（转换后流字段），数据字段（转换后流字段对应值的原数据流字段），关键字值（原数据流列转换的字段值）

16、行转列

把数据字段的字段名转换为一列，把数据行变成数据列

指定key字段、字段名称、key值、value字段

17、行扁平化

把同一组的多行数据合并成一行

注意：只有数据流的同类数据数据行记录一致的情况才可使用；数据流必须进行排序

转换

1、替换NULL值

把null转换为其它的值

指定字段进行null替换

2、写日志

在调试的时候使用，把日志信息打印到日志窗口

流程

1、Switch/case

让数据流从一路到多路

一般通过Switch/case，让数据流进行拆分到多个输出

2、过滤记录

让数据流从一路到两路，true-false

3、空操作

空操作一般作为数据流的终点，什么都不做

4、中止

中止是数据流的终点，如果有数据了到这里，将会报错并中止后续操作

一般用于校验数据

查询

1、HTTP client

使用GET的方式提交请求，获取返回的页面内容

步骤：

自定义常量（url）— HTTP client — Get data from XML — Excel输出

2、数据库查询（同一个库）

就是数据库里面的左连接

两张表执行左关联查询，把左边的表数据全部查询出来

步骤：

表输入 — 数据库查询 — Excel输出

3、数据库连接（不同库）

可以执行两个数据库的查询，和单参数的表输入

表输入 — 数据库连接（编写查询语句，通过?匹配表输入指定的字段） — Excel输出

4、流查询

在查询前把数据库都加载 内存中，并且只能进行等值查询

连接

1、合并记录

将两个不同来源的数据合并，这两个来源的数据分别为旧数据和新数据，该步骤将旧数据和新数据按照指定的关键字匹配、比较、合并

注意：

旧数据和新数据需要事先按照关键字排序；

旧数据和新数据要有相同的字段名称；

合并记录后，会对新旧数据进行比较，并得到新记录数据流并多了一个flagfield字段标识（identical-相同、changed-发送变化、new-新数据中增加的记录、deleted-新数据中被删除的记录）

2、记录关联（笛卡尔积）

对两个数据流进行笛卡尔积操作

3、记录集连接

类似于数据库中的左连接、右连接、内连接、外连接

注意：需要提前对数据流进行排序记录

统计

1、分组

指按照某一个或某几个进行分组，同时可以将其余字段按照某种规则进行合并

注意：需要提前对数据流进行排序记录

映射

1、映射（子转换）

用来配置子转换，对子转换进行调用的一个步骤，可以理解为子转换的封装步骤

映射（映射输入规范 — 数据库查询 — 映射输出规范）

如：映射输入规范指定字段，在数据库查询中通过该字段查询到指定字段记录输出

脚本

1、Java代码

在Common use — Main 下编写代码

Excel输入 — Java代码 — Excel输出

示例：

//获取输入流中的code字段值
String code = get(Fields.In,"code").getString(r);
//如果code为空，为空设置为name的值，不为空code值后面加0
if(code == null || "".equals(code)){
	code = get(Fields.In,"name").getString(r);
}else{
	code += "0";
}
//设置code的值
get(Fields.Out,"newcode").setValue(r.code);
//新的字段需要在下面定义好类型
           

2、执行SQL脚本

可以执行一个update语句，用来更新某表中的数据

表输入（指定输出id字段） — 执行SQL脚本（根据获取的id进行脚本执行）

update t_user set status = 1 where id = ?

操作 - 作业

参数

1、全局参数

作用于所有转换，在当前用户.kettle文件夹中的kettle.properties文件中定义

使用方式：方式一 %%变量名%% 方式二 ${变量名}

注意：配置全局参数，需要重启才能生效；

在SQL中使用变量，需要勾选“是否替换变量”选项；

2、局部参数

通过Set Variables 与 Get Variables方式设置

使用方式：方式一 %%变量名%% 方式二 ${变量名}

注：Set Variables 作用于下一步骤使用

3、自定义常量数据

自定义常量变量名和类型，并设置对应值，在表输入的SQL语句中使用?来替换，其先后顺序根据常量定义的顺序

注意：需要指定从步骤输入数据选项

4、转换命名参数

在转换内部定义的变量，作用于转换内部

使用方式：${变量名}

在转换空白处右键 — 转换设置

设置变量/获取变量

获取变量，需要在作业中的下一步骤中使用

如：在一个转换中添加了设置变量，下一步转换中的第一个步骤是获取变量（使用?）