CDC

抽取的一個(gè)重要的方法是增量抽取，使用CDC改變數(shù)據(jù)捕獲。
這節(jié)，將介紹幾種自己實(shí)現(xiàn)的改變數(shù)據(jù)捕獲機(jī)制。

1. 時(shí)間戳
   某些操作型系統(tǒng)中的表含有timestamp列。如果存在時(shí)間戳列，就可以方便地識(shí)別最新數(shù)據(jù)。下例中的查詢可以方便地從order表中抽取今天的數(shù)據(jù)。

    SELECT * FROM orders 
    WHERE TRUNC(CAST(order_date AS date),'dd') = 
      TO_DATE(SYSDATE,'dd-mon-yyyy');
   

2. Partition分割
   有些系統(tǒng)可能使用范圍分割，使得源表根據(jù)日期鍵分割，這樣也可以方便地識(shí)別改變數(shù)據(jù)。

3. Trigger觸發(fā)器
   Oracle推薦使用synchronous Change Data Capture 這一基于觸發(fā)器的改變數(shù)據(jù)捕獲技術(shù)。

抽取例子

使用數(shù)據(jù)文件進(jìn)行抽取

大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)提供了從內(nèi)部數(shù)據(jù)庫(kù)格式導(dǎo)出或者卸載數(shù)據(jù)到普通文件的機(jī)制，和工具。
當(dāng)源系統(tǒng)是Oracle數(shù)據(jù)庫(kù)時(shí)，可以使用下面幾種方法來(lái)將數(shù)據(jù)抽取到文件中：

• 使用SQL*Plus
  使用select語(yǔ)句抽取有用數(shù)據(jù)，然后將結(jié)果重定向?qū)懭氲轿募小?br> SET echo off SET pagesize 0 SPOOL country_city.log
  SELECT distinct t1.country_name ||'|'|| t2.cust_city
  FROM countries t1, customers t2 WHERE t1.country_id = t2.country_id
  AND t1.country_name= 'United States of America';
  SPOOL off

這種抽取結(jié)束可以被并行化，每個(gè)對(duì)話查詢?cè)磾?shù)據(jù)的不同partition部分。被抽取的文件分開(kāi)存儲(chǔ)在獨(dú)立文件中，可以通過(guò)SQLLoader來(lái)并行地載入到數(shù)據(jù)庫(kù)中。
與SQLPlus不同的是，使用外部表external table data pump unload功能可以實(shí)現(xiàn)透明的并行能力。

• 使用OCI或者 ProC程序
  OCI為Oracle調(diào)用接口，如ProC程序。

• 使用Export工具
  Export文件既包含數(shù)據(jù)也包含元數(shù)據(jù)；一個(gè)export文件可以包含一個(gè)對(duì)象的子集、許多數(shù)據(jù)庫(kù)對(duì)象、甚至整個(gè)數(shù)據(jù)庫(kù)schema模式；Export只能抽取數(shù)據(jù)庫(kù)對(duì)象的子集，而不能抽取復(fù)雜查詢語(yǔ)句的結(jié)果；Export的輸出結(jié)果只能被Import工具處理。
  Oracle提供原生的Export和Import工具來(lái)向前兼容，并提供data pump export/import infrastructure進(jìn)行高性能、可擴(kuò)展且并行化的抽取。

• 使用External Tables外部表
  除了使用Export工具你還可以使用外部表來(lái)抽取任何Select操作的結(jié)果。下面的例子演示了并行抽取一個(gè)連接操作的結(jié)果到四個(gè)文件中。

    CREATE DIRECTORY def_dir AS '/net/dlsun48/private/hbaer/WORK/FEATURES/et';
    DROP TABLE extract_cust;
    CREATE TABLE extract_cust
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY def_dir ACCESS PARAMETERS
    (NOBADFILE NOLOGFILE)
    LOCATION ('extract_cust1.exp', 'extract_cust2.exp', 'extract_cust3.exp',
              'extract_cust4.exp'))
    PARALLEL 4 REJECT LIMIT UNLIMITED AS
    SELECT  c.*, co.country_name, co.country_subregion, co.country_region 
    FROM customers c, countries co where co.country_id=c.country_id;
  

