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

经常听到客户抱怨一些企业级的数据仓库软件许可证过于昂贵；也曾经见到过一次性的Access到 Oracle的数据迁移就使用了DataStage（这是真的！在发现 Server版本不能连接Access后，还专门购买了DataStage for ODBC --__--!）。实际上一些简单的ETL流程完全可以使用Oracle内建的功能完成。本文的主旨在于说明这一思想，文中代码未必是最佳实践。本文中所有的代码经过了测试，但一些错误与疏忽还是难免的。如果你发现了错误，或者有不同的观点，欢迎讨论。
    概述
    假如把一个完整的数据仓库系统比作一个提供美味菜肴的餐馆，那么ETL系统就是这个餐馆的厨房。在大多数情况下，ETL对最终用户来说是透明的，正像食客往往不需要进入厨房获取服务，厨房也不会对食客开放。但是，ETL模块常常占用整个数据仓库项目70%以上的资源，实际情况也大概也是如此，厨师的工资比侍者要高出很多。
    在当前的数据仓库项目中，企业级ETL工具昂贵的许可证、培训费用常常使许多中小型用户望而却步。真实世界的应用中相当一部分的数据源(E) 都是以文件或数据库的方式提供，进行的转换 (T) 也可以在数据库之内完成，基于这一实际情况，利用Oracle自身的一些功能，我们可以构建相对简单的ETL流程。
    在Oracle9i中引入了外部表(External Table)的概念，通过访问驱动程序ORACLE_LOADER可以对数据库之外的文件进行只读的SQL查询。在Oracle10g中，提供了第二个访问驱动程序ORACLE_DATAPUMP，允许用户对外部表进行写操作，写出的结果文件以特定的结构保存并允许DPAPI访问，即该结果文件可以被加载到另一个数据库中。同时，新的访问驱动程序也支持列对象的投影。
    配合10g中新增的DBMS_SCHEDULER包，可以很方便地实现ETL工具的定时调度功能。这个新的DBMS_SCHEDULER包取代了原有的DBMS_JOB包，并提供了更加强大完善的功能，笔者在本文中仅举一简单示例说明。 　
　

    Ralph Kimball在他的The Data Warehouse ETL Toolkit中提出了ECCD(Extract-Clean-Conform-Deliver)的架构，在此文章中笔者将使用ECCD的四个步骤进行描述：源系统通过FTP提供文件格式的数据源文件，使用ORACLE_LOADER访问驱动程序使用该文件构建外部表（抽取），与数据库中的标准数据表进行校验并写入对应的Staging表（清洗与整合），处理的结果通过ORACLE_DATAPUMP访问驱动程序写入目标文件（分发）。
　　

   准备工作
    在使用外部表之前，首先要建立DIRECTORY对象。同时给需要进行外部表操作的用户赋予适当的权限。

SQL> create OR REPLACE DIRECTORY source_dir as ’C:oracleoradatasource’;   --源文件目录 Directory created. SQL> grant connect, dba to stenny identified by stenny;   Grant succeeded. SQL> create OR REPLACE DIRECTORY source_dir as ’C:oracleoradatasource’;   Directory created. SQL> create OR REPLACE DIRECTORY target_dir as ’C:oracleoradatatarget’;   Directory created. SQL> create OR REPLACE DIRECTORY log_dir as ’C:oracleoradatalog’;   Directory created. SQL> grant read on directory source_dir to stenny;   Grant succeeded. SQL> grant write on directory target_dir to stenny;   Grant succeeded. SQL> grant write on directory log_dir to stenny;   Grant succeeded.