快乐学习
前程无忧、中华英才非你莫属!

Kettle入门到精通

一、前言


ETL是英文 Extract-Transform-Load 的缩写,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。

抽取(Extract):一般抽取过程需要连接到不同的数据源,以便为随后的步
骤提供数据。这一部分看上去简单而琐碎,实际上它是 ETL解决方案的成功实施的一个主要障碍。

转换(Transform):任何对数据的处理过程都是转换。这些处理过程通常包
括(但不限于)下面一些操作:

移动数据
根据规则验证数据
数据内容和数据结构的修改
将多个数据源的数据集成
根据处理后的数据计算派生值和聚集值

加载(Load):将数据加载到目标系统的所有操作。
官方的帮助文档:https://help.pentaho.com/Documentation/7.0


三、为什么要学ETL

  1. “生产工具的进步是生产力发展的标志”。和传统的编写代码相比,ETL工具在效率(包括代码复用)、可靠性、低出错率、可维护性上面绝对都是巨大的进步;
  2. 对于程序员而言,使用ETL工具意味着很多习惯的改变,这个需要适应!我相信在ETL领域,编程语言的工作未来可能会减少到总任务量的10%,剩下90%均需要借助ETL工具来实现;
  3.  和任何工具一样,导入期都比较漫长,但是请各位务必坚持下去,因为回报会非常丰厚!
  4.  和任何工具一样,ETL工具也有自己的局限性,了解工具,熟悉工具的局限的领域是学习工具的目的之一;
  5.  使用ETL工具实现会非常容易,因此重要的就是大家的设计思路了。不要轻言ETL实现不了而轻易转到脚本或者其他编程语言,我本人就经历了多次,本来打算放弃ETL工具了结果再想想,发现另外一条设计路径完全可以用ETL实现;
  6. 在一定程度上Kettle的运行效率的确不如脚本,但是说实话从易用性和可维护性角度,以及日益便宜的硬件(内存,CPU),我觉得这点运行效率的损失完全不是问题;
  7. 特殊针对企业内部IT的人员:ETL工具队企业内部IT(尤其是针对很多开发的工作已经外包的情况)一个很好的选择,毕竟维护性和开发效率都很高,而且又不会把自己拖入繁复的代码维护和修改之中(改个ETL跟打游戏差不多,前提是你的设计思路很清晰)。
  8. 学习和使用ETL的最重要的基础,我相信是对数据库的了解和感觉。毕竟很懂ETL的step都和数据库密切相关,例如Kettle里面的DB Lookup步骤在MySQL数据库表建索引后效率可以提升10倍以上。输出表的结构设计和关联因此非常重要;

常用的ETL工具比较分析 (Kettle,Talend,Informatica 等)

成本:
软件成本包括多方面,主要包括软件产品, 售前培训, 售后咨询, 技术支持等。
开源产品本身是免费的,成本主要是培训和咨询,所以成本会一直维持在一个较低水平。
商业产品本身价格很高,但是一般会提供几次免费的咨询或支持,所以采用商用软件最初成本很高,但是逐渐下降。

手工编码最初成本不高,主要是人力成本,但后期维护的工作量会越来越大。
 

风险:
项目都是有风险的尤其是大项目。
项目的风险主要包括:超出预算,项目延期,没有达到用户的满意和期望
开源产品由于价格上的优势,可以在很大程度上降低项目的风险。

 

易用性:
Talend:有 GUI 图形界面但是以 Eclipse 的插件方式提供。
Kettle:有非常容易使用的 GUI,出现问题可以到社区咨询。
Informatica:有非常容易使用的 GUI,但是要专门的训练。

Inaplex Inaport:没有GUI

技术支持:
Talend:主要在美国
Kettle:在美国,欧洲(比利时,德国,法国,英国),亚洲(中国,日本,韩国)都可以找到相关技术支持人员。
Informatica:遍布全世界
Inaplex Inaport:主要在英国


部署:
Talend:创建 Java 或perl 文件,并通过操作系统调度工具来运行
Kettle:可以使用 job 作业方式或操作系统调度,来执行一个转换文件或作业文件,也可以通过集群的方式在多台机器上部署。
Informatica:需要有 Server
Inaplex Inaport:需要 .net 2.0

 

速度:
Talend:需要手工调整,对特定数据源有优化知识。
Kettle:比 Talend 快,不过也需要手工调整,对 Oracle 和 PostGre 等数据源做了优化,同时也取决于转换任务的设计。
Informatica:是最快的

Inaplex Inaport:没有使用什么优化技术。

数据质量:
Talend:在 GUI 里有数据质量特性,可以手工写 SQL 语句。
Kettle:在 GUI 里有数据质量特性,可以手工写 SQL 语句、java脚本、正则表达式来完成数据清洗。
Informatica:专门有一个产品 Informatica Data Quality 来保证数据质量
Inaplex Inaport:因为只处理特定数据,所以比较容易进行数据清洗。


监控:
Talend:有监控和日志工具
Kettle:有监控和日志工具
Informatica:有非常详细的监控和日志工具
Inaplex Inaport:有监控和日志工具

 
连接性:
Talend:各种常用数据库,文件,web service。
Kettle:非常广泛的数据库,文件,另外可以通过插件扩展。
Informatica:各种数据源

Inaplex Inaport:ODBC 连接





四、Kettle 介绍

1、Kettle概念

Kettle是一款国外开源的ETL工具,纯java编写,可以在Window、Linux、Unix上运行,绿色无需安装,数据抽取高效稳定。

Kettle 中文名称叫水壶,该项目的主程序员MATT 希望把各种数据放到一个壶里,然后以一种指定的格式流出。

Kettle这个ETL工具集,它允许你管理来自不同数据库的数据,通过提供一个图形化的用户环境来描述你想做什么,而不是你想怎么做。

Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制。

2、下载和部署

下载kettle压缩包,因kettle为绿色软件,解压缩到任意本地路径即可
3、Kettle环境配置

 安装jdk(7版本以上,已经是jdk8版本。)
4、组件

      Spoon.bat :图形界面方式启动作业和转换设计器
      Pan.bat :命令行方式执行转换。
      Kitchen.bat :命令行方式执行作业。
      Carte.bat 启动WEB服务。用于Kettle 的远程运行或集群运行。




Kettle 简单使用


强调:kettle7.0版本,必须配套使用JDK1.7及以上版本。 

3.3运行Kettle 

解压之后Windows下找到$KETTLE_HOME/spoon.bat,双击运行. 


欢迎界面如下图所示: 

4、Kettle组件介绍与使用 

4.1 Kettle使用 

Kettle提供了资源库的方式来整合所有的工作,; 

1)创建一个新的transformation,点击保存到本地路径,例如保存到G:/etltest下,保存文件名为Trans,kettle默认transformation文件保存后后缀名为ktr; 

2)创建一个新的job,点击保存到本地路径,例如保存到G:/etltest下,保存文件名为Job,kettle默认job文件保存后后缀名为kjb;

4.2 组件树介绍

4.2.1 Transformation的主对象树和核心对象分别如下图:

Transformation中的节点介绍如下: 

主要对象树:菜单列出的是一个transformation中基本的属性,可以通过各个节点来查看。 

DB连接:显示当前transformation中的数据库连接,每一个transformation的数据库连接都需要单独配置。 

Steps:一个transformation中应用到的环节列表 

Hops:一个transformation中应用到的节点连接列表 

核心对象菜单列出的是transformation中可以调用的环节列表,可以通过鼠标拖动的方式对环节进行添加: 

Input:输入环节 

Output:输出环节 

Lookup:查询环节 

Transform:转化环节 

Joins:连接环节 

Scripting:脚本环节

4.2.2 Job的主对象树和核心对象分别如下图:

主要对象树:菜单列出的是一个Job中基本的属性,可以通过各个节点来查看。 

DB连接:显示当前Job中的数据库连接,每一个Job的数据库连接都需要单独配置。 

Job entries/作业项目:一个Job中引用的环节列表。 

核心对象菜单:列出的是Job中可以调用的环节列表,可以通过鼠标拖动的方式对环节进行添加。 

每一个环节可以通过鼠标拖动来将环节添加到主窗口中。 

并可通过shift+鼠标拖动,实现环节之间的连接。



五、Kettle 实战案例

【例1】  EXCEL 文件的输入,去除某些字段然后输出excle。


备注:字段选择输出获取不到 字段,设置为主输出步骤即可。

【例2】 运用资源库  

  把kettle配置的流程属性配置条件等,都存入mysql数据库,那个mysql底下的库就叫做资源库。
备注:因kettle没有msyql驱动jar包,需要下载数据库对应的驱动jar包放入到keetle的lib下。
         资源库默认登陆账号:admin  密码:admin
          
  【例3】 参数与配置文件
     命名参数、位置参数、变量
 
    1)转换属性中设置命名参数
    2)执行转换或Job时,设置命名参数,位置参数
    3)配置文件中设置参数
    4)批处理文件中设置参数(动态生成配置文件)
   
