Kettle的入门

ETL  :  Extract-Transform-Load 的缩写，即数据抽取，转换，装载的过程，这里使用Kettle进行学习

Kettle：中文名字水壶，把各种数据放到一个水壶里，然后以一种特定的格式流出，允许管理来自不同的数据库的数据，通过提供一个图形化的用户环境来描述你想做什么，而不是你想怎么做。

Kettle有两种脚本文件，transformation和job,transformation完成针对数据的基础转换，job 完成整个工作流的控制。kettle现在已经更名为PDI.(Pentaho Data Integeration-Pentaho数据集成)

Kettle的结构  

  spoon 是构建ETL jobs和Transformations  的工具。spoon 通过依托拽的方式图形化设计能够通过spoon 调用专用的数据集成引擎或者集群

data Integration Server 是一个专用的ETL Server  主要功能有

执行    通过Pertaho  Data Integration 引擎执行ETL的作业或转换

安全性  管理用户角色集成的安全性

内容管理  提供一个集中地资源库，用来管理ETL的作业和转换，资源库包含所有内容和特定的历史版本

时序安排  在spoon 设计者环境中提供管理Data  Integration Server上的活动的时序和监控服务

EnterPrise  console  一个小型的客户端  用于管理Pentaho Data Integration 企业版的部署

                                   包括企业版本的证书管理，监控，控制远程服务器上的活动，分析已登记的                                     作业和转换的动态绩效

Kettle 的核心组件

spoon 通过图像接口，用于编辑作业和转换的桌面应用

Pan   一个独立的命令行程序 ，用于执行由spoon编辑的转换和作业

Kitchen   一个独立的命令行程序，用于执行spoon编辑的脚本

Carte 一个轻量级的web容器，用于简历专用，远程的ETL Server

step    发送行数据 给hop     hop从step 读取行数据

Kettle下载及目录文件

 配置JAVA 环境 和KETTLE

运行spoon.bat

KETTLE 快速体验

读取csv文件写入excel

 

 

 

 

 日志信息

 

 

Preview  Data

 KETTLE的核心概念

可视化编程  ：可以被归类于可视化编程 使用图形化的方式定义复杂的ETL程序和工作流

Kettle里的图就是转换和作业，可以快速构建复杂的ETL作业。

转换 ：是ETL解决方案中最主要的部分，他处理抽取转换，加载各种对数据行的操作

转换包含一个或多个步骤step,如读取文件，过滤数据行，数据清洗或将数据加载到数据库

转换里的步骤通过跳(hop)来连接，跳定义一个单向通道，允许数据从一个步骤向另一个步骤六流动。在kettle的里，单位是行，数据流就是数据行从一个步骤向零一个步骤的移动

  Step 步骤

步骤控件是转换里基本的组成部分。

存在步骤，“csv文件输入”和“Excel输出”  一个步骤，

1.要有一个名字。这个名字在转换范围内唯一

2.每个步骤都会读写数据行（唯一例外是“生成记录”步骤，该步骤只写数据）

3.步骤将数据写到与之相连的一个或者多个输出跳，在传送到数据的另一端的步骤

4.大多数步骤都可以有多个输出跳，一个步骤的数据发送可以被设置为分发和复制。分发是目标步骤轮流接受记录。复制是所有的记录被同事发送到所有的目标步骤。

跳：

步骤之间带箭头的连线，跳定义了步骤之间的数据通路

跳实际上是两个步骤之间的被称为行集的数据行缓存（行集的大小可以在转换的设置里定义）

 当行级满了，从行读取数据的步骤停止读取，直到空

数据行的数据类型

元数据  : 输出数据行时对数据的描述

并行  

跳的这种基于行级缓存的规则允许每个步骤都由一个独立的线程运行，这样并发度最好，这一i规则也允许数据以最小消耗内存的数据流的方式来处理。这种低消耗内存的方式也是ETL核心需求

 KETTLE 的转换不可能定义一个执行顺序，因为所有的步骤都以并发方式执行难，当转换启动后

