Oracle装载和转换数据01

一，提取，转换和装载概览

在Oracle数据库上运行应用之前，需要填充数据库。数据库数据最常见来源之一时来自旧系统或某些其他来源的一组扁平文件。

过去，使用SQL*Loader进行传统的或直接的数据装载，是把数据从外部文件装入数据库表的唯一方法。SQL*Loader现在仍然是Oracle提供从外部文件装载数据的主要应用程序，不过现在还可以使用外部表特性，该特性利用SQL*Loader工具访问位于外部数据文件中的数据。

因为原始数据可能包含格式不符合应用要求的无直接联系的信息或数据，通常需要在数据库使用它们前进行某种转换。转换数据是对数据仓库的一个特别普遍的要求，因为它是从多个来源所需的数据。在SQL*Loader中可以对原始数据进行一些初步的或基本的转换。但是，更为复杂的数据转换要求有独立的步骤，可从几种技术中任选其一来处理转换过程。在分析数据前，多数数据仓库的数据要经历3个重要的步骤：提取，转换和装载。这些步骤定义如下：

1. 提取（extraction）: 识别和提取原始数据（并非全都是关系型数据库），可能以多种格式，有很多来源

2. 转换（transformation）: 转换数据是3个步骤中最富挑战性和最耗时的，这涉及将复杂规则应用于数据，还包括诸如汇集数据和将函数应用于原始数据等操作

3. 装载（loading）:  装载是指将数据放入数据库表，可能还包括维护表上的索引和约束的任务。

传统上，组织机构使用两种不同的方法来完成ETL过程：转换再装载（transfom-then-load）的方法以及装载再转换（load-then-transform）的方法。前者将数据装入Oracle表之前对其进行清理或转换，并用定制的ETL过程转换数据；后者在多数情形下并不是完全利用Oracle的内建转换功能。在装载再转换的方法中，首先将原始数据装入临时表，在数据库中执行数据转换处理后再将其移动到最终的表。中间临时表是装载再转换方法的关键。这种技术的缺点在于，必须在表中维护多种类型的数据，有的数据处于原始的状态，而有的数据处于最终的状态。

Oracle DataBase 11g提供了极好的ETL能力，支持以一种较新的方法将数据装入数据库，这种方法称为装载时转换(transform-while-loading)方法。通过利用Oracle数据库执行所有的ETL步骤，可有效地完成费时费力的ETL处理。Oracle提供了一整套互补的工具和技术，用来减少数据装入数据库的时间，同时简化所需的操作。Oracle的ETL方案包括下面的部分。

1. 外部表（external table）：外部表提供了一个合并装载和转换过程的方法。使用外部表能够在数据装载中消除一些麻烦的，耗时的中间临时表。

2. 多表插入（multitable insert）: 使用多表插入特性，可同时将数据插入多个表中，各个表使用不同的条件。这种能力省去了首先把数据分成不同的组，然后才进行数据装载的额外步骤。

3. upsert: 这是一个简单拼写成的名字。用来表示可用一条简单的语句merge将数据insert表中，或只是更新(update)行数据。merge语句将插入新数据，如果行已经存在于表中则更新数据。这简化了装载过程，因为无需担心表中是否存在数据。

4. 表函数（table function）: 表函数产生一组行作为输出。表函数返回一个集合类型的实例（嵌套表和VARRAY数据类型）。表函数类似于视图，但不是在SQL中声明性地定义转换，而是在PL\SQL中过程性地定义它。表函数在进行大量复杂的转换时很有用处，因为可以在装载数据到数据仓库前进行转换。

5. 可移植表空间（transportable tablespace）: 这些表空间提供将数据从一个数据库移到另一个数据库的快速有效的方法。例如，可利用可移植表空间在一个OLTP数据库和一个数据仓库之间移植数据。

注：也可以使用OWB（oracle warehouse builder）来有效地装载数据。OWB提供了一个通过SQL * Loader将数据装入数据库的向导驱动的实用程序。OWB可从oracle数据库或扁平文件中装载数据。此外，OWB还可以通过oracle transparent gateways(oracle透明网关)从其他数据库(如Sybase, Informix, 和Microsoft SQL Server等)提取数据。OWB以一种易于使用的格式组合了ETL和设计函数。

二， 使用SQL*Loader使用程序

SQL*Loader 实用程序通常被DBA用于将数据装载到数据库，与oracle数据库服务器一起提供。

SQL * Loader 实用程序的能力：

1. 可在将数据装入数据库前，或者在数据装载中使用SQL * Loader转换数据（有限的能力）

2. 可从多种来源（磁盘，磁带和命名管道）装载数据。还可以在相同的装载会话中使用多个数据文件

3. 可通过网络装载数据

4. 可根据条件从输入文件有选择地进行装载

5. 可以同时进行几组数据的装载

6. 可使装载过程自动化，使它按计划的时间执行

7. 可装载复杂的对象相关的数据

可使用SQL*Loader实用程序完成以下几种类型的数据装载

1. 传统数据装载。在传统数据装载中，SQL* Loader一次读取多个行，并将它们存储在一个绑定的数组中。然后，SQL * Loader将整个数据一次性插入数据库并提交操作。

2. 直接装载。 直接装载方法在装载数据到Oracle表时不使用SQL的insert语句。它先基于要装载的数据构造列数组结构，用这些结构来格式化oracle数据块，然后将其直接写到数据库表。

3. 外部数据装载。oracle的新外部表特性依赖于SQL*Loader的功能访问外部文件中的数据，就像这些数据是数据库表的组成部分一样。

传统和直接装载方法有自己固定的优点和缺点。直接装载方法绕过了oracle SQL机制，它比传统装载方法更快。但是，说到数据转换能力，传统装载方法要比直接装载强多了，因为它允许在装载中应用各种函数。直接装载方法在装载中支持的转换很有限。oracle建议对于少量的数据装载使用传统装载方法，而对于大数据装载才使用直接装载方法。

使用SQL*Loader实用程序装载数据涉及两个主要步骤

1. 选择包含装载数据的数据文件。此数据文件通常以扩展名.dat结尾，包含想要装载的数据，这些数据可能有几种格式。

2. 创建控制文件。控制文件指示SQL*Loader如何映射数据字段到oracle表，并说明数据是否需要以某种方式转换。此控制文件通常用扩展名.ctl结尾。