通過(guò)分布式操作進(jìn)行抽取

使用分布式查詢技術(shù)，一個(gè)Oracle數(shù)據(jù)庫(kù)可以直接查詢位于不同原系統(tǒng)中的表，比如另一個(gè)數(shù)據(jù)庫(kù)或者一個(gè)舊的使用Oracle gateway技術(shù)連接的系統(tǒng)。這種方法可以吧抽取和傳輸結(jié)合起來(lái)。比如使用一個(gè)Oracle Net連接和distributed-query，可以用一個(gè)SQL語(yǔ)句實(shí)現(xiàn)，將employee names 和 department names數(shù)據(jù)抽取并存儲(chǔ)到數(shù)據(jù)倉(cāng)庫(kù)。

    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';

這個(gè)語(yǔ)句創(chuàng)建了一個(gè)本地的數(shù)據(jù)集市country_city，并用countries和customers中的數(shù)據(jù)進(jìn)行填充。
這種技術(shù)是進(jìn)行少量數(shù)據(jù)轉(zhuǎn)移的理想方式。然而，由于數(shù)據(jù)傳輸只使用了一個(gè)Oracle NET connection，所以其可擴(kuò)展性受限。對(duì)于更大的數(shù)據(jù)量，基于文件的數(shù)據(jù)抽取和傳輸方式可擴(kuò)展性好，更合適。

第14章 傳輸數(shù)據(jù)

數(shù)據(jù)傳輸總覽

數(shù)據(jù)倉(cāng)庫(kù)環(huán)境下，最常見(jiàn)的數(shù)據(jù)轉(zhuǎn)移需求在于：

• 從源系統(tǒng)傳輸?shù)綍捍鏀?shù)據(jù)庫(kù)或到一個(gè)數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)
• 從暫存數(shù)據(jù)庫(kù)到數(shù)據(jù)倉(cāng)庫(kù)
• 從數(shù)據(jù)倉(cāng)庫(kù)到數(shù)據(jù)集市

數(shù)據(jù)傳輸時(shí)ETL過(guò)程中最簡(jiǎn)單的部分，甚至可以被集成到ETL過(guò)程中的其他部分，如上一章介紹到的分布式查詢技術(shù)提供了一種抽取和傳輸數(shù)據(jù)的機(jī)制。

數(shù)據(jù)倉(cāng)庫(kù)中的傳輸機(jī)制介紹

使用Flat Files普通文件

傳輸數(shù)據(jù)最常用的方法是傳輸flat files普通文件，可以使用FTP或其它任何遠(yuǎn)程文件系統(tǒng)訪問(wèn)協(xié)議。由于源系統(tǒng)和目標(biāo)系統(tǒng)往往使用的是不同的操作系統(tǒng)和數(shù)據(jù)庫(kù)系統(tǒng)，使用flat files往往是在異構(gòu)系統(tǒng)中交換數(shù)據(jù)的最簡(jiǎn)單的方法（不需要太多的轉(zhuǎn)換）。而且，即使是在同構(gòu)的系統(tǒng)之間傳輸數(shù)據(jù)，flat files也往往是最高效、最好管理的數(shù)據(jù)傳輸機(jī)制。

使用分布式操作

如上一章分布式查詢方法的介紹，缺點(diǎn)是耗時(shí)，吃資源。

使用Transportable Tablespace可傳輸表空間

Oracle Transportable Tablespace是在兩個(gè)Oracle數(shù)據(jù)庫(kù)之間傳輸數(shù)據(jù)的最快的方法。之前講到，最可擴(kuò)展的數(shù)據(jù)傳輸機(jī)制是移動(dòng)flat files普通文件，而這些技術(shù)需要先進(jìn)行數(shù)據(jù)的export，傳輸之后還要進(jìn)行import操作。而可傳輸表空間則直接省去了unload和reload步驟。

