ETL及kettle介绍知识讲解

E T L及k e t t l e介绍

目录

1. ETL知识 (3)

1.1. ETL定义 (3)

1.1.1. 定义 (3)

1.1.2. 前提 (3)

1.1.3. 原则 (3)

1.2. 模式及比较 (4)

1.3. ETL过程 (7)

1.3.1. 总流程 (7)

1.3.2. 数据抽取流程 (8)

1.3.3. 数据清洗流程 (8)

1.3.4. 数据转换流程 (10)

1.3.5. 数据加载流程 (11)

1.4. 问题分析 (12)

1.4.1. 字符集问题 (12)

1.4.2. 缓慢变化维处理 (13)

1.4.3. 增量、实时同步的处理 (13)

1.4.4. 断点续传 (14)

1.5. ETL工具 (14)

2. Kettle简介及使用 (15)

2.1. 什么Kettle？ (15)

2.2. 下载及安装Kettle (15)

2.3. Kettle简单例子 (17)

2.3.1. 启动Kettle (17)

2.3.2. 创建transformation过程 (18)

2.3.3. 创建job过程 (38)

2.3.4. 命令行运行ktr和kjb (42)

1.ETL知识

1.1.ETL定义

1.1.1.定义

●定义：

数据的抽取(Extract)、转换(Transform)、装载(Load)的过程。

●目标：

数据优化。以最小代价(包括对日常操作的影响和对技能的要求) 将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据。

1.1.

2.前提

●确定ETL范围

通过对目标表信息的收集，确定ETL的范围

●选择ETL工具

考虑资金

运行的平台、对源和目标的支持程度、可编程的灵活性、对源数据变化的监测、数据处理时间的控制、管理和调度功能、对异常情况的处理

●确定解决方案

抽取分析、变化数据的捕获、目标表的刷新策略、数据的转换及数据验证

1.1.3.原则

●应尽量利用数据中转区对运营数据进行预处理。保证数据的安全性、集

成与加载的高效性。

●ETL的过程应是主动“拉取”，而不是从内部“推送”，其可控性将大

为增强。

●流程化的配置管理和标准协议

●数据质量的保证

正确性（Accuracy）：数据是否正确体现在现实或可证实的来源

完整性（Integrity）：数据之间的参照完整性是否存在或一致

一致性（Consistency）：数据是否被一致的定义或理解

完备性（Completeness）：所有需要的数据是否都存在

有效性（Validity）：数据是否在企业定义的可接受的范围之内

时效性（Timeliness）：数据在需要的时间是否有效

可获取性（Accessibility）：数据是否易于获取、易于理解和易于使用数据格式错误（如缺失数据、数据值超出范围或数据格式非法等）

数据一致性：数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID，在例如有些代码在代码表中找不到等。

1.2.模式及比较

两种模式

异构

同构

模式比较的维度：特点

环境

1.3.ETL过程1.3.1.总流程

●数据抽取

●数据清洗

●数据转换

●数据加载

1.3.

2.数据抽取流程

●数据来源

文件系统，业务系统

●抽取方式

根据具体业务进行全量或增量抽取

●抽取效率

将数据按一定的规则拆分成几部分进行并行处理

●抽取策略

根据具体业务制定抽取的时间、频度，以及抽取的流程1.3.3.数据清洗流程

清洗规则：

●数据补缺

对空数据、缺失数据进行数据补缺操作，无法处理的作标记

●数据替换

对无效数据进行数据的替换

●格式规范化

将源数据抽取的数据格式转换成为便于进入仓库处理的目标数据格式●主外键约束

通过建立主外键约束，对非法数据进行替换或导出到错误文件重新处理

1.3.4.数据转换流程

转换规则

●数据合并

多用表关联实现，大小表关联用lookup，大大表相交用join（每个字段加索引，保证关联查询的效率）

●数据拆分

按一定规则进行数据拆分

●行列互换

●排序/修改序号

●去除重复记录

●数据验证：lookup，sum，count

1.3.5.数据加载流程

实现方式优点缺点

时戳方式在业务表中统一添加字

