Kettle学习笔记-数据抽取性能优化!

Kettle学习笔记-数据抽取性能优化!

 

近期工作中,对kettle及各种数据库做了一些学习,背景是数据库到数据库的抽取。然后,做了一些调优,数据抽取速度从500条/秒,到4000条/秒,到12000条/秒,最后到30000条/秒不断提升,于是把经验记录下来。

ETL(Extract-Transform-Load),是将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。所以,这里分别从extract、transform、load三个方面,讲述如何变得更快的故事。

其实,若非性能问题,尽量用Kettle的控件。因为:

25aa.svg_ Kettle中,对数据的逻辑、异常、log机制非常完善,对数据进行全面的跟踪,排错方便。

25aa.svg_ 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操作。

25aa.svg_查看容量

      select pg_relation_size('ods.table01 ')

执行清理

      vacuum  ods.table01; 

 

      ● 用ANALYZE收集表内容的统计信息

ANALYZE可以优化执行计划。如创建索引后,执行此命令,对于随即查询将会利用索引,如下:

      analyze sapods.table01;

 

      ● 性能调优时参考EXPLAIN执行计划     

006fRELkly4gze8hdcfsbj30lh05eq32

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。

006fRELkly4gze8hcbmzij30oi0mogoj

 

找到性能瓶颈

运行转换时,观察下面的窗口值。1076/0表示输入1076条记录,输出0条记录。如果输入远大于输出,就说明这个步骤来不及处理,就是瓶颈。

006fRELkly4gze8hc943aj30v702zq33

调整Rowset值的设置

 

编辑>设置>杂项>记录集合里的记录数>调整成你需要的值

006fRELkly4gze8hc4sitj30ij07saak

 

     ●尽量少用“数据库查询”、“数据库连接”

Kettle的step是每一条数据执行一次,所以执行这些查询实际上是每条数据连一次数据库。

 

      ●“流查询”vs“记录集连接”

006fRELkly4gze8hcax2ij30on04xglk

"流查询"比"记录集连接"要慢,"流查询"中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值逐条比对,按照比对的结果再执行操作。操作的表数据量越大,越慢。

006fRELkly4gze8hc9p5vj306503kglj

 

      ●“表输出”(500/秒)
“表输出”就是普通插入,逐条插入。即便选择了批量插入,而数据库连接没有设置好,也是逐条插入。

006fRELkly4gze8hc5725j30c408oaa7

 

 

      ●MySQL优化连接后的“表输出”(4000/秒)

对于MySQL数据库可以在数据库连接设置如下:

006fRELkly4gze8hca6doj30eo07x3z2

useServerPrepStmts=false  -- 关闭预编译
useCompression=true  --启动压缩

rewriteBatchedStatements=true  --普通插入变成批量插入

 

      ●MySQL Load 批量加载 (65000条/秒)

MySQL的批量加载只能在Linux下运行,因为用到"管道"文件,这是Linux的特有的。于是,我在Ubuntu上安装了Kettle,进行开发:

006fRELkly4gze8hcb5n5j30td0jaq4y

再在Ubuntu上安装MySQL数据,Kettle的MySQL批量加载step也没有太多的配置,管道文件已经定义好放在/tmp/fifo下,很快设置好就可以运行。居然达到65880条/秒,也许因为Kettle和MySQL在同一台Linux,再加上管道的作用才有如此快的速度。

006fRELkly4gze8hcazywj30u007pjs0

 

      ●Greenplum Load 批量加载 (12000条/秒)

“Greenplum Load”就是“批量加载”,通过特有外部表和gpfdist完成。原理是先把其他数据库导出的数据,保存到kettle服务器的一个文档中,通过Greenplum的外部表,把kettle服务器的文件看成Greenplum 内部的一个表,实现批量加载

006fRELkly4gze8hcb0rdj308f02a745

Kettle所在的Windows或Linux安装插件greenplum loaders并设置环境变量

开启gpfdist服务

开发kettle的greenplum Load程序

具体实现方式,查看之前分享的另外一篇文章《kettle使用gpload加载数据到greenplum数据库

》。
      ●ORACLE Load 批量加载 (17000条/秒)

ORACLE Load需要sqlldr.exe文件,我本机安装了ORACLE数据库,我在Oralce的安装目录下找到这个文件。

006fRELkly4gze8hcc3ilj30k605z3yv

但是,测试的时候发现不配置sqlldr.exe,也是照样可以跑,不知道是否有默认的程序存在:

006fRELkly4gze8hc7fvoj30ln0qs0ve

速度约17000条/秒:

006fRELkly4gze8hcij5jj30u0063t9b

后来我又配置了sqlldr.exe文件,速度差不多。

 

      ●同数据库加载 (30000条/秒)
如果数据加载位于同一个数据中,直接使用批量加载

通过Kettle里,作业的“SQL”或转换的“执行SQL脚本”来运行以上语句。当逻辑比较复杂的时候,可以使用WITH语法,把复杂的语句串在一起。我测试的是用greenplum数据库,速度为30000条/秒。

006fRELkly4gze8hcfltej30ln0hhwfs

 

以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。

 

学习资料见知识星球。

以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。

快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​!

更多技巧, www.excelbook.cn

欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;

电商数据分析360°实战攻略!

你将获得:

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群,共享资源。

5、优惠的会员商品。

6、一次付费只需99元,即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称:Excelbook
2、本站永久网址:http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报。
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>