使用可傳輸表空間，Oracle數(shù)據(jù)文件（包含表數(shù)據(jù)、索引、幾乎所有其它Oracle數(shù)據(jù)庫(kù)對(duì)象）可以從一個(gè)系統(tǒng)傳到另一個(gè)系統(tǒng)。另外，類似import和export，oracle Transportable Tablespace提供了在傳輸數(shù)據(jù)的同時(shí)傳輸元數(shù)據(jù)的機(jī)制。
可傳輸表空間最常見(jiàn)的應(yīng)用時(shí)staging database --> a data warehouse，和data warehouse --> a data mart的數(shù)據(jù)傳輸。

可傳輸表空間的例子

假設(shè)你有一個(gè)包含銷(xiāo)售數(shù)據(jù)的數(shù)據(jù)倉(cāng)庫(kù)以及幾個(gè)每月更新的數(shù)據(jù)集市。并假設(shè)你正要從數(shù)據(jù)倉(cāng)庫(kù)向數(shù)據(jù)集市中移動(dòng)數(shù)據(jù)。

• step1 將要傳輸?shù)臄?shù)據(jù)放入自己的表空間
  本月的數(shù)據(jù)必須放入單獨(dú)的表空間以用于傳輸。本例中，你有一個(gè)表空間ts_temp_sales，其中包含了一份本月的數(shù)據(jù)。使用create table ... as select語(yǔ)句，本月的數(shù)據(jù)可以高效地拷貝到這個(gè)表空間中。

    CREATE TABLE temp_jan_sales NOLOGGING TABLESPACE ts_temp_sales
    AS SELECT * FROM sales 
    WHERE time_id BETWEEN '31-DEC-1999' AND '01-FEB-2000';
  

然后，將ts_temp_sales表空間設(shè)置為只讀（表空間只有在沒(méi)有活動(dòng)的事務(wù)的時(shí)候才能被傳輸，設(shè)置為只讀強(qiáng)制實(shí)現(xiàn)這一要求）。

    ALTER TABLESPACE ts_temp_sales READ ONLY;

在可傳輸表空間操作中，表空間中的所有對(duì)象都被傳輸。多個(gè)表可以在同一個(gè)表空間中被傳輸，同時(shí)這個(gè)表空間中還可能包含索引等其它數(shù)據(jù)庫(kù)對(duì)象。
本例中，我們創(chuàng)建了一個(gè)臨時(shí)的表空間，如果要傳輸?shù)臄?shù)據(jù)本來(lái)就在一個(gè)獨(dú)立地表空間中，那么該表空間也是可以被傳輸?shù)亩恍枰~外創(chuàng)建臨時(shí)表空間。

• step 2 導(dǎo)出元數(shù)據(jù)
  我們將用到Export工具來(lái)導(dǎo)出描述可傳輸表空間中的對(duì)象的元數(shù)據(jù)。在本例場(chǎng)景下，導(dǎo)出操作是：

    EXP TRANSPORT_TABLESPACE=y TABLESPACES=ts_temp_sales FILE=jan_sales.dmp
  

這一操作生成了一個(gè)導(dǎo)出文件jan_sales.dmp，該文件很小，因?yàn)橹话藅emp_jan_sales表的描述信息，如列名，列類型和其他所有目標(biāo)Oracle數(shù)據(jù)庫(kù)系統(tǒng)訪問(wèn)ts_temp_sales所需要的信息。

• step 3 拷貝數(shù)據(jù)文件和包含元數(shù)據(jù)的Export文件到目標(biāo)系統(tǒng)
  使用任何普通文件傳輸機(jī)制，拷貝構(gòu)成ts_temp_sales的數(shù)據(jù)文件和包含元數(shù)據(jù)的Export文件jan_sales.dmp到數(shù)據(jù)集市平臺(tái)?？截愅瓿珊?，如有需要，表空間ts_temp_sales可以重新被設(shè)置為READ WRITE模式。

