SQL*Loader を使ってみる
SQL*Loader とは、Oracle DB 付属のツールで、CSV ファイルなどに書かれたデータを一気に INSERT できるツールだ。コントロールファイルと呼ばれる設定ファイルの書き方に特徴があったり、設定値未指定の場合の初期値に難があったりするので、参考となる情報をまとめておく。
SQL*Loader の使い方
SQL*Loader は以下のように 、コマンドプロンプトなどからSqlldr コマンドで実行する。
Sqlldr userid=scott/tiger control=sample.ctl
control オプションに書いたファイルが、設定項目を持つ「コントロールファイル」となる。
Sqlldr コマンドのみを打つと、このコマンドで使えるパラメータやデフォルト値が確認できる。
コントロールファイルのサンプル
以下をテンプレート代わりにしてもらえると良いかと。仮に sample.ctl としておく。
OPTIONS (
LOAD = -1,
SKIP = 0,
ERRORS = 0,
ROWS = -1,
DIRECT = TRUE,
MULTITHREADING = TRUE,
PARALLEL = TRUE
)
LOAD DATA
CHARACTERSET UTF8
INFILE 'sample.csv'
BADFILE 'sample.bad'
APPEND
INTO sample_table
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
column_A,
column_B "TO_DATE(:column_B, 'YYYY/MM/DD HH24:MI:SS')",
column_C DATE 'YYYY/MM/DD HH24:MI:SS',
column_D DECIMAL EXTERNAL(10),
column_E ZONED(5, 2),
column_F CHAR(100),
column_G CONSTANT "Text",
column_H SYSDATE
column_I NULLIF column_E=BLANKS,
column_J TERMINATED BY ';',
column_K
)
コントロールファイル内でのコメントは「--」で可能。
OPTIONS について
OPTIONS句はコマンドライン引数でも書ける。コマンドライン引数の設定の方が優先されるが、コントロールファイルにまとめておくのが分かりやすいかと。- 書かなかった設定項目は初期値が設定されるのだが、この初期値が微妙な場合があるので、よくよく注意してほしい。
LOAD: ロードする件数。デフォルトは-1で全てを読み込む。SKIP: スキップするレコード数。ファイルの先頭から何行無視するかを指定できる。デフォルトは-1でスキップなし。ERRORS: 許容するエラー数。レコードの登録に失敗したレコードが何件あったら処理を中断するか、という指定。デフォルトは50レコード失敗で中断。0ならエラーを許容しない。-1でどれだけエラーが出ても無視する。
以前、この設定をせず、初期値である 50 を有効にしていたシステムがあって、日次バッチでデータを取り込むためにSQL*Loaderを使っていた。普段から40数件エラーがあったのだがそれには気付いておらず、ギリギリ取り込める状態が続いていたのだが、ある日エラー件数が50件を超えてデータが取り込めず、翌日の業務に影響が出るという本番障害があった。公式のリファレンスやヘルプを読まずに使うのは必ず問題を引き起こすので注意。DIRECT:TRUEでダイレクト・パス・ロードを使う。高速に取り込める仕組みだが、条件があるので注意。MULTITHREADING:TRUEで、ダイレクト・パス・ロードを使用しているときにマルチスレッディング処理させる。PARALLEL: 読み込み操作をパラレル化する。
LOAD DATA について
CHARACTERSET: 入力ファイル (データファイル) のキャラセット指定。INFILE: データファイル。SQL*Loader を実行するディレクトリからの相対パス指定。複数ファイル指定することもできる。BADFILE: エラーがあったときに、このファイルにログを出力する。オプション指定を省略すると、「データファイル名.bad」で作られる。APPEND: データを追加する。既にデータが存在する場合は、重複しないデータだけ追加される。他のモードはINSERT、REPLACE、TRUNCATE。PRESERVE BLANKSを指定すると、カラムの前後にある区切り文字前後の空白を消せる。全カラムにTRIM()関数をかませるようなもので、副作用もあるので使用は注意。INTO (テーブル) (フィールド)を複数指定すれば、異なるテーブルにデータを出力できる。INTO (テーブル) WHEN (条件)と書くと、条件を満たしたデータだけ挿入できる。条件に合わないデータはDISCARDFILEに出力されるが、BADFILEと違ってDISCARDFILE項目を指定しておかないとファイルに出力されないので注意。INFILE、BADFILEの流れでDISCARDFILE 'sample.dis'などと書いておく。FIELDS TERMINATED BY: データを区切る、区切り文字を指定する。CSV ファイルはカンマ区切りなので","と記載。タブであれば「BY X'09'」、スペース・タブ・改行のいずれかであれば「BY WHITESPACE」とすれば良い。TRAILING NULLCOLS: データのない項目にはNULLを入れる。
カラム指定について
- 大抵は
column_A, column_Bのように、カラム名だけ列挙すれば良い。 - 項目の後ろに関数を書けば、その内容が入れられる。
column_B "TO_DATE(:column_B, 'YYYY/MM/DD HH24:MI:SS')"は、:column_Bの値をTO_DATE()関数で変換した内容をcolumn_Bに挿入する、となる。 column_C DATE 'YYYY/MM/DD HH24:MI:SS'というように、データ属性と書式を指定することもできる。DECIMAL EXTERNAL(10)は NUMBER 型の整数、ZONED(5, 2)は NUMBER 型の小数。CHAR(100)は CHAR および VARCHAR2 型。column_G CONSTANT "Text"とすれば、定数としてTextという文字列を追加できる。データファイルの内容を使わずにインサートする。同様にSYSDATEも挿入可能。column_I NULLIF column_E=BLANKSは、column_Eが空白およびそれに類するもの (BLANKS) の時に、column_IにNULLが挿入される。column_J TERMINATED BY ';', column_Kというように、カラムごとに区切り文字を指定できる。普段の書いていない場合はFIELDS TERMINATED BYで指定した区切り文字になる。
データファイルは？
通常は CSV ファイルとして、1行が1レコードのファイルを用意しておけば良い。コントロールファイルで定義したカラムに、カンマごとにデータが入っていく。
改行を含んだ CSV ファイルが上手く取り込めるかは知らない (試したことがない)。