使用Oracle内建功能构建ETL流程

核心提示： 经过上面的步骤，我们将数据库之外的一个平文件通过访问驱动程序ORACLE_LOADER与一个数据库表STENNY_EXT_PRODUCT建立了映射关系，使用Oracle内建功能构建ETL流程(4)，我们对这个外部表可以进行排序，表连接等只读操作，在target_dir目录下可以看到创建的T

经过上面的步骤，我们将数据库之外的一个平文件通过访问驱动程序ORACLE_LOADER与一个数据库表STENNY_EXT_PRODUCT建立了映射关系。我们对这个外部表可以进行排序，表连接等只读操作。

数据清洗与数据整合

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

--proc_txn_productcreateORREPLACEPROCEDUREproc_txn_productASBEGINinsertintostg_productselectproduct_id,product_name,loc_idfromstenny_ext_product,loc_stdwhereloc_std.loc_name=stenny_ext_product.location;insertintostg_excepselect*fromstenny_ext_productwhereproduct_idnotin(selectproduct_idfromstg_product);commit;ENDproc_txn_product

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

SQL>execproc_txn_product;PL/SQLproceduresuccessfullycompleted.SQL>select*fromstg_product;PRODUCT_IDPRODUCT_NAMELOC_ID----------------------------------------1Bicycle12Camps23Wearings34Gloves35Food4　SQL>select*fromstg_excep;　PRODUCT_IDPRODUCT_NAMELOCATION-------------------------------------------------------6ShoesNULLENDproc_txn_product

数据分发 – ORACLE_DATAPUMP

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

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

SQL>edWrotefileafiedt.buf　1createTABLEtgt_product2ORGANIZATIONEXTERNAL(TYPEORACLE_DATAPUMP3DEFAULTDIRECTORYtarget_dir4LOCATION(’tgt_product.dmp’))5PARALLEL26AS7selectproduct_id,8product_name,9loc_id10*FROMstg_productSQL>/　Tablecreated.　SQL>select*fromtgt_product;　PRODUCT_IDPRODUCT_NAMELOC_ID----------------------------------------1Bicycle12Camps23Wearings34Gloves35Food4

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