ETL的过程原理和数据仓库建设

1、ETL的过程原理和数据仓库建设数据仓库建立中的ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程，整个过程根本是通过掌握用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率将直接影响到数据仓库后台的性能。目前，国内的大中型企业根本都具有四年以上计算机信息系统应用阅历，积存了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建立成为IT领域热门话题的缘由。优化的思路分析数据仓库ETL过程的主要特点是：面对海量的数据进展抽取；分时段对大批量数据进展删除、更新和插入操作；面

2、对特别的数据进展规章化的清洗；大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开头到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的状况从如何采纳高效的SQL入手来进展。优化的实例分析目前数据仓库建立中的后台数据库大局部采纳Oracle,以下的SQL采纳Oracle的语法来说明，全部的测试在Oracle9i环境中通过，但其优化的方法和原理同样适合除Oracle之外的其他数据库。3.1索引的正确使用在海量数据表中，根本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则：

3、当插入的数据为数据表中的记录数量10%以上时，首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。避开在索引列上使用函数或计算，在WHERE子句中，假如索引列是函数的一局部，优化器将不使用索引而使用全表扫描。举例：低效：SELECT * ROM DEPT WHERE SAL * 12 25000；高效：SELECT * FROM DEPT WHERE SAL 25000/12；避开在索引列上使用NOT和 ！二，索引只能告知什么存在于表中，而不能告知什么不存在于表中，当数据库遇到NOT和 ！二时，就会停顿使用索引转而执行全表扫描。索引列上用二替代高效：SELECT * FRO

4、M EMP WHERE DEPTNO =4低效：SELECT * FROM EMP WHERE DEPTNO 3两者的区分在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。函数的列启用索引方法，假如肯定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-Based Index)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。CREATE INDEX EMP_I ON EMP (UPPER( ENAME)；SELECT * FROM EMP W

5、HERE UPPER(ENAME)二 BLACKSNAIL ；3.2游标的正确使用当在海量数据表中进展数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程中的使用又必不行少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。对数据仓库维表的数据进展维护时，由于需要保证维表ID的全都性，所以采纳游标的是数据维护完整性的方式。由于它的效率低，假如根据一般的方式将无法处理大数据量的维表数据维护(一般是指10万条记录以上的维表)，以下是处理这种状况的有效方式：在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进展，大大削减需要维护的数据记

6、录数。在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为：INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHEREODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)使用显式的游标(CURSORs)，由于使用隐式的游标将会执行两次操作，第一次检索记录，其次次检查TOO MANY ROWS这个EXCEPTION，而显式游标不执行其次次操作。3.3数据抽取和上载时的SQL优化3.3.1 WHERE子句中的连接挨次ORACLE采纳自下而上的挨次解析WHERE子句，依据

7、这个原理，表之间的连接必需写在其它WHERE条件之前，那些可以过滤掉数量记录的条件必需写在WHERE子句的末尾。低效：SELECT * FROM EMP E WHERE SAL 50000 AND JOB 二 MANAGERAND 25 50000 AND JOB 二 MANAGER ；删除全表时用TRUNCATE替代DELETE当DELETE删除表中的记录时，有回滚段(rollback segments )用来存放可以被恢复的信息，而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息，所以执行时间也会很短。同时需要留意TRUNCATE只在删除全表时适用，由于TRUNCATE是DDL而

8、不是DML。尽量多使用COMMITETL中同一个过程的数据操作步骤许多，数据仓库采纳的是数据抽取后分析模型重算的原理，所以对数据的COMMIT不像业务系统为保证数据的完整和全都性而需要某个操作过程全部完成才能进展，只要有可能就在程序中对每个DELETE、INSERT和UPDATE操作尽量多使用COMMIT,这样系统性能会由于COMMIT所释放的资源而大大提高。用 EXISTS 替代 IN在很多基于根底表的查询中，为了满意一个条件往往需要对另一个表进展联接，例如在ETL过程写数据到模型时常常需要关联10个左右的维表，在这种状况下，使用EXISTS而不用IN将提高查询的效率。用 NOT EXIST

9、S 替代 NOT IN子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种状况下，NOT IN都是最低效的，由于它对子查询中的表执行了一个全表遍历。用NOT EXISTS替代NOT IN将提高查询的效率。优化 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有条件的使用UNION-ALL替换UNIONETL过程针对多表连接操作的状况许多，有条件的使用UNION-ALL替换UNION的前提是:所连接的各个表中无主关键字一样的记录，由于UNIONALL将重复输出两个结果集合中一样记录。当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，