• step 4 導(dǎo)入元數(shù)據(jù)
  一旦文件被拷貝到數(shù)據(jù)集市中，就可以將元數(shù)據(jù)import到數(shù)據(jù)集市。
  IMP TRANSPORT_TABLESPACE=y DATAFILES='/db/tempjan.f'
  TABLESPACES=ts_temp_sales FILE=jan_sales.dmp

現(xiàn)在，你就可以將新數(shù)據(jù)合并到數(shù)據(jù)集市中的表里。你有兩種方式將temp_sales_jan表中的數(shù)據(jù)插入到數(shù)據(jù)集市的sales表中：
方法一：

    INSERT /*+ APPEND */ INTO sales SELECT * FROM temp_sales_jan;

方法二：
如果數(shù)據(jù)集市的sales表本來(lái)就是依據(jù)月份分割的，那么這個(gè)可傳輸表空間就可以直接成為數(shù)據(jù)集市中的一個(gè)永久部分，而temp_sales_jan 表將成為數(shù)據(jù)集市sales表的一個(gè)分割：

    ALTER TABLE sales ADD PARTITION sales_00jan VALUES
      LESS THAN (TO_DATE('01-feb-2000','dd-mon-yyyy'));
    ALTER TABLE sales EXCHANGE PARTITION sales_00jan 
      WITH TABLE temp_sales_jan INCLUDING INDEXES WITH VALIDATION;

第15章 裝載和轉(zhuǎn)換

本章將幫助你創(chuàng)建和管理數(shù)據(jù)倉(cāng)庫(kù)。

數(shù)據(jù)加載和轉(zhuǎn)換總覽

數(shù)據(jù)的轉(zhuǎn)換時(shí)ETL過(guò)程中最復(fù)雜的過(guò)程，覆蓋簡(jiǎn)單的conversion數(shù)據(jù)變換，到復(fù)雜的scrubbing數(shù)據(jù)擦洗。許多數(shù)據(jù)轉(zhuǎn)換工作，但不是所有，是在Oracle數(shù)據(jù)庫(kù)中發(fā)生的。

本章介紹在Oracle數(shù)據(jù)庫(kù)中實(shí)現(xiàn)可擴(kuò)展、高效的數(shù)據(jù)轉(zhuǎn)換。本章不覆蓋所有轉(zhuǎn)換技術(shù)，但是卻介紹了最基本的方法（more scalability and less programming）以及如何選擇這些方法。

轉(zhuǎn)換流程

從結(jié)構(gòu)的角度來(lái)分，你可以以2種方式（流程）來(lái)轉(zhuǎn)換數(shù)據(jù)：

• Multistage Data Teansformation多暫存區(qū)數(shù)據(jù)轉(zhuǎn)換
  大多數(shù)數(shù)據(jù)倉(cāng)庫(kù)的轉(zhuǎn)換邏輯由多個(gè)步驟組成。比如，在將新記錄插入到sales表中是，可能存在多個(gè)獨(dú)立地轉(zhuǎn)換步驟來(lái)處理每個(gè)維度的鍵。

Multistage Data Transformation

當(dāng)使用Oracle數(shù)據(jù)庫(kù)作為轉(zhuǎn)換引擎是，一個(gè)常見(jiàn)的策略是用單獨(dú)的SQL操作實(shí)現(xiàn)每個(gè)轉(zhuǎn)換，并創(chuàng)建一個(gè)獨(dú)立地、臨時(shí)的暫存表。這種load-then-transfer的策略相當(dāng)于設(shè)置了自然的checkpoint，方便監(jiān)督和重啟，缺點(diǎn)是需要額外的時(shí)間和空間。

• Pipelined Data Transformation管道化數(shù)據(jù)傳輸
  將一系列的transform-then-load（此為大多數(shù)轉(zhuǎn)換在數(shù)據(jù)庫(kù)外進(jìn)行的情形）或load-then-transform過(guò)程，變?yōu)閠ransfer-while-loading。
  下圖演示了這一功能（下文主要介紹之）。

