Kettle学习笔记-数据抽取性能优化!
Kettle学习笔记-数据抽取性能优化!
近期工作中,对kettle及各种数据库做了一些学习,背景是数据库到数据库的抽取。然后,做了一些调优,数据抽取速度从500条/秒,到4000条/秒,到12000条/秒,最后到30000条/秒不断提升,于是把经验记录下来。
ETL(Extract-Transform-Load),是将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。所以,这里分别从extract、transform、load三个方面,讲述如何变得更快的故事。
其实,若非性能问题,尽量用Kettle的控件。因为:
Kettle中,对数据的逻辑、异常、log机制非常完善,对数据进行全面的跟踪,排错方便。
Kettle控件的可读性和可扩展性非常强。
此外,任何数据库,在优化的时候,多使用“执行计划”去查看哪个写法最优。
1.Extract
每一种数据库都有各自的特性,在不同数据源抽取数据,要利用其特性进行调优。
(1)ORACLE数据库
ORACLE 有2种优化器:RBO,CBO。ORACLE 11g引进自动sql调优(Automatic SQL Tuning),比先前的9i,10g做了很多优化。至于具体的调优方式,在我的另外一篇学习笔记《Oracle笔记04-SQL调优实战经验》中有详细讲述。
(2)MySQL数据库
我收集到如下的优化理论:
●字段优化
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME,
单表不要有太多字段,建议在20以内
避免使用NULL字段,很难查询优化且占用额外索引空间
用整型来存IP
●索引优化
索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引,例如”性别”这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键,由程序保证约束
尽量不用UNIQUE,由程序保证约束
使用多列索引时注意顺序和查询条件保持一致,同时删除不必要的单列索引
●查询SQL优化
可通过开启慢查询日志来找出较慢的SQL
不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边
sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库
不用SELECT *
OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内
不用函数和触发器,在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较,比如用'123'和'123'比,123和123比
尽量避免在WHERE子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大
(3)Greenplum数据库
Greenplum是分布式数据库,内核是PostgreSQL SQL。查询语法和ORACLE差不多,但因为是分布式数据库,所以要注意分布式的一些特性。
●建表参考语句
create table ods.test_partition_range( id int, name varchar(64), fdate varchar(64)) distributed by (id) partition by range(fdate) ( partition p1 start ('2017-01-01') inclusive end ('2017-01-31') exclusive, partition p2 start ('2017-02-01') inclusive end ('2017-02-29') exclusive, default partition default_p );distributed by : 分布partition by : 分区inclusive :指定包含,例如上面的 start ('2017-01-01') inclusive 则是包含'2017-01-01'exclusive : 指定不包含, 例如上面的 end ('2017-01-31') exclusive 则是不包含'2017-01-31'
●选择合适分布(Distributed)键
分布键应该和经常用于查询的主键一致。
当我们在greenplum创建一张表时,需要创建一个分布键(distributed by),如果不指定则以第一个字段为分布键。数据入库时,根据分布键均匀的存储在各个segment节点。
当我们进行计算时,可以在各个节点并行计算后,将结果汇总反馈。
当两张表关联的时候,如果有一张表的关联键不是分布键,那么就会发生表的广播或者重分布,将数据移动到一个节点上进行关联,从而获得数据。
●数据重新分布
查看某个表的分布情况
SELECT gp_segment_id, count(1) FROM ods.table01 GROUP BY gp_segment_id;
查看某个表的分布键
--展现数据表+详细的列,取出oidselect oid from pg_class where relname='table01'--展现数据表+详细的列 attrelid=oidselect * from pg_attribute where attrelid= 123--展现存储分布键(字段的序号)select * from gp_distribution_policy where localoid=123
修改分布key,用REORGANIZE=TRUE来强制重新分布
ALTER TABLE ods.table01 SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (key01);
删除原来的主键,重建主键
ALTER TABLE ods.table01 DROP CONSTRAINT table_pkeyALTER TABLE ods.table01 ADD CONSTRAINT table_pkey PRIMARY KEY (key01);
●必要时进行分区(Partition)
分区表(partition by)通常是基于数据量很大的事实表,在逻辑上将他们分成若干个小表。通过分区表的定义,Greenplum查询优化器可以在查询时精准定位到某个分区,而不是扫描整张大表。
一般用时间作为分区字段,这样在查询时,执行计划会分析查询的时间点,进而选择合适的分区。
● 用VACUUM清理数据库表中的垃圾空间
delete或update操作造成的实际物理空间没有从所对应的表中移除的话,vacuum操作可以将此磁盘释放出来,所以对那些经常性更新的表很有需要来做下vacuum操作。
查看容量
select pg_relation_size('ods.table01 ')
执行清理
vacuum ods.table01;
● 用ANALYZE收集表内容的统计信息
ANALYZE可以优化执行计划。如创建索引后,执行此命令,对于随即查询将会利用索引,如下:
analyze sapods.table01;
● 性能调优时参考EXPLAIN执行计划
explain参数
cost:返回第一行记录前的启动时间, 和返回所有记录的总时间(以磁盘页面存取为单位计量)
rows:根据统计信息估计SQL返回结果集的行数
width:返回的结果集的每一行的长度,这个长度值是根据pg_statistic表中的统计信息来计算的。
● 其他优化策略
用group by对distinct改写,因为DISTINCT要进行排序操作
用UNION ALL加GROUP BY的方式对UNION改写
尽量使用GREENPLUM自身提供的聚合函数和窗口函数去完成一些复杂的分析
2.Transform
数据转换(transform)的工作,一般建议在Kettle实现,但基于性能的考虑,以及Oracle、Greenplum数据库等特性,可以适当利用数据库的SQL脚本、函数、触发器、存储过程等实现数据转换。
●尽量减少转换中的Hop
Hop是两个step之间的连线,一个hop创建一个Rowset。Rowset是两个步骤之间的缓存。所以,step越多,需要的缓存越多。尤其需要注意一些隐藏的HOP,下图上方的“增加序列1”和“增加序列2”中间只有一条线,但是一个开了3个线程,一个开了2个,所以中间是有6个hop。
找到性能瓶颈
运行转换时,观察下面的窗口值。1076/0表示输入1076条记录,输出0条记录。如果输入远大于输出,就说明这个步骤来不及处理,就是瓶颈。
调整Rowset值的设置
编辑>设置>杂项>记录集合里的记录数>调整成你需要的值
●尽量少用“数据库查询”、“数据库连接”
Kettle的step是每一条数据执行一次,所以执行这些查询实际上是每条数据连一次数据库。
●“流查询”vs“记录集连接”
"流查询"比"记录集连接"要慢,"流查询"中A表的每条数据要遍历B表,但"流查询"不用事先排序。
"记录集连接",逐条往下比对,运算量少,但是要事先排序。
对于大表关联字典表,字典表数据较少,遍历的时间少;而大表用"记录集连接"要多进行2次排序,排序花的时间多,所以使用"流查询"。
●如果数据量很大,而所有数据源又在同一个数据库中,则使用WITH通过一条SQL完成查询。
WITH的功能非常强大, 如以下SQL可以递归实现kettle某个子节点,属于哪个目录的查询:
WITH T1 AS ( SELECT id_directory,id_directory_parent, directory_name FROM R_DIRECTORY ), T2 (id_directory,id_directory_parent,directory_name) AS (SELECT id_directory, id_directory_parent, directory_name FROM R_DIRECTORY WHERE id_directory=8 UNION ALL SELECT a.id_directory, a.id_directory_parent, a.directory_name ||'/' ||b.directory_name AS directory_name FROM t1 a , t2 b WHERE a.id_directory=b.id_directory_parent )SELECT * FROM t2 WHERE id_directory_parent=0
3.Load
数据加载(Load)其实就是INSERT操作,而ETL的大部分瓶颈都在加载。INSERT有三种方式:
普通插入: INSERT INTO table2 (field1,field2) VALUE(v001,v002);批量插入: INSERT INTO table2 (field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302);批量加载: INSERT INTO table2 (field1,field2) SELECT col1,col2 FROM table1 WHERE ...
其中,批量加载最快,普通插入最慢。
●“插入/更新”、“更新”、“删除”(10条/秒)
不要使用“插入/更新”、“更新”、“删除”等step。在Kettle的设计思想里,step是按“数据流”处理, 也就是说,经过“插入/更新”、“更新”、“删除”的数据,会逐条发到数据库,按key值逐条比对,按照比对的结果再执行操作。操作的表数据量越大,越慢。
●“表输出”(500/秒)
“表输出”就是普通插入,逐条插入。即便选择了批量插入,而数据库连接没有设置好,也是逐条插入。
●MySQL优化连接后的“表输出”(4000/秒)
对于MySQL数据库可以在数据库连接设置如下:
useServerPrepStmts=false -- 关闭预编译
useCompression=true --启动压缩
rewriteBatchedStatements=true --普通插入变成批量插入
●MySQL Load 批量加载 (65000条/秒)
MySQL的批量加载只能在Linux下运行,因为用到"管道"文件,这是Linux的特有的。于是,我在Ubuntu上安装了Kettle,进行开发:
再在Ubuntu上安装MySQL数据,Kettle的MySQL批量加载step也没有太多的配置,管道文件已经定义好放在/tmp/fifo下,很快设置好就可以运行。居然达到65880条/秒,也许因为Kettle和MySQL在同一台Linux,再加上管道的作用才有如此快的速度。
●Greenplum Load 批量加载 (12000条/秒)
“Greenplum Load”就是“批量加载”,通过特有外部表和gpfdist完成。原理是先把其他数据库导出的数据,保存到kettle服务器的一个文档中,通过Greenplum的外部表,把kettle服务器的文件看成Greenplum 内部的一个表,实现批量加载:
Kettle所在的Windows或Linux安装插件greenplum loaders并设置环境变量
开启gpfdist服务
开发kettle的greenplum Load程序
具体实现方式,查看之前分享的另外一篇文章《kettle使用gpload加载数据到greenplum数据库
》。
●ORACLE Load 批量加载 (17000条/秒)
ORACLE Load需要sqlldr.exe文件,我本机安装了ORACLE数据库,我在Oralce的安装目录下找到这个文件。
但是,测试的时候发现不配置sqlldr.exe,也是照样可以跑,不知道是否有默认的程序存在:
速度约17000条/秒:
后来我又配置了sqlldr.exe文件,速度差不多。
●同数据库加载 (30000条/秒)
如果数据加载位于同一个数据中,直接使用批量加载。
通过Kettle里,作业的“SQL”或转换的“执行SQL脚本”来运行以上语句。当逻辑比较复杂的时候,可以使用WITH语法,把复杂的语句串在一起。我测试的是用greenplum数据库,速度为30000条/秒。
以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。
学习资料见知识星球。
以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。
快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利!
更多技巧, www.excelbook.cn
欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群,共享资源。
5、优惠的会员商品。
6、一次付费只需99元,即可下载本站文章涉及的文件和软件。
共有 0 条评论