1.引言
数据仓库建设中的ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程,整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作,SQL语句的效率将直接影响到数据仓库后台的性能。
目前,国内的大中型企业基本都具有四年以上计算机信息系统应用经验,积累了大量可分析的业务数据,这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析,这也是近几年数据仓库系统建设成为IT领域热门话题的原因。
2.优化的思路分析
数据仓库ETL过程的主要特点是:面对海量的数据进行抽取;分时段对大批量数据进行删除、更新和插入操作;面对异常的数据进行规则化的清洗;大量的分析模型重算工作;有特定的过程处理时间规律性,一般整个ETL过程需要在每天的零点开始到6点之前完成。所以,针对ETL过程的优化主要是结合数据仓库自身的特点,抓住需要优化的主要方面,针对不同的情况从如何采用高效的SQL入手来进行。
3.优化的实例分析
目前数据仓库建设中的后台数据库大部分采用Oracle,以下的SQL采用Oracle的语法来说明,所有的测试在Oracle9i环境中通过,但其优化的方法和原理同样适合除Oracle之外的其他数据库。
3.1索引的正确使用
在海量数据表中,基本每个表都有一个或多个的索引来保证高效的查询,在ETL过程中的索引需要遵循以下使用原则:
(1) 当插入的数据为数据表中的记录数量10%以上时, 首先需要删除该表的索引来提高数据的插入效率,当数据全部插入后再建立索引。
(2) 避免在索引列上使用函数或计算,在where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。举例:
低效: select * ROM DEPT where SAL * 12 > 25000;
高效: select * FROM DEPT where SAL > 25000/12;
(3) 避免在索引列上使用NOT和”!=” , 索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到NOT和”!=”时,就会停止使用索引转而执行全表扫描。
(4) 索引列上用>=替代>
高效: select * FROM EMP where DEPTNO >=4
低效: select * FROM EMP where DEPTNO >3
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
(5) 函数的列启用索引方法,如果一定要对使用函数的列启用索引,Oracle9i以上版本新的功能:基于函数的索引(Function-Based Index)是一个较好的方案,但该类型索引的缺点是只能针对某个函数来建立和使用该函数。
create INDEX EMP_I ON EMP (UPPER( ENAME));
select * FROM EMP where UPPER(ENAME) = ‘BLACKSNAIL’;
3.2 游标的正确使用
当在海量数据表中进行数据的删除、更新和插入操作时,用游标处理的效率是最慢的方式,但它在ETL过程中的使用又必不可少,而且使用有着及其重要的地位,所以游标的正确使用尤为重要。
对数据仓库维表的数据进行维护时,因为需要保证维表ID的一致性,所以采用游标的是数据维护完整性的最好方式。由于它的效率低,如果按照普通的方式将无法处理大数据量的维表数据维护(一般是指10万条记录以上的维表),以下是处理这种情况的有效方式:
(1) 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。
(2) 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,实例为:
insert INTO DIM_CUSTOMER select * FROM ODS_CUSTOMER where ODS_CUSTOMER.CODE NOT exists (DIM_CUSTOMER.CODE)
(3) 使用显式的游标(CURSORs) ,因为使用隐式的游标将会执行两次操作,第一次检索记录,第二次检查TOO MANY ROWS 这个EXCEPTION,而显式游标不执行第二次操作。
3.3数据抽取和上载时的SQL优化
3.3.1 where子句中的连接顺序
ORACLE采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其它where条件之前,那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。
低效:select * FROM EMP E where SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (select count(*) FROM EMP where MGR=E.EMPNO);
高效:select * FROM EMP E where 25 < (select count(*) FROM EMP where MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;
3.3.2 删除全表时用truncate替代delete
当delete删除表中的记录时,有回滚段(rollback segments ) 用来存放可以被恢复的信息,而当运用truncate时,回滚段不再存放任何可被恢复的信息,所以执行时间也会很短。同时需要注意truncate只在删除全表时适用,因为truncate是DDL而不是DML。
3.3.3 尽量多使用COMMIT
ETL中同一个过程的数据操作步骤很多,数据仓库采用的是数据抽取后分析模型重算的原理,所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行,只要有可能就在程序中对每个delete、insert和update操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放的资源而大大提高。
3.3.4 用exists替代IN
在许多基于基础表的查询中,为了满足一个条件往往需要对另一个表进行联接,例如在ETL过程写数据到模型时经常需要关联10个左右的维表,在这种情况下,使用exists而不用IN将提高查询的效率。
3.3.5 用NOT exists替代NOT IN
子查询中,NOT IN子句将执行一个内部的排序和合并,无论在哪种情况下,NOT IN都是最低效的,因为它对子查询中的表执行了一个全表遍历。用NOT exists替代NOT IN将提高查询的效率。
3.3.6 优化GROUP BY
提高GROUP BY 语句的效率,可以通过将不需要的记录在GROUP BY 之前过滤掉。
低效: select JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效: select JOB , AVG(SAL) FROM EMP where JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB
3.3.7 有条件的使用union-ALL 替换union
ETL过程针对多表连接操作的情况很多,有条件的使用union-ALL 替换union的前提是:所连接的各个表中无主关键字相同的记录,因为union ALL 将重复输出两个结果集合中相同记录。
当SQL语句需要union两个查询结果集合时,这两个结果集合会以union-ALL的方式被合并,然后在输出最终结果前进行排序。如果用union ALL替代union,这样排序就不是必要了,效率就会因此得到提高3-5倍
3.3.8 分离表和索引
总是将你的表和索引建立在不同的表空间内,决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上
相关推荐
优化方案:ETL的过程原理和数据仓库建设
数据仓库建设中的ETL Extract Transform Load 是数据抽取 转换和装载到模型的过程 整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作 SQL语句的效率将直接影响到数据仓库后台的性能
优化方案:ETL的过程原理和数据仓库建设.txt 基于云技术的电信ETL方案+IBM刀片性能测试报告.pdf 如何使用ETL_技术.pdf 数据仓库ETl工具箱3.pdf 数据仓库ETl工具箱5.pdf 数据仓库ETl工具箱6.pdf 数据仓库ETl...
除了介绍PDI(Kettle)工具的使用和功能,本书还结合Kimball博士的数据仓库和ETL子系统的理论,从实践的角度介绍数据仓库的模型设计、数据仓库的构建方法,以及如何使用 PDI实现Kimball博士提出的34种ETL子系统。
Kettle解决方案:使用PDI构建开源ETL解决方案 kettle介绍,使用,内容完善,是哥非常不错的内容
《Pentaho Kettle解决方案:使用PDI构建开源ETL解决方案》介绍的PDI(Kettle)是一种开源的 ETL 解决方案,书中介绍了如何使用PDI来实现数据的剖析、清洗、校验、抽取、转换、加载等各类常见的ETL类工作。 除了ODS/DW...
《Pentaho Kettle解决方案:使用PDI构建开源ETL解决方案》介绍的PDI(Kettle)是一种开源的 ETL 解决方案,书中介绍了如何使用PDI来实现数据的剖析、清洗、校验、抽取、转换、加载等各类常见的ETL类工作。 除了ODS/DW...
数据仓库工具箱:ETL完全指南 中文版 作者简介:Ralph Kimball 博士,Kimball 集团的创始人,从1982 年起就成为数据仓库领域的领导者,也是当今最著名的演说家、咨询专家、教师和作家之一。
推荐,数据仓库建设学习资料合集,包含建设规范、架构、工具及模型等资料。共38份。 2021数据仓库服务常见问题-华为-51页 2021云数据仓库专业服务-华为-168页 阿里云数据中台-金融行业新一代数据仓库解决方案 ...
解决方案:使用PDI构建开源ETL解决方案源码示例,书中PDI示例和SQL
基于电信数据仓库系统的ETL研究与设计 ...式编辑整个ETL过程,实现部分图形化交互界面,并针对电信系统大数据量的特点,提出性能优化及改进的方法,提高了ETL效率。 关键词 ETL 电信 数据仓库 大数据量
ETL流程、数据流图及ETL过程解决方案
《Pentaho Kettle解决方案:使用PDI构建开源ETL解决方案》介绍的PDI(Kettle)是一种开源的 ETL 解决方案,书中介绍了如何使用PDI来实现数据的剖析、清洗、校验、抽取、转换、加载等各类常见的ETL类工作。 除了ODS/DW...
ETL构建数据仓库 ETL构建数据仓库 ETL构建数据仓库 ETL构建数据仓库 ETL构建数据仓库
ETL流程、数据流图及ETL过程解决方案 ETL实施解决方案 ETL流程设计
第02章 数据仓库、商业智能、OLAP和数据挖掘 共64页.ppt 第03章 定义数据仓库概念和术语 共46页.ppt 第04章 业务、逻辑、维度和物理建模 共66页.ppt 第05章 数据库大小、存储、性能和安全注意事项 共52页.ppt 第06章...
第02章 数据仓库、商业智能、OLAP和数据挖掘 共64页.ppt 第03章 定义数据仓库概念和术语 共46页.ppt 第04章 业务、逻辑、维度和物理建模 共66页.ppt 第05章 数据库大小、存储、性能和安全注意事项 共52页.ppt 第06章...
第02章 数据仓库、商业智能、OLAP和数据挖掘 共64页.ppt 第03章 定义数据仓库概念和术语 共46页.ppt 第04章 业务、逻辑、维度和物理建模 共66页.ppt 第05章 数据库大小、存储、性能和安全注意事项 共52页.ppt 第06章...
数据仓库ETL建设宝典,最新最全的ETL建设方案和详解!与大家分享!
解决方案:使用PDI构建开源ETL解决方案.pdf 完整中文版,带目录,460页