Pipelined data transformation

• Staging Area暫存區(qū)
  對(duì)于Load加載而言，速度取決于原始數(shù)據(jù)從staging area寫(xiě)到目標(biāo)表中的速度。強(qiáng)烈推薦使用盡量多的物理磁盤(pán)來(lái)暫存原始數(shù)據(jù)，以防止讀取數(shù)據(jù)不會(huì)成為加載的瓶頸。
  一個(gè)暫存數(shù)據(jù)的絕佳地點(diǎn)是Oracle Database File System（DBFS）

裝載機(jī)制

使用SQL*Loader裝載

傳輸數(shù)據(jù)最常用的技術(shù)時(shí)使用flat files普通文件。
SQLLoader用來(lái)將數(shù)據(jù)從普通文件導(dǎo)入到Oracle數(shù)據(jù)倉(cāng)庫(kù)中。在這一過(guò)程中，SQLLoader也可以用于進(jìn)行基本的數(shù)據(jù)轉(zhuǎn)換。
當(dāng)使用direct-path SQL*Loader時(shí)，基本的數(shù)據(jù)操作如類型轉(zhuǎn)換和NULL值處理等可以在數(shù)據(jù)裝載時(shí)處理。大多數(shù)數(shù)據(jù)倉(cāng)庫(kù)出于性能的考慮使用直接路徑加載（配合最小日志nologging設(shè)置）。

Oracle提供兩種類型的插入語(yǔ)句：常規(guī)插入（conventional insert）和直接路徑插入（direct-path insert），直接路徑插入的目的是為了高效地加載大量的數(shù)據(jù)，它以犧牲部分功能為代價(jià)，因此受到很多的限制。直接加載的使用方法是：1）在SQL語(yǔ)句中加append提升；2）并行地執(zhí)行SQL語(yǔ)句。只有insert inot ... select ... 語(yǔ)句、merge語(yǔ)句和使用OCI直接路徑接口的應(yīng)用程序才可以使用。

下面是裝載sales表的控制文件（sh_sales.ctl）:

    LOAD DATA INFILE sh_sales.dat APPEND INTO TABLE sales
    FIELDS TERMINATED BY "|"
    (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)

可以通過(guò)下面的命令來(lái)執(zhí)行載入：

    $  sqlldr control=sh_sales.ctl direct=true
    Username:
    Password:

使用external tables外部表裝載

另一種處理外部數(shù)據(jù)資源的方法是使用外部表。Oracle的外部表特性使你可以把外部當(dāng)做虛擬表，并在無(wú)需先被載入到數(shù)據(jù)哭的情況下，直接并行地進(jìn)行查詢和連接操作。
外部表使得我們可以將裝載和轉(zhuǎn)換階段管道化起來(lái)，將轉(zhuǎn)換過(guò)程合并到加載過(guò)程中。
外部表和普通表的區(qū)別在于externally organized tables外部組織的表是只讀的，不能對(duì)其進(jìn)行DML操作(UPDATE/INSERT/DELETE)也不能對(duì)其索引。
你可以創(chuàng)建一個(gè)代表完整的交易事務(wù)數(shù)據(jù)的外部表sales_transactions_ext，并存在外部文件sh_sales.gz
中。產(chǎn)品部門(mén)對(duì)產(chǎn)品和時(shí)間分析很感興趣。因此我們?cè)趕h模式下創(chuàng)建了一個(gè)cost事實(shí)表。建cost表的數(shù)據(jù)源和建立sales事實(shí)表的數(shù)據(jù)源相同，但是由于舍棄了多個(gè)維度，所以數(shù)據(jù)相對(duì)粗糙，需要進(jìn)行聚集。
外部表對(duì)這種情緒提供了一種解決方案。不像SQL*Loader那樣需要在應(yīng)用聚集函數(shù)之前裝載數(shù)據(jù)，外部表運(yùn)行你將裝載和轉(zhuǎn)換結(jié)合到一條SQL語(yǔ)句中，也不用暫存數(shù)據(jù)。
下例演示基本的外部表操作。

    CREATE TABLE sales_transactions_ext
    (PROD_ID NUMBER, CUST_ID NUMBER,
     TIME_ID DATE, CHANNEL_ID NUMBER,
     PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
     AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),
     UNIT_PRICE NUMBER(10,2))
    ORGANIZATION external (TYPE oracle_loader
      DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        PREPROCESSOR EXECDIR:'zcat' 
        BADFILE log_file_dir:'sh_sales.bad_xt'
        LOGFILE log_file_dir:'sh_sales.log_xt'
        FIELDS TERMINATED BY "|" LDRTRIM
        ( PROD_ID, CUST_ID,
          TIME_ID         DATE(10) "YYYY-MM-DD", 
          CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
          UNIT_COST, UNIT_PRICE))
      location ('sh_sales.gz') 
    )REJECT LIMIT UNLIMITED;

