1. ユーティリティ
  2. 外部表
  3. 外部表の概要

14 外部表の概要

外部表機能は、既存のSQL*Loader機能を補足する機能です。この機能により、データベース内の表にあるデータと同様に、外部ソースのデータにアクセスできるようになります。

  • 外部表の作成方法
    外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。
  • データ・ファイルおよび出力ファイルの位置
    データ・ファイルおよび出力ファイルはサーバーに配置する必要があります。ファイルの読取りおよび書込みを行う位置を指定するディレクトリ・オブジェクトが必要です。
  • 外部表のアクセス・パラメータ
    外部表のアクセス・ドライバのデフォルトの動作を変更するには、アクセス・パラメータを指定します。
  • 外部表使用中のデータ型変換
    ソースとターゲットのデータ型が一致しない場合、Oracle Databaseが外部表から読み取るとき、および外部表に書き込むときに変換エラーが発生する可能性があります。

親トピック: 外部表

14.1 外部表の作成方法

外部表は、SQLのCREATE TABLE...ORGANIZATION EXTERNAL文を使用して作成されます。

データ・ロード時にステージング表の追加の索引付けが必要な場合、SQL*Loaderを使用する方が有効です。SQL*Loaderと外部表との処理内容の違いについては、「SQL*Loaderと外部表との処理内容の違い」を参照してください。

Oracle Database 12cリリース2 (12.2.0.1)からは、外部表に含まれるデータをパーティション化でき、これにより、データベースに格納された表をパーティション化した場合(パーティション・プルーニングなど)と同様にパフォーマンスを高めることができます。

関連項目:

外部表の作成と管理の詳細、および外部表のパーティション化の詳細は、『Oracle Database管理者ガイド』を参照してください。

ノート:

外部表はSQL文のインライン外部表として使用できるため、データ・ディクショナリ内に永続データベース・オブジェクトとして外部表を作成する必要がなくります。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

外部表の作成時に、次の属性を指定します。

  • TYPE—外部表の型を指定します。外部表の各型は、固有のアクセス・ドライバによってサポートされます。

    • ORACLE_LOADER—これがデフォルトのアクセス・ドライバです。これによって、外部表から内部表にデータがロードされます。データはテキスト・データ・ファイルからのものである必要があります。(ORACLE_LOADERアクセス・ドライバは、アンロードを実行できないため、内部表から外部表にデータを移動することはできません。)

    • ORACLE_DATAPUMP—このアクセス・ドライバは、ロードとアンロードの両方を実行できます。データは、バイナリのダンプ・ファイルからロードする必要があります。内部表から外部表へのロードは、バイナリのダンプ・ファイルからフェッチすることによって実行されます。内部表から外部表へのアンロードは、外部表のバイナリのダンプ・ファイルを移入することによって実行されます。ORACLE_DATAPUMPアクセス・ドライバは、SQLのCREATE TABLE AS SELECT文を使用した外部表の作成の一環としてのみダンプ・ファイルにデータを書き込むことができます。作成されたダンプ・ファイルは、何回でも読み取ることができますが、変更はできません(つまり、DML操作は実行できません)。

    • ORACLE_HDFS—Hadoop Distributed File System (HDFS)に格納されたデータを抽出します。

    • ORACLE_HIVE—Apache HIVEに格納されたデータを抽出します。

  • DEFAULT DIRECTORY—明示的にディレクトリ・オブジェクトに名前を付けないすべての入出力ファイルに対して、使用するデフォルトのディレクトリを指定します。位置はディレクトリ・パスではなく、ディレクトリ・オブジェクトで指定されます。外部表を作成する前にディレクトリ・オブジェクトを作成しておく必要があります(これを行わない場合はエラーが生成されます)。詳細は、「データ・ファイルおよび出力ファイルの位置」を参照してください。

  • ACCESS PARAMETERS—外部データ・ソースを記述し、指定された外部表の型を実装します。各外部表の型には、その外部表の型に固有のアクセス・パラメータを提供する専用のアクセス・ドライバがあります。アクセス・パラメータはオプションです。詳細は、「アクセス・パラメータ」を参照してください。

  • LOCATION—外部表のデータ・ファイルを指定します。

    • ORACLE_LOADERおよびORACLE_DATAPUMPの場合、ファイルの名前はdirectory:fileという形式になります。directoryの部分はオプションです。この部分を指定しないと、デフォルトのディレクトリがファイルのディレクトリとして使用されます。ORACLE_LOADERアクセス・ドライバを使用している場合には、ファイル名にはワイルドカードを使用でき、アスタリスク(*)は複数文字を、疑問符(?)は1文字を表します。

    • ORACLE_HDFSの場合、LOCATION句は、ディレクトリまたはファイルのUniform Resource Identifier (URI)のリストです。URIに関連付けられたディレクトリ・オブジェクトはありません。

    • ORACLE_HIVEの場合、LOCATION句は使用されません。かわりに、Hadoop HCatalog表を読み取ってデータ・ソースの場所(ファイルや別のデータベース)に関する情報を取得します。

