hive数据倾斜原因分析及解决方案

hive数据倾斜原因分析及解决方案
hive数据倾斜原因分析及解决方案

hive数据倾斜原因分析及解决方案

1.hive数据倾斜有哪些原因造成的?化过程中,遇到了数

2.数据倾斜可以修改哪些参数?

3.有数据倾斜的时候进行负载均衡,可以通过哪个参数来设置?

在做Shuffle阶段的优化过程中,遇到了数据倾斜的问题,造成了对一些情况下优化效果不明显。主要是因为在Job完成后的所得到的Counters是整个Job的总和,优化是基于这些Counters得出的平均值,而由于数据倾斜的原因造成map处理数据量的差异过大,使得这些平均值能代表的价值降低。Hive的执行是分阶段的,map处理数据量的差异取决于上一个stage的reduce输出,所以如何将数据均匀的分配到各个reduce中,就是解决数据倾斜的根本所在。规避错误来更好的运行比解决错误更高效。在查看了一些资料后,总结如下。

1数据倾斜的原因

2数据倾斜的解决方案

2.1参数调节:

hive.map.aggr = true

Map 端部分聚合,相当于Combiner

hive.groupby.skewindata=true

有数据倾斜的时候进行负载均衡,当选项设定为true,生成的查询计划会有两个MR Job。第一个MR Job 中,Map 的输出结果集合会随机分布到Reduce 中,每个Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的Group By Key 有可能被分发到不同的Reduce 中,从而达到负载均衡的目的;第二个MR Job 再根据预处理的数据结果按照Group By Key 分布到Reduce 中(这个过程可以保证相同的Group By Key 被分布到同一个Reduce 中),最后完成最终的聚合操作。

2.2 SQL语句调节:

如何Join:

关于驱动表的选取,选用join key分布最均匀的表作为驱动表

做好列裁剪和filter操作,以达到两表做join的时候,数据量相对变小的效果。

大小表Join:

使用map join让小的维度表(1000条以下的记录条数)先进内存。在map端完成reduce. 大表Join大表:

把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null 值关联不上,处理后并不影响最终结果。

count distinct大量相同特殊值

count distinct时,将值为空的情况单独处理,如果是计算count distinct,可以不用处理,直接过滤,在最后结果中加1。如果还有其他计算,需要进行group by,可以先将值为空的记录单独处理,再和其他计算结果进行union。

group by维度过小:

采用sum() group by的方式来替换count(distinct)完成计算。

特殊情况特殊处理:

在业务逻辑优化效果的不大情况下,有些时候是可以将倾斜的数据单独拿出来处理。最后

union回去。

3典型的业务场景

3.1空值产生的数据倾斜

场景:如日志中,常会有信息丢失的问题,比如日志中的user_id,如果取其中的user_id

和用户表中的user_id关联,会碰到数据倾斜的问题。

解决方法1:user_id为空的不参与关联

select * from log a join users b on https://www.360docs.net/doc/dd16841330.html,er_id is not null and https://www.360docs.net/doc/dd16841330.html,er_id = https://www.360docs.net/doc/dd16841330.html,er_idunionallselect * from log a https://www.360docs.net/doc/dd16841330.html,er_id is null;

解决方法2 :赋与空值分新的key值

select * from log a left outer join users b on case when https://www.360docs.net/doc/dd16841330.html,er_id is null then concat(‘hive’,rand() ) else https://www.360docs.net/doc/dd16841330.html, = https://www.360docs.net/doc/dd16841330.html,er_id;

结论:方法2比方法1效率更好,不但io少了,而且作业数也少了。解决方法1中log读

取两次,jobs是2。解决方法2 job数是1 。这个优化适合无效id (比如-99 , ’’, null 等) 产

生的倾斜问题。把空值的key 变成一个字符串加上随机数,就能把倾斜的数据分到不同的

reduce上,解决数据倾斜问题。

3.2不同数据类型关联产生数据倾斜

场景:用户表中user_id字段为int,log表中user_id字段既有string类型也有int类型。当

按照user_id进行两个表的Join操作时,默认的Hash操作会按int型的id来进行分配,这

样会导致所有string类型id的记录都分配到一个Reducer中。

解决方法:把数字类型转换成字符串类型

select * from users a left outer join logs b on https://www.360docs.net/doc/dd16841330.html,r_id = cast(https://www.360docs.net/doc/dd16841330.html,er_id as string)

3.3小表不小不大,怎么用map join 解决倾斜问题

使用map join 解决小表(记录数少)关联大表的数据倾斜问题,这个方法使用的频率非常高,

但如果小表很大,大到map join会出现bug或异常,这时就需要特别的处理。以下例子:

select * from log a left outer join users b on https://www.360docs.net/doc/dd16841330.html,er_id = https://www.360docs.net/doc/dd16841330.html,er_id;

users 表有600w+ 的记录,把users 分发到所有的map 上也是个不小的开销,而且

map join 不支持这么大的小表。如果用普通的join,又会碰到数据倾斜的问题。

解决方法:

