我对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 countries@source_db t1, customers@source_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 /*+APPEND*/ 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）操作，也不能在外部表上建立索引。