我對ORACLE BI 的ETL的一些總結（原）

http://blog.chinaunix.net/u/25176/showart_2036107.html

資料倉庫中的ETL詳細的分為四個階段：提取，傳輸，轉換，裝載。我先簡單的介紹一下提取和傳輸的分類和方法：

一：提取

   提取可以分為邏輯提取，和實體提取。

   1：邏輯提取按照規模分為：完全提取，增量提取。

 完全提取簡單運用EXP或者全表掃描可以完成。

      增量提取是提取相比上次提取增加了的資料，也可以是按照資料産生時間PATITION了的一個分區等等。Oracle's Change Data Capture 是ORACLE為增量提取提供的一個完備的機制。可以運用基于Timestamps，Partitioning，Triggers的增量提取。

   2：實體提取又分為線上提取和離線提取。

      線上提取是直接連接配接資料庫，通路資料庫的表，然後提取。

      離線提取是指提取資料庫以外的一些檔案，比如Flat file，Dump file，Redo or Archive log.Transportable tablespaces。等等。

提取的方法很多。可以用sqlplus把資料提取到FLAT file中，也可以用exp，甚至可以直接用oracle net處理。比如：

CREATE TABLE country_city AS SELECT distinct t1.country_name, t2.cust_city

FROM [email protected]_db t1, [email protected]_db t2

WHERE t1.country_id = t2.country_id 

AND t1.country_name='United States of America';

所有提取不是ETL中困難的過程。

二：傳輸

通過FTP或者Transportable Tablespaces（建立一個臨時的表空間用來存提取出來需要傳輸的資料，然後EXP這個表空間）

三：轉換

轉換的過程是ETL最複雜，處理時間最長的過程。這個過程涉及的ORACLE知識比較多。開發人員需要知道怎樣選擇最有效，最便捷的技術，我将在本文詳細說明。

我了解的轉化過程就是，通過若幹個步驟來處理轉化過程中需要處理的每一個問題，而這若幹步驟是通過建立若幹的臨時表來完成的，後一個步驟建立的臨時表是在前一個步驟建立的臨時表的基礎上建立起來的。這樣一次一次的轉化，最後得到轉化的結果。

1：Transformation Flow

如果你自己涉及轉化的過程，你會想到什麼？首先明确，咱們的目的是什麼，我們有一個STAGING表，我們是要把這個表的資料添加到DW的事實表中，但是不是簡單的添加，這些資料需要按照SCHEMA DESIGN的要求，把所有和維表對應的描述資訊分離到維表中。這是一步，第二，我們需要考慮事實表的主鍵和staging表的主鍵一定有沖突，因為他們不是同一個SEQUENCE生成的。第三步，就是INSERT到事實表。

Transformation Flow就是按照這樣的邏輯來處理的。我們可以寫PL/SQL實作整個功能。

以下這個SQL可以建立一個表，大家一看就能明白它的作用了：

CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS SELECT sales_transaction_id,

  product.product_id sales_product_id, sales_customer_id, sales_time_id,

  sales_channel_id, sales_quantity_sold, sales_dollar_amount

FROM  temp_sales_step1, product

WHERE temp_sales_step1.product_name = product.product_name;

解釋一下：

一般，從資料源過來的staging表帶有和維表某個字段相同或者相似的資訊，比如說産品名稱。我們就可以通過産品名稱連結維表和staging表，SQL中WHERE中的連接配接就是這樣做的。然後就可以把在維表中的産品名稱對應的産品ID找出來，辨別成為要插入的事實表中的sales_product_id。然後建立temp表把查詢結果儲存下來。這樣就實作了和維的主外鍵對接。

這個過程會衍生出一個問題。如果product_name在product中沒有，就需要嗎？大部分情況可能答案是需要的。那就需要做一個驗證操作。咱們看看以下的代碼：

CREATE TABLE temp_sales_step1_invalid NOLOGGING PARALLEL AS

SELECT * FROM temp_sales_step1 s

WHERE NOT EXISTS (SELECT 1 FROM product p WHERE p.product_name=s.product_name);

這個CTAS statement語句就可以把查詢出的新的SALE記錄。

咱們也可以做左連結：

CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS

SELECT sales_transaction_id, product.product_id sales_product_id,

   sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold,

   sales_dollar_amount

FROM  temp_sales_step1, product

WHERE temp_sales_step1.upc_code = product.upc_code (+);

把所有在維表中沒有找到product_name的記錄的sales_product_id設定為空。

2：Transformation Mechanisms

   Transformation在oracle大緻有三種方法：

   a)使用sql語句

 方法一：

      CREATE TABLE ... AS SELECT (CTAS) 然後INSERT AS SELECT。

    先按照需求SELECT出來資料然後存在一張臨時表中，然後從臨時表取出然後插入到要load的表中。

    此外（CTAS）方式使用NOLOGGING模式可以提高性能

 方法二：

      Transforming Data Using UPDATE

     你也可以按照你的TRANSFORM規則直接用UPDATE臨時表中的資料。達到轉化的效果。

 方法三：

 Transforming Data Using MERGE

     下面我先以一個例子說明：

 MERGE INTO products t USING products_delta s

 ON (t.prod_id=s.prod_id)

 WHEN MATCHED THEN UPDATE SET

   t.prod_list_price=s.prod_list_price, t.prod_min_price=s.prod_min_price

 WHEN NOT MATCHED THEN INSERT (prod_id, prod_name, prod_desc, prod_subcategory,

   prod_subcategory_desc, prod_category, prod_category_desc, prod_status, 

   prod_list_price, prod_min_price)

 VALUES (s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory, 

   s.prod_subcategory_desc, s.prod_category, s.prod_category_desc, 

   s.prod_status, s.prod_list_price, s.prod_min_price);

       例子中運用MERGE的好處是：擴充維表，因為有一些從外部資料源來的産品資料可能和DW中的維        表中的産品資料有一些重疊，為了擴充維表又保證資料不重複，可以使用MERGE。

      方法四：

 Transforming Data Using Multitable INSERT

      無條件的insert：

 INSERT ALL

    INTO sales VALUES (product_id, customer_id, today, 3, promotion_id,

                      quantity_per_day, amount_per_day)

    INTO costs VALUES (product_id, today, promotion_id, 3,

                      product_cost, product_price)

 SELECT ...FROM..

 有條件的ALL insert：

        INSERT ALL  

        WHEN ...THEN INTO ..TABLE VALUES(...)

   WHEN ...THEN INTO ..TABLE VALUES(...) SELECT ...FROM ...;

      有條件的FRIST insert：

   INSERT FIRST 

   WHEN ...THEN INTO... 

        WHEN ...THEN INTO...

   ELSE INTO ... SELECT...FROM... 

   b）使用PL/SQL

   運用PL/SQL可以處理更加複雜的轉化邏輯，以前我參與的郵政的資料倉庫項目就是直接寫  PL/SQL來完成ETL過程的。

四：裝載

1:using sql*loader

sql*loader是一個很好的從FLAT檔案load資料到DW中來的工具。可以處理非常複雜的LOAD過程。有自己的control file文法

2：External Tables

External Tables是對sql*loader的一個補充，提供了一些進階的功能，它使你像通路資料庫裡的資料一樣通路外部源資料。

我有一個文章也對外部表做了一個簡單的介紹：

http://blog.chinaunix.net/u/25176/showart_2036046.html

外部表和普通表有個功能缺陷是外部表不能做DML（UPDATE/INSERT/DELETE）操作，也不能在外部表上建立索引。