Kettle

一、定义

ETL（Extract-Transform-Load的缩写，即数据==抽取、转换、装载==的过程），对于企业或行业应用来说，我们经常会遇到各种数据的处理，转换，迁移，所以了解并掌握一种etl工具的使用，必不可少的，Kettle就是强大的ETL工具。

Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行，绿色无需安装，数据抽取高效稳定。 Kettle 中文名称叫水壶，该项目的主程序员MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。 Kettle这个ETL工具集，它允许你管理来自不同数据库的数据，通过提供一个图形化的用户环境来描述你想做什么，而不是你想怎么做。 Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。 Kettle(现在已经更名为PDI，Pentaho Data Integration-Pentaho数据集成)。

二、kettle相关说明

1、核心组件

2、目录文件

3、XPath-语法

4、JSONPath

5、Spoon工具介绍

 三、入门操作

1、常用控件

　　1、更新
　　2、插入更新
　　3、删除
　　4、concat fields 
　　5、增加常量（增加常量就是在本身的数据流里面添加一列数据，该列的数据都是相同的值）

　　　　1、从excel读取数据，增加新列language值为en，who为me，把数据保存在excel和数据库中

　　　　2、选择excel文件并获取字段

 　　　　3、新增转换-增加常量

 　　　　4、excel输出

 　　　　5、mysql数据库输出，先执行创建表

　　6、增加序列（给数据流添加一个序列字段，有点类似数据库的自增）

　　　　1、从excel读取数据，添加序列字段id，输出到数据库

　　　　2、添加转换-增加序列

 　　　　3、输出到数据库，没有表就直接点击下面的SQL按钮创建

 

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

　　　　1、从excel读取数据，修改id为key、gender为sex，删除列country

　　　　2、添加转换-字段选择

 　　　　3、输出到数据库，没有表就直接点击下面的SQL按钮创建

 

