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を指定するとログファイルは出力されなくなる)