object?directories?對(duì)象目錄必須已經(jīng)存在，并指向包含sh_sales.gz和包含bad and log文件的目錄。
然后，外部表就可以在數(shù)據(jù)庫(kù)中使用了，

    INSERT /*+ APPEND */ INTO COSTS
    (TIME_ID, PROD_ID, UNIT_COST, UNIT_PRICE)
    SELECT TIME_ID, PROD_ID, AVG(UNIT_COST), AVG(amount_sold/quantity_sold)
    FROM sales_transactions_ext GROUP BY time_id, prod_id;

使用OCI和Direct-path API裝載

在數(shù)據(jù)庫(kù)外進(jìn)行轉(zhuǎn)換和計(jì)算，不需要flat file staging。

使用Export/import裝載

參見(jiàn)Chapter 13 Extraction in Data Warehouses。

轉(zhuǎn)換機(jī)制

使用SQL進(jìn)行轉(zhuǎn)換

• CREATE TABLE ... AS SELECT And INSERT /+APPEND/ AS SELECT
  或簡(jiǎn)稱CTAS，結(jié)合nologging實(shí)現(xiàn)高性能。

    INSERT /*+ APPEND NOLOGGING PARALLEL */
    INTO sales SELECT product_id, customer_id,               TRUNC(sales_date), 3,
           promotion_id, quantity, amount
    FROM sales_activity_direct;
  

• 使用update
  只在少量數(shù)據(jù)需要改變的情況下合適。

• 使用merge（upsert）
  新數(shù)據(jù)需要插入或者更新。

    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);
  

• 使用multitable insert多表插入
  經(jīng)常，需要將外部數(shù)據(jù)資源依據(jù)邏輯屬性進(jìn)行分割，插入到不同的目標(biāo)對(duì)象中，又或者需要從數(shù)據(jù)倉(cāng)庫(kù)扇出數(shù)據(jù)到多個(gè)目標(biāo)對(duì)象。
  使用INSERT ...?SELECT，新的語(yǔ)句可以被并行化，并使用direct load mechanism達(dá)到更高的性能。

    INSERT ALL
    WHEN promotion_id IN (SELECT promo_id FROM promotions) THEN
       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)
    WHEN num_of_orders > 1 THEN
       INTO cum_sales_activity VALUES (today, product_id, customer_id,
         promotion_id, quantity_per_day, amount_per_day, num_of_orders)
    SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id,
       s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity)
       quantity_per_day, COUNT(*) num_of_orders, p.prod_min_price*0.8
       AS product_cost, p.prod_list_price AS product_price
    FROM sales_activity_direct s, products p
    WHERE s.product_id = p.prod_id
    AND TRUNC(sales_date) = TRUNC(SYSDATE)
    GROUP BY TRUNC(sales_date), s.product_id, s.customer_id,
     s.promotion_id, p.prod_min_price*0.8, p.prod_list_price;
  