所有的步骤都同时启动，从他们的输入跳中读取数据，并把处理过的数据写入到输入跳，知道输入跳里不在有数据，就中止步骤的运行，当所有的步骤都种植了，整个转换就终止了（要与数据流向区分开）

 如果要按照特定的顺序，要用到作业。

CSV文件输入  ： 

    输入：用来抽取数据或者生成数据。

csv :带有特定文件格式的文本文档。

多文件合并

数据往往以多个文件的形式出现，有的数据还会分散在多个子文件夹，所以有时候需要合并文件。

xml

 

 生成记录  

       静态数据   ：数据仓库中的绝大多数数据都是业务系统中生成的动态数据，打死你hi其中一部分纬度数据不是动态的，比如说日期纬度。静态纬度数据可以提前生成

表输入 ：需要添加mysql的驱动到lib 目录下，但是有个大坑，但是驱动限定版本5.1.47，以上的可能不支持，我试过了八以上的版本，不太行

 新建连接然后选择数据库表获取查询sql

 Excel输出

      ETL 中的L Load,加载。

 

 可以进行分发和复制

 sql输出

更新： 把数据库已经存在的记录与数据流里面的记录惊醒对比，如果不同就进行更新。

如果记录不存在，就会报错。

 插入更新：会把数据与数据库的对比，如果有记录就更新，没有就新增。

 空操作

            一般作为数据流的终点，一般在kettle的sample中经常使用，但在实开发中比较少用

 

 中止：终止是数据流的终点，如果数据到这里，会报错误，用来校验数据的时候使用

查询：查询是转换里面的第七个分类 ，用来查询数据源里的数据并合并到主数据流里。

HTTPCLIENT   是使用get提交方式，获取返回的页面内容。

 自定义常量数据

 

数据库查询是数据库里的左连接

如果中文乱码，就点数据库连接-》高级--》中文--》set names gbk 

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

 

 流查询

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

 

 连接  是结果集通过关键字进行连接

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

需要设置的参数，

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

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

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

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

2.“changed”  -数据发生了变化。

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

4.“delete” --旧数据有而新数据中没有的记录

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

比较字段：对于两个数据源中的同一条记录中，指定需要比较的字段。合并后的数据将包括数据来源里的所有数据，对于变化的数据，使用新数据代替旧数据。同时在结果里用一个表示字段，来指定新旧数据的比较结果。

注意：

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

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

但是一般要先排序，在进行合并记录

 记录关联（笛卡尔积）：

记录关联就是对两个数据流进行笛卡尔积操作。

 

 分组：

统计

 

设置常量

 

 

 

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

在数据质量的规范上使用非常多，比如很多系统对应性别gender 字段的定义不同

 

 

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

 ​​​​​​​

 

 ​​​​​​​

 

 增加序列：给流增加一个序列字段。

字段选择：

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

计算器：

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

 

剪切字符串 ：用来指定输入流字段裁剪的位置剪切出新的字段。

 

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

 

字符串操作：

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

 去除重复记录

去除数据流里面相同的数据行，（必须先对数据流进行排序）

排序记录：按照指定的字段进行升序或者降序对数据流进行排序。

 

先排序，才能进行去除重复记录

 

 唯一行（哈希值）：

删除数据流重复的行。 唯一行哈希和（排序记录+去除重复记录）效果一样但是实现原理不一样

唯一行哈希效率快

 拆分字段

把字段按照分隔符拆分成两个或者多个字段

 列拆分为多行：

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

 列转行：

列转行就是如果数据一列有相同的值，按照指定的字段，把多行数据转换为一行数据。

去除一些原来的列名，把一列数据变为字段。

 注意：列转行之前数据流必须进行排序；

 

 行扁平化：就是把同一组的多行数据合并成一行

注意：只有数据流的同类数据数据行记录一致的情况才可以使用

数据流必须进行排序，否则结果会不正确

 

 替换null值：把null值转换为其他的值

 写日志：

主要在测试的时候使用，把日志信息打印到日志窗口。

 流程：用来控制数据流程和数据流向

 过滤记录：

让数据从一路到两路

 

 

 

 

映射输出规范：是向调用的转换输出所有列，不做任何处理

 

