1、目录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、.2. 下载及安装 Kettle142.3. Kettle 简单例子 .162.3.1. 启动 Kettle.162.3.2. 创建 transformation 过程 .172.3.3. 创建 job 过程 372.3.4. 命令行运行 ktr 和 kjb .411. ETL 知识1.1. ETL 定义1.1.1. 定义 定义：数据的抽取(Extract)、转换(Transform)、装载(Load)的过程。 目标：数据优化。以最小代价(包括对日常操作的影响和对技能的要求) 将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据 。1.1.2. 前提 确定 ETL范围通过对目标表信
3、息的收集，确定 ETL的范围 选择 ETL工具考虑资金运行的平台、对源和目标的支持程度、可编程的灵活性、对源数据变化的监测、数据处理时间的控制、管理和调度功能、对异常情况的处理 确定解决方案抽取分析、变化数据的捕获、目标表的刷新策略、数据的转换及数据验证1.1.3. 原则 应尽量利用数据中转区对运营数据进行预处理。保证数据的安全性、集成与加载的高效性。 ETL的过程应是主动“拉取” ，而不是从内部“推送” ，其可控性将大为增强。 流程化的配置管理和标准协议 数据质量的保证正确性（Accuracy）：数据是否正确体现在现实或可证实的来源 完整性（Integrity）：数据之间的参照完整性是否存在
4、或一致 一致性（Consistency）：数据是否被一致的定义或理解 完备性（Completeness）：所有需要的数据是否都存在 有效性（Validity）：数据是否在企业定义的可接受的范围之内时效性（Timeliness）：数据在需要的时间是否有效可获取性（Accessibility）：数据是否易于获取、易于理解和易于使用 数据格式错误（如缺失数据、数据值超出范围或数据格式非法等）数据一致性：数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID，在例如有些代码在代码表中找不到等。1.2. 模式及比较 两种模式异构同构
5、模式比较的维度：特点环境1.3. ETL 过程1.3.1. 总流程 数据抽取 数据清洗 数据转换 数据加载1.3.2. 数据抽取流程 数据来源文件系统，业务系统 抽取方式根据具体业务进行全量或增量抽取 抽取效率将数据按一定的规则拆分成几部分进行并行处理 抽取策略根据具体业务制定抽取的时间、频度，以及抽取的流程1.3.3. 数据清洗流程清洗规则： 数据补缺对空数据、缺失数据进行数据补缺操作，无法处理的作标记 数据替换对无效数据进行数据的替换 格式规范化将源数据抽取的数据格式转换成为便于进入仓库处理的目标数据格式 主外键约束通过建立主外键约束，对非法数据进行替换或导出到错误文件重新处理1.3.4.
6、 数据转换流程转换规则 数据合并多用表关联实现，大小表关联用 lookup，大大表相交用 join（每个字段加索引，保证关联查询的效率） 数据拆分按一定规则进行数据拆分 行列互换 排序/修改序号 去除重复记录 数据验证：lookup，sum，count1.3.5. 数据加载流程实现方式 优点 缺点时戳方式 在业务表中统一添加字段作为时戳，当 OLTP系统更新修改业务数据时，同时修改时戳字段值源数据抽取相对简单清楚，速度快，适合数据的增量加载需要修改业务表中的数据结构，业务数据变动时工作量比较大，相对风险较大日志表方式 在 OLTP系统中添加日志表，业务数据发生变化时，更新维护日志表内容不需要修
7、改业务表中的数据结构。源数据抽取简单清楚，速度快，适合数据的增量加载业务系统中更新记录日志操作麻烦全表对比方式 抽取所有源数据，在更新目标表之前先根据主键和字段进行数据比对，有更新的进行 update或insert对系统表结构没有任何影响，管理维护统一，可以实现数据的增量加载数据比对复杂，设计比较复杂，执行速度慢全表删除插入方式 删除目标表数据，将源数据全部插入ETL规则简单，速度快对维表加代理健不适应，OLTP系统有删除数据时，不能在数据仓库体现被删数据，不能实现增量加载1.4. 问题分析1.4.1. 字符集问题1. 字符集定义字符集是字符(包含字母，数字，符号和非打印字符等)以及所指定的内
8、码所组成的特定的集合。是基于某种操作系统平台和某种语言集支持的。语言集的集合被称为语言组，它可能包含一种或多种语言。2. C/S字符集转换 直接转换对于同一语言组的不同字符集之间，可以直接进行字符的转换，不会产生乱码 通过 Unicode转换Unicode支持超过 650种语言的国际字符集 Unicode系统缺省字符集 utf-81.4.2. 缓慢变化维处理1. 缓慢变化维定义在现实世界中，维度的属性并不是静态的，会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维。2. 处理方式 不保留历史数据 保留历史数据起始-结束日期字段标识真/假状态字段标识版本号字段标识代
9、理键字段标识 自增序列 构造算法 保留且分析历史信息添加新的维度列（数据增多，维度列增多）1.4.3. 增量、实时同步的处理 整表匹配同一个库中进行 写触发器客户是否允许创建触发器是否影响数据库性能 读数据库日志Oracle：设定物化视图日志1.4.4. 断点续传 利用源表的索引机制，抽取时按”数据块”顺序抽取 采取 DBLink的机制，结合 oracle自身机制优化效率 生成本地文件块，FTP 传输减少对带宽影响。若中断，流程控制自动回滚加载当前数据块 ETL工具大都支持异常中止后读取断点重新加载的处理 支持对变化数据的捕获 与目标数据库松耦合1.5. ETL 工具目前 ETL工具来源：1.
10、 数据库厂商自带的 ETL工具OWB、ODI：oracle 数据库厂商提供的工具，有局限性，与 oracle数据库耦合太深SSIS：Microsoft SQL Server Integration Services的简称，是生成高性能数据集成解决方案（包括数据仓库的提取、转换和加载 (ETL) 包）的平台。前身是 mssql2000的 DTS第三方数据库源需要采用 ODBC数据转换，效率上有一定折扣若项目以 msssql server数据库为主，采用它肯定是最佳方案。2. 第三方工具提供商Ascential公司的 Datastage：最专业的 ETL工具，价格不菲，使用难度一般Informat
11、ica公司的 Powercenter：专业程度如 Datastage旗鼓相当，价格似乎比 Datastage便宜。IBM SPSS Statistics：IBM SPSS Statistics 产品家族能够帮助企业解决从计划和数据收集到分析、报告和部署的整个分析过程中的问题。它由多个模块集成，您可以根据需求从中选择一个或多个模块来实现您所希望达到的功能。这些模块可单独购买，也可以捆绑购买。3. 开源 ETL工具kettle：业界最有名的开源 ETL工具。开源当然就免费，免费的有些东西使用就不是很方便。2. Kettle 简介及使用2.1. 什么 Kettle？Kettle是一个开源的 ETL（
12、Extract-Transform-Load 的缩写，即数据抽取、转换、装载的过程）项目，项目名很有意思，水壶。按项目负责人 Matt的说法：把各种数据放到一个壶里，然后呢，以一种你希望的格式流出。Kettle 包括三大块：Spoon转换/工作(transform/job)设计工具 (GUI 方式)Kitchen工作(job)执行器 (命令行方式)Span转换(trasform)执行器 (命令行方式)Kettle是一款国外开源的 etl工具，纯 java编写，绿色无需安装，数据抽取高效稳定。Kettle 中有两种脚本文件，transformation 和job，transformation 完
13、成针对数据的基础转换，job 则完成整个工作流的控制。2.2. 下载及安装 Kettle1. 下载地址：http:/ 本地安装 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目录的绝对路径，添加到 Pat
14、h变量的值中，并使用半角的分号和已有的路径进行分隔。变量名：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.sh
15、2.3. Kettle 简单例子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
16、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 log
17、in(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,
18、创建语句为：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
19、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 n
20、ull,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 默认
21、 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然后点击”Te
22、st”按钮测试连接是否成功，如果成功后，点击 OK按钮。同样地，我们创建 MySql和 SQLServer的连接，如：最后我们看到这样的界面：2.3.2.4. 抽取、转换、装载过程（1）表输入在 EtltestTrans页面下，点击左侧的【核心对象】 ，点击【输入】 ，选中【表输入】 ，拖动到主窗口释放鼠标。双击工作区中【表输入】图标，数据库连接选择刚刚创建好的连接名为Oracle的数据库连接，在主窗口写入对应的查询语句：SELECTID, NAME, AGE, ADDRESSFROM USERINFO ORDER BY NAME步骤名称写为：oracle - userInfo 表输入可以通过预览，查看这个步骤输出的数据。同样地，我们建立一个 MySql的表输入组件：查询语句为SELECTid, realname, username, password, activeFROM login ORDER BY realname步骤名称为：mysql - login 表输入（2）表连接查询在 EtltestTrans页面下，点击左侧的【核心对象】 ，点击【连接】 ，选中【Merge Join】 ，拖动到主窗口释放鼠标。如图：然后，点击表输入，按住 shift键，再拖动鼠标到 Merge Join，这时就实现了在两个组建之间的连线。如图：