次の各例に、各アクセス・ドライバの属性の簡単な使用方法を示します。

例14-1 ORACLE_LOADERアクセス・ドライバの属性の指定

次の例では、ORACLE_LOADERアクセス・ドライバを使用して、これらの各属性の使用方法を示しています(デフォルト・ディレクトリdef_dir1はすでに存在するものとします)。 

SQL> CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );
 
Table created.

アクセス・ドライバで指定する情報により、データ・ソースのデータが、外部表の定義と一致するように処理されます。CREATE TABLE emp_loadの後にリストされるフィールドが、実際にinfo.datソース・ファイル内のデータのメタデータを定義します。

例14-2 ORACLE_DATAPUMPアクセス・ドライバの属性の指定

この例では、inventories_xtという名前の外部表を作成し、外部表のダンプ・ファイルにoeサンプル・スキーマ内のinventories表のデータを移入します。 

SQL> CREATE TABLE inventories_xt
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_DATAPUMP
5 DEFAULT DIRECTORY def_dir1
6 LOCATION ('inv_xt.dmp')
7 )
8 AS SELECT * FROM inventories;
Table created.

例14-3 ORACLE_HDFSアクセス・ドライバの属性の指定

CREATE TABLE sales_external
(   time_id           DATE NOT NULL, …
    amount_sold       NUMBER(10,2)
)
ORGANIZATION EXTERNAL 
(  TYPE ORACLE_HDFS
   ACCESS PARAMETERS (com.oracle.bigdata.cluster=hadoop1)
   LOCATION (“hdfs:/usr/sales_1.csv”, “hdfs:/usr/my_sales_*.csv”)
)

例14-4 ORACLE_HIVEアクセス・ドライバの属性の指定

CREATE TABLE sales_external
(   time_id           DATE NOT NULL, …
    amount_sold       NUMBER(10,2)
)
ORGANIZATION EXTERNAL 
(   TYPE ORACLE_HIVE
    ACCESS PARAMETERS (com.oracle.bigdata.cluster=hadoop1        
                       com.oracle.bigdata.tablename=default.ratings_hive_table)
);

親トピック: 外部表の概要

14.2 データ・ファイルおよび出力ファイルの位置

データ・ファイルおよび出力ファイルはサーバーに配置する必要があります。ファイルの読取りおよび書込みを行う位置を指定するディレクトリ・オブジェクトが必要です。

ノート:

この項に示すディレクトリ・オブジェクト関連の情報は、ORACLE_HDFSアクセス・ドライバまたはORACLE_HIVEアクセス・ドライバには適用されません。ORACLE_HDFSドライバを使用する場合、データの場所はディレクトリまたはファイルのURIのリストで指定します。URIに関連付けられたディレクトリ・オブジェクトはありません。ORACLE_HIVEドライバではデータ・ソースの場所を指定しません。その情報はHiveメタストア表を読み取って取得するため、ディレクトリ・オブジェクトは必要ありません。

アクセス・ドライバは、データベース・サーバー内で実行されます。この動作は、SQL*Loaderが、ロードするデータをサーバーに送信するクライアント・プログラムであるという点で、SQL*Loaderとは異なります。この違いは、次のことを意味しています。

  • アクセス・ドライバがロード可能なファイルにサーバーがアクセスできる必要があります。

  • サーバーでは、アクセス・ドライバによって作成された出力ファイル(ログ・ファイル、不良ファイルおよび廃棄ファイル)、およびORACLE_DATAPUMPアクセス・ドライバによって作成されたダンプ・ファイルの作成と書込みを行う必要があります。

ファイルの読取りおよび書込みを実行する場所を指定するには、アクセス・ドライバでディレクトリ・オブジェクトを使用する必要があります。ディレクトリ・オブジェクトは、ファイル・システムのディレクトリ名に名前をマップします。たとえば、次の文は/usr/apps/datafilesにあるディレクトリにマップされる、ext_tab_dirという名前のディレクトリ・オブジェクトを作成します。

CREATE DIRECTORY ext_tab_dir AS '/usr/apps/datafiles';

DBAまたはユーザーがCREATE ANY DIRECTORY権限でディレクトリ・オブジェクトを作成できます。

ノート:

Oracle Real Applications Cluster(Oracle RAC)構成で外部表を使用するには、ディレクトリ・オブジェクトのパスがクラスタ・ファイル・システム上に存在するようにする必要があります。

ディレクトリの作成後、ディレクトリ・オブジェクトを作成するユーザーは、そのディレクトリのREAD権限およびWRITE権限を他のユーザーに付与する必要があります。これらの権限は、ロールを使用して割り当てるのではなく、明示的に付与する必要があります。たとえば、ext_tab_dirで指定されたディレクトリのユーザーscottのかわりに、サーバーがファイルを読み込むことができるようにするには、ディレクトリ・オブジェクトを作成したユーザーが、次のコマンドを実行する必要があります。 

GRANT READ ON DIRECTORY ext_tab_dir TO scott;

SYSユーザーのみがディレクトリ・オブジェクトを所有できます。ただし、SYSユーザーは、ディレクトリ・オブジェクトを作成する権限を他のユーザーに付与できます。ディレクトリ・オブジェクトに対するREAD権限またはWRITE権限は、Oracle Databaseがそのファイルに読取りまたは書込みを実行できるという意味にすぎません。適切なオペレーティング・システム権限がないかぎり、Oracle Databaseの外部にあるファイルには直接アクセスできません。同様に、Oracle Databaseには、ディレクトリのファイルに対して読取りおよび書込みを行うオペレーティング・システム権限が必要です。

親トピック: 外部表の概要

14.3 外部表のアクセス・パラメータ

外部表のアクセス・ドライバのデフォルトの動作を変更するには、アクセス・パラメータを指定します。

特定の型の外部表を作成する場合、アクセス・パラメータを指定して、アクセス・ドライバのデフォルトの動作を変更できます。各アクセス・ドライバには、アクセス・パラメータ用の固有の構文があります。外部表に使用するためのアクセス・ドライバORACLE_LOADER、ORACLE_DATAPUMP、ORACLE_HDFSおよびORACLE_HIVEが提供されています。

ノート:

これらのアクセス・パラメータは、SQL文のCREATE TABLE...ORGANIZATION EXTERNALのopaque_format_specとしてまとめて参照されます。ACCESSパラメータ句を使用すると、SQLコメントを使用できます。

関連項目:

親トピック: 外部表の概要

14.4 外部表の使用時のデータ型の変換

ソースとターゲットのデータ型が一致しない場合、Oracle Databaseが外部表から読み取るとき、および外部表に書き込むときに、変換エラーが発生する可能性があります。

外部表の読取り時の変換エラー

外部表から行を選択すると、アクセス・ドライバによって、データ・ソースのデータを外部表の対応する列のデータ型と一致させるために必要な変換が実行されます。データおよび必要な変更タイプによっては、変換でエラーが発生する可能性があります。

外部表からの読取り時に発生する可能性のあるデータ変換の問題のタイプを示すために、2つの列(データ型がVARCHAR2(4)のKEYと、データ型がNUMBERのVAL)を使用して次の外部表KV_TAB_XTを作成するとします。 

SQL> CREATE TABLE KV_TAB_XT (KEY VARCHAR2(4), VAL NUMBER)
2 ORGANIZATION EXTERNAL
3 (DEFAULT DIRECTORY DEF_DIR1 LOCATION (‘key_val.csv’));

外部表KV_TAB_XTは、アクセス・パラメータのデフォルト値を使用します。したがって、次のようになります。

  • レコードは改行で区切られます。
  • データ・ファイルとデータの文字セットは同じです。
  • データ・ファイル内のフィールドの名前と順序は、外部表の列と同じです。
  • フィールドのデータ型はCHAR(255)です。
  • 各フィールドのデータはカンマで終了します。

KV_TAB_XT外部表のデータ・ファイル内のレコードの要件は、次のとおりです。

  • 最大4バイトの文字列。文字列が空の場合、フィールドの値はNULLです。
  • 終了のカンマ。
  • 数字の文字列。文字列が空の場合、このフィールドの値はNULLです。
  • オプションの終了カンマ。

アクセス・ドライバでは、データ・ファイルからレコードを読み取るとき、データ・ファイル内のKEYフィールドの値の長さが4以下であることを確認し、データ・ファイル内のVALフィールドの値をOracle Databaseの数値に変換しようとします。

KEYフィールドの値の長さが4より大きい場合、またはVALの値に数値以外の文字がある場合、ORACLE_LOADERアクセス・ドライバはその行を拒否します。その結果、行のコピーが不良ファイルに書き込まれ、エラー・メッセージがログ・ファイルに書き込まれます。

すべてのアクセス・ドライバは、外部表のソースにあるフィールドのデータ型からの変換、および外部表の列のデータ型からの変換を処理する必要があります。アクセス・ドライバが実行する変換およびチェックのタイプについて、いくつかの例を次に示します。

  • 文字データを、ソース・データで使用されている文字セットからデータベースで使用される文字セットに変換します。
  • 文字データから数値データに変換します。
  • 数値データから文字データに変換します。
  • 文字データから日付またはタイムスタンプに変換します。
  • 日付またはタイムスタンプから文字データに変換します。
  • 文字データから内部データ型に変換します。
  • 内部データ型から文字データに変換します。
  • キャラクタ列のデータ値の長さがその列の長さ制限を超えていないことを確認します。

必要な変換または検証の実行中にエラーが発生した場合、アクセス・ドライバではエラーの処理方法を決定できます。ORACLE_LOADERおよびORACLE_DATAPUMPアクセス・ドライバでは、エラーを検出すると、レコードを拒否し、エラー・メッセージをログ・ファイルに書き込みます。その場合、そのレコードがデータ・ソースになかったかのようになります。ORACLE_HDFSおよびORACLE_HIVEアクセス・ドライバがエラーを検出すると、エラーが見つかったフィールドの値がNULLに設定されます。この動作は、HiveがHadoopでエラーを処理する際の動作と一致しています。

外部表列のデータ型と一致させるために、データ・ソースからのデータがアクセス・ドライバによって変換された後も、その外部表にアクセスするSQL文で、追加のデータ型変換が必要になる場合があります。この追加変換のいずれかでエラーが発生した場合は、文全体が失敗します。(ただし、SQL文でDMLエラー・ロギング機能を使用してこれらのエラーを処理する場合は例外です。)これらの変換は、通常、SQL文の実行時に必要になる可能性がある変換と同じです。たとえば、KV_TAB_XT外部表の定義を文字データ型の列のみが含まれるように変更した後、INSERT文を実行して、外部表のデータを列VALのデータ型がNUMBERの別の表にロードするとします。 

SQL> CREATE TABLE KV_TAB_XT (KEY VARCHAR2(20), VAL VARCHAR2(20))
2 ORGANIZATION EXTERNAL
3 (DEFAULT DIRECTORY DEF_DIR1 LOCATION (‘key_val.csv’));
4 CREATE TABLE KV_TAB (KEY VARCHAR2(4), VAL NUMBER);
5 INSERT INTO KV_TAB SELECT * FROM KV_TAB_XT;

この例において、VALのデータに数字以外の文字が含まれている場合、アクセス・ドライバはレコードを拒否しません。これは、外部表のVALが(NUMBERではなく) VARCHAR2になったためです。ただし、SQLの処理でKV_TAB_XTの文字データ型からKV_TABの数値データ型への変換処理が必要になります。外部表のVALの値に数字以外の文字がある場合、SQLで変換エラーが発生し、挿入された行はすべてロールバックされます。SQLでの変換エラーを回避するため、外部表の列のデータ型が、これらの列の値を使用する他の表または関数で要求されるデータ型と一致させることをお薦めします。

外部表への書込み時の変換エラー

ORACLE_DATAPUMPアクセス・ドライバを使用すると、CREATE TABLE AS SELECT文を使用してデータを外部表にアンロードできます。SELECT式における列のデータ型が外部表の列のデータ型と一致しない場合、データ変換が発生します。SQLでデータ型変換のエラーが検出されると、SQLは文を停止し、データ・ファイルは読み取ることができなくなります。

変換エラーによる操作の失敗に関連する問題を回避するには、外部表の列のデータ型を、外部表への書込みに使用されるソース表または式の列のデータ型と一致させる必要があります。ただし、外部表はすべてのデータ型をサポートするわけではないため、必ず成功するとはかぎりません。そのような場合、ソース表でサポートされていないデータ型を、外部表でサポートするデータ型に変換する必要があります。次のCREATE TABLE文は、この変換の例を示しています。 

CREATE TABLE LONG_TAB_XT (LONG_COL CLOB) 
ORGANIZATION EXTERNAL...SELECT TO_LOB(LONG_COL) FROM LONG_TAB;

LONG_TABという名前のソース表には、LONG列があります。そのため、作成する外部表の対応する列LONG_TAB_XTはCLOBである必要があり、外部表の移入に使用されるSELECT副問合せは、TO_LOB演算子を使用して列をロードする必要があります。

ノート:

LONGデータ型(LONG、LONG RAW、LONG VARCHAR、LONG VARRAW)のすべての形式は、Oracle8iリリース8.1.6で非推奨になりました。以降のリリースでは、LONGデータ型は既存のアプリケーションとの下位互換性のために提供されていました。以降のリリースで開発された新しいアプリケーションでは、大量の文字データにCLOBおよびNCLOBデータ型を使用することをお薦めします。

親トピック: 外部表の概要