Corredor

ウェブ、プログラミングの勉強メモ。

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件を超えてデータが取り込めず、翌日の業務に影響が出るという本番障害があった。公式のリファレンスやヘルプを読まずに使うのは必ず問題を引き起こすので注意。
  • DIRECTTRUE でダイレクト・パス・ロードを使う。高速に取り込める仕組みだが、条件があるので注意。
  • MULTITHREADINGTRUE で、ダイレクト・パス・ロードを使用しているときにマルチスレッディング処理させる。
  • PARALLEL : 読み込み操作をパラレル化する。

LOAD DATA について

  • CHARACTERSET : 入力ファイル (データファイル) のキャラセット指定。
  • INFILE : データファイル。SQL*Loader を実行するディレクトリからの相対パス指定。複数ファイル指定することもできる。
  • BADFILE : エラーがあったときに、このファイルにログを出力する。オプション指定を省略すると、「データファイル名.bad」で作られる。
  • APPEND : データを追加する。既にデータが存在する場合は、重複しないデータだけ追加される。他のモードは INSERTREPLACETRUNCATE
  • PRESERVE BLANKS を指定すると、カラムの前後にある区切り文字前後の空白を消せる。全カラムに TRIM() 関数をかませるようなもので、副作用もあるので使用は注意。
  • INTO (テーブル) (フィールド) を複数指定すれば、異なるテーブルにデータを出力できる。
  • INTO (テーブル) WHEN (条件) と書くと、条件を満たしたデータだけ挿入できる。条件に合わないデータは DISCARDFILE に出力されるが、BADFILE と違って DISCARDFILE 項目を指定しておかないとファイルに出力されないので注意。INFILEBADFILE の流れで 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_INULL が挿入される。
  • column_J TERMINATED BY ';', column_K というように、カラムごとに区切り文字を指定できる。普段の書いていない場合は FIELDS TERMINATED BY で指定した区切り文字になる。

データファイルは?

通常は CSV ファイルとして、1行が1レコードのファイルを用意しておけば良い。コントロールファイルで定義したカラムに、カンマごとにデータが入っていく。

改行を含んだ CSV ファイルが上手く取り込めるかは知らない (試したことがない)。

参考