select /*+mapjoin(x)*/* from log a left outer join ( select /*+mapjoin(c)*/d.* from ( select distinct u from log ) c join users d on https://www.360docs.net/doc/dd16841330.html,er_id = https://www.360docs.net/doc/dd16841330.html,er_id ) x on https://www.360docs.net/doc/dd16841330.html,er_id = https://www.360docs.net/doc/dd16841330.html,er_id;

假如,log里user_id有上百万个,这就又回到原来map join问题。所幸,每日的会员uv

不会太多,有交易的会员不会太多,有点击的会员不会太多,有佣金的会员不会太多等等。

所以这个方法能解决很多场景下的数据倾斜问题。

4总结

使map的输出数据更均匀的分布到reduce中去,是我们的最终目标。由于Hash算法的局限性,按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤:

1、采样log表,哪些user_id比较倾斜,得到一个结果表tmp1。由于对计算框架来说,所有的数据过来,他都是不知道数据分布情况的,所以采样是并不可少的。

2、数据的分布符合社会学统计规则,贫富不均。倾斜的key不会太多,就像一个社会的富人不多,奇特的人不多一样。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。

3、map读入users和log,假如记录来自log,则检查user_id是否在tmp2里,如果是,输出到本地文件a,否则生成的key,value对,假如记录来自member,生成的key,value对,进入reduce阶段。

4、最终把a文件,把Stage3 reduce阶段输出的文件合并起写到hdfs。

如果确认业务需要这样倾斜的逻辑,考虑以下的优化方案:

1、对于join,在判断小表不大于1G的情况下,使用map join

2、对于group by或distinct,设定hive.groupby.skewindata=true

3、尽量使用上述的SQL语句调节进行优化

Kettle多线程导致的抽取问题解决

今天在测试kettle一个转换的问题,据现场描述,在手工执行该job转换时,获取到的时间是正确的,但是一旦使用批处理计划任务调用执行时,获取到的时间就有问题,转换如下: '查找数据库当前时间' 这个步骤的数据库连接是’AA‘,这一步骤的动作是查找当前系统时间 select sysdate as cksj from dual ’插入/更新‘的数据库连接是’BB‘,这一步骤的动作是把上一步骤得到的时间,更新到表tt的bcgxsj字段中 ’删除'的数据库连接是‘BB’,这一步骤的动作是删除表t1中CQZT='1'的数据 ‘获取更新时间’的数据库连接是'BB',这一步骤是获取步骤‘插入/更新’中更新表tt的bcgxsj字段后的值 现在出现的问题就是,在单独执行时,发现‘获取更新时间’步骤中,获取到的时间,确实是‘插入/更新’步骤执行后的值,

但是,如果通过bat调用,在计划任务中执行时,发现‘获取更新时间’步骤中,获取到的bcgxsj就不对了,查看日志,发现获取到的时间,是更新前的时间。 猜想,应该是kettle多线程的问题。再仔细看日志,发现执行的步骤好像有点不对 发现‘获取更新时间’这一步骤根本不是在’插入/更新‘后执行的,而是一开始就已经连接数据库查询,这样肯定出来的结果不可能是更新后的当前系统时间 这步骤甚至比’查找数据库当前时间‘更早执行。 网上查找原因: 在多线程软件下处理数据库连接,最推荐的方法是在转换执行的过程中为每个线程创建单一的连接。因此,每个步骤复制都打开它们自己单独的事务或者事务集。 这将导致一个潜在的后果,就是你在使用同一个数据库资源的场景下,例如一张数据表或者视图,条件竞争在同一个转换中可能而且会经常发生。 一个常见产生错误的场景,就是当你往一个关系数据表里面写入数据,在随后的步骤里面读回。因为这两个步骤运行在不同的数据库连接下,而且拥有不同的事务上下文,你不能确保这个被第一个步骤写入的数据将可见于其他正在执行读操作的步骤。

ETL之kettle进行二次开发简单demo

demo,利用kettle的api,将一个数据源中的信息导入到另外一个数据源中:[java]view plain copy 1.package https://www.360docs.net/doc/dd16841330.html,.saidi.job; 2. 3.import https://www.360docs.net/doc/dd16841330.html,mons.io.FileUtils; 4.import org.pentaho.di.core.KettleEnvironment; 5.import org.pentaho.di.core.database.DatabaseMeta; 6.import org.pentaho.di.core.exception.KettleDatabaseException; 7.import org.pentaho.di.core.exception.KettleXMLException; 8.import org.pentaho.di.core.plugins.PluginRegistry; 9.import org.pentaho.di.core.plugins.StepPluginType; 10.import org.pentaho.di.trans.TransHopMeta; 11.import org.pentaho.di.trans.TransMeta; 12.import org.pentaho.di.trans.step.StepMeta; 13.import org.pentaho.di.trans.steps.insertupdate.InsertUpdateMeta; 14.import org.pentaho.di.trans.steps.tableinput.TableInputMeta; 15. 16.import java.io.File; 17. 18./** 19. * Created by 戴桥冰 on 2017/1/16. 20. */ 21.public class TransDemo { 22. 23.public static TransDemo transDemo; 24. 25./** 26. * 两个库中的表名 27. */ 28.public static String bjdt_tablename = "test1"; 29.public static String kettle_tablename = "test2"; 30. 31./** 32. * 数据库连接信息,适用于DatabaseMeta其中一个构造器 DatabaseMeta(String xml) 33. */ 34.public static final String[] databasesXML = { 35. 36."" + 37."" + 38."bjdt" + 39."192.168.1.122" + 40."Mysql" +

kettle 使用中的一些常见问题

kettle 使用中的一些常见问题 问题1: 从excel 中抽取数据,插入到oracle 9 数据库中,报下面的错误. 2008/06/25 13:30:57 - 插入更新数据表.0 - Insert row: ![field1=1.3965E8, field2=1,798, field3=2002/05/27 00:00:00.000] 2008/06/25 13:30:57 - oracl - ERROR : java.sql.SQLException: ORA-01722: 无效数字2008/06/25 13:30:57 - oracl - ERROR : at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) 原因: 从错误信息可以看出,字段"field2" 对应的数据是1,798,该字段在数据库中是Number 类型,Oracle 不能将1,798 格式的字符串转换为数字。 解决方法: 在【Excel输入步骤】-【字段】标签下对应的字段设置为正确的类型。将"field2" 设置为"Number" 类型(默认是String 类型)。 问题2: 由sqlserver 向mysql 迁移数据, 迁移以后的数据是乱码 原因: 这是由于mysql 的默认编码是utf-8, 而中文环境下客户端的默认编码一般都是gbk 或gb18030, 由于客户端和服务器编码不一样导致保存到mysql 的数据是乱码 解决方法: 在新建连接窗口中有一个选项(option) 标签, 在这个标签下可以设置数据库的一些连接参数. 在这里我们要设置mysql 的客户端编码参数, 参数名characterEncoding, 参数值gbk. 问题3: 在SQLServer 表输入步骤中使用以问号作为参数的sql 语句(参数的值从以前的步骤中获得),向mysql 数据库导入数据(使用mysql 表输出步骤). 在执行的时候报告错误Unable to get queryfields for SQL: AND e.LOCAL_TIME>? 00909 PRS INC Invalid character. 原因: 从错误语句看,是参数没有被替换掉, 因为发送给mysql 服务器的是这样的语句"AND e.LOCAL_TIME>?". 参数没有被替换掉是因为从以前步骤中读取的参数值是null. 解决方法: 增加对null 值的判断, 可以有两种方法, 如果这个值是从数据库获取的,一般的数据库都提供了处理null值的函数,如mysql 的ISNULL 函数. 如果这个值是从其他步骤获得的,可以通过Javascript 步骤进行判断并转换. 问题4: 使用PDI 3.x 版本连接DB2 数据库时会报下面的错误 Error connecting to database: (using class com.ibm.db2.jcc.DB2Driver) encoding not supported!!

