使用Oracle内建功能构建ETL流程(三)

    数据清洗与数据整合
    在典型的数据仓库系统中，事实表中的大部分字段都采用KEY的形式进行存储。在我们的例子中，我们将对LOCATION列进行LOOKUP，满足地区标准的数据被视为正确数据并附以标准键值；如果出现在标准表中不存在的纪录，我们将认为其为脏数据。脏数据将被另行处理。我们的标准表为LOC_STD，正确记录的Staging表为STG_PRODUCT，异常记录的Staging表为STG_EXCEP。建表语句与测试数据见附录A。在数据文件中，ID为6的产品没有对应的Location，该记录将被视为异常记录。通过一个简单的存储过程，我们将STENNY_EXT_PRODUCT与LOC_STD表进行连接，正确的记录与异常记录将被写入对应的Staging表中。

--proc_txn_product CREATE OR REPLACE PROCEDURE proc_txn_product AS BEGIN insert into stg_product select product_id,product_name,loc_id from stenny_ext_product,loc_std where loc_std.loc_name=stenny_ext_product.location; insert into stg_excep select * from stenny_ext_product where product_id not in (select product_ id from stg_product); commit; END proc_txn_product

    执行存储过程可以得到如下的结果：

SQL> exec proc_txn_product; PL/SQL procedure successfully completed. SQL> select * from stg_product; PRODUCT_ID PRODUCT_NAME LOC_ID ---------- -------------------- ---------- 1 Bicycle 1 2 Camps 2 3 Wearings 3 4 Gloves 3 5 Food 4 SQL> select * from stg_excep; PRODUCT_ID PRODUCT_NAME LOCATION ---------- -------------------- ------------------------- 6 Shoes NULLEND proc_txn_product

    数据分发 ? ORACLE_DATAPUMP
    在经过了上面的抽取、清洗整合的步骤后，我们将使用ORACLE_DATAPUMP将staging表中的数据写入目标文件中。典型的数据仓库系统最终的目标是数据库，而本文中的ECCD流程可以是数据准备的一个过程，众所周知，数据仓库的刷新频率与Staging区域可能不同，目标文件可以被传输到数据仓库系统，并按照其刷新频率进行第二步加载。顺便提一句，DBMS_SCHEDULER可以用来完成在数据库之间进行文件传输。 

    下面的代码演示了如何使用ORACLE_DATAPUMP驱动访问程序unload数据库表的过程。

SQL> ed Wrote file afiedt.buf 1 CREATE TABLE tgt_product 2 ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP 3 DEFAULT DIRECTORY target_dir 4 LOCATION ('tgt_product.dmp')) 5 PARALLEL 2 6 AS 7 SELECT product_id, 8 product_name, 9 loc_id 10* FROM stg_product SQL> / Table created. SQL> select * from tgt_product; PRODUCT_ID PRODUCT_NAME LOC_ID ---------- -------------------- ---------- 1 Bicycle 1 2 Camps 2 3 Wearings 3 4 Gloves 3 5 Food 4

    同时，在target_dir目录下可以看到创建的TGT_PRODUCT.DMP文件，该文件可以被加载到真正的目标数据库，但是必须使用ORACLE_DATAPUMP访问驱动程序。

查看本文来源