　　8、计算器（一个函数集合来创建新的字段，还可以设置字段是否移除（临时字段））
Function描述Required fields
Set field to constant A 创建一个具有常数值的字段。 A
Create a copy of field A 创建一个具有给定字段值的字段副本。 A
A + B A + B。 A and B
A - B A - B。 A and B
A * B A乘以B。 A and B
A / B A除以B。 A and B
A * A A的平方。 A
SQRT( A ) A的平方根。 A
100 * A / B A占B的百分比。 A and B
A - ( A * B / 100 ) 减去B%的A。 A and B
A + ( A * B / 100 ) 将B%加到A。 A and B
A + B *C 加上A和B乘以C。 A, B and C
SQRT( AA + BB ) 算? (A2 + B2)。 A and B
ROUND( A ) 返回与参数最接近的整数。 将结果四舍五入为整数 使用以下方法不带小数(B=0)的四舍五入(A, B)。 A
ROUND( A, B ) 用B小数四舍五入到最接近的偶数 A and B
STDROUND( A ) 将A取整到最接近的整数。常用的舍入方法是“离零半舍”，也称为标准或普通舍入 A
STDROUND( A, B ) 使用与stround (A)相同的舍入方法，但采用B小数。 A and B
CEIL( A ) 上限函数将一个数字映射到后面最小的整数。 A
FLOOR( A ) floor函数将一个数字映射到之前最大的整数。 A
NVL( A, B ) 如果A不为空，则返回A，否则返回b。注意，有时变量不是空的，而是一个空字符串。 A and B
Date A + B days 在日期字段A中添加B天。 A and B
Year of date A 计算日期A的年份。 A
Month of date A 计算日期A的月份。 A
Day of year of date A计算一年中的一天(1-365)。 A
Day of month of date A 计算月(1-31)的日期。 A
Day of week of date A 计算一周的天数(1-7)。 A
Week of year of date A 计算一年中的一周(1-54)。 A
ISO8601 Week of year of date A ISO8601样式(1-53)计算一年中的星期。 A
ISO8601 Year of date A 计算年份ISO8601风格。 A
Byte to hex encode of string A 将字符串中的字节编码为十六进制表示形式。 A
Hex encode of string A 用字符串自己的十六进制表示来编码字符串。 A
Char to hex encode of string A 将字符串中的字符编码为十六进制表示形式。 A
Hex decode of string A 解码十六进制表示的字符串(当a的长度为奇数时加前导0)。 A
Checksum of a file A using CRC-32 使用CRC-32计算文件的校验和。 A
Checksum of a file A using Adler-32 使用Adler-32计算文件的校验和。 A
Checksum of a file A using MD5 使用MD5计算文件的校验和。 A
Checksum of a file A using SHA-1 使用SHA-1计算文件的校验和。 A
Levenshtein Distance (Source A and Target B) 计算Levenshtein距离:http://en.wikipedia.org/wiki/Levenshtein_distance A and B
Metaphone of A (Phonetics) 计算A的变音位:http://en.wikipedia.org/wiki/Metaphone A
Double metaphone of A 计算A的双变音:http://en.wikipedia.org/wiki/Double_Metaphone A
Absolute value ABS(A) 计算A的绝对值。 A
Remove time from a date A 删除的时间值。 A
Date A - Date B (in days) 计算日期字段和B日期字段之间的差异(以天为单位)。 A and B
A + B + C A + B + C。 A, B, and C
First letter of each word of a string A in capital 转换字符串中每个单词的第一个字母。 A
UpperCase of a string A 将字符串转换为大写。 A
LowerCase of a string A 将字符串转换为小写。 A
Mask XML content from string A 转义XML内容;用&值替换字符。 A
Protect (CDATA) XML content from string A 表明XML字符串是通用字符数据，而不是非字符数据或具有更特定、有限结构的字符数据。给定的字符串将包含在<![CDATA[string]]>中。 A
Remove CR from a string A 从字符串中移除回车。 A
Remove LF from a string A 从字符串中移除换行符。 A
Remove CRLF from a string A 从字符串中移除回车/换行。 A
Remove TAB from a string A 从字符串中移除制表符。 A
Return only digits from string A Outputs only从字符串中只输出字符串中的数字(0-9)。 A
Remove digits from string A 从字符串中删除所有数字(0-9)。 A
Return the length of a string A 返回字符串的长度。 A
Load file content in binary 加载给定文件的内容(字段A)到二进制数据类型(如图片)。 A
Add time B to date A 将时间添加到日期中，将日期和时间作为一个值返回。 A and B
Quarter of date A 返回日期的四分之一(1到4)。 A
variable substitution in string A 在字符串中替换变量。 A
Unescape XML content 不转义字符串中的XML内容。 A
Escape HTML content 在字符串中转义HTML。 A
Unescape HTML content 在字符串中不可转义HTML。 A
Escape SQL content 转义字符串中适合传递给SQL查询的字符。 A
Date A - Date B (working days) 计算日期字段A和日期字段B(周一至周五工作日)的差值。 A and B
Date A + B Months 为日期字段A添加B个月。 A
Check if an XML file A is well formed 验证XML文件输入。 A
Check if an XML string A is well formed 验证XML字符串输入。 A
Get encoding of file A 猜测给定文件的最佳编码(UTF-8)。 A
Dameraulevenshtein distance between String A and String B 计算字符串之间的Dameraulevenshtein距离:http://en.wikipedia.org/wiki/Damerau–Levenshtein_distance A and B
NeedlemanWunsch distance between String A and String B 计算字符串之间的needlemanwonsch距离:http://en.wikipedia.org/wiki/Needleman–Wunsch_algorithm A and B
Jaro similitude between String A and String B 返回两个字符串之间的Jaro相似系数. A and B
JaroWinkler similitude between String A and String B 返回两个字符串之间的Jaro相似系数:http://en.wikipedia.org/wiki/Jaro–Winkler_distance A and B
SoundEx of String A 将字符串编码为Soundex值。 A
RefinedSoundEx of String A 检索给定字符串对象的精炼Soundex代码 A
Date A + B Hours 为日期字段A添加B小时 A and B
Date A + B Minutes 向日期字段A添加B分钟 A and B
Date A - Date B (milliseconds) 从日期字段A中减去B毫秒 A and B
Date A - Date B (seconds) 从日期字段A中减去B秒 A and B
Date A - Date B (minutes) 从日期字段A中减去B分钟 A and B
Date A - Date B (hours) 从日期字段A中减去B小时 A and B
Hour of Day of Date A 提取给定日期的小时部分 A
Minute of Hour of Date A 提取给定日期的分钟部分 A
Second of Hour of Date A 提取给定日期的第二部分 A

 

