ETL数据清洗Kettle工具

一、简介

Kettle是一项进行数据清洗的工具。
概念：

1. 资源库
   • 官方提供的一个需要部署的在线资源库
   • 数据库资源库
   • 文件资源库（最常用）
2. 数据库连接：生成.kdb文件
3. 转换：为了完成数据加载、处理。生成的文件为.ktr。kettle的转换都是从输入开始到输出结束。
4. 作业JOB：用来调度，生成的文件是.kjb。

二、资源库（新建、管理）

新建资源库

三、转换

输入 --> 输出
输入：表输入、Excel输入、CVS输入
输出：表输出、Excel输出、CVS输出、删除（delete）、插入/更新（merge）、更新（update）
其中的删除、插入、更新与数据库中的操作没有太大区别。不过Kettle更加强大可以读取文件，与不同的数据源。

1. 新建数据源

选择DB连接，右键单击，选择新建 -》 配置数据源连接信息 -》 完成配置

2. 简单的输入输出 配置步骤

2.1 配置表输入

1. 将左侧核心对象的输入(表输入)拖入右侧面板，双击表输入打开配置框
   2. 配置步骤名称，选择数据库连接 -》点击获取sql语句 -》 选择对应表 -》 根据需求选择是否需要字段
   
   

2.2 配置表输出

2.3 保存

2.4 启动与执行结果

注意：在启动前必须保存转换

3. 转换

1. 去重（去重前需要 排序）

表输入 -> 排序记录 -> 去除重复记录 -> 表输出

注意：

1. 在去重之前需要进行排序。
2. 在输出的时候如果乱码，需要对DB连接进行设置
   

2. 剪切字符串

3. 拆分字段

4. 增加常量

5. 增加序列

可以是数据库序列、或者程序自动生成的

6. 字段选择

选择、改名、改类型

7. 字符串操作

out stream filed改名、trim type去除空格、lower/upper大小写转换、padding填充、pad char填充字符、pad length填充到长度、initCap首字母大写、escape忽略大小写、remove special character 移除或替换
其中的pad相当于占位符

8. 字符串替换

相当于replace(‘#’,‘’);其中的搜索利用正则表达式。

9. 计算器

可以对日期、数字类型按照公式进行计算

10. 值映射

源值 -> 目标值
类比SQL：case when

11. 行/列转换

11.1 列转行（在数据库中叫做行专列）

对应SQL的行列转换语句。行列转换需要先对字段进行排序。
表输入 -》 排序记录 -》列转行 -》Excel输出

行转列类比SQL

SELECT nation,
MAX(case when rank='第一' THEN city end) as '第一',
MAX(case when rank='第二' THEN city end) as '第二'
FROM city_1 t group by nation;

select DISTINCT  t.nation,
(SELECT c.city from city_1 c WHERE t.nation = c.nation and c.`rank` = '第一') as '第一',
(SELECT c.city from city_1 c WHERE t.nation = c.nation and c.`rank` = '第二') as '第二'
from city_1 t;

11.2 行转列（在数据库中叫做列转行）

类比列转行SQL

select t.nation ,t.city1 ,'第一' as `rank` from city_2 t
union all
select t.nation ,t.city2 ,'第二' as `rank` from city_2 t;

// TODO 24-51

4. 应用

写日志、发邮件、运行SSH，空值替换

4.1 写日志

5. 流程

5.1 switch/case

6. 脚本

Java脚本、Sql脚本、公式

7. 查询

调用存储过程

8. 连接

使用连接之前需要进行排序，对于可以使用SQL解决的排序，应尽量使用SQL解决，效率相较于Kettle工具要高

• 记录集连接：俩个记录集连接
• 合并记录集：将多个记录集合合并为一个，如果重复会过滤。switch/case是分支拆分。
• 笛卡尔集连接
• Multiway merge join：多记录连接

kettle连接插件的缺陷：仅支持等值连接

9. 统计

分组：group by

四、作业

开始 -> 成功。用于调度。

案例一

1. 可以设置作业是否重复、重复时间间隔。
   2. 设置变量，获取当前时间，可以从数据库获取。可以设置变量可见范围，parent（父任务）和root（根任务）或者jvm（虚拟机全局）。
   3. 插入执行记录
   4. 配置邮件发送
   

作业的调度

kettle中有三个命令：

1. spoon：图形化界面开发
2. kitchen：执行作业的命令
   • /rep 配置资源库
   • /user 配置资源库用户名
   • /pass 配置资源库密码
   • /job 配置作业的名字
   • /logfile 日志文件路径
   • /level 日志级别
3. pan：执行转换的命令
   • /rep 配置资源库
   • /user 配置资源库用户名
   • /pass 配置资源库密码
   • /trans 转换的名称
   • /logfile 日志文件路径
   • /level 日志级别

可以用shell脚本来进行调度，也可以使用KettleOnline等调度工具。