1.简介
ETL(EXTRACT-Transform-Load的缩写,即数据抽取,转换,装载的过程),对于企业或行业的应用来说数据的处理,转换,迁移必不可少,所以需要我们掌握,这里的ETL工具是Kettle.
Kettle纯java编写,翻译为水壶,它允许使用者管理来则不同数据库的数据,通过提供一个图形化的用户环境来描述你想做什么,而不是你想怎么做.
Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制.
Kettle(现在已经更名为PDI,Pentaho Data Integration-Pentaho数据集成)
Kettle的结构-Spoon和Data Integration Server
Spoon是构建ETL Jobs和Transformations的工具,Spoon以拖拽的方式图形化设计,能够通过spoon调用装用的数据集成引擎或者集群
Data Integration Server是一个专用的ETL Sever,它的主要功能有:
执行:通过Pentaho Data Integration引擎执行ETL的作业或转换
安全性:管理用户,角色或者集成的安全性
内容管理:提供一个集中的资源库,用来管理ETL的作业和转换,资源库包含所有内容和特征的历史版本
时间安排:在spoon设计这环境中提供管理Data Integration Server上的活动的时许和监控服务
Kettle的结构-Enterprose Console
Enterprose Console(企业控制台)提供了一个小型的客户端,用于管理Pentaho Data Integration企业版本的部署.
包括企业版本的证书管理,监控和控制远程Pentaho Data Integration 服务器上的活动,分析已登记的作业和转换的动态绩效.
Kettle的核心组件:
Spoon:通过图形接口,用于编辑作业和转换的桌面应用
Pan:一个独立的命令行程序,用于执行由Spoon编辑的转换和作业
Kitche 一个独立的命令行程序,用于执行由Sponn编辑的作业
Carte：Carte是一个降两级的web容器,用于建立专用,远程的ETLServer
Kettle:概念模型:
2.Kettle安装
需要jdk1.8配置环境
安装后文件以及文件夹说明:
3.Kettle基本操作 基
按住shift连接流程线.
4.Kettle核心概念 核
(1)可视化编程:Kettle可以呗归类为可视化变成语言(Visual Programming Languages,VPL)因为Kettle可以使用图形化方式定义复杂的ETL程序和工作流.
Kettle里的图就是转换和作业
可视化变成一直是Kettle里的核心概念,它可以让你快速构建复杂的ETL作业和减低维护工作量.它通过隐藏很多技术细节,使得IT领域更贴近于商务领域.
(2)转换(ransaformation)是ETL解决方案中最主要的部分,它处理抽取,转换,加载各种对数据行的操作.
转换包含一个或者多个步骤(step),如读取文件,过滤数据行,数据清洗,或将数据加载到数据库.
step:是转换里最基本的组成部分.
(3)一个步骤的关键特性:
1.步骤都需要一个名字,整个名字在转换范围内唯一.
2.每个步骤都会读,写数据行(唯一例外就是"生成记录"步骤,该步骤只写数据).
3.步骤将数据写到与之相连的一个或者多个输出跳,在传送到跳的另一端的步骤.
4.大多数的步骤都可以有多个输出跳,一个步骤的数据发送可以被设置为分发和复制,分发是目标步骤轮流接受记录,复制是所有的记录同时发送到所有的目标步骤(默认是分发).
(4)跳(Hop):跳就是步骤之间的连线,跳定义了步骤之间的数据通道.
跳实际上是两个步骤之间的被称之为行集的数据行缓存(行集的大小可以在转换的设置里定义)
当行集满了,向行集写数据的步骤将停止写入,直到行集里又有了空间.
当行集空了,从行集读取数据的步骤停止读取,直到行集里又有课读取的数据行.
(5)数据行-数据类型:
数据以数据行的形式沿着步骤移动,一个数据行是零到多个字段的集合,字段包含下面几种数据类型.
1.String:字符类型数据
2.Number:双精度浮点型
3.Integer:带符号的长整型(64位)
4.BigNumber:任意精度数据
5.Date:带毫秒精度的日期时间值.
6.Boolean:取值位true和false的布尔值
7.Binary:二进制字段可包含图像,声音,视频以及其他类型的二进制数据.
(6)数据行-元数据:
每个步骤在输出数据行时都有对字段的描述,这种描述就是数据行的元数据通常包含下面一些信息:
1.名称:行里的字段名应用时唯一的.
2.数据类型:字段的数据类型.
3.格式:数据显示的方式,如Integer的#,0.00
4.长度:字符串的长度或者BigNumber类型的长度.
5.精度：BigNumber数据类型的十进制精度.
6.货币符号:￥
7.小数点符号:十进制数据的小数点格式,不同文化背景下小数点符号是不同的,一般是点(.)或者逗号(,)
8.分组符号:数值类型数据的分组符号,不同文化背景下数字里的分组符号也是不同的,一般是点(.)或者逗号(,)或者单引号(')
(7)并行:跳的这种基于行集缓存的规则允许每个步骤都是由一个独立的线程运行,这样并发程度高,这一规则也允许数据以最小消耗内存的数据流的方式来处理.
在数据仓库里,我们经常要处理大量数据,这种并发低消耗内存的方式也是ETL工具的核心需求.
(8)对于kettle的转换,不可能定义一个执行顺序,因为所有步骤都是以并发方式执行:当转换启动后,所有的步骤
都同时启动,从它们的输入跳中读取数据,并把处理过的数据写到输入跳,知道输入跳不再有数据,就终止步骤的运行,当所有的步骤都终止了,
整个转换就终止了.(要与数据流向区分开,如果需要一个任务沿着指定的顺序执行,那么就需要用到后面的"作业")
5.转换的一些基本操作 转
1.文本转换Excel:
2.Excel转Excel:基本
和文本转Excel类似
3.多文件合并:一般数据是以多个文件的形式出现,有的数据还会分散在而多个子文件夹中.所以合并数据也是开发中非常常见的操作。 下面是针对Excel
进行对同一个目录下多个文件进行合并
从XML中获取数据:XML指的是可扩展标记语言(EXtensible Markup
Language),XML被设计用来传输和存储数据.
6.Get data from XML
1.从XML中获取数据:XML指的是可扩展标记语言(EXtensible Markup Language),XML被设计用来传输和存储数据. 2.选取节点XPath使用路径表达式在
XML文档中选取节点,节点是通过沿着路径或者step来选取的 下面列出了最有用的路径表达式:
nodename:选取此节点的所有子节点 /:从根节点选取 //:从匹配选择的当前节点选择文档中的节点,而不考虑他们的位置 .:选取当前节点 …:选取当前节点
的父节点 @:选取属性
3.从xml中读取数据到excel(输出节点设置和上面的Excel设置类似): xml样式:
输入节点设置:
7.Json Input
1.Json介绍
2.JsonPath
3.JsonInput
1.Json样例:
2.分段输出数据:
8.生成记录 生
数据仓库中绝大多数的数据都是业务系统生成的动态数据,但是其中一部分维度数据不是动态的:比如日期维度
静态维度数据可以提前生生成
9.表输入 表
连接共享 连
10.SQL文件输出 文
将数据库中某一张表的表结构和具体数据以sql文件输出
11.表输出 表
将数据输出到表中
例如将excel中的数据导入到一张表中
12.更新更
更新就是把数据库已经存在的记录与数据流里面的记录进行比对,如果不同就进行更新.
13.插入更新 插
就是把数据库中的数据和流里边的数据进行对比,如果存在则更新,如果不存在则插入(相当于调用了merge into）
14.自定义常量数据 自 -删除删
用常量数据控件和删除控件指定删除行(相当于拼写sql）
15.concatfields/转换转
转换属于转换大类中的第四个大块,它是整个ETL中的T即:Transform清洗,转换.
ETL部分中T花费的时间最长,一般情况下它占工作量的2/3.
concatfields:将多个字段连接起来形成一个新的字段.
例如将Excel中一个字段与另一个字段并联并输出到一个新的Excel中
16.值映射 值
就是把字段里的一个值映射成其他的值,这在数据质量规范上使用非常多.
17.增加常量 增
增加常量就是在本身的数据流里面添加一列数据,该列的数据都是相同的值
18.增加序列 增
增加序列是给数据流一个序列字段
19.字段选择 字
主要是用于对数据的筛选和过滤,可以对字段进行改名,移除输出等操作
20.计算器 计
是一个函数集合来创建新的字段,还可以设置字段是否移除(临时字段)
21.字符串的剪切操作替换 字
剪切:相当于substring.
替换:相当于replace
操作:去除字段空格和转换大小写.
22.去除重复记录 去
去除重复记录之前需要先排序记录.去重指的是去除数据流里面相同的数据行
23.唯一行 唯 (哈希值 哈 )
唯一行(哈希值)就是删除数据流重复的行
注意:唯一行(哈希值)和(排序记录+去除重复记录)效果是一样的但是实现原理不同,它的效率高于排序加去重的方式.
24.拆分字段 拆
将字段按照分割符号拆分出两个或者多个字段
注意:拆分字段后原字段就不存在于数据流中了
25.列拆分为多行 列
列拆分为多行就是把指定的分隔符的字段进行拆分为多行
26.列转行 列
列转行就是如果数据一列有相同的值,按照指定的字段,把多行数据转换为一行数据,去除一些原来的列明,把一列数据变为字段
注意:列转行之前也需要进行排序处理.
27.行转列 行
行转列:就是把数据字段的字段名转为一列,把数据行转为数据列
28.行扁平化 行
行扁平化就是把同一组的多行数据合并成为一行.
注意:只有数据流的同类数据数据行记录一致的情况才可以使用,数据流必须进行排序,否则结果会不正确.
29.替换替 Null值
替换null值就是把null转换为其他的值
30.写日志 写
写日志功能主要是用在调试的时候,主要是将日志信息打印到日志窗口
31.switch/case
就是一个分流的控件
32.过滤记录 过
也是分流的控件,相当于if…else…
33.空操作 空
当数据流到这里的时候什么也不做.相当于else中什么也没写,用得很少
34.中止中
中止是数据流的终点,如果有数据到这里将会报错.一般用于校验数据
35.查询模块中的 查 HttpClient
HttpClient是采用GET的方式提交请求,获取返回的页面内容
示例网络地址:https://services.odata.org/V3/Northwind/Northwind.svc/Products/
36.连接数据库查询 连
数据库查询的方式是按照左连接的方式,以前面输入的数据为基础查询所有的信息
37.数据库连接 数
数据库连接可以执行两个数据库的查询和单参数的表输入
38.流查询 流
38.流查询 流
流查询在查询前将数据都加载到内存中,并且只能进行等值查询.
39.合并记录 合
合并记录是用于将两个不同来源的数据合并,这两个来源的数据分别为旧数据和新数据,该步骤将旧数据和新数据按指定的关键字匹配,比较,合并.
要设置的参数：旧数据来源的步骤和新数据来源的步骤.
标志字段:设置标志字段的名称,标志字段用于保存比较的结果,比较的结果有以下几种:
(1).“identical”-旧数据和新数据一样
(2).“changed”-数据发生了变化
(3).“new”-新数据中有而旧数据中没有的记录
(4).“deleted”-旧数据中有而新数据中没有的记录
关键字段:用于定位两个数据源是不是同一条记录.
比较字段:对于两个数据源中间的同一条记录中,指定需要比较的字段.
合并和的数据将包括旧数据来源和新数据来源里的所有数据,对于变化的数据使用新数据代替旧数据,同时在结构里用一个表示字段,来指定旧数据的比较结果.
注意:
旧数据和新数据需要事先按照关键字段排序.
旧数据和新数据要有相同的字段名称.
40.记录集连接 记
记录集连接就像数据库的左连接(以左表为主表),右连接(右边为主表),内连接(只展示能够匹配上的),full outer(全匹配外连接,展示所有的信息).
注意:在进行记录集连接之前,应该要对记录集进行排序.
41.统计统 /分组分
分组是按照某一个或某几个进行分组,同时可以将其余字段按照某种规则进行合并.
注意:
分组之前数据应该进行排序.
42.映射子转换 映
映射是用来定义子转换,便于封装和重用.
映射(子转换)是用来配置子转换,对子转换进行调用的一个步骤.
映射输入桂芬是输入字段,由调用的转换输入.
映射输出规范是向调用的转换输出所有列,不做任何处理.
43.JavaScript脚本脚
javascript脚本就是使用javascript语言通过代码编程来完成对数据流的操作,JS中由很多内置函数,可以在编写JS代码时查看.
存在两种不同的模式:不兼容模式和兼容模式
不兼容模式:是默认的,也是推荐的
兼容模式:兼容老版本的kettle
44.java脚本脚
java脚本就是使用java语言通过代码编程来完成对数据流的操作.
内置了很多函数可以直接使用
java脚本Main
Main函数对应一个processRow()函数,processRow()函数是用来处理数据流的场所.
45.执行执 Sql脚本脚
执行sql脚本可以执行一个update语句,用来更新某张表的数据.
46.作业作
转换是并行执行的,而作业是串行执行.
作业项时作业的基本构成部分,如同转换的步骤,作业项也可以使用图标的方式图形化展示.
作业项之间可以传递一个结果对象(result object).这个结果对象里面包含了数据行,他们不是以数据流的方式来传递的,而是等待一个作业项执行完了再传递给下一个作业项.
因为作业顺序执行作业项,所以必须顶一个七点,有一个叫"开始"的作业项就定义了这个点,一个作业只能定一个开始作业项.
作业跳:它是作业项之间的连接线,他定义了作业的执行路径,作业里每个作业项的不同运行结果决定了作业的不同执行路径.
(1)无条件执行:无论上一个作业执行成功还是失败,下一个作业都会执行,这是一种蓝色的连线,上面有一个锁的图标.
(2)当运行结果为真的时候执行:当上一个作业项的执行结果为真的时候,执行下一个作业项,要无错误执行的情况下使用.这是一种绿色的连接线,上面有一个对勾号的图标.
(3)当运行结果为假的时候执行:当上一个作业项的执行结果为假或者没有成功执行,执行下一个作业项,这是一种红色的连接线,上面有一个红色的停止图标.
在图标上单击可以对跳进行设置.
47.参数参
对于ETL参数传递是一个很重要的环节,因为参数的传递会涉及到业务数据时如何抽取。
参数分为两种:全局参数/局部参数.
全局参数定义是通过当前用户下.kettle文件夹中的kettle.properties文件来定义.
定义方式是采用键=值对方式来定义:strat_date=20130101
注意全局参数配置后需要重启kettle后这个参数才能生效.
局部参数:它是通过"Set Variables"与"Get Variables"方式来设置.注意:在"Set Variables"时在当前转换当中是不能马上使用,需要在作业中的下一步骤中使用.
Kettle中参数的使用方法有两种:一种是"%%变量名%%",一种是${变量名}.
48.常量传递 常
常量传递就是先自定义常量数据,在表输入的SQL语句里面使用?来替换
?号的替换顺序就是常量定义的顺序.
49.转换命名参数 转
转换命名参数就是在转换内部定义的变量,作用范围是在转换内部
在转换的空白处右键,选择转换设置就可以看见.
50.设置变量 设 -获取变量 获
在转换的作业分类中,有一个设置变量和获取变量的步骤
注意:"获取变量"在当前转换中是不能马上使用,而需要在作业中的下一个步骤中使用.
51.设置作业的变量 设
同转换一样,作业也可以设置变量.
52.发送邮件 发
注意这个功能只有企业级邮箱才能发送,并且需要开通授权码.