Pentaho ETL工具Kettle转换实现原理

关于Pentaho ETL工具Kettle转换实现原理的研究·ETL和Kettle简介 ETL即数据抽取(Extract)、转换(Transform)、装载(Load)的过程。它是构建数据仓库的重要环节。数据仓库是面向主题的、集成的、稳定的且随时间不断变化的数据集合,用以支持经营管理中的决策制定过程。 Kettle 是”Kettle E.T.T.L. Envirnonment”只取首字母的缩写,这意味着它被设计用来帮助你实现你的ETTL 需要:抽取、转换、装入和加载数据;翻译成中文名称应该叫水壶,名字的起源正如该项目的主程序员MATT 在一个论坛里说的哪样:希望把各种数据放到一个壶里然后以一种指定的格式流出。 Kettle的四大块: Chef——工作(job)设计工具(GUI方式) Kitchen——工作(job)执行器(命令行方式) Spoon——转换(transform)设计工具(GUI方式) Pan——转换(trasform)执行器(命令行方式) Spoon 是一个图形用户界面,它允许你运行转换或者任务,其中转换是用Pan 工具来运行,任务是用Kitchen 来运行。Pan 是一个数据转换引擎,它可以执行很多功能,例如:从不同的数据源读取、操作和写入数据。Kitchen 是一个可以运行利用XML 或数据资源库描述的任务。通常任务是在规定的时间间隔内用批处理的模式自动运行。下面将具体介绍Pan,和Kitchen在ETL中的实现过程。 ·数据转换原理及具体实现过程 大致步骤如下:

转换的过程中(在windows环境下),首先会调用Pan.bat,这一步主要是做一些初始化,连接验证,环境设置,检查之类;然后在Pan的最后一步会调用launcher.jar包,这个包用于启动JDBC驱动,并向JDBC传入相关连接信息和参数,然后开始传数据,最后是完成数据传输,关闭相关协议,写入日志。 首先来看Kitchen的实现,Kitchen在一个job中一般包含以下几个步骤: 转换:指定更细的转换任务,通过Spoon生成。通过Field来输入参数; SQL:sql语句执行; FTP:下载ftp文件; 邮件:发送邮件; 检查表是否存在; 检查文件是否存在; 执行shell脚本:如dos命令。 批处理:(注意:windows批处理不能有输出到控制台)。 Job包:作为嵌套作业使用。 SFTP:安全的Ftp协议传输; HTTP方式的上/下传。 以下是一个简单的ETL过程:

kettle转换步骤的类型功能