段作为时戳，当OLTP

系统更新修改业务数据

时，同时修改时戳字段

值源数据抽取相对简

单清楚，速度快，

适合数据的增量加

载

需要修改业务表中

的数据结构，业务

数据变动时工作量

比较大，相对风险

较大

日志表方式在OLTP系统中添加日

志表，业务数据发生变

化时，更新维护日志表

内容不需要修改业务表

中的数据结构。源

数据抽取简单清

楚，速度快，适合

数据的增量加载

业务系统中更新记

录日志操作麻烦

全表对比方式抽取所有源数据，在更

新目标表之前先根据主

键和字段进行数据比

对，有更新的进行

update或insert 对系统表结构没有

任何影响，管理维

护统一，可以实现

数据的增量加载

数据比对复杂，设

计比较复杂，执行

速度慢

全表删除插入方式删除目标表数据，将源

数据全部插入

ETL规则简单，速

度快

对维表加代理健不

适应，OLTP系统有

删除数据时，不能

在数据仓库体现被

删数据，不能实现

增量加载

1.4.问题分析

1.4.1.字符集问题

1.字符集定义

字符集是字符(包含字母，数字，符号和非打印字符等)以及所指定的内码所组成的特定的集合。是基于某种操作系统平台和某种语言集支持的。语言集的集合被称为语言组，它可能包含一种或多种语言。

2.C/S字符集转换

●直接转换

对于同一语言组的不同字符集之间，可以直接进行字符的转换，不会产生乱码

●通过Unicode转换

Unicode支持超过650种语言的国际字符集

Unicode系统缺省字符集utf-8

1.4.

2.缓慢变化维处理

1.缓慢变化维定义

在现实世界中，维度的属性并不是静态的，会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维。

2.处理方式

●不保留历史数据

●保留历史数据

起始-结束日期字段标识

真/假状态字段标识

版本号字段标识

代理键字段标识

?自增序列

?构造算法

●保留且分析历史信息

添加新的维度列（数据增多，维度列增多）

1.4.3.增量、实时同步的处理

●整表匹配

同一个库中进行

●写触发器

客户是否允许创建触发器

是否影响数据库性能

●读数据库日志

Oracle：设定物化视图日志

1.4.4.断点续传

●利用源表的索引机制，抽取时按”数据块”顺序抽取

●采取DBLink的机制，结合oracle自身机制优化效率

●生成本地文件块，FTP传输减少对带宽影响。若中断，流程控制自动回

滚加载当前数据块

●ETL工具大都支持异常中止后读取断点重新加载的处理

●支持对变化数据的捕获

●与目标数据库松耦合

1.5.ETL工具

目前ETL工具来源：

1.数据库厂商自带的ETL工具

OWB、ODI：oracle数据库厂商提供的工具，有局限性，与oracle数据库耦合太深

SSIS：Microsoft SQL Server Integration Services的简称，是生成高性能数据集成解决方案（包括数据仓库的提取、转换和加载 (ETL) 包）的平台。前身是mssql2000的DTS．第三方数据库源需要采用ODBC数据转换，效率上有一定折扣．若项目以msssql server数据库为主，采用它肯定是最佳方案。

2.第三方工具提供商

Ascential公司的Datastage：最专业的ETL工具，价格不菲，使用难度一般

Informatica公司的Powercenter：专业程度如Datastage旗鼓相当，价格似乎比Datastage便宜。

IBM SPSS Statistics：IBM SPSS Statistics 产品家族能够帮助企业解决从计划和数据收集到分析、报告和部署的整个分析过程中的问题。它由多个模

块集成，您可以根据需求从中选择一个或多个模块来实现您所希望达到的功能。这些模块可单独购买，也可以捆绑购买。

3.开源ETL工具

kettle：业界最有名的开源ETL工具。开源当然就免费，免费的有些东西使用就不是很方便。

2.Kettle简介及使用

2.1.什么Kettle？

Kettle是一个开源的ETL（Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程）项目，项目名很有意思，水壶。按项目负责人Matt的说法：把各种数据放到一个壶里，然后呢，以一种你希望的格式流出。Kettle 包括三大块：

