OracleでCSVファイルをあたかもテーブルのように扱う方法(外部表)

はじめに

Oracleの外部表という機能を使うと、Windows上に存在するファイルを取り込んであたかもテーブルが存在しているかのように、SQLから操作することができます。

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニングSQLチューニング (DB SELECTION)

posted with amazlet at 13.04.20

加藤祥平中島益次郎
翔泳社
売り上げランキング: 233,324

Amazon.co.jpで詳細を見る

CSVファイルを外部表として取り込む（事前準備）

外部表を使うには、CSVファイルが格納されているフォルダをOracleから見えるように、パスを指定してあげる必要があります。
Oracleが外部表を扱うためのフォルダの指定は、sysユーザでcreate directory文を使います。以下の例では、C:\oracle_tempを外部表が格納されているフォルダと指定しています。

connect sys/oracle as sysdba
CREATE OR REPLACE DIRECTORY external_data AS 'C:\oracle_temp';

フォルダの指定に続いて、同フォルダに対する権限の付与を行います。
以下の例では、hogeユーザに対して、さきほど作成したexternal_dataという名前の外部表フォルダに読み書きの権限を付与しています。

GRANT READ ON DIRECTORY external_data TO hoge;
GRANT WRITE ON DIRECTORY external_data TO hoge;

CSVファイルを外部表として取り込む

外部表の準備ができたら、いよいよ取り込んでみます。
仮想的なテーブルとして扱うため、create table文を使います。ORGANIZATION EXTERNALという指定で、CSVファイルのファイル名やデータ構造の定義をあわせて行っています。

CREATE TABLE table02 (
    id char(5),
    name VARCHAR2(50),
    furigana VARCHAR2(50))
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY external_data
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
        (
            id,
            name,
            furigana
        )
    )
    LOCATION (
        'table02.csv'
    )
);

読み込み先のCSVファイルには、以下のようなデータを書いておきます。

create tableが完了すると、通常のテーブルと同じように操作ができます。

SQL> set head off
SQL> select * from table02;

00001
山田　太郎
やまだ　たろう

00002
鈴木　太郎
すずき　たろう

外部表の制約

外部表を使った場合、SQLから外部のCSVファイルに対してinsert、update、deleteによるファイル操作はできません。「ORA-30657: 外部構成表で操作がサポートされていません」のエラーとなります。

おわりに

ちょっとしたCSVファイルとデータベース内のデータをあわせて何か処理したい場合に、外部表を使うとSQLだけで処理が書けるので便利です。
外部表を使う際の注意したい点は、常にファイルアクセスが発生するため頻繁に読む必要があるデータには向いていないということです。頻繁に読む必要がある場合には、普通にテーブルに格納してまってから処理するようにしたほうが良いです。

ファイル ⇒ アクセスドライバ・メモリ(内部形式に変換) ⇒ テーブルで毎回読み込み操作が行われるために永続表に比べて非常に効率が悪い。(頻繁に参照する表には向いていない)
外部表の特徴を無視した使い方をするとレコード件数が増加したときに満足できるレスポンスが得られなくなる。
LONG 列はサポートされていない。
外部表 - オラクル・Oracleをマスターするための基本と仕組み