文本文件输入(text input):读取大量不同的文本文件。大多是通过工具生成的CSV文件。 表输入(table Input):常用来利用连接和SQL,从数据中读取信息,自动生成基本的SQL语句。 获取系统信息(get system info):这个步骤从Kettle 环境中获取信息。 生成行(Generate Rows ):这个步骤输出一定数量的行,缺省为空。可选包括一定数量的静态字段。 Cube输入(文件反序列化)(De-serialize from file):从二进制Kettle Cube 文件中读取数据行。备注:这个步骤仅仅用来存储短期数据。不同版本之间不保证文件的格式一样。 XBase输入:使用这一步可以读取大多数被称为XBase family派生的DBF文件。 Excel输入:利用这个步骤可以从Kettle 支持的系统的Excel文件里面读取数据。 XML输入:这个步骤允许你读取存储在XML 文件中的数据。它也提供一个接口,你可以定义你想读取的文件名、XML 文件的数据重复部分、获取的字段等。你可以指定元素或属性字段。 获取文件名(Get File Names):这个步骤可以获取系统的文件名信息。 文本文件输出(Text File Output): 表输出(Table output):这个步骤可以存储信息到数据库表中。 插入/更新(Insert/Update):这个步骤利用查询关键字在表中搜索行。如果行没有找到,就插入行。如果能被找到,并且要被更新的字段没有任何改变,就什么也不做。如果有不同,行就会被更新。 更新(Update ):这个步骤类似于插入/更新步骤,除了对数据表不作插入操作之外。它仅仅执行更新操作。 删除(Delete):这个步骤类似于上一步,除了不更新操作。所有的行均被删除。 Cube output(序列化到文件)(Serialize to file):这一步骤存储数据到一个二进制文件。这个步骤有个优势就是回读的时候,文本文件的内容不需要解析。这是因为元数据也同时存储在CUBE 文件里面。 XML输出:这个步骤允许你从源中写入行到一个或者多个XML 文件。 EXCEL输出:利用这个步骤,在Kettle 支持的系统中,你可以写入数据到一个或者多个

开源ETL工具kettle系列之动态转换

摘要:本文主要讨论使用Kettle来设计一些较为复杂和动态的转换可能使用到的一些技巧,这些技巧可能会让你在使用Kettle的时候更加容易的设计更强大的ETL任务。 动态参数的传递 Kettle 在处理运行时输入参数可以使用JavaScript 来实现,大部分工作只是按照一个模板来处理的 动态参数传递主要使用在像数据清理,调式,测试,完成复杂的条件过滤等等,这种方式一般不会在产品已经运行稳定了一段时间之后使用,因为我们一般仍然是做定时任务来自动转换数据,所以在开始介绍如何使用动态参数之前,希望大家能明白不要在产品数据库上做实验,即使你已经知道你的转换有什么影响并且做了备份,因为这种方法是不可能自动执行的。 Kettle有两种动态参数传递的方法,一种是非常轻量级的传argument , 另一种是对付较复杂一点情况使用JavaScript . 下面分别介绍这两种方法。 1. argument 当你在运行一个转换的时候,不管这个转换是一个Job的一部分还是只有这个转换,你都可以传递参数给它,当你运行一个转换的时候,会弹出一个Execution a Transformation 的对话框,让你选择执行转换的方式,本地执行,远程执行,分布式执行,下面就是日志记录的级别和回放时间,然后是argument 和variables 的设定。Argument 和variables 的区别在官方FAQ里面也有解释。你也可以参考一下官方的解释和下面解释的异同。 Q : Argument 和variables 的区别/ A : variables 也可以认为叫做environment variables , 就像它的名字一样,主要是用来设定环境变量的,比如最常见的:文件的存放地址,smtp的配置等等,你也可以把它认为是编程语言里面的全局变量,即使是不同的转换它们也拥有同样的值,而argument 自然就类似与局部变量,只针对一个特定的转换,比如像是限定结果集的大小和过滤条件。 取得argument的值 我们在转换之前设置了argument的值,需要用到的时候就使用get system info 步骤,这个步骤取得在运行时参数,需要注意的是我们是先设置get system info ,然后在里面决定要使用多少个参数,最多10个,每个参数名叫什么,然后我们才能在运行时看到你设置了的参数名后面跟一个要你输入的值,并且参数类型是不能够指定,全部都当作字符串处理,如果你需要对参数类型有要求,你需要自己转换,使用一个Mapping步骤或者Select values步骤。 取得variable的值 Variable 的值个数不受限制,你可以在kettle菜单的set environment里面设置,也可以使用文件储存这些值,在第一次运行kettle之后,kettle会在%HOME_USER_FOLDER%菜单里面创建一个 .kettle文件夹,如果是windows 用户可能就是C:\Documents and Settings\${your user name}\.kettle这个文件夹,如果是linux用户可能就是/home/${your user name }/.kettle文件夹,这个文件夹下面有kettle.properties文件,如果你打开这个文件,你会发现里面有一些以#开头的注释,其中设置了一些像是:PRODUCTION_SERVER = Hercules 这样的键值对,你可以自己定义一些环境变量比如像是smtp的地址,ftp服务器的地址,你放log文件的目录名等等,当然不能直接编辑这个文件就设置环境变量,要先设置KETTLE_HOME环境变量,windows就是点我的电脑,然后在设置path的那个地方添加一个KETTLE_HOME变量,linux就是export KETTLE_HOME=’一个目录’,这个目录可以任意地方,不过一般还是指向kettle的安装目录或是你自己的文档目录,然后启动kettle它会创建一个新的.kettle目录,编辑里面的kettle.properties文件就可以设置环境变量了. 2. 使用脚本 Kettle使用的是JavaScript来作为它的脚本实现,使用的是mozilla 的rhino 1.5r5版本实现,如果你打算

