SQL Loader (SQL ローダ) ユーティリティの使用方法
SQL Loader 概要
SQLLoader を使用することで外部ファイルに記述されている制御ファイルと一定の書式で記述されたデータレコードのみの大量のテキストデータ(※)を効率よく高速にデータベースの表にローディングすることができる。
(※) テキスト形式だけではなく、Native な型のデータや LOB 型のデータもコントロールファイルの設定によってローディングすることが可能。
SQL Loader の主な特徴
- ダイレクト・パス API を使用した高速なデータのローディング(ダイレクト・パス・インサート)
- 複数のデータファイルからのパラレルロード
- 漢字コード(キャラクタセット)の変換
- SQL 関数、ユーザー関数、シーケンスなどを使用してデータの生成、合成、変換が可能
- 不適格データの排除とレポート、ログ
SQL Loader はユーティリティであり OS のコマンドラインから実行する。
Oracle のクライアント・ソフトとしてもインストールすることができ、クライアントのデータファイルをサーバーに転送することなく SQL*Loader 経由で直接サーバーにエントリすることも可能。
実行例
# sqlldr userid="username/password" control='ldrSample.ctl'
SQL Loader の使い方
SQL Loader に ACCESS と ODBC 経由でのコピー＆ぺーストによるエントリのようなお手軽さを期待してはいけない。
SQL*Loader はまさに、拡張され続けて多く機能を詰め込んだユーティリティの典型である。
実行時の引数やオプション選択が非常に多く、このユーティリティを初めて使おうとする場合、最初の一歩の敷居の高さは相当なものである。
とはいってもコントロールファイル(※) の基本事項さえ抑えておけばテンプレートとして使えるので、一般的なデータファイルであれば、苦労するのは最初だけである。(提供されている多くの機能を使用したい場合には、マニュアルとそれなりの格闘はします。)
(※) SQL Loader コントロールファイル: SQL*Loader は、LOADER コントロールファイルにデータファイルのプロパティとレコードのフォーマッティング情報を登録しておくファイル。SQL*Loader はコントロールファイルに基づいてデータファイルを処理する。
コントロールファイル （制御ファイル）
コントロールファイルの例 (ファイル名： ldrSample.ctl )
OPTIONS (
-- ダイレクト・パス・ロード
-- マルチスレッディング処理(ダイレクトモードのみ有効)
-- パラレル読み込み
DIRECT = TRUE,
MULTITHREADING = TRUE,
PARALLEL = TRUE
)
LOAD DATA
-- 入力ファイルの漢字コード(キャラクタセット名を指定)
CHARACTERSET JA16SJISTILDE
-- DATA files , BAD , DISC file
INFILE 'ldrSample01.dat' "FIX 62"
INFILE 'ldrSample02.dat' "FIX 62"
BADFILE 'ldrSample.bad'
DISCARDFILE 'ldrSample.dis'
-- APPEND ROWS
APPEND INTO TABLE NEW_MEMBER_LDR
(
DEPT DECIMAL EXTERNAL(2),
USER_ID CHAR(5) ,
FIRST_NAME POSITION(*+001) CHAR(10),
LAST_NAME CHAR(10) "UPPER(:LAST_NAME)", -- ← (※1)
SHOE_SIZE DECIMAL EXTERNAL(4),
HIRE_DATE DATE 'yyyy/mm/dd hh24:mi:ss',
FULL_NAME EXPRESSION ":FIRST_NAME || ' ' || :LAST_NAME", -- ← (※1)
-- (※1) :LAST_NAME 変数と LAST_NAME カラムは独立しているため大文字にならない
-- EXPRESSION は Oracle 9i からの機能
-- 関数も EXPRESSION も ダブルクォート(") で囲んで使用する
MEMO_PAD2 CONSTANT 'DUMMY'
)
データファイルのフォーマット
このサンプルでポイントにしている点
- ダイレクト・パス・ロードを使用する？(※2)
- 読み込み操作をパラレル化する。
- 入力するデータデータファイルは複数である。
- 動作ログや破棄、除外されたログなどをファイルに出力する。
- 漢字コード(キャラクタセット)が異なる。
(データファイルは ShiftJIS、DB サーバーは EUC)
- データに改行コードが含まれている。(改行コード = CR+LF を想定)
- レコード長は固定長。(60 バイト＋改行(CR+LF) = 62 バイト)
- レコードデリミタは改行。(改行は 2 バイト:Win系 CR+LF / UNIX系 LFのみ)
但し 改行をレコード終了記号としては認識させていない(※2)。
- データファイルに不要なデータを含んでいる。(＊ で表現)
- データファイルの複数フィールドを結合して作成するカラムがある。
- 関数を使用してデータを変換しているカラムがある。(UPPER 関数) (※1)
- データファイルに存在しないカラムがありデフォルト値の設定が必要。
(※2) Oracle 9i のマニュアルからは 「SQL 関数はダイレクト・パス・ロードでは使用できません」の表記が消されている。（Oracle 9i,10g 以降でもダイレクトパスになっているかは未確認）
ダイレクトパスはクライアントサイドでデータを整形するという性格上からクライアント（ローダー）で解釈＋変換可能な式（DETERMINISTIC な関数）なら実現の可能性も十分であるがユーザー定義関数は間違いなく実現されないだろうと思う。
(※2) 説明と見やすくするためにデータ中に改行コードを設置しているが、データファイルとしては改行がなくても、
固定長ファイルフォーマットによる SQL*Loader の入力ファイルとしてなんら問題ない。
しかし、ほとんどのテキストエディタでは、そのような１行だけの長大なファイルを閲覧するとメモリを大量消費する。
データベースサーバー上で、そのファイルをエディタで開かないほうがよい。
レコードレイアウト
| フィールド名 | テキストフォーマット |
| 部門 | DEPT | DECIMAL(2) |
| ユーザーID | USER_ID | CHARACTER(5) |
| 不要なデータ | - | CHARACTER(1) |
| 名前 | FIRST_NAME | CHARACTER(10) |
| 名字 | LAST_NAME | CHARACTER(10) |
| 靴の大きさ | SHOE_SIZE | NUMERIC(3,1) |
| 雇用日 | HIRE_DATE | DATE_TIME_STRING(19) |
| 不要なデータ | - | CHARACTER(9) |
| 行区切り文字 | | CR + LF |
１レコードのテキストイメージ
| 1 レコード 62 バイト |
| 9(2) | X(5) | X(1) | X(10) | X(10) | 9(3,1) | X(19) | X(9) | CR+LF |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
サンプル (ldrSample01.dat) .. データに改行(CR+LF)を含む
10U1000*ごんべい ななし 23.51999/12/31 23:58:59*********
10U1001*nonameCxxxnonameDxxx25.52005/12/31 01:02:03*********
10U1002*nonameE nonameF 35.02005/12/31 02:02:03*********
10U1003*ゴンベイ ナナシ 35.02005/12/31 03:02:03*********
10U1004*アイ
ウエオ カキクケコ 35.02005/12/31 04:02:03*********
サンプル (ldrSample02.dat)
20U1000*２んべい ななし 23.51999/12/31 23:58:59*********
20U1001*2onameCxxxnonameDxxx25.52005/12/31 01:02:03*********
20U1002*2onameE nonameF 35.02005/12/31 02:02:03*********
20U1003*2゛ンベイ ナナシ 35.02005/12/31 03:02:03*********
20U1004*2イ
ウエオ カキクケコ 35.02005/12/31 04:02:03*********
テーブル定義
CREATE TABLE RIVUS.NEW_MEMBER_LDR
(
USER_ID VARCHAR2(10),
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(15),
FULL_NAME VARCHAR2(31),
DEPT NUMBER(2,0),
SHOE_SIZE NUMBER(3,1),
HIRE_DATE DATE,
MEMO_PAD1 VARCHAR2(5),
MEMO_PAD2 VARCHAR2(5)
);
関連事項