　　9、字符串剪切、替换、操作

　　　　1、从Excel中读取数据，获取title的前两位，生成title_shear字段，把description中的11111替换为123456生成description_update字段，去除author两边的空格，字符变大写，生成author_update，保存到Excel

 　　　　2、添加转换-剪切字符串、字符串操作、字符串替换

 　　　　3、输出到excel

　　10、去除重复记录、排序记录（要先排序才能去重）

　　　　1、从excel读取数据，先进行排序，然后出去重复记录

 　　　　2、添加转换-排序记录

 　　　　3、添加转换-去除重复记录

 　　　　4、输出excel

　　11、唯一行（哈希值）（效果和去除重复记录+排序记录的效果一样，但是效率更高一点）

　　　　1、从excel读取数据

　　　　2、添加转换-唯一行（哈希值）

 　　　　3、输出excel

　　12、拆分字段
　　13、列拆分多行
　　14、列转行（要先进行转换-排序记录）
　　15、行转列
　　16、行扁平化（要先进行转换-排序记录）
　　17、替换null指
　　18、写日志
　　19、Switch-case
　　20、行扁平化
　　21、替换null值
　　22、写日志
　　23、switch-case
　　24、过滤记录
　　25、中止（数据校验）
　　26、数据库查询（相当于左连接）
　　27、合并记录（要先排序）（identical、changed、new、deleted）

 　　28、javascript脚本（推荐使用不兼容模式）
1、获取字段
myVar = fieldName;
2、给字段赋值
fieldName =  myVar;
3、在脚本中使用java类
var myVar = new java.lang.String(“ki16.com”);
...
 　　29、java脚本
　　30、发送邮箱
发送邮件就是执行成功、失败、其它某种情景给相关人员发送邮件。
注意：
只有企业邮箱才可以！个人邮箱不行！
并且需要在邮件设置中开通客户端授权码！
　　31、HTTP client、HTTP post、REST client，API调用（url要使用自定义常量控件或者生成记录控件）　
　......

 四、开发案例

五、linux运行项目

　　1、配置JAVA_HOME

1、把安装包xxx.tar.gz放入linux上/usr/local
2、解压文件tar -zxvf xxx.tar.gz
3、修改配置文件/etc/profile，在文件末尾添加
export JAVA_HOME=/usr/local/jdk1.8.0_311
export PATH=$JAVA_HOME/bin:$PATH
4、重启配置文件
source /etc/profile
5、查看是否配置成功
java -version

　　2、直接把整个kettle软件目录上传到linux上，配置KETTLE_HOME

1、配置kettle环境
2、修改配置文件/etc/profile，在文件末尾添加
export KETTLE_HOME=/usr/local/kettle/data-integration
export PATH=$KETTLE_HOME/bin:$PATH
3、重启配置文件
source /etc/profile
4、查看是否配置成功
kettle -version
5、添加权限（chmod +x *.sh）
chmod +x kitchen.sh
chmod +x spoon.sh
 

 　　3、执行转换命令

./pan.sh -rep=kettle -user=admin -pass=admin -trans=test -dir=/

-rep     :     资源库的名称
-user    :     资源库用户名
-pass    :     资源库密码
-trans   :     转换名称
-dir     :     目录

 　　4、执行作业命令

./kitchen.sh -rep=kettle_connect_mysql -user=admin -pass=admin -job=test_update -dir=/ -logfile=./logs/log.txt
查看运行的脚本
ps aux | grep kitchen.sh
杀掉运行的脚本
kill <PID>

九、源码 

　　1、导入项目

