Oracleのデータインポートユーティリティ、SQL*Loaderについてまとめています。
SQL*Loaderは、Oracleにデータをインポートするためのユーティリティ
ファイル構造を定義することで、多彩なフォーマットのファイルインポートが可能です。実行するには、sqlldrコマンドを使用します。例えば以下のコマンドは、ユーザID scottを使用して、def.ctlというファイル構造定義ファイルを指定して、データのインポートをおこないます。実行時のログは、Import.logファイルに出力します。
sqlldr USERID=scott CONTROL=def.ctl LOG=import.log
SQL DeveloperにもSQL*Loader形式でエクスポートするオプションが用意されています。
【関連記事】
▶SQL Developerを使ってみよう！資格取得の学習にも使える便利なツール
SQL*Loader制御ファイルで、データの構造を指定する
SQL Loaderを使うことで、たとえばメインフレーム系から出力したデータファイルを容易にOracleに取り込むなど、他システムとの連携が簡単になります。
以下は固定長を指定する場合の制御ファイルです。ファイルexample.datを11桁の固定長データとして、テーブル名exampleに取り込みます。
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,.
カンマで区切られたデータを、第一カラム、第二カラムとしてロードします。1行目は、ty,.までが11バイトで、以降は2行目として扱われます。
以下は、可変長を指定する制御ファイルです。ファイルexample.datを最初の3バイトでレコード長を決めています。
1行目は、レコード長9バイトで「hello」と「cd」、レコード長10バイトで「world」と「im（＋1バイトの改行文字）」、2行目は12バイトで「my」と「name is（+1バイトの改行文字）」となります。
load data infile 'example.dat' "var 3" into table example fields terminated by ',' optionally enclosed by '"' (col1 char(5), col2 char(7)) example.dat: 009hello,cd,010world,im, 012my,name is,
以下は、ストリーム・レコード形式の制御ファイルです。終了文字が出現することで１つのレコードとして認識します。以下の例は、ファイルexample.datに「|（+改行文字）」が出現したら1レコードとみなす例です。1行目では「hello」と「world」、2行目では「james」と「bond」がそれぞれcol1とcol2に入ります。
load data infile 'example.dat' "str '|\n'" into table example fields terminated by ',' optionally enclosed by '"' (col1 char(5), col2 char(7)) example.dat: hello,world,| james,bond,|
参考）Oracle 公式ドキュメント SQL*Loaderの概念
バイナリ形式をロードするための制御ファイル
カンマなどのデリミタ文字を使わず、レコード先頭からのバイト数でカラムを定義することもでき、バイナリファイルを扱う際に有効です。
以下の制御ファイルは、ファイルsample.datから対応するデータの物理位置を指定し、テーブルdepartmentsに対応するカラムのデータをロードします。dept_mgrはCOLUMN OBJECTでname、age、emp_idで構成されています。
LOAD DATA
INFILE 'sample.dat'
INTO TABLE departments
(dept_no POSITION(01:03) CHAR,
dept_name POSITION(05:15) CHAR,
1 dept_mgr COLUMN OBJECT
(name POSITION(17:33) CHAR,
age POSITION(35:37) INTEGER EXTERNAL,
emp_id POSITION(40:46) INTEGER EXTERNAL) )
データ・ファイル(sample.dat)
101 Mathematics Johny Quest 30 1024
237 Physics Albert Einstein 65 0000
参考）Oracle公式ドキュメント オブジェクト、LOBおよびコレクションのロード
制御ファイルとOracleによるデータフォーマットのチェック
SQL*Loaderの制御ファイルに沿わないデータは拒否されます。例えば、デリミタで区切られたあるべきフィールドが存在しない場合、フィールドがカラム定義で指定した最大長を超えている場合などです。拒否されたレコードは不良ファイル（拡張子.bad）に書き込まれます。
続いて、Oracle Databaseの表に行として挿入される際に、不適切なデータは拒否されます。データ型やデータ長が異なったり、SQL*Loaderの制御ファイル定義がOracle Databaseとマッチしていなかった場合、キーが重複している場合、NULL不可フィールドがNULLの場合などがこれにあたります。
なお、Oracle Databaseに拒否されたデータも不良ファイルに書き込まれます。
外部表を使ったロードとSQL*Loaderの違い
SQL*Loaderで直接データベースにデータをインポートせず、外部表を使ってインポートすることが可能です。外部表は、データベース上には存在しないが実在のテーブルのように見えます。データへのアクセスは、ORACLE_LOADER、OACLE_DATAPUMPといったアクセスドライバを利用しておこないます。
外部表の最大のメリットは、パラレル処理が可能なことで大きなファイルをロードする際の時間短縮に有効です。ただし、SJIS、EUCコードのデータの場合、パラレル処理ができないという制約があります。
SQL*Loaderにはパラレル処理機能はありませんが、ファイルを分割することでパラレル処理の実現が可能です。
使い分けとしては、バッチ処理などに組み込む定常的な処理は外部表ロードを使い、定常的なローディング処理には手早く対応できるSQL*Loaderを使うのが有効です。
まとめ
- SQL*Loaderは、ファイルを制御ファイルで構造定義してデータベースにロードするユーティリティ
- 制御ファイルの記述によって、固定長、可変長、終了文字を使ったストリームレコード形式、バイナリ形式のファイルが扱える
- SQL*Loaderをバッチ処理などに組み込むことも可能だが、大量データは外部表を使ったロードの方が速い
