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）：数据是否被一致的定义或理解 完备性（Completeness）：所有需要的数据是否都存在 有效性（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系统缺省字符集utf-81.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（Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程）项目，项目名很有意思，水壶。按项目负责人Matt的说法：把各种数据放到一个壶里，然后呢，以一种你希望的格式流出。Kettle包括三大块：Spoon转换/工作(transform/job)设计工具 (GUI方式)Kitchen工作(job)执行器 (命令行方式) Span转换(trasform)执行器 (命令行方式)Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高效稳定。Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。2.2. 下载及安装Kettle1. 下载地址：/projects/pentaho/files2. 本地安装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:kettledata-integration5. 以上步骤完成后直接启动kettle即可Windows直接双击批处理文件 Spoon.bat具体路径为：kettledata-integrationSpoon.batLinux 则是执行spoon.sh,具体路径为：/kettle/data-integration/spoon.sh2.3. Kettle简单例子2.3.1. 启动Kettle点击D:data-integration下面的Spoon.bat，过一会儿，就会出现Kettle的欢迎界面：2.3.2. 创建transformation过程. 配置数据环境在做这个例子之前，我们需要先配置一下数据源，这个例子中，我们用到了三个数据库，分别是：Oracle、MySql、SQLServer，以及一个文本文件。而且都放置在不同的主机上。Oralce：ip地址为03,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地址为07,数据库名为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为15,创建语句为：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,位于03zhang上，即跟Oracle同一个主机。数据仓库：位于07上，跟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);. Spoon界面进入工作空间。. 创建transformation文件（1） 新建transformation文件双击左边转换将创建一个新的transformation(也可以通过菜单里面的文件-新建-转换方式新建文件)，点击另存为，保存到本地路径，例如保存到D:/etltest下，保存文件名为EtltestTrans，kettle默认transformation，文件保存后后缀名为ktr。（2） 创建数据库连接在transformation页面下，点击左边的【主对象树】，双击【DB连接】，进行数据库连接配置。在Connection Name下面的数据库里输入连接的名字，如oracle在Settings里的Host Name里输入主机名，如03在Settings里的Database Name里输入数据库名，如scgtoa在 Settings里的User Name里输入数据库用户名,如bbs在Settings里的Password里输入数据库密码，如bbs然后点击”Test”按钮测试连接是否成功，如果成功后，点击OK按钮。同样地，我们创建MySql和SQLServer的连接，如：最后我们看到这样的界面：. 抽取、转换、装载过程（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按钮，将表字段，与流利的字段进行对应，即流里字段里的值输入到表里的哪个字段里去。. 执行transformation这样，我们的transformation就创建完成了，这时，我们可以点击运行，测试创建的transformation是否成功。点击这个按钮，就会执行ETL过程，这时我们可以查看数据仓库的表，如果有数据，并符合我们的要求，那么说明我们的transformation成功了。2.3.3. 创建job过程Job就是把一个或多个transformation按照一定的顺序组合起来，形成一个流程。. 创建job文件通过菜单里的“文件-新建-作业”就可以创建一个job文件，点击另存为D:etltestjobtest.kjb。. job流程1. 拖入图标并连线在EtlscriptJob页面，点击【核心对象】，点击【通用】，选中【START】拖动到主窗口释放鼠标，再选中一个【Transformation】，拖动到主窗口释放鼠标，建立【START】与【Transformation】之间的连接。2. 编辑”开始”流程双击”START”图标，就进入编辑窗口。我们可以选择这个job是否是重复的，如果是重复的，需要选择重复的类型，在根据类型判断是否需要编辑时间间隔。3. 编辑transformation流程双击”transformation”图标，就进入编辑窗口。其中，我们要输入job名称：如jobOK，点击转换文件名后面的按钮，浏览我们我们要转换的文件，并选中。其他的选项可以根据实际需要再进行配置。点击确定按钮，退出编辑窗口。. 运行job这时一个简单的job流程就建立好了，这时我们点击按钮，就可以运行这个job了。如图：. 什么时候用Job这个job跟上面的转换其实是完成了同样的工作，只不过job可以把多个transforma