SQL*Loader

業務で大量データをテーブルに投入する必要がありSQL*Loaderを使った。その際に調べた内容を備忘録として残しておきます。

SQLLoaderとは？

固定長やCSV形式の外部ファイルからOracleデータベースにロードするユーティリティ。制御ファイルで指定した内容をもとにデータファイルのデータをデータベースに格納する。

データファイル
ロードするデータを保持するファイル。固定長やCSV形式等を指定することができる。
制御ファイル
データファイルの場所やデータの解釈方法などロードに必要な制御情報を保持するファイル
ログファイル
ロード件数やエラーログなどが記録される。
不良ファイル
エラーによりロードされなかったレコードが記録される
廃棄ファイル
エラー以外の理由でロードされなかったレコードが記録される



制御ファイル記述例

  • サンプルテーブル
CREATE TABLE EMP ( 
	EMPNO						NUMBER(4)		NOT NULL,
	ENAME						VARCHAR2(10),
	JOB						VARCHAR2(9),
	MGR						NUMBER(4),
	HIREDATE					DATE,
	SAL						NUMBER(7,2),
	COMM						NUMBER(7,2),
	DEPTNO						NUMBER(2), 
	CONSTRAINT PK_EMP PRIMARY KEY(EMPNO) 
) 
/
  • CSVファイル
1,１０１,10,,20100101,10000,10,10
2,１０２,10,,20100101,10001,10,20
3,１０３,10,,20100101,10002,10,30
4,１０４,10,,20100101,10003,10,40
5,１０５,10,,20100101,10004,10,10
6,１０６,10,,20100101,10005,10,20
7,１０７,10,,20100101,10006,10,30
8,１０８,10,,20100101,10007,10,40
9,１０９,10,,20100101,10008,10,10
・・・
  • 制御ファイル
LOAD DATA
  INFILE      'C:\DATA.csv'
  APPEND INTO TABLE EMP
  FIELDS TERMINATED BY ','
  TRAILING NULLCOLS
  (
    empno,
    ename,
    job,
    mgr,
    hiredate DATE 'YYYYMMDD',
    sal,
    comm,
    deptno
  )
  1. INFILE句には、データ・ファイルの名前を指定します。
  2. BADFILE句には、不良ファイルの名前を指定します。指定しない場合は制御ファイル名.BAD。
  3. DISCARDFILE句には、破棄ファイルの名前を指定します。指定しない場合は制御ファイル名.DSC。
  4. INTO TABLE句にははデータをロードするモードを指定する。
    1. INSERT：表にデータを追加する。ただしテーブルが空でない場合はエラーとなる。
    2. APPEND：表にデータを追加する。既にデータがある場合、重複しないレコードのみが追加される
    3. REPLACE：表のデータをすべて入れ替える。
    4. TRUNCATE：表のデータを切り捨てて追加する。
  5. FIELDS句には全ての列に対するオプションを指定する
    1. カンマ区切りの場合：TERMINATED BY ','
    2. タブ区切りの場合：TERMINATED BY X'09'
    3. ダブルクォーテーションで囲まれている場合は「OPTIONALLY ENCLOSED BY '"'」を指定する
    4. データがない場合にNULLを入れるように指定する場合は「TRAILING NULLCOLS」を指定する
    5. 通常空白文字は切り捨てられるが、これを行わないようにする場合は「PRESERVE BLANKS」を指定する
  • 実行
C:\Documents and Settings\Administrator>sqlldr user/passwd@orcl control="C:\DATA.CTL" log="C:\DATA.log"

SQL*Loader: Release 11.2.0.1.0 - Production on 月 7月 18 20:07:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

コミット・ポイントに達しました。 - 論理レコード件数64
コミット・ポイントに達しました。 - 論理レコード件数128
コミット・ポイントに達しました。 - 論理レコード件数192
コミット・ポイントに達しました。 - 論理レコード件数256
コミット・ポイントに達しました。 - 論理レコード件数320
コミット・ポイントに達しました。 - 論理レコード件数384
コミット・ポイントに達しました。 - 論理レコード件数448
コミット・ポイントに達しました。 - 論理レコード件数512
コミット・ポイントに達しました。 - 論理レコード件数576
コミット・ポイントに達しました。 - 論理レコード件数640
コミット・ポイントに達しました。 - 論理レコード件数704
コミット・ポイントに達しました。 - 論理レコード件数768
コミット・ポイントに達しました。 - 論理レコード件数832
コミット・ポイントに達しました。 - 論理レコード件数896
コミット・ポイントに達しました。 - 論理レコード件数960
コミット・ポイントに達しました。 - 論理レコード件数1000

C:\Documents and Settings\Administrator>