优先级:(覆盖)
命令行>Transform>job
kitchen命令行里的参数先传给job(Job中需要设置命名参数),在传给job中的Transform(它可以不设置此参数名,也可以设置同名参数以方便单独在TransformUI界面中执行)
位置参数:
   

命名参数

 Kettle中命名参数可以在转换设置和作业设置里面设置。
 快捷键:ctrl+t

 使用:ctrl+alt+空格,选择一个变量。

输入:

  


输出:



外部文件变量

在此文件夹下:C:UsersAdministrator.kettle  

编辑:kettle.properties 文件,增加zhangtongle = NB 相当于增加一个名为zhangtongle的系统变量并且值为NB.
备注:需要重启下Kettle,因为启动时候才被加载。
        变量不能友好的支持中文,切记,切记。
【例4】在表输入控件里使用命名参数。


【例5】处理复杂表头的Excle


备注:字段需要手动输入,并指定其适合类型。
1、去掉头部勾选,使其不让表头从零计数
2、修改工作表中从第二行之后开始读取。
3、自定义相对应字段,和其适配类型。
【例6】JS处理时间格式
var TEMP_TIME=null;
       if(接收时间!=null){
             TEMP_TIME=new Date(接收时间.replace("-","/").replace("-","/").trim());
       }
   




【例7】查找表映射转换
  字段选择控件,先用输出控件获取所有字段之后,在插入选择字段。
  
    

例8:合并大量CSV文件


1、用通配符设置文件所在目录。

2、设置内容模块与需要抽取文件的内容格式相符。

3、自定义字段,建议是用string类型。空格以及特殊符号,都能应对。


 






例9,字段的拆分、映射、合并






例9,字符串的操作

上述是Kettle的案例,可以引入到直接环境直接使用,需要更改下文件路径,

例10,数据去重

例11,处理异常数据




【例12】 生成测试文件
 备注:当测试某个功能时,需要的文本文件,可以利用keetle进行控制自动生成。

【例13】 数据质量分析
     js代码:

//Script here
//姓名是否为空
var 姓名是否为空=1;
if(name !=null && name.trim()!=null&&name!=''){
姓名是否为空=0;
}
//电话是否为数字
var 电话号码非数值=0;
if(isNaN(Number(phone))){
电话号码非数值=1;
}
// 正则表达式 是上面的第二种方式
var 电话号码非数值1=0;
var reg = new RegExp("^[0-9]*$");
if(!reg.test(phone)) {
电话号码非数值1=1;
}
【例14】 多数据源合并


逻辑实现是,用js代码定义补齐另两个excle的字段,然后在通过追加流把他们合并在一起。
【例15】 异常处理

   当输入数据被移动位置,就会自动检测到数据,并且进行终止
【例16】 发送邮件
【例17】 数据自定义分流


js代码:
var 姓名分流  = null;
if(name=="张三"){
姓名分流 =0;
} else {
       姓名分流 =1;
}
例18.数据更新
ALTER TABLE  目标表,源表  ADD ETL_TIMESTEMP  varchar; 加这个时间戳,按照目标表和源表的时间对比,进行增量更新。

例19.作业

 kettle可以把不同的任务,用作业的方式连接起来,可以连续不断的执行单独的转换任务。

例20.合并记录集–》并标识原始表和新表那些数据的变化。



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

需要设置的参数:

旧数据来源:旧数据来源的步骤

新数据来源。新数据来源的步骤

标志字段:设置标志字段的名称,标志字段用于保存比较的结果,比较结果有下列几种。

1. “identical” – 旧数据和新数据一样

2. “changed” – 数据发生了变化;

3. “new” – 新数据中有而旧数据中没有的记录

4. “deleted” –旧数据中有而新数据中没有的记录

关键字段:用于定位两个数据源中的同一条记录。

比较字段:对于两个数据源中的同一条记录中,指定需要比较的字段。

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

注意:

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

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

例子:

旧数据:

field1, field2

1, 1

2, 2

3, 3

4, 4



新数据

field1, field2

1, 1

2, 9

5, 5



设置:标志字段是flag,关键字段是 field1, 比较字段是field2



合并后的数据

field1; field2; flag

1; 1; identical

2; 9; changed

3; 3; deleted

4; 4; deleted

5; 5; new
打赏

未经允许不得转载:同乐学堂 » Kettle入门到精通

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!