使用PL/SQL進(jìn)行轉(zhuǎn)換

PL/SQL過(guò)程可以打開(kāi)多個(gè)游標(biāo)來(lái)讀取多個(gè)源表的數(shù)據(jù)。PL/SQL提供了過(guò)程化處理的機(jī)制，可以將一個(gè)轉(zhuǎn)換封裝到一個(gè)過(guò)程中，而表函數(shù)在其基礎(chǔ)上將結(jié)果無(wú)縫的銜接起來(lái)。TABLE（function()）

使用表函數(shù)進(jìn)行轉(zhuǎn)換

表函數(shù)提供了管道化并行執(zhí)行轉(zhuǎn)換的支持（implemented in PL/SQL, C, or Java.）。

表函數(shù)是一個(gè)產(chǎn)生a set of rows多行數(shù)據(jù)集，并可以多行數(shù)據(jù)集為輸入的函數(shù)。
下圖演示了一個(gè)進(jìn)行聚集操作的表函數(shù)的例子。

表函數(shù)的例子

另外，表函數(shù)可以在其內(nèi)部的原子事務(wù)中扇出數(shù)據(jù)，如下圖：

管道化同步轉(zhuǎn)換（帶數(shù)據(jù)扇出）

1. 預(yù)先工作：創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象類型（as object） 、和對(duì)應(yīng)游標(biāo)包（as record）。
2. 編寫(xiě)表函數(shù)tf，輸入為游標(biāo)，輸出為table of product_t 。
3. TABLE（tf（））可以被當(dāng)做表來(lái)處理（或者等價(jià)于select的結(jié)果）。

當(dāng)使用強(qiáng)類型的游標(biāo)作為參數(shù)時(shí)，可以進(jìn)行并行化（ALTER TABLE products PARALLEL 4;）。另外表函數(shù)結(jié)合可傳輸表空間、游標(biāo)、PIPELINED和PIPE ROW 可以實(shí)現(xiàn)增量的處理。

錯(cuò)誤記錄和處理機(jī)制

有兩種主要的錯(cuò)誤：

Business Rule?Violations業(yè)務(wù)規(guī)則違例

• 使用SQL語(yǔ)句過(guò)濾不和規(guī)則的數(shù)據(jù)。
• 識(shí)別并分離，如table function中將錯(cuò)誤數(shù)據(jù)導(dǎo)出額外的數(shù)據(jù)表中。

Data Rule?Violations (Data Errors)數(shù)據(jù)規(guī)則違例

數(shù)據(jù)錯(cuò)誤只能使用PL/SQL，還可以將數(shù)據(jù)錯(cuò)誤記錄到特殊的錯(cuò)誤表中而運(yùn)行DML操作繼續(xù)執(zhí)行。

• SQL中處理數(shù)據(jù)錯(cuò)誤

    DECLARE
    errm number default 0;
    BEGIN
    FOR crec IN (SELECT product_id, customer_id, TRUNC(sales_date) sd,
                       promotion_id, quantity, amount
                 FROM sales_activity_direct) loop
     
    BEGIN
     INSERT INTO sales VALUES (crec.product_id, crec.customer_id,
                               crec.sd, 3, crec.promotion_id,
                               crec.quantity, crec.amount);
    exception
    WHEN others then
     errm := sqlerrm;
     INSERT INTO sales_activity_error
            VALUES (errm, crec.product_id, crec.customer_id, crec.sd,
                    crec.promotion_id, crec.quantity, crec.amount);
    END;
    END loop;
    END;
    /
  

• 使用錯(cuò)誤日志表來(lái)處理數(shù)據(jù)錯(cuò)誤
  使用Oracle DBMS_ERRLOG包來(lái)創(chuàng)建DML錯(cuò)誤記錄表sales_activity_errors。
  用法：

    INSERT /*+ APPEND PARALLEL */
    INTO sales SELECT product_id, customer_id,         TRUNC(sales_date), 3,
       promotion_id, quantity, amount
    FROM sales_activity_direct
    LOG ERRORS INTO         sales_activity_errors('load_20040802')
    REJECT LIMIT UNLIMITED
  