kettle增量更新设计

kettle增量更新设计 ETL中增量更新是一个比较依赖与工具和设计方法的过程, Kettle中主要提供sert/ Update步骤, Delete步骤和 Database Lookup步骤来支持增量更新增量更新的设计方法也是根据应用场景来选取的,虽然本文讨论的是 Kettle的实现方式,但也许对其他工具也有一些帮助。 1、增量分类 应用场景增量更新按照数据种类的不同大概可以分成: 1、增加,不更新 2、更新,不增加 3、即增加也更新 4、有删除,有增加,有更新 其中1,2,3种大概都是相同的思路,使用的步骤可能略有不同,通用的方法是在原数据库增加一个时间戳,然后在转换之后的对应表保留这个时间戳,然后每次抽取数据的时候,先读取这个目标数据库表的时间戳的最大值,把这个值当作参数传给原数据库的相应表,根据这个时间戳来做限定条件来抽取数据,抽取之后同样要保留这个时间戳,并且原数据库的时间戳一定是指定默认值为date当前(以原数据库的时间为标准),抽取之后的目标数据库的时戬要保留原来的时间戳,而不是抽取时候的时间。 对于第一种情况,可以使用 Kettle的 Insert/ Update步骤,只是可以勾选Don' t perform any update选项,这个选项可以告诉 Kettle你只会执行 Insert步骤。 对于第二种情況可能比较用在数据出现错误然后原数据库有一些更新,相应的标数据库也要更新,这时可能不是更新所有的数据,而是有一些限定条件的数排你可以使用 Kettle 的 Update步骤来只执行更新。关于如何动态的执行限定条件,可以参考前一篇文章。 第三种情况是最为常见的一种情況,使用的同样是 Kettle的 Insert/ Update步骤,只是不要勾选Don' t perform any update选项第四种情況有些复杂,后面专门讨论:对于第1,2,3种个以参考下面的例子这个例子假设原数据库表为 customers,含有一个id, firstname,1 astnameage字段,主键为id,然后还加默认值为 sysdate的时间戳字段。转换之后的结果类似:id, firstname,1 astname,age, updatedate.整个设计流程大概如: 其中第一个步骤的sql 大概如下模式: Select max(updatedate) from target_customer ; 你会注意到第二个步骤和第一个步骤的连接是黄色的线,这是因为第二个table input 步骤把前面一个步骤的输出当作一个参数来用,所有Kettle用黄色的线来表示,第二个table input 的sql 模式大概如下: Select field1 , field2 , field3 from customers where updatedate > ? 后面的一个问号就是表示它需要接受一个参数,你在这个table

kettle之多表数据复制

kettle之------ 多表数据复制 需求:把A库中N个表抽取至B库中,表结构相同或只是增加统一的一些字段,过滤条件基本类似。 解决方法: 我们把以上内容在解决问题上拆分为二步, 1,从一配制表中读出相应的表名及过滤条件,存于一结果。 2,根据第一步的结果,一条条循环来进行取数插数的过程。 以上过程相当简单,以下为讨论的实现方式。 1,如果是ORACLE同字符集,用DBLINK结合着存储过程,写一个通用的程序很容易搞定,只需要建立一个表名及过滤条件的配制表(在此就不多说了)。 2,用ETL工具实现,据我所知,以infa为例,它以字段为对应关系,且它的流程内部循环相当困难,曾经我们有个项目,infa就是硬生生的画了一二百个mapping(的确,不会累死人,但看着相当不爽,当然,欢迎提供好的方案), 在此,我们以kettle为例进行分析,如何解决这个问题(先抛开性能,kettle的确是个好东西) 1),主流程大概是如下这样的 在此,我们再看一下第一组件(得到多表表名)里面内容, 我们再看一下得到表名中的内容

这其中的sql只是模拟一个得到表名的sql,可灵活使用,这个组件其实就完成了把要抽取的表放入一个结果集中了。 我们再看第二个组件(表迁移) 就是一个取数一个插数的过程。 再看一下它们的内容 在此就完成了,但是,我们主job设置(表迁移)组件时,也就是把前面的内容当后面的参数循环执行。

基本就是如此,另,记得在各主流程及二转换中都设置tt_tab这个参数。 说了这么多,其实很简单。如果你是要做数据库表复制的话,kettle有其它的方式,可以根据工具-》向导-》复制表向导完成。 以上这种适应的是,可实现一些sql的拼接,且要增加减少表时只需求维护配制表了,相对来说会变的简单。。 kettle交流群87340451

kettle中通过 时间戳(timestamp)方式 来实现数据库的增量同步操作(一)

kettle中通过时间戳(timestamp)方式来实现数据库的增 量同步操作(一) 这个实验主要思想是在创建数据库表的时候, 通过增加一个额外的字段,也就是时间戳字段, 例如在同步表tt1 和表tt2 的时候, 通过检查那个表是最新更新的,那个表就作为新表,而另外的表最为旧表被新表中的数据进行更新。 实验数据如下: mysql database 5.1 test.tt1( id int primary key , name varchar(50) ); mysql.tt2( id int primary key, name varchar(50) ); 快照表,可以将其存放在test数据库中, 同样可以为了简便,可以将其创建为temporary 表类型。数据如图kettle-1 kettle-1 ============================================== ==============

