ETL及kettle介绍.doc

目录1、 ETL知识21、1、 ETL定义21、1、1、 定义21、1、2、 前提21、1、3、 原则21、2、 模式及比较31、3、 ETL过程61、3、1、 总流程61、3、2、 数据抽取流程71、3、3、 数据清洗流程71、3、4、 数据转换流程91、3、5、 数据加载流程101、4、 问题分析111、4、1、 字符集问题111、4、2、 缓慢变化维处理121、4、3、 增量、实时同步得处理121、4、4、 断点续传131、5、 ETL工具132、 Kettle简介及使用142、1、 什么Kettle？142、2、 下载及安装Kettle142、3、 Kettle简单例子162、3、1、 启动Kettle162、3、2、 创建transformation过程172、3、3、 创建job过程372、3、4、 命令行运行ktr与kjb411. ETL知识1.1. ETL定义1.1.1. 定义l 定义:数据得抽取(Extract)、转换(Transform)、装载(Load)得过程。l 目标:数据优化。以最小代价(包括对日常操作得影响与对技能得要求) 将针对日常业务操作得数据转化为针对数据仓库而存储得决策支持型数据 。1.1.2. 前提l 确定ETL范围通过对目标表信息得收集,确定ETL得范围l 选择ETL工具考虑资金运行得平台、对源与目标得支持程度、可编程得灵活性、对源数据变化得监测、数据处理时间得控制、管理与调度功能、对异常情况得处理l 确定解决方案抽取分析、变化数据得捕获、目标表得刷新策略、数据得转换及数据验证1.1.3. 原则l 应尽量利用数据中转区对运营数据进行预处理。保证数据得安全性、集成与加载得高效性。 l ETL得过程应就是主动“拉取”,而不就是从内部“推送”,其可控性将大为增强。 l 流程化得配置管理与标准协议 l 数据质量得保证正确性(Accuracy):数据就是否正确体现在现实或可证实得来源 完整性(Integrity):数据之间得参照完整性就是否存在或一致 一致性(Consistency):数据就是否被一致得定义或理解 完备性(pleteness):所有需要得数据就是否都存在 有效性(Validity):数据就是否在企业定义得可接受得范围之内时效性(Timeliness):数据在需要得时间就是否有效可获取性(Accessibility):数据就是否易于获取、易于理解与易于使用 数据格式错误(如缺失数据、数据值超出范围或数据格式非法等)数据一致性:数据源系统为了性能得考虑,会在一定程度上舍弃外键约束,这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有得用户ID,在例如有些代码在代码表中找不到等。1.2. 模式及比较l 两种模式异构同构l 模式比较得维度:特点环境1.3. ETL过程1.3.1. 总流程l 数据抽取l 数据清洗l 数据转换l 数据加载1.3.2. 数据抽取流程l 数据来源文件系统,业务系统l 抽取方式根据具体业务进行全量或增量抽取l 抽取效率将数据按一定得规则拆分成几部分进行并行处理l 抽取策略根据具体业务制定抽取得时间、频度,以及抽取得流程1.3.3. 数据清洗流程清洗规则:l 数据补缺对空数据、缺失数据进行数据补缺操作,无法处理得作标记l 数据替换对无效数据进行数据得替换l 格式规范化将源数据抽取得数据格式转换成为便于进入仓库处理得目标数据格式l 主外键约束通过建立主外键约束,对非法数据进行替换或导出到错误文件重新处理1.3.4. 数据转换流程转换规则l 数据合并多用表关联实现,大小表关联用lookup,大大表相交用join(每个字段加索引,保证关联查询得效率)l 数据拆分按一定规则进行数据拆分l 行列互换l 排序/修改序号l 去除重复记录l 数据验证:lookup,sum,count1.3.5. 数据加载流程实现方式优点缺点时戳方式在业务表中统一添加字段作为时戳,当OLTP系统更新修改业务数据时,同时修改时戳字段值源数据抽取相对简单清楚,速度快,适合数据得增量加载需要修改业务表中得数据结构,业务数据变动时工作量比较大,相对风险较大日志表方式在OLTP系统中添加日志表,业务数据发生变化时,更新维护日志表内容不需要修改业务表中得数据结构。源数据抽取简单清楚,速度快,适合数据得增量加载业务系统中更新记录日志操作麻烦全表对比方式抽取所有源数据,在更新目标表之前先根据主键与字段进行数据比对,有更新得进行update或insert对系统表结构没有任何影响,管理维护统一,可以实现数据得增量加载数据比对复杂,设计比较复杂,执行速度慢全表删除插入方式删除目标表数据,将源数据全部插入ETL规则简单,速度快对维表加代理健不适应,OLTP系统有删除数据时,不能在数据仓库体现被删数据,不能实现增量加载1.4. 问题分析1.4.1. 字符集问题1. 字符集定义字符集就是字符(包含字母,数字,符号与非打印字符等)以及所指定得内码所组成得特定得集合。就是基于某种操作系统平台与某种语言集支持得。语言集得集合被称为语言组,它可能包含一种或多种语言。2. C/S字符集转换l 直接转换对于同一语言组得不同字符集之间,可以直接进行字符得转换,不会产生乱码l 通过Unicode转换Unicode支持超过650种语言得国际字符集 Unicode系统缺省字符集utf81.4.2. 缓慢变化维处理1. 缓慢变化维定义在现实世界中,维度得属性并不就是静态得,会随着时间得流失发生缓慢得变化。这种随时间发生变化得维度我们一般称之为缓慢变化维。2. 处理方式l 不保留历史数据l 保留历史数据起始结束日期字段标识真/假状态字段标识版本号字段标识代理键字段标识n 自增序列n 构造算法l 保留且分析历史信息添加新得维度列(数据增多,维度列增多)1.4.3. 增量、实时同步得处理l 整表匹配同一个库中进行l 写触发器客户就是否允许创建触发器就是否影响数据库性能l 读数据库日志Oracle:设定物化视图日志1.4.4. 断点续传l 利用源表得索引机制,抽取时按”数据块”顺序抽取 l 采取DBLink得机制,结合oracle自身机制优化效率 l 生成本地文件块,FTP传输减少对带宽影响。若中断,流程控制自动回滚加载当前数据块 l ETL工具大都支持异常中止后读取断点重新加载得处理l 支持对变化数据得捕获l 与目标数据库松耦合1.5. ETL工具目前ETL工具来源:1. 数据库厂商自带得ETL工具OWB、ODI:oracle数据库厂商提供得工具,有局限性,与oracle数据库耦合太深SSIS:Microsoft SQL Server Integration Services得简称,就是生成高性能数据集成解决方案(包括数据仓库得提取、转换与加载 (ETL) 包)得平台。前身就是mssql2000得DTS.第三方数据库源需要采用ODBC数据转换,效率上有一定折扣.若项目以msssqlserver数据库为主,采用它肯定就是最佳方案。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(ExtractTransformLoad得缩写,即数据抽取、转换、装载得过程)项目,项目名很有意思,水壶。按项目负责人Matt得说法:把各种数据放到一个壶里,然后呢,以一种您希望得格式流出。Kettle包括三大块:Spoon转换/工作(transform/job)设计工具 (GUI方式)Kitchen工作(job)执行器 (命令行方式) Span转换(trasform)执行器 (命令行方式)Kettle就是一款国外开源得etl工具,纯java编写,绿色无需安装,数据抽取高效稳定。Kettle中有两种脚本文件,transformation与job,transformation完成针对数据得基础转换,job则完成整个工作流得控制。2.2. 下载及安装Kettle1. 下载地址:2. 本地安装jdk 1、5或以上版本。3. 配置java环境变量（1） 打开我得电脑属性高级环境变量（2） 新建系统变量JAVA_HOME与CLASSPATH变量名:JAVA_HOME变量值: 具体路径以自己本机安装目录为准变量名:CLASSPATH变量值:、;%JAVA_HOME%libdt、jar;%JAVA_HOME%libtools、jar;（3） 选择“系统变量”中变量名为“Path”得环境变量,双击该变量,把JDK安装路径中bin目录得绝对路径,添加到Path变量得值中,并使用半角得分号与已有得路径进行分隔。变量名:Path变量值:%JAVA_HOME%bin;%JAVA_HOME%jrebin;4. 配置kettle环境变量在系统得环境变量中添加KETTLE_HOME变量,目录指向kettle得安装目录:D:kettledataintegration5. 以上步骤完成后直接启动kettle即可Windows直接双击批处理文件 Spoon、bat具体路径为:kettledataintegrationSpoon、batLinux 则就是执行spoon、sh,具体路径为:/kettle/dataintegration/spoon、sh2.3. Kettle简单例子2.3.1. 启动Kettle点击D:dataintegration下面得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、103zhang上,即跟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。（2） 创建数据库连接在transformation页面下,点击左边得【主对象树】,双击【DB连接】,进行数据库连接配置。在Connection Name下面得数据库里输入连接得名字,如oracle在Settings里得Host Name里输入主机名,如102、169、1、103在Settings里得Database Name里输入数据库名,如scgtoa在 Settings里得User Name里输入数据库用户名,如bbs在Settings里得Password里输入数据库密码,如bbs然后点击”Test”按钮测试连接就是否成功,如果成功后,点击OK按钮。同样地,我们创建MySql与SQLServer得连接,如:最后我们瞧到这样得界面:2.3.2.4. 抽取、转换、装载过程（1） 表输入在EtltestTrans页面下,点击左侧得【核心对象】,点击【输入】,选中【表输入】,拖动到主窗口释放鼠标。双击工作区中【表输入】图标,数据库连接选择刚刚创建好得连接名为Oracle得数据库连接,在主窗口写入对应得查询语句:SELECT ID, NAME, AGE, ADDRESSFROM USERINFO ORDER BY NAME步骤名称写为:oracle userInfo表输入可以通过预览,查瞧这个步骤输出得数据。同样地,我们建立一个MySql得表输入组件:查询语句为SELECT id, realname, username, password, activeFROM login ORDER BY realname步骤名称为:mysql login表输入（2） 表连接查询在EtltestTrans页面下,点击左侧得【核心对象】,点击【连接】,选中【Merge Join】,拖动到主窗口释放鼠标。如图:然后,点击表输入,按住shift键,再拖动鼠标到Merge Join,这时就实现了在两个组建之间得连线。如图:双击工作区中得Merge Join,在出现得窗口里:步骤名称命名为:表连接查询第一个步骤选择:oracle userInfo表输入第二个步骤选择:mysql login表输入连接类型选择:FULL OUTER在连接字段里,第一个步骤写:NAME,第二个步骤写:realname（3） 表连接查询与表输入再次连接查询SQLServer表输入,如图:（4） 远程文本文件输入在EtltestTrans页面下,点击左侧得【核心对象】,点击【输入】,选中【文本文件输入】,拖动到主窗口释放鼠标。双击这个图标,进入编辑窗口:输入步骤名称:远程文本文件输入在选中得文件里得文件/目录下面输入,文件所在得路径,注:这个路径可以通过本机得网上邻居访问。（5） 第三次表连接查询再次拖入一个表连接查询图标,将上次得查询结果与远程文本文件相连,再进行一次表连接查询。（6） 插入/更新在EtltestTrans页面下,点击左侧得【核心对象】,点击【输出】,选中【插入/更新】,拖动到主窗口释放鼠标。将它与第三次表连接查询进行连线。双击”插入/更新”图标,进入编辑窗口。数据库连接选择数据仓库得数据库,目标表选择对应得infortaotal表,在查询关键字里,表字段写name(表示仓库表里得字段),流里得字段1里写入NAME(即上一个步骤输入得内容里得NAME)。比较符用”=”号。点击Edit mapping按钮,将表字段,与流利得字段进行对应,即流里字段里得值输入到表里得哪个字段里去。2.3.2.5. 执行transformation这样,我们得transformation就创建完成了,这时,我们可以点击运行,测试创建得transformation就是否成功。点击这个按钮,就会执行ETL过程,这时我们可以查瞧数据仓库得表,如果有数据,并符合我们得要求,那么说明我们得transformation成功了。2.3.3. 创建job过程Job就就是把一个或多个transformation按照一定得顺序组合起来,形成一个流程。2.3.3.1. 创建job文件通过菜单里得“文件新建作业”就可以创建一个job文件,点击另存为D:etltestjobtest、kjb。2.3.3.2. job流程1. 拖入图标并连线在EtlscriptJob页面,点击【核心对象】,点击【通用】,选中【START】拖动到主窗口释放鼠标,再选中一个【Transformation】,拖动到主窗口释放鼠标,建立【START】与【Transformation】之间得连接。2. 编辑”开始”流程双击”START”图标,就进入编辑窗口。我们可以选择这个job就是否就是重复得,如果就是重复得,需要选择重复得类型,在根据类型判断就是否需要编辑时间间隔。3. 编辑transformation流程双击”transformation”图标,就进入编辑窗口。其中,我们要输入job名称:如jobOK,点击转换文件名后面得按钮,浏览我们我们要转换得文件,并选中。其她得选项可以根据实际需要再进行配置。点击确定按钮,退出编辑窗口。2.3.3.3. 运行job这时一个简单得job流程就建立好了,这时我们点击按钮,就可以运行这个job了。如图:2.3.3.4. 什么时候用Job这个job跟上面得转换其实就是完成了同样得工作,只不过job可以把多个transformation放在一起组成一个流程,什么时候用job或transformation,就是根据需要来定得,比如只有一个transformation时,就可以直接用transformation,但如果这个转换需要定时,那么就必须用job了。多个transformation需要组成一个流程时也必须用job。2.3.4. 命令行运行ktr与kjb在上面得过程中,我们都就是在IDE工具中,直接点击按钮进行运行文件得,但在实际中,我们需要脱离IDE,进行单独得运行,这时就必须用到命令行来运行文件了。2.3.4.1. Cmd或shell方式运行1. Windows环境（1） ktr得运行:运行transformation文件就是通过Pan、bat来运行得。打开cmd命令行窗口,转到Pan、bat所在得目录,如d:dataintegration,然后执行文件得命令为:pan /file D:etltestEtltestTrans、ktr（2） kjb得运行:运行job文件就是通过kitchen、bat来运行得。打开cmd命令行窗口,转到Pan、bat所在得目录,如d:dataintegration,然后执行文件得命令为:kitchen /file D:etltestjobOK、kjb2. Linux环境（1） ktr得运行:运行transformation文件就是通过Pan、sh来运行得。（2） kjb得运行:运行job文件就是通过kitchen、sh来运行得。2.3.4.2. 脚本文件运行1、Windows 批处理文件如果觉得通过打开命令行输入麻烦,我们可以把它写在一个批处理文件中。如:d:cd D:dataintegrationpan /file D:etltestEtltestTrans、ktr把这些内容保存在pan、bat里,通过双击panKtr、bat就可以执行ktr文件了。同样地,我们把下面得内容:d:cd D:dataintegrationkitchen /file D:etltestjobOK、kjb保存在kitchenKjb、bat里,双击它,也可以执行kjb文件。2、shell脚本Linux下环境。