ITPub博客

首页 > 大数据 > 数据挖掘 > ETL优化 (R2)

ETL优化 (R2)

数据挖掘 作者:thamsyangsw 时间:2014-03-21 11:01:41 0 删除 编辑
转载地址:http://blog.csdn.net/jadeite/article/details/1837426 

在讨论ETL优化之前我们应该首先讨论的是当前程序的性能。首先要定义ETL过程的基线标准。在建立了基线标准的基础上我们再来讨论该如何对已有过程进行优化。
基线标准也应该包括标准情况下网络传输速度,硬盘读取速度等等的硬件指标。在此之上测量多次ETL过程得到比较稳定的 数据量(M)/时间(s) 的比值作为基线标准。当然前提是在一定的范围内数据量的增长和计算量的增长一定要呈线性关系。一种比较理想的假设就是每条数据都经过相同的计算/转换过程。
建立基线的好处在于有了调试以及优化的依据。最大程度地避免了人的主观感觉所造成的错误判断。
“经过优化,现在的ETL性能好了一些!”这种话应该换成“经过优化,现在性能比基线性能提升了1%”
 
当然我们最终的目的是进行ETL过程的优化。优化的方面包括索引的调整,数据抽取的并行处理,复杂运算的程序处理等手段。当然编写出高效的SQL应该是首要的考虑因素了!
 
笔者当初经历过一个项目。用的SQL SERVER自带的DTS作为ETL工具。在项目设计阶段没有考虑ETL的性能问题。在开发过程中随着实现的功能越来越多,算法越来越复杂,需要处理的数据也是越来越多 每次ETL过程所需要的时间不断增长。而项目本身需要每半小时同步一次数据。没办法减少需求和数据量,只好着手开始优化。
 
 
分析了一下整个ETL过程。发现大致可以分为两类操作。一类是直接从数据源拷贝数据,一类是从数据源获得原始数据在过程中计算再把结果存到结果数据库中。
 
从数据量看直接拷贝的部分数据量比较小,需要计算的部分数据量较大。两者比例大概 30% / 70%
 
 
我们首先从直接拷备的数据开始入手。先测试了一下网络环境,因为两台服务器在一个局域网里,网络很稳定所以只要能排除锁表等原因的话应该没有什么优化的余地了。
 
再看需要计算的部分。由于整个过程的逻辑比较复杂,数据量较大所以耗时较长。用掉了整个ETL过程 90% 时间。
 
 
 
1.       SQL 脚本
SQL脚本的优化是个很宽泛的话题。在ETL中我们需要主要的内容主要包括:
?尽量避免使用游标进行数据操作
作过程序开发的人估计对游标都很熟悉,很多程序员刚开始用SQL脚本写逻辑的时候很自然的使用游标进行计算和操作。
但我建议在抽取/转换/计算等等过程中都要尽可能地避免使用游标。应该意识到用游标遍历一个1000条记录的数据集就等于做了1000次select !
如果说在数据量小,或者完全没有时间限制的情况下可以使用的话。那么在大数据量,有严格的时间控制的情况下使用游标绝对是程序员的噩梦!
笔者刚出道的时候就犯过类似错误。过程略去不说。结果是写出来的ETL过程需要消耗尽两个小时才能完成。这在当时是完全不能接受的,因为项目的设计是每半小时同步一次。而且我自己调试起来也是十分痛苦!
后来经人指点修改了数据的计算方式,改用临时表代替游标,整个过程只需要不到三分钟就完成了!
希望这样血淋淋的教训能够警示后人,慎用游标!
 
当然我们也不是说绝对不能用。在后来的项目中我们还是不时会用到。比如遍历一个实例上的所有数据库等操作。用游标实现就显得十分自然。
 
?SQL 脚本尽量避免逻辑为“非”的条件
用“非”条件进行数据检索的时候会使索引失效。这点相信大家都很清楚。类似的还有在用like的时候不要以 “%”开头,不要使用关键字 “in”等等。
 
?不要在任何地方写 SELECT * FROM TABLE
哪怕是确实需要所有的字段,最好也一个一个地列出来。更便于维护/修改。而且大部分情况是我们不需要一张表里的全部字段。写 “select *”是因为我们在偷懒!
 
?尽量避免使用Lfet join 和 Null
Left Join 效率低我想就不用多解释了吧。其实任何大表的join操作都是应该避免的。如果一定要join可以考虑视图索引(SQL SERVER) 物化视图(Oracle)
Null 值是需要数据引擎单独处理的内容。自然需要牺牲一部分性能。
……更多请参见SQL 优化的相关文章
 
 
2.       调整索引
数据源的索引我们很难控制,在这里就不加讨论了。我们只讨论目的数据库的索引问题。
总所周知索引的作用在于查询时能更快的得到结果。但这是以降低插入,修改,删除操作的性能为代价换来的。但目的表又不能没有索引。
索引是一把双刃剑,用得好就是提升性能的利器,而使用不当也会成为性能的杀手。
所以在我们需要在建立和使用索引时候格外注意。
如果需要插入的数据量很大,我们可以考虑在插入数据前先删除索引,插入操作完毕后再建立索引。这样避免了引擎在插入数据的同时维护索引,新建的索引也会更整齐连贯。
 
3.       数据抽取的并行化
并行的数据抽取主要包括从多个数据源同时进行抽取操作,如果目的数据库分了文件组并且分布在目的服务器有多块硬盘上的话效率会有较大提高。
 
4.       复杂运算的程序处理
有时从数据源抽取的数据在进入数据仓库之前要经过很复杂的逻辑运算。在这种时候我们可以考虑用程序的方式在ETL处理流程外进行处理,再把结果返回。毕竟用SQL脚本写逻辑很强的程序不如用.NET/JAVA来的顺手。同时在程序计算的过程中还可以进行数据的传输。
 
但这种方式会给ETL整体的控制和维护造成负面影响,设计时要仔细考虑,权衡利弊。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26613085/viewspace-1126455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-01-12

  • 博文量
    160
  • 访问量
    1176675