主流程如图kettle-2 kettle-2 在prepare中,向tt1,tt2 表中增加时间戳字段, 由于tt1,tt2所在的数据库是不同的,所以分别创建两个数据库的连接。 prepare kettle-3 在执行这个job之后,就会在数据库查询的时候看到下面的字段: kettle-4 然后,我们来对tt1表做一个insert 操作一个update操作吧~ kettle-5 在原表上无论是insert操作还是update操作,对应的updateTime都会发生变更。

如果tt1 表和tt2 表中updateTime 字段为最新时间的话,则说明该表是新表。 下面只要是对应main_thread的截图: kettle-6 在这里介绍一下Main的层次: Main START Main.prepare Main.main_thread { START main_thread.create_tempTable main_thread.insert_tempTable main_thread.tt1_tt2_syn SUCCESS } Main.finish SUCCESS

kettle使用总结

Kettle使用笔记 一、基本概念: 1.1、资源库 保存kettle脚本或转换、存放数据库连接的地方,可以建立多个数据库连接,使用时就无需每次重复建立 1.2、数据库连接(db links) 连接数据库的功能,需处理数据库中的数据时创建,可连接Oracle、SqlServer、MySQL、DB2等 1.3、转换(trans) 处理数据的ETL过程,里面存放许多处理数据的组件,完成后保存会生成一个ktl文件。 1.4、作业(job) 自动、定时执行转换的步骤的名称,可以在自动执行转换的过程添加参数进行控制。 1.5、步骤(steps) 转换和作用的每个操作都是一个步骤。 二、工具栏: 2.1、资源库概念 保存kettle脚本或转换的地方,相当于myeclipes的workspace,另一种保存kettle脚本或流程的方法是需要保存的时候用文件保存,点击另存为出现 (PS:打开kettle的时候加载的也是资源库)

2.2、资源库位置 Tools -> 数据库-> 连接数据库 2.3、数据库连接 创建数据库连接的时候会同时创建数据库连接? 右键点击新建转换-> 点击主对象树-> 右键DB连接->新建数据库连接 (PS:创建数据库后可以点击Test 判断数据库连接创建成功了没!,Oracle RAC 环境下的数据库连接创建数据库连接的方法不同) 三、基本操作: 3.1、轮流发送模式和复制发送模式的区别 如果获取的数据必须同时进行多步处理(一种方式是将数据复制后处理,一种是获取的数据进行轮流的间隔处理),设置方式为:选中Data Grid –> 点击右键–> 数据发送-> 选择轮流发送模式或复制发送模式 下图为复制处理的方式:红框选中的标签为复制处理 3.2、分离步骤的方法 比如下图中分离出步骤”删除”的方法:选中”删除”->点击右键-> 点击分离步骤

ETL工具Kettle用户手册及Kettle5.x使用步骤带案例超详细版

ETL工具Kettle用户手册 之 Spoon 2.5.0用户手册 Spoon 2.5.0用户手册 (1) 1、Spoon介绍 (14) 1、1 什么是Spoon (14) 1、2 安装 (14) 1、3 运行Spoon (14) 1、4 资源库 (14) 1、5 资源库自动登录 (15) 1、6 定义 (15) 1、6、1 转换 (15) 1、6、2 任务 (16) 1、7 工具栏 (17) 1、8 选项 (17) 1、8、1 General标签 (18) 1、8、2 Look Feel标签 (19) 1、9 搜索元数据 (19) 1、10设置环境变量 (20) 2、创建一个转换或任务(Creating a Transformation or Job) (21) 3、数据库连接(Database Connections) (21) 3、1 描述 (21) 3、2 设置窗口 (22) 3、3 选项 (22) 3、4 数据库用法 (22) 4、SQL编辑器(SQL Editor) (23) 4、1 描述 (23) 4、2 屏幕截图 (23) 4、3 局限性 (24) 5、数据库浏览器(Database Explorer) (24) 5、1 屏幕截图 (24) 5、2 描述 (24) 6、节点连接(Hops) (25) 6、1 描述 (25) 6、1、1 转换连接 (25)

6、2 屏幕截图 (25) 6、3 创建一个连接 (26) 6、4 拆分一个连接 (26) 6、5 转换连接颜色 (26) 7、变量(Variables) (27) 7、1 变量使用 (27) 7、2 变量范围 (27) 7、2、1 环境变量 (27) 7、2、2 Kettle变量 (27) 7、2、3 内部变量 (27) 8、转换设置(Transformation Settings) (28) 8、1 描述 (28) 8、2 屏幕截图 (29) 8、3 选项 (32) 8、4 其它 (33) 9、转换步骤(Transformation steps) (33) 9、1 描述 (33) 9、2 运行步骤的多个副本 (33) 9、3 分发或者复制 (35) 9、4 常用错误处理 (35) 9、5 Apache虚拟文件系统(VFS)支持 (37) 9、6 转换步骤类型 (39) 9、6、1 文本文件输入(Text Input) (39) 9、6、1、1 屏幕截图 (39) 9、6、1、2 图标 (41) 9、6、1、3 常用描述 (42) 9、6、1、4 选项 (42) 9、6、1、5 格式化 (47) 9、6、1、6 其它 (48) 9、6、2 表输入(Table Input) (49) 9、6、2、1 屏幕截图 (49) 9、6、2、2 图标 (49) 9、6、2、3 常用描述 (49) 9、6、2、4 选项 (50) 9、6、2、5 示例 (50) 9、6、2、6 其它 (51) 9、6、3 获取系统信息(Get System Info) (51) 9、6、3、1 屏幕截图 (51) 9、6、3、2 图标 (52) 9、6、3、3 常用描述 (52) 9、6、3、4 选项 (53) 9、6、3、5 用法 (53) 9、6、4 生成行(Generate Rows) (54)

