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 の入力ファイルとしてなんら問題ない。
しかし、ほとんどのテキストエディタでは、そのような1行だけの長大なファイルを閲覧するとメモリを大量消費する。 データベースサーバー上で、そのファイルをエディタで開かないほうがよい。

レコードレイアウト
フィールド名テキストフォーマット
部門DEPTDECIMAL(2)
ユーザーIDUSER_IDCHARACTER(5)
不要なデータ-CHARACTER(1)
名前FIRST_NAMECHARACTER(10)
名字LAST_NAMECHARACTER(10)
靴の大きさSHOE_SIZENUMERIC(3,1)
雇用日HIRE_DATEDATE_TIME_STRING(19)
不要なデータ-CHARACTER(9)
行区切り文字CR + LF
1レコードのテキストイメージ
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*2んべい  ななし    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)
);


関連事項

 | 概要・用法 | オプション | FAQ |
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー