SQL ローダーの使い方 制御ファイルの生成方法とよく使うオプション解説

SQLローダーについてまとめています。

SQLローダーの使い方

SQL*Loaderは大量データを高速にロードするためのツールです。

Importユーティリティでは処理に時間がかかりすぎるような大量データをロードする場合は、SQL*Loaderを使いましょう。

この記事では、SQLローダー用の制御ファイル生成方法と、よく使うコマンドラインオプションを解説しています。

SQLローダーの制御ファイルを簡単に生成する方法

制御ファイルには、ロードするファイルの形式を指定します。項目をカンマで区切るcsv形式などのほか、自由に書式の設定が可能です。

load data
infile 'example.dat'  "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)

example.dat:
396,...ty,.4922,beth,\n
68773,ben,.
1,.."dave",
5455,mike,.

【関連記事】
▶SQL LoaderはOracleユーティリティ 制御ファイル定義でお手軽データロード可能 

制御ファイルは、Oracle公式の開発ツール、Oracle SQL Developer（Windows用）で自動生成することが可能です。

対象テーブルをエクスポート時にDDLをLoader形式で出力する指定をすることで、SQL*Loaderの制御ファイル形式でファイルがエクスポートされるんですね。

INFILE指定と改行コード関連を手動書き換えすることで、Unix機上でも利用できます。

参考）[Oracle]SQL*Loader制御ファイルを生成する手軽な方法 | Developers.IO

SQL*Loaderを実行するには、sqlldrコマンドにオプション指定を記述してEnterキーを押します。下記の例ではCONTROLに制御ファイル指定、LOGにログファイルを指定し、ユーザIDとパスワードを指定しています。

> sqlldr CONTROL=ulcase1.ctl LOG=ulcase1.log
Username: scott
Password: password

参考）SQL*Loaderコマンドライン・リファレンス

ユーザの@（接続識別子）指定でネットワーク上のサーバにロード

なお、コンソール機ではなくネットワーク上のサーバにデータをロードするには、ユーザ名に接続識別子を指定します。実行するマシンでOracle Net Listenerが実行されていることが前提です。

下記の例ではユーザ名で入力するscotto@inst1のinst1の部分が接続識別子です。

> sqlldr CONTROL=ulcase1.ctl
Username: scott@inst1
Password: password

BADパラメータは、ロードに失敗した不良ファイルを記録

SQL*Loader実行時に、ロードに失敗した不良レコードを記録します。BADパラメータにはディレクトリとファイル名を指定可能です。以下の例では、emp1.badという不良ファイルに挿入時にエラーの原因になったレコードや、不適切に書式化されたレコードが格納されます。

sqlldr CONTROL=ulcase1.ctl LOG=ulcase1.log BAD=emp1

不良ファイル指定は、制御ファイル中のINFILEに記述することが可能です。

なお、出力されるファイルは不良になったデータ行そのものです。多くの場合には、型違い、区切り文字の欠如や間違い、1行のデータ個数の間違いになるケースでしょう。

「データファイルのフィールドが最大長を超えています」というエラーが出た場合は、SQL*Loaderの制御ファイルでデータの型・桁数指定をすると解消することがあります。

エラー原因はログファイルを参照するか、エラーが起こったデータを直接SQL*PlusやMicrosoft Access経由でinsertを試みると、エラーの特定がしやすくなるでしょう。

ERRORSパラメータで、許容するエラーの数を指定

指定した数以上のエラーが発生したら処理を止めたい、というときにはERRORSパラメータを指定します。ERRORS=0とすると、エラーが発生したときにロード処理を止めます。

sqlldr CONTROL=ulcase1.ctl LOG=ulcase1.log BAD=emp1 ERRORS=0

ERRORSパラメータのデフォルト値は50なので、エラーチェックを行う場合はERRORS=0を指定すると良いでしょう。発生したエラーは、不良ファイルに書き込まれます。

ロードの途中でエラーが発生した場合、エラー発生以前のデータはロードされます。エラー発生レコードは削除されます。

発生エラー数を無制限にしたい場合は、ERRORSの数値に非常に大きな値を指定します。

PARFILEパラメータで、コマンドラインオプションをファイルに記述

sqlldrのコマンドラインオプションが長くなってきたら、PARFILEパラメータでファイルにまとめることを考えましょう。

例えば、daily_report.parというパラメータファイルを作成しておきます。

USERID=scott
CONTROL=daily_report.ctl
ERRORS=9999
LOG=daily_report.log

実行時には、以下のコマンドのみでユーザID、制御ファイル、エラー許容数、ログファイルを指定して実行することができるんですね。

sqlldr PARFILE=daily_report.par
Password: password

SKIPパラメータで途中でエラーが出たレコードを飛ばして、ロードを継続

指定したレコード数を対象外にするパラメータです。以下の例では、500件分のレコードをスキップします。

sqlldr PARFILE=daily_report.par SKIP=500

ロード中にエラーになった場合、エラー原因を修正して、ロードを継続したいときに便利。なお、複数表のダイレクト・パス・ロードや外部表のロードには使用できません。

また、ロード対象のCSVファイルなどで1行目がカラム名などを記述したヘッダーの場合は、SKIP=1と指定することで、1行目を読み飛ばすことができます。

SILENTパラメータでエラー内容を抑制

SQL*Loaderでは、デフォルト状態だと全てのメッセージが出力されます。

SILENTパラメータで、不要なメッセージのフィルタリングが可能。

SILENTに指定できるのは、HEADER、FEEDBACK、ERRORS、DISCARDS、PARTITIONS、ALL。複数の値を設定するには、カンマ区切りで指定します。

下記の指定は、SQL*Loaderのヘッダーメッセージと、ロード時のエラーメッセージが抑止されログファイルにも出力されなくなります。ただし、エラー時の拒否件数は出力されます。

sqlldr PARFILE=daily_report.par SILENT=HEADER,ERRORS

まとめ

ポテパンダの一言メモ

• SQL*Loaderは、CSVファイルなどを高速に対象テーブルにロードするユーティリティ
• SQL*Loader用の制御ファイルは、Oracle SQL Developerから生成可能
• SQL*Loaderのオプション指定が長すぎる場合は、PARFILEパラメータで短縮できる