kagamihogeの日記

kagamihogeの日記です。

Oracleの外部表でken_all.csvを読み込んでみる

外部表 - オラクル・Oracleをマスターするための基本と仕組み

Oracleの外部表という機能の存在を知ったので、試しに使ってみる。適当なcsvファイルということで、郵便番号データダウンロード - 日本郵便からダウンロードできる、住所の郵便番号一覧であるken_all.csvを使うことにする。

ディレクトリオブジェクトの準備と権限付与

まずcsvを配置するディレクトリオブジェクトをOracleに作成する。ログ出力が要らない場合、ログ出力ディレクトリは作らなくても良い。

CREATE OR REPLACE DIRECTORY EXTERNAL_TABLE_DIR AS 'C:\mydata\oracle\external_table_dir';
CREATE OR REPLACE DIRECTORY EXTERNAL_TABLE_LOG AS 'C:\mydata\oracle\external_table_log';

権限付与。

GRANT  READ, WRITE ON DIRECTORY "EXTERNAL_TABLE_DIR" TO KAGAMIHOGE;
GRANT  READ, WRITE ON DIRECTORY "EXTERNAL_TABLE_LOG" TO KAGAMIHOGE;

外部表の作成

テーブル名とかカラム名とかはMySQLのデータサンプルとして郵便番号のデータを入れる - kenjiskywalker no memoとか郵便番号データの説明 - 日本郵便とかを参考にしました。

CREATE TABLE POSTAL_CODE
(
  JIS_CODE CHAR(5),
  POSTAL_CODE_OLD CHAR(5),
  POSTAL_CODE CHAR(7),
  PREFECTURE_KANA VARCHAR2(100),
  CITY_KANA VARCHAR2(400),
  TOWN_KANA VARCHAR2(400),
  PREFECTURE_KANJI VARCHAR2(100),
  CITY_KANJI VARCHAR2(300),
  TOWN_KANJI VARCHAR2(300),
  TOWN_DIVIDE_FLAG CHAR(1),
  KOAZA_BANCHI_FLAG CHAR(1),
  TYHOUME_FLAG CHAR(1),
  HAS_SOME_TOWN_FLAG CHAR(1),
  UPDATE_STATE_FLAG CHAR(1),
  UPDATE_REASON CHAR(1)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY "EXTERNAL_TABLE_DIR"
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET JA16SJISTILDE
    BADFILE "EXTERNAL_TABLE_LOG":'BADFILE_LOG.bad'
    DISCARDFILE "EXTERNAL_TABLE_LOG":'DISCARDFILE_LOG.log'
    LOGFILE "EXTERNAL_TABLE_LOG":'LOGFILE_LOG.log' 
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    (
      JIS_CODE CHAR(5),
      POSTAL_CODE_OLD CHAR(5),
      POSTAL_CODE CHAR(7),
      PREFECTURE_KANA CHAR(100),
      CITY_KANA CHAR(400),
      TOWN_KANA CHAR(400),
      PREFECTURE_KANJI CHAR(100),
      CITY_KANJI CHAR(300),
      TOWN_KANJI CHAR(300),
      TOWN_DIVIDE_FLAG CHAR(1),
      KOAZA_BANCHI_FLAG CHAR(1),
      TYHOUME_FLAG CHAR(1),
      HAS_SOME_TOWN_FLAG CHAR(1),
      UPDATE_STATE_FLAG CHAR(1),
      UPDATE_REASON CHAR(1)
    )
  )
  LOCATION ('KEN_ALL.CSV')
);

あとは普通のテーブルのようにSELECT文でアクセスできる。

select * from POSTAL_CODE;

ログ出力が要らない場合は該当のオプション(BADFILE, DISCARDFILE, LOGFILE)をそれぞれ下記にする。

    NOBADFILE
    NODISCARDFILE
    NOLOGFILE

ハマりどころなど

CREATE TABLEが成功しても、ファイルの読み込みに失敗するとSELECT文はエラーになる

ので、実際のCSVファイルのフォーマットとCREATE TABLEの定義をよく見直す必要がある。自分の場合もFIELDS TERMINATED BYの位置がおかしくて下記のようなエラーが発生した。参考:Oracle Technology Network (OTN) Japan - 掲示板 : テキストファイルを外部表より参照 ...


ORA-29913: ODCIEXTTABLEOPENコールアウトの実行中にエラーが発生しました。
ORA-29400: データ・カートリッジ・エラーが発生しました
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "optionally": expecting one of: "badfile, byteordermark, characterset, column, data, delimited, discardfile, disable_directory_link_check, fields, fixed, io_options, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, preprocessor, readsize, string, skip, territory"
KUP-01007: at line 6 column 5

なんかうまくいかない

下記引用を参照。設定がおかしいと実行時エラーになるだけで、コンパイルエラー的なものは無いので、目視で一つずつ確認していくしかない。

>CREATE OR REPLACE DIRECTORY dir_text AS 'c:\text';
のパスが間違っている。

>GRANT READ ON DIRECTORY dir_text TO [ユーザ名];
>GRANT WRITE ON DIRECTORY dir_text TO [ユーザ名];
GRANTの忘れ。

> LOCATION ('data.txt')
ファイル名の間違い。

のいずれかのはず。
フルパス上に全角文字、空白、半角カタカナがあってもダメのはず。^^;
Oracle Technology Network (OTN) Japan - 掲示板 : 外部表でデータが無効になる ... より抜粋

文字化けする

これは環境に依存する問題なので、こうすれば上手くいくという解はタブンない。

ウチの環境だと、WindowsにOracle XEをデフォルトインストールしたのでDatabase CharactersetのNLS_CHARACTERSETはAL32UTF8になっている。よって、外部表を作るときCHARACTERSETを書かないと文字化けする。参考:JA16SJISとJA16SJISTILDEの違い

ACCESS PARAMETERS...FIELDSセクションにVARCHAR2は使えない


KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: VARCHAR2
参考:external table error | Oracle Forums

ORA-12899

テーブルの型のサイズと、CSVファイルの大きさが合ってなければ上記のエラーが出る。参考:ORA-12899 - オラクル・Oracle エラー FAQ

ちなみに、ken_all.csvを読み込む外部表のCREATE TABLEのサンプルは、テキトーに型のサイズ変えていって上手くいった値を載せてるだけです。

その他

じつのところ、郵便番号ファイルを外部表で取り込んで、オンライン処理でそのまま使うのはかなりビミョウだと思われる。外部表は実ファイルのビューのようなものなので、フツウのテーブルと比べてアクセス効率が悪いんで。外部表で取り込んで実テーブルに入れるとか、バッチ処理で一度だけしか読み込ま無い、とかだったら良いんじゃないかな〜とか思う。