Spoon——转换/工作(transform/job)设计工具 (GUI方式)

Kitchen——工作(job)执行器 (命令行方式)

Span——转换(trasform)执行器 (命令行方式)

Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高效稳定。Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。

2.2.下载及安装Kettle

1.下载地址：

https://www.360docs.net/doc/4716946800.html,/projects/pentaho/files

2.本地安装jdk 1.5或以上版本。

3.配置java环境变量

（1）打开我的电脑--属性--高级--环境变量

（2）新建系统变量JAVA_HOME和CLASSPATH

变量名：JAVA_HOME

变量值： [具体路径以自己本机安装目录为准]

变量名：CLASSPATH

变量值：.;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar; （3）选择“系统变量”中变量名为“Path”的环境变量，双击该变量，把JDK安装路径中bin目录的绝对路径，添加到Path变量的值中，并使用半角的分号和已有的路径进行分隔。

变量名：Path

变量值：%JAVA_HOME%\bin;%JAVA_HOME%\jre\bin;

4.配置kettle环境变量

在系统的环境变量中添加KETTLE_HOME变量，目录指向kettle的安装目录：D:\kettle\data-integration

5.以上步骤完成后直接启动kettle即可

Windows直接双击批处理文件 Spoon.bat具体路径为：

kettle\data-integration\Spoon.bat

Linux 则是执行spoon.sh,具体路径为：

~/kettle/data-integration/spoon.sh

2.3.Kettl e简单例子

2.3.1.启动Kettle

点击D:\data-integration\下面的Spoon.bat，过一会儿，就会出现Kettle的欢迎界面：

2.3.2.创建transformation过程

2.3.2.1.配置数据环境

在做这个例子之前，我们需要先配置一下数据源，这个例子中，我们用到了三个数据库，分别是：Oracle、MySql、SQLServer，以及一个文本文件。而且都放置在不同的主机上。

Oralce：ip地址为192.168.1.103,Oracle的实例名为scgtoa,创建语句为：

create table userInfo(

id int primary key,

name varchar2(20) unique,

age int not null,

address varchar2(20)

);

insert into userInfo values(1,'aaa',22,'成都市二环路');

insert into userInfo values(2,'东方红',25,'中国北京');

insert into userInfo values(3,'123',19,'广州白云区');

MySql：ip地址为192.168.1.107,数据库名为test2,创建语句为：

create database test2;

use test2;

create table login(

id int primary key,

realname varchar(20) unique,

username varchar(20) unique,

password varchar(20) not null,

active int default 0

);

insert into login values(1,'aaa','admin','admin',0);

insert into login values(2,'东方红','test','test',1);

insert into login values(3,'123','xxx123','123456',1);

SQLServer：本机，ip为192.168.1.115,创建语句为：

create database test3;

use test3;

create table student(

sid varchar(20) primary key,

sname varchar(20) unique,

teacher varchar(20) not null,

);

insert into student values('078','aaa','李老师');

insert into student values('152','东方红','Mr Wu');

insert into student values('034','123','徐老师');

文本文件：名为dbtest.log,位于192.168.1.103\zhang\上，即跟Oracle 同一个主机。

数据仓库：位于192.168.1.107上，跟MySql一台主机，而且数据库也是MySql，也就是说，MySql上有2个数据库，test2和test4。创建语句为：create database test4;

use test4;

create table inforTotal(

id int primary key,

name varchar(20) unique,

age int not null,

address varchar(20),

username varchar(20) unique,

password varchar(20) not null,

active int default 0,

sid varchar(20) unique,

teacher varchar(20) not null,

cat varchar(20),

dog varchar(20)

);

2.3.2.2.Spoon界面

进入工作空间。

2.3.2.3.创建transformation文件

（1）新建transformation文件

双击左边转换将创建一个新的transformation(也可以通过菜单里面的文件-新建-转换方式新建文件)，点击另存为，保存到本地路径，例如保存到D:/etltest下，保存文件名为EtltestTrans，kettle默认transformation，文件保存后后缀名为ktr。