ETL的過程原理和數(shù)據(jù)倉庫建設(shè)

1、ETL的過程原理和數(shù)據(jù)倉庫建設(shè)數(shù)據(jù)倉庫建立中的ETL(Extract, Transform, Load)是數(shù)據(jù)抽取、轉(zhuǎn)換和裝載到模型的過程，整個過程根本是通過掌握用SQL語句編寫的存儲過程和函數(shù)的方式來實現(xiàn)對數(shù)據(jù)的直接操作，SQL語句的效率將直接影響到數(shù)據(jù)倉庫后臺的性能。目前，國內(nèi)的大中型企業(yè)根本都具有四年以上計算機信息系統(tǒng)應(yīng)用閱歷，積存了大量可分析的業(yè)務(wù)數(shù)據(jù)，這些信息系統(tǒng)中的數(shù)據(jù)需要通過搭建數(shù)據(jù)倉庫平臺才能得到科學的分析，這也是近幾年數(shù)據(jù)倉庫系統(tǒng)建立成為IT領(lǐng)域熱門話題的緣由。優(yōu)化的思路分析數(shù)據(jù)倉庫ETL過程的主要特點是：面對海量的數(shù)據(jù)進展抽?。环謺r段對大批量數(shù)據(jù)進展刪除、更新和插入操作；面

2、對特別的數(shù)據(jù)進展規(guī)章化的清洗；大量的分析模型重算工作；有特定的過程處理時間規(guī)律性，一般整個ETL過程需要在每天的零點開頭到6點之前完成。所以，針對ETL過程的優(yōu)化主要是結(jié)合數(shù)據(jù)倉庫自身的特點，抓住需要優(yōu)化的主要方面，針對不同的狀況從如何采納高效的SQL入手來進展。優(yōu)化的實例分析目前數(shù)據(jù)倉庫建立中的后臺數(shù)據(jù)庫大局部采納Oracle,以下的SQL采納Oracle的語法來說明，全部的測試在Oracle9i環(huán)境中通過，但其優(yōu)化的方法和原理同樣適合除Oracle之外的其他數(shù)據(jù)庫。3.1索引的正確使用在海量數(shù)據(jù)表中，根本每個表都有一個或多個的索引來保證高效的查詢，在ETL過程中的索引需要遵循以下使用原則：

3、當插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時，首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當數(shù)據(jù)全部插入后再建立索引。避開在索引列上使用函數(shù)或計算，在WHERE子句中，假如索引列是函數(shù)的一局部，優(yōu)化器將不使用索引而使用全表掃描。舉例：低效：SELECT * ROM DEPT WHERE SAL * 12 25000；高效：SELECT * FROM DEPT WHERE SAL 25000/12；避開在索引列上使用NOT和 ！二，索引只能告知什么存在于表中，而不能告知什么不存在于表中，當數(shù)據(jù)庫遇到NOT和 ！二時，就會停頓使用索引轉(zhuǎn)而執(zhí)行全表掃描。索引列上用二替代高效：SELECT * FRO

4、M EMP WHERE DEPTNO =4低效：SELECT * FROM EMP WHERE DEPTNO 3兩者的區(qū)分在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。函數(shù)的列啟用索引方法，假如肯定要對使用函數(shù)的列啟用索引，Oracle9i以上版本新的功能：基于函數(shù)的索引(Function-Based Index)是一個較好的方案，但該類型索引的缺點是只能針對某個函數(shù)來建立和使用該函數(shù)。CREATE INDEX EMP_I ON EMP (UPPER( ENAME)；SELECT * FROM EMP W

5、HERE UPPER(ENAME)二 BLACKSNAIL ；3.2游標的正確使用當在海量數(shù)據(jù)表中進展數(shù)據(jù)的刪除、更新和插入操作時，用游標處理的效率是最慢的方式，但它在ETL過程中的使用又必不行少，而且使用有著及其重要的地位，所以游標的正確使用尤為重要。對數(shù)據(jù)倉庫維表的數(shù)據(jù)進展維護時，由于需要保證維表ID的全都性，所以采納游標的是數(shù)據(jù)維護完整性的方式。由于它的效率低，假如根據(jù)一般的方式將無法處理大數(shù)據(jù)量的維表數(shù)據(jù)維護(一般是指10萬條記錄以上的維表)，以下是處理這種狀況的有效方式：在數(shù)據(jù)抽取的源表中使用時間戳，這樣每天的維表數(shù)據(jù)維護只針對更新日期為最新時間的數(shù)據(jù)來進展，大大削減需要維護的數(shù)據(jù)記

6、錄數(shù)。在INSERT和UPDATE維表時都加上一個條件來過濾維表中已經(jīng)存在的記錄，實例為：INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHEREODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)使用顯式的游標(CURSORs)，由于使用隱式的游標將會執(zhí)行兩次操作，第一次檢索記錄，其次次檢查TOO MANY ROWS這個EXCEPTION，而顯式游標不執(zhí)行其次次操作。3.3數(shù)據(jù)抽取和上載時的SQL優(yōu)化3.3.1 WHERE子句中的連接挨次ORACLE采納自下而上的挨次解析WHERE子句，依據(jù)

7、這個原理，表之間的連接必需寫在其它WHERE條件之前，那些可以過濾掉數(shù)量記錄的條件必需寫在WHERE子句的末尾。低效：SELECT * FROM EMP E WHERE SAL 50000 AND JOB 二 MANAGERAND 25 50000 AND JOB 二 MANAGER ；刪除全表時用TRUNCATE替代DELETE當DELETE刪除表中的記錄時，有回滾段(rollback segments )用來存放可以被恢復(fù)的信息，而當運用TRUNCATE時，回滾段不再存放任何可被恢復(fù)的信息，所以執(zhí)行時間也會很短。同時需要留意TRUNCATE只在刪除全表時適用，由于TRUNCATE是DDL而

8、不是DML。盡量多使用COMMITETL中同一個過程的數(shù)據(jù)操作步驟許多，數(shù)據(jù)倉庫采納的是數(shù)據(jù)抽取后分析模型重算的原理，所以對數(shù)據(jù)的COMMIT不像業(yè)務(wù)系統(tǒng)為保證數(shù)據(jù)的完整和全都性而需要某個操作過程全部完成才能進展，只要有可能就在程序中對每個DELETE、INSERT和UPDATE操作盡量多使用COMMIT,這樣系統(tǒng)性能會由于COMMIT所釋放的資源而大大提高。用 EXISTS 替代 IN在很多基于根底表的查詢中，為了滿意一個條件往往需要對另一個表進展聯(lián)接，例如在ETL過程寫數(shù)據(jù)到模型時常常需要關(guān)聯(lián)10個左右的維表，在這種狀況下，使用EXISTS而不用IN將提高查詢的效率。用 NOT EXIST

9、S 替代 NOT IN子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并，無論在哪種狀況下，NOT IN都是最低效的，由于它對子查詢中的表執(zhí)行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。優(yōu)化 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的前提是:所連接的各個表中無主關(guān)鍵字一樣的記錄，由于UNIONALL將重復(fù)輸出兩個結(jié)果集合中一樣記錄。當SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNION-ALL的方式被合并，