加載和轉(zhuǎn)換場(chǎng)景

下面是一些典型的裝載和轉(zhuǎn)換任務(wù)的例子：

鍵查詢場(chǎng)景

比如，假設(shè)銷(xiāo)售交易數(shù)據(jù)被裝載到零售數(shù)據(jù)倉(cāng)庫(kù)。雖然數(shù)據(jù)倉(cāng)庫(kù)sales表包含product_id列，但是，源表中抽取出來(lái)的數(shù)據(jù)對(duì)應(yīng)的是Uniform Price Codes (UPC)列。
為了執(zhí)行這一轉(zhuǎn)換，一個(gè)關(guān)聯(lián)product_id和Uniform Price Codes (UPC)的?查詢表必須被建立，這個(gè)表就是我們的product維度表。這一轉(zhuǎn)換可以用下面的CTAS語(yǔ)句實(shí)現(xiàn)：

    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;

商業(yè)規(guī)則違例場(chǎng)景

假設(shè)有些數(shù)據(jù)不合規(guī)范，沒(méi)有UPC列。有以下解決方案：

• 使用CTAS 將違例數(shù)據(jù)寫(xiě)入額外的一張temp_sales_step1_invalid表總。
• 仍然使用CTAS，使用外連接，將那些upc_code為空的項(xiàng)設(shè)為null加入
• 使用insert/*+ APPEND PARALLEL */ first when into... else into .... select同時(shí)實(shí)現(xiàn)上述兩步。

數(shù)據(jù)錯(cuò)誤場(chǎng)景

使用insert/*+ APPEND PARALLEL */ first when into... else into .... select
最后加上錯(cuò)誤日志記錄。
LOG ERRORS INTO sales_step2_errors('load_20040804')
REJECT LIMIT UNLIMITED;

pivoting旋轉(zhuǎn)場(chǎng)景

假設(shè)數(shù)據(jù)如下：
SELECT * FROM sales_input_table;

    PRODUCT_ID CUSTOMER_ID WEEKLY_ST  SALES_SUN  SALES_MON  SALES_TUE  SALES_WED SALES_THU  SALES_FRI  SALES_SAT
    ---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
           111         222 01-OCT-00        100        200        300        400       500        600        700
           222         333 08-OCT-00        200        300        400        500       600        700        800
           333         444 15-OCT-00        300        400        500        600       700        800        900

我們要把它轉(zhuǎn)化為一個(gè)典型的關(guān)系型數(shù)據(jù)表sales中，如下：

    SELECT prod_id, cust_id, time_id, amount_sold FROM sales;

       PROD_ID    CUST_ID   TIME_ID   AMOUNT_SOLD
    ---------- ----------   --------- -----------
           111        222   01-OCT-00         100
           111        222   02-OCT-00         200
           111        222   03-OCT-00         300
           111        222   04-OCT-00         400
           111        222   05-OCT-00         500
           111        222   06-OCT-00         600
           111        222   07-OCT-00         700
           222        333   08-OCT-00         200
           222        333   09-OCT-00         300
           222        333   10-OCT-00         400
           222        333   11-OCT-00         500
           222        333   12-OCT-00         600
           222        333   13-OCT-00         700
           222        333   14-OCT-00         800
           333        444   15-OCT-00         300
           333        444   16-OCT-00         400
           333        444   17-OCT-00         500
           333        444   18-OCT-00         600
           333        444   19-OCT-00         700
           333        444   20-OCT-00         800
           333        444   21-OCT-00         900

操作的方法是使用insert all into... into... select，代碼如下：

INSERT ALL INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date, sales_sun)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
      INTO sales (prod_id, cust_id, time_id, amount_sold)
      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
      sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;

第16章 維護(hù)數(shù)據(jù)倉(cāng)庫(kù)

第17章 CDC改變數(shù)據(jù)捕獲