外部表 - オラクル・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は使えない
参考:external table error | Oracle Forums
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
ORA-12899
テーブルの型のサイズと、CSVファイルの大きさが合ってなければ上記のエラーが出る。参考:ORA-12899 - オラクル・Oracle エラー FAQ
ちなみに、ken_all.csvを読み込む外部表のCREATE TABLEのサンプルは、テキトーに型のサイズ変えていって上手くいった値を載せてるだけです。
その他
じつのところ、郵便番号ファイルを外部表で取り込んで、オンライン処理でそのまま使うのはかなりビミョウだと思われる。外部表は実ファイルのビューのようなものなので、フツウのテーブルと比べてアクセス効率が悪いんで。外部表で取り込んで実テーブルに入れるとか、バッチ処理で一度だけしか読み込ま無い、とかだったら良いんじゃないかな〜とか思う。