js脚本使用js语言通过代码编程来完成对数据流的操作，js中有很多内置函数，可以在js代码编写时查看，存在两种不同的模式：不兼容模式和兼容模式。

不兼容模式是默认的也是推荐的，兼容模式：兼容老版本的 kettle

js中获取字段--

不兼容模式：

myVar = FieldName;

兼容模式：

根据字段类型的不同，使用不同的方法：

myVar  =FieldName.getString();

myVar  =FieldName.getNumber();

js脚本中给字段赋值：

FieldName = myVar;

兼容模式

FieldName.setValue(myVar);

在js 脚本中使用java类

不兼容模式 ：

var  myVar = new java.lang.String("xxxxx.com");

兼容模式：

var myVar = new Packages.java.lang.String("xxxx.com");

 

java脚本：

     就是使用java语言通过代码变成来完成对数据流的操作。内置了很多函数可以使用。

 

作业：

大多数的ETL项目都需要完成各种各样的维护工作。例如，如何传送文件，验证数据库表如何存在等等，这些操作都是按照一定的顺序来完成的，因为转换以并行的方式运行，就需要一个串行执行的作业来处理这些操作。 

      一个作业包含一个或者多个作业项，这些作业项以某种顺序来执行，作业执行顺序由作业向之间的跳（job hop） 和每个作业项目的执行结果来决定。

作业项：

    是作业的基本构成部分，如同转换的步骤，作业项也可以以图标的方式图形化展示

但是在作业项之间可以传递一个结果对象(result object) 这个结果对象里面包含了数据行，他们之间不是以数据流的方式来传递的，而是等待一个作业项执行完了，在执行下一个作业项。因为作业 顺序执行作业项，所以必须定义一个起点。又一个叫"开始" 的作业项，一个作业智能定义一个开始的作业项

 作业跳：

      作业的跳是作业项之间的连接线，他定义了作业的执行路径。作业里每个作业项的不同运算结果决定了做作业的不同执行路径。

 1.无条件执行：不论上一个作业项成功还是失败，下一个作业项都会执行。这是一种蓝色的连接线

上面有一个锁的标志

2.当运行的结果为真时执行：当上一个作业项的执行结果为真时，执行下一个作业项。通常在需要在无错误执行的情况下使用。这是一种绿色的连接线，上面有一个对钩号的图标

3.当运行结果为假时候执行：当上一个作业项的执行结果为假或者没有成功执行是，执行下一个作业项，这是一种红色的连接线，上面有一个红色的停止图标

参数：

 全局参数：

  全局参数是通过.kettle文件夹里的kettle.properties文件来定义。

定义方式采用键值对 的方式         键 = 值

配置完全局变量需要重新启动kettle 才能生效

局部参数：

局部参数变量通过“Set Variables”  与“Get Variables”方式来设置

注意：在set vriables时在当前转换中是不能马上使用的，需要在作业的下一步骤中使用

参数的使用：

Kettle中参数的使用方法有两种：一种是%%变量名%% ，一种是${变量名}

在使用参数的时候需要把“是否替换变量勾选上”，否则无法使变量生效。

kettle的配置可以加快读写速度：

defaultRowPrefetch  500
rewriteBatchedStatements  true
zeroDateTimeBehavior  convertToNull
useServerPrepStmts  false
characterEncoding  utf-8
useCompression  true
readTimeout  60

常量传递：

先自定义常量数据，在表输入的sql语句里面使用？来替换。

？定义的顺序就是常量定义的顺序。 要勾选从步骤传递数据。

 

 转换命名参数：

转换命名参数就是转换内部定义的变量，作用范围实在转换内部。

在转换的空白处右键，选择转换设置就可以看见。

设置变量，获取变量

  在转换里有一个作业分类，里面有设置变量和获取变量的步骤。

注意：“获取变量” 时在当前转换当中是不能马上使用的，需要在作业中的下一步骤使用

 

 

变量可以在转换里设置，也可以在作业里设置 

发送邮件：

 发送邮件就是执行成功，失败，其他某种情景给相关人员发送邮件

注意：只有企业邮箱才可以 ，个人邮箱不行，并且需要在邮件设置中开通客户端授权码