当前位置: 首页 > >

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

发布时间:

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 表空间里。 同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘 上。

版权声明:本文内容由专业老师自互联网搜集整理而来,仅供本校校园网内用户学*参考和交流 之用,任何人不得将本文用于其他用途。




友情链接: