Oracleの外部テーブル（external table）のメモ。
|
|
external table（外部表）は、表領域（テーブルスペース）の外にテーブルのデータファイルを置き、テーブルとしてアクセスできるようにするもの。
データファイルはCSVファイルとDataPump（バイナリー）ファイルに対応している。
（DataPumpの方がCSVと違ってパース（構文解析）が必要ない分、速い？）
SQL*Loaderを使う場合、「データファイルを読み込んで対象テーブルにロードする」という動作になるが、
外部テーブルを使うと、「データファイルを外部テーブルとして定義し、そのテーブルから対象テーブルへSELECT-INSERTする」ということが出来る。
処理速度はSQL*Lodaerを使うより外部テーブルを使う方が速い。（インデックス無しのEMPテーブルへの100万件ロードなら3〜4倍くらい違う）
Oracle10gより前は外部テーブルは読み取り専用だったらしいが、Oracle10g以降は書き込みも出来るらしい。
とは言え、外部テーブルはあくまでSQL*Loaderのようなロードツールの補足機能らしいので、永続的なデータを置くべきではないだろう。
インデックスも作れないので、ランダムアクセスには向かないし。
外部テーブルの実体となるデータファイルを置く場所は、Oracle DB内では「ディレクトリーオブジェクト」で管理される。
ディレクトリーオブジェクトは、管理者権限のあるユーザー（sys等）で作成する。
create or replace directory ディレクトリーオブジェクト名 as 'システム上のパス';
SQL> CREATE OR REPLACE DIRECTORY OLH_EXTTAB_DIR AS '/home/hishidama/oraloader/emp2'; Directory created.
そして、外部テーブルを作りたいユーザーに対し、ディレクトリーオブジェクトにアクセスできる権限を付ける必要がある。
grant read,write on directory ディレクトリーオブジェクト名 to ユーザー;
SQL> GRANT READ, WRITE ON DIRECTORY OLH_EXTTAB_DIR TO SCOTT; Grant succeeded.
外部テーブルは、通常のCREATE文に、オプションとしてORGANIZATION EXTERNALを付ける。
create table テーブル名 ( カラム定義… ) organization external ( 外部テーブル定義 );
| プロパティー | 内容 | |
|---|---|---|
| TYPE t | アクセス・ドライバーの種類を指定する。 CSVファイルの場合：ORACLE_LOADER DataPumpの場合：ORACLE_DATAPUMP |
|
| DEFAULT DIRECTORY d | 実体のデータファイルの場所を示すディレクトリーオブジェクトを指定する。 | |
| ACCESS PARAMETERS | アクセス・ドライバー用のパラメーターを設定する。 | |
| RECORDS DELIMITED BY c | レコードの区切り文字（改行コード）。NEWLINEだとたぶんOSの改行コード（UNIXならLF）。明示したい場合は「 0x'0d0a'」の様に書く。 |
|
| RECORDS FIXED n | 固定長レコード。 | |
| CHARACTERSET encoding | エンコーディング。 | |
| BADFILE file | BADFILE（エラーがあったレコードを出力するファイル）のファイル名。 | |
| DISCARDFILE file | 廃棄ファイルのファイル名。 | |
| LOGFILE file | ログファイルのファイル名。 | |
| NOBADFILE | BADFILEを出力しない。 | |
| NODISCARDFILE | 廃棄ファイルを出力しない。 | |
| NOLOGFILE | ログファイルを出力しない。 | |
| FIELDS TERMINATED BY c | フィールド区切り文字。例：「','」「0x'09'」 |
|
| OPTIONALLY ENCLOSED BY c | フィールドを囲む文字。例：「'"'」 |
|
| SKIP n | たぶん、「SKIP 1」で1レコードスキップする。 | |
| STRING SIZES ARE IN CHARACTERS | ||
| MISSING FIELD VALUES ARE NULL | ||
| LOCATION file | データファイルのファイル名。カンマ区切りで複数指定可能。 | |
ファイル名は「'ファイル名'」の様に指定する。この場合、実際の（OS上の）ディレクトリーは「DEFAULT
DIRECTORY」で指定したディレクトリーオブジェクトの指す場所になる。
「ディレクトリーオブジェクト名:'ファイル名'」とすると、個別にディレクトリーを指定できる。
外部テーブルをCREATEする際は、指定したデータファイルが存在している必要は無い。
一度外部テーブルを作ると、データファイルの中身を書き換えれば、SELECTした際の値も自動的に変わる。
CSVファイルの場合は、SQL*Loaderのコントロールファイルと同様のカラム定義を指定する。
CREATE TABLE EXT_EMP2
(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY OLH_EXTTAB_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
CHARACTERSET AL32UTF8
STRING SIZES ARE IN CHARACTERS
FIELDS TERMINATED BY ','
(
EMPNO DECIMAL EXTERNAL,
ENAME CHAR(10),
JOB CHAR(9),
MGR DECIMAL EXTERNAL,
HIREDATE DATE "SYYYY-MM-DD HH24:MI:SS",
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO DECIMAL EXTERNAL
)
)
LOCATION ('oraloader-00000-csv-0.dat')
);
→Oracle Loader for HadoopでCSVファイルと外部表のCREATE文を作る
CREATE TABLE EXT_EMP2
(
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY OLH_EXTTAB_DIR
LOCATION ('oraloader-00000-dp-0.dat')
);
→Oracle Loader for HadoopでDataPumpファイルと外部表のCREATE文を作る
外部テーブルの使用に関するエラー。
select * from ext_emp2 where rownum <= 2 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file /home/hishidama/oraloader/emp2/EXT_EMP2_12410.log
外部テーブルを使用する際は、ログファイル等を書き込む場所に（UNIXの）oracleユーザーの書込権限が必要。
（たぶん、Oracleプロセスを実行しているUNIXユーザーがoracle）
そのディレクトリー（ディレクトリーオブジェクトが指している実際の場所）に対してoracleユーザーへの書込権限を付けるか、
書き込もうとしているファイルを書き込まない設定にする。（例えばNOLOGFILEを指定するとログファイルは出力されなくなる）