　　　　1、导入报错503 Service Temporarily Unavailable
Could not transfer artifact org.pentaho:pentaho-ce-jar-parent-pom:pom:9.2.0.0-290 from/to pentaho-public (http://nexus.pentaho.org/content/groups/omni/): Transfer failed for http://nexus.pentaho.org/content/groups/omni/org/pentaho/pentaho-ce-jar-parent-pom/9.2.0.0-290/pentaho-ce-jar-parent-pom-9.2.0.0-290.pom 503 Service Temporarily Unavailable

　　解决方法：更换根目录下pom.xml中的仓库地址

　　　　2、Could not find artifact org.hitachivantara.karaf.tooling:karaf-maven-plugin:pom:4.2.6-R2 in nexus-aliyun

 　　解决方法：修改最新的阿里云仓库地址（https://developer.aliyun.com/mvn/guide）

    <mirror>
      <id>pentaho-public</id>
      <name>Pentaho Public Mirror</name>
      <url>https://repo.orl.eng.hitachivantara.com/artifactory/pnt-mvn/</url>
      <mirrorOf>*</mirrorOf>
    </mirror>
    <mirror>
      <id>aliyunmaven</id>
      <mirrorOf>*</mirrorOf>
      <name>阿里云公共仓库</name>
      <url>https://maven.aliyun.com/repository/public</url>
    </mirror>
　　　　3、找不到程序包javax.xml.bind.annotation.XmlRootElement;

 　　　　解决方法：修改jdk版本为8，因为我使用的是17的

　　　　4、Could not transfer artifact org.eclipse.core:commands:jar:3.3.0 from/to 

　　解决方法：找到本地仓库包的位置把lastUpdated删掉重新编译

　　　　5、test (default-test) on project kettle-core: There are test failures.

 　　解决方法：打包跳过测试，选中闪电按钮

　　　　6、无法加载到库

 　　解决方法：复制swt.jar文件，然后在ui的pom.xml添加依赖

　　　　7、Unable to find plugin with ID 'Kettle'
org.pentaho.di.core.exception.KettleException: 
Unable to find plugin with ID 'Kettle'.  If this is a test, make sure kettle-core tests jar is a dependency.  If this is live make sure a kettle-password-encoder-plugins.xml exits in the classpath

    at org.pentaho.di.core.encryption.Encr.init(Encr.java:61)
    at org.pentaho.di.core.KettleClientEnvironment.init(KettleClientEnvironment.java:124)
    at org.pentaho.di.core.KettleClientEnvironment.init(KettleClientEnvironment.java:80)
    at org.pentaho.di.core.KettleEnvironment.init(KettleEnvironment.java:134)
    at org.pentaho.di.core.KettleEnvironment.init(KettleEnvironment.java:101)
    at org.pentaho.di.core.KettleEnvironment.init(KettleEnvironment.java:82)
    at org.pentaho.di.ui.spoon.Spoon$1.call(Spoon.java:643)
    at org.pentaho.di.ui.spoon.Spoon$1.call(Spoon.java:635)
    at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
    at java.util.concurrent.FutureTask.run(FutureTask.java)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Disconnected from the target VM, address: '127.0.0.1:50840', transport: 'socket'

 　　解决方法：把文件复制过去

 

 

 十、问题汇总

1、设置的数据库和获取sql查询的数据库不匹配

 解决方法：不要使用系统默认的mysql表

2、输出数据到excel文件中，后缀xls，只支持65535，太多就会报错，速度也会比较慢，大数据推荐使用xlsx

  解决方法：使用Microsoft Excel输出控件

2、mysql数据库相关乱码问题

解决方法一：添加命名参数：characterEncoding，值utf-8

解决方法二：设置高级：set names utf8;

 3、kettle工具从windows移植到linux上启动报错，一直报null指针的错误

问题： windows没有配置KETTLE_HOME环境，导致拷贝过去的没有repositories.xml，一直报null指针

解决方法：把windows的.kettle文件下的repositories.xml拷贝到linux上

 

posted @ 2023-12-25 17:56  ki1616  阅读(45)  评论(0)  编辑  收藏  举报