Kettle插表操作说明文档V11.12

kettle插表操作说明 1.配置Java环境变量 安装JDK后,然后配置Java环境变量。 新建系统变量 变量名:JAVA_HOME 变量值:C:\Program Files\Java\jdk1.5.0_17(该目录为JDK安装的目录) 编辑CLASSPATH,如果没有该变量则新建 变量名:CLASSPATH 变量值:%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\tools.jar; 编辑PATH,如果没有则新建 变量名:PATH 变量值:.;%JAVA_HOME%\bin; (注:该变量值置于PATH前面,如果你只有一个JDK 版本,可以不用编辑PATH,如果为了区别版本,那还是配置PATH变量值)。 2.编写Query Query分为Query的定义、Execute、Fetch、Close四部分,其中定义和Execute部分需要根据具体需要编写和拟定,Fetch和Close部分为固定结构,只需要和Query的定义对应即可。[详见Query示例.txt,其中以取科室字典信息为例] 3.Spoon工具的使用 3.1 建资源库 资源库可以看做是Kettle使用和运行的环境和缓存库,Kettle的资源库可以用数据库来充当,也可以用文件夹来充当,我们这里推荐使用后者。 所以,下图中,选择第二个类型的资源库进行新建。当然,需要首先先新建个文件夹来供其使用。

浏览选定相应的文件夹即可,ID和名称可以自定义。 3.2 新建转换

3.3在转换中新建数据库连接 并测试数据库连接,保证连接正确。 下一步,共享这个转换里面的数据库连接,保证其他以后新建的转换也可以共用。

Kettle数据集成实验指导书

Kettle数据集成 本示例演示如何使用Kettle实现数据ETL过程,示例的源数据是一个记录销售信息的CSV文件,数据经过处理,生成一个客户邮件地址列表,然后,导入到一个数据库中。为了演示数据处理过程,数据记录中一些客户信息缺少邮编。在导入数据库之前,补充缺失信息。整个过程逻辑图如图 0-1: 图 0-1流程示意图 1.1从CSV文档中抽取数据(Extraction) 这个步骤是从一个CSV格式的文档中录入销售数据,一般的,Kettle转换都是从数据抽取开始的。详细配置如下: (1)单击菜单栏“文件->新建->转换”创建新的转换。另外,双击“主对象树”分页的“转换”,同样可以创建一个新的转换,或者使用快捷键“Ctrl+N”。 (2)选择“核心对象”分页,展开“输入”节点,选择并拖曳“文本文件输入”到右面的设计面板上面。 (3)双击“文本文件输入”步骤,出现“文本文件输入”步骤的编辑属性对话框,如图 0-2。通过设置对话框显示的选项,到对数据的输入进行控制。

图 0-2属性对话框 (4)在“步骤名”文本框中,输入“销售数据读取”。将步骤名更改为“销售数据读取”。 (5)单击“浏览”按钮定位到数据源文件sales_data.csv,例如:文件在E:\Kettle\sales_data.csv路径下面。定位到文件夹E:\Kettle\,选择文件sales_data.csv,单击“打开”按钮。 (6)单击“增加”按钮,添加文件路径到选中的文件列表中。单击“显示文件内容”按钮。可以查看文件内容的详细格式,使用了什么分隔符,是否有行首(列标)。例如:文件使用了逗号(,)作为分隔符,使用引号(“)作为文本限定符,以及包含一行标题。 (7)单击“文本文件输入”的“内容”分页,“内容”分页用来设置输入数据文件的格式。 (8)在“分隔符”文本框中,输入“,”(中英文逗号不同)。在“文本限定符”文本框中,输入“"”,因为文件“sales_data.csv”中有行首,选择“头部”,在“头部行数量”文本框中,输入“1”。如图 0-3所示。 图 0-3内容属性页 (9)单击“字段”分页,单击“获得字段”,从数据文件中读取字段。此时会弹出一个对话框,要求指定要扫描数据的行数,可以设置任意值,“0”表示扫描整个文件,网格中的每一行都允许定义字段的属性,例如,格式,长度和精度,是否允许有重复行出现。单击“确定”按钮,将显示定义格式下输入数据的汇总信息。通过扫描可以检查输入的数据是否正确,从而减少转换运行时错误。单击“取消”,不扫描数据文件。扫描完成后,单击“关闭”按钮,回到属性设置对话框。 (10)在“字段”分页下,找到“SALES”字段,“SALES”的字段类型显示为

kettle实现表同步

同步数据常见的应用场景包括以下4个种类型: ?只增加、无更新、无删除 ?只更新、无增加、无删除 ?增加+更新、无删除 ?增加+更新+删除 1只增加、无更新、无删除 对于这种只增加数据的情况,可细分为以下2种类型: 1) 基表存在更新字段。 通过获取目标表上最大的更新时间或最大ID,在“表输入”步骤中加入条件限制只读取新增的数据。 这里要注意的是,获取最大更新时间或最大ID时,如果目标表还没有数据,最大值会获取不了。其中的一个解决方法是在“获取最大ID”步骤的SQL中,加入最小日期或ID的联合查询即可,如:SELECT MAX(ID) FROM (SELECT MAX(ID) AS ID FROM T1 UNION ALL SELECT 0 AS ID FROM DUAL) 2) 基表不存在更新字段。 通过“插入/更新”步骤进行插入。 插入/更新步骤选项:

2只更新、无增加、无删除通过“更新”步骤进行更新。

更新选项: 3增加+更新、无删除通过“插入/更新”步骤进行插入。

区别是“插入/更新步骤”中的选项,去掉“不执行任何更新”的勾选:

4增加+更新+删除 这种数据同步情况,可细分为以下2种情况: 1) 源库有表保存删除、更新和新增的信息。 通过条件判断,分别进行“插入/更新”和“删除”即可,如下图所示。 2) 源库没有保存增删改信息 Kettle提供了一种对比增量更新的机制处理这种情况,可通过“合并记录”步骤实现,该步骤的输入是新旧两个数据源,通过关键字进行数据值比对,对比结果分为以下4种类型: “Identical” : 关键字在新旧数据源中都存在,域值相同 “changed” : 关键字在新旧数据源中都存在,但域值不同 “new” :旧数据源中没有找到关键字 “deleted”:新数据源中没有找到关键字 两个数据源的数据都进入下一步骤,上述4种结果类型作为输出表的标志字段进行保存。 以下为示例: ?源数据库测试脚本 create table k1 (f1 varchar2(200),f2varchar2(200)) truncate table k1; insert into k1(f1,f2) values('1','11'); insert into k1(f1,f2) values('2','22'); insert into k1(f1,f2) values('5','5'); commit; ?目标数据库测试脚本 create table k1 (f1 varchar2(200),f2varchar2(200)) truncate table k1; insert into k1(f1,f2) values('1','1'); insert into k1(f1,f2) values('2','2'); insert into k1(f1,f2) values('3','3'); insert into k1(f1,f2) values('4','4'); commit; 合并过程如下:

Kettle高级应用

在昨天的帖子里面,我介绍了一些ETL初级的东西,不知道大家是否已经掌握,我现在介绍一些Kettle应用中,比较有帮助的一些地方。 1,Kettle跨平台使用。 例如:在AIX下(AIX是IBM商用UNIX操作系统,此处在LINUX/UNIX同样适用),运行Kettle的相关步骤如下: 1)进入到Kettle部署的路径 2)执行 chmod *.sh,将所有shell文件添加可执行权限 3)在Kettle路径下,如果要执行transformation,就运行./pan.sh -file=?.ktr -debug=debug -log=log.log 其中。-file说明你要运行的transformation文件所在的路径;-debug说明日志输出的级别;-log说明日志输出的路径 4)同理,对于job的执行,请将./pan.sh更换成./kitchen.sh,其他部分说明不变。 2,Kettle环境变量使用。 在transformation中,Core Objects-->Job-->Set Variables,可疑设置环境变量,对于绝对路径和相对路径的转换很有帮助,Kettle的跨平台很大程度依靠他的 3,其它功能的使用。 其它功能包括DB存储过程调用,流查询,值映射,聚合记录等,各位自行摸索,有问题可以和我联系:) 4,Kettle定时功能。 在Job下的start模块,有一个定时功能,可以每日,每周等方式进行定时,对于周期性的ETL,很有帮助。 5,Kettle经验之日志。 Kettle对于日志的处理,存在一个BUG,看过上一篇的人或许已经看到了我的留言,Kettle对于日志处理有一个BUG,当日志多于49M(不是50M,也不是49M),Kettle就会自动停止,这一点我在源码里面也没有找到对应的设置和约束,原因还找不到,因为是日志没有写,所以原因也不好跟踪还不知道具体原因。 6,Kettle之效率提升。 Kettle作为一款ETL工具,肯定无法避免遇到效率问题,当很大的数据源输入的时候,就会遇到效率的问题。对此有几个解决办法: 1)数据库端创建索引。对需要进行查询的数据库端字段,创建索引,可以在很大程度上提升查询的效率,最多的时候,我不创建索引,一秒钟平均查询4条记录,创建索引之后,一秒钟查询1300条记录。 2)数据库查询和流查询注意使用环境。因为数据库查询为数据输入端输入一条记录,就对目标表进行一次查询,而流查询则是将目标表读取到内存中,数据输入端输入数据时,对内从进行查询,所以,当输入端为大数据量,而被查询表数据量较小(几百条记录),则可以使用流查询,毕竟将目标表读到内存中,查询的速度会有非常大的提升(内存的读写速度是硬盘的几百倍,再加上数据库自身条件的制约,速度影响会更大)。同理,对于目标表是大数据量,还是建议使用数据库查询,不然的话,一下子几百M的内存被干进去了,还是很恐怖的。 3)谨慎使用javascript脚本,因为javascript本身效率就不高,当你使用js的时候,就要考虑你每一条记录,就要执行一次js所需要的时间了。

相关文档
最新文档