SQL*Loader

CSVファイルからOracleのテーブルへデータを流し込むツール。
大量のinsert文を発行するよりは、断然高速。

データであるCSVファイルや固定長ファイルと、ロード方法を指定するコントロールファイルを用意して実行する。

（CSVファイルからのロードはこのSQL*Loaderが使えるが、CSV出力には標準的な方法は無いらしくて、select文で加工する方法がよく使われるらしい。このSQL文をいちいち書くのは少々面倒なので、SQL生成用Excelマクロを作ってみました）

コントロールファイルの例 sqlldrの実行
- コントロールファイルの内容 [/2007-12-28]
- データファイルの概要 [/2007-12-28]
- 項目の指定方法 [/2007-12-28]
- 複数テーブル出力 [2007-12-28]

コントロールファイル

CSVファイルの各項目とテーブルの項目との関連付け等を指定する。
（コントロールファイルをテキストエディタで書くのはけっこう面倒なので、コントロールファイル作成用Excelマクロを作ってみました（CSVファイル用、固定長ファイル用））

例）emp.ctl：

OPTIONS(LOAD=100,SKIP=1,ERRORS=-1,ROWS=10)
LOAD DATA
INFILE 'data/emp.csv'
BADFILE 'emp.bad'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
)

コントロールファイルの内容

命令が書ける順番は概ね決まっている。不要な命令は書かなくてよい。

キーワード		説明		備考
	OPTIONS	sqlldrに渡す引数を、コントロールファイルの中に記述できる。[2004-11-15]		たぶん、「sqlldr -?」で出てくるオプションを指定できる。
		LOAD	ロードするレコード数	-1の場合、全て
		SKIP	スキップするレコード数	-1の場合、4294967295（0xffffffff）
		ERRORS	許容するエラーの数	-1の場合、全て
		ROWS	何件毎にコミットするか	-1の場合、4294967295（0xffffffff）
	LOAD DATA	ほぼお約束の記号
	CHARACTERSET	文字コードを指定したい場合に指定する。[2005-03-18]		例：「CHARACTERSET JA16SJIS」 JA16EUC、UTF8、JA16DBCS（EBCDIC）等
入力指定	INFILE	データファイルを指定する。入力データであるCSVファイルや固定長ファイル。複数ファイルを指定することも可能。[2007-12-28]		sql*loaderを実行したディレクトリからの相対パスでディレクトリを指定可能。
	INFILE			行末を示す文字コードを明示的に指定するには「`INFILE 'ファイル名' "STR x'0d0a'"`」の様にする。[2007-12-28]
	BADFILE	何らかのエラーがあってDBに入れられないデータがあった場合、そのデータがこのファイルに出力される。
	DISCARDFILE	WHENによってロード対象外となった廃棄データが、このファイルに出力される。[2005-03-18]
	INSERT APPEND REPLACE TRUNCATE	以下のいずれかのモードを指定する。
		INSERT	新規にデータをロードする。	テーブルは空である必要がある。既にデータがある場合はエラーとなる。（重複しないデータであっても！）
		APPEND	データを追加する。	既にデータがある場合は、duplicateしないデータだけが追加される。
		REPLACE	テーブルの内容を全て削除し、新規にデータをロードする。	削除は、DELETEに相当。
		TRUNCATE	テーブルの内容を全て削除し、新規にデータをロードする。	削除は、TRUNCATEに相当。truncateできる権限が必要。参照整合性制約を設定している場合は、それをオフにしておくべき。
出力指定	INTO TABLE	データを入れるテーブルを指定する。複数の出力先を指定することも可能。[2007-12-28]
	WHEN	データを入れる条件。SQLのWHERE句と似た書き方。しかし`=`,`<>`（`!=`）と`AND`しか使えない。[/2007-12-28] この条件によって廃棄されたデータは、廃棄ファイルに出力される。[2005-03-18]		例：「WHEN 列名=値」「WHEN (列名!=値) AND (列名<>値)」
	WHEN			例：「WHEN (桁位置) = 値」 [2007-12-28] 「`WHEN (1) = 'ABC'`」…行の一番左が“ABC”のデータが対象
	FIELDS	可変長の場合、項目の区切り方を指定する。		→項目毎の区切り
		TERMINATED BY	データを区切る文字を指定。	カンマ区切りの場合は「TERMINATED BY ","」タブ区切りにしたい場合は「TERMINATED BY X'09'」固定長ファイルの場合は不要
		OPTIONALLY ENCLOSED BY	データを囲む文字を指定。囲まない場合は不要。	ダブルクォーテーションで囲む場合は「OPTIONALLY ENCLOSED BY '"'」
	TRAILING NULLCOLS	この指定があると、データの無い項目にNULLを入れる。
	(項目 …)	データ移送先テーブルの項目名を記述する。（フィールドリスト）		ファイル内のデータの並び順に合わせて列挙する。

コントロールファイルの中で、「--」で始まっている行はコメント扱いになる。

データファイル（入力データ）

固定長ファイルも可能だが、よく使われるのはCSV形式のファイルだと思う。
データファイルの名前はコントロールファイル内（INFILE）に記述する。

コントロールファイルで指定した項目数よりCSVファイル側の項目数が多い場合は、無視されるだけで問題ない。

Windowsのテキストファイルの場合、ファイルの最後にEOFのコードが付いている場合がある。
これも一つの行と見なされ、（属性が不一致であれば（というかまず間違いなく不一致なので））エラーとなり、ロードされない。（badファイルに出力される）

INFILE（・BADFILE・DISCARDFILE）を複数書くことにより、複数のデータファイルを読み込むことが出来る。[2007-12-28]

～LOAD DATA
INFILE 'ファイル1' BADFILE '不良ファイル1.bad' DISCARDFILE '廃棄ファイル1.dis'
INFILE 'ファイル2' BADFILE '不良ファイル2.bad' DISCARDFILE '廃棄ファイル2.dis'
INFILE 'ファイル3' BADFILE '不良ファイル3.bad' DISCARDFILE '廃棄ファイル3.dis'
…
APPEND～

上記の例では、ファイル1のデータがエラーによって使えなかった場合、そのデータは不良ファイル1.badに出力される。
ファイル2のデータが不正の場合は不良ファイル2.badに出力される。

BADFILEを指定しなかった場合は、INFILEのファイル名の拡張子をbadに変えたものが使われる。
DISCARDFILEを指定しなかった場合は、WHENによって廃棄されたデータはどこにも出力されない。指定されたファイル名に拡張子が無い場合は、.dscが付けられる。
これらのファイル名はログに出力される。

フィールドリスト

各項目の後ろには関数を書いて演算をすることも出来る。

  MGR,
  HIREDATE "TO_DATE(:HIREDATE,'YYYY/MM/DD HH24:MI:SS')",
  SAL,

この際、関数の引数に書く項目名は「:（コロン）」を付ける事。これを忘れるとORA-00984に悩むことになる。

また、項目毎にファイル内のデータの属性を指定することも出来る。これは特に固定長ファイルの場合に重要。

属性	説明	DBの属性	指定例	データ例	DBに入るもの
CHAR	文字列	char、varchar2	CHAR	hoge	hoge
DECIMAL EXTERNAL	数値	number	DECIMAL EXTERNAL	123	123
ZONED	数値（小数扱い）	number	ZONED(7,2)	1234567	12345.67
DATE	日付。書式を後ろに付ける	date	DATE "YYYYMMDD"	20041030	2004-10-30
CONSTANT	定数（ファイル内のデータを使わない）	何でも	CONSTANT 100		100

空白のみの項目はやはり空文字列として扱われ、nullにはならない。nullを入れたい場合は以下のような工夫が必要。

  SAL POSITION( 36 : 42 ) ZONED(7,2),
  COMM POSITION( 43 : 49 ) CHAR "decode(:COMM, '',null, to_number(:COMM))",
  DEPTNO POSITION( 50 : 51 ) DECIMAL EXTERNAL

と思っていたら、nullに変換してくれる方法が用意されていた(汗) [2007-12-28]

  SAL POSITION( 36 : 42 ) ZONED(7,2),
  COMM POSITION( 43 : 49 ) ZONED(7,2) NULLIF COMM=BLANKS,
  DEPTNO POSITION( 50 : 51 ) DECIMAL EXTERNAL

NULLIFの後ろの条件が満たされるとnullになる。BLANKSは色々な空白を表す。「COMM="0000000"」とか「COMM=X'30303030303030'」とか「(43:44)="00"」（桁位置指定）とかも可。

FIELDSで共通の区切り文字を指定する他に、項目毎に個別の終端文字を指定することも出来る。[2007-12-28]

(
EMPNO    TERMINATED BY ':',
ENAME    TERMINATED BY '/',
JOB      TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
MGR      TERMINATED BY '#',
HIREDATE TERMINATED BY '#' "TO_DATE(:HIREDATE,'YYYY/MM/DD HH24:MI:SS')",
SAL      TERMINATED BY ';',
COMM     TERMINATED BY ',',
DEPTNO
)

個別の終端文字を指定しないと、FIELDSで指定した文字が終端文字として使われる。

複数データ出力先

「INTO TABLE ～ (項目 …)」の組も複数指定することが出来る。[2007-12-28]

データ出力先のテーブルは同一テーブルでもいいし別テーブルでもいい。
ただ、項目群（フィールドリスト）は（特に何も指定しない場合は）入力ファイルの同一行内の続きとして扱われる。

コントロールファイルの例：

～APPEND
INTO TABLE table1 FIELDS TERMINATED BY ',' (t1col1, t1col2, t1col3)
INTO TABLE table2 FIELDS TERMINATED BY ',' (t2col1, t2col2)

データファイルの例：

data1,data2,data3,data4,data5

上記の例の場合、data1→t1col1、data2→t1col2、data3→t1col3、data4→t2col1、data5→t2col2、という入り方をする。

2つ目のフィールドリストの先頭の項目で桁位置を指定してやれば、データの位置をずらせる。[2007-12-28]

コントロールファイルの例：

～APPEND
INTO TABLE table1 FIELDS TERMINATED BY ',' (t1col1, t1col2, t1col3)
INTO TABLE table2 FIELDS TERMINATED BY ','
(
 t2col1 POSITION(1), --行の1桁目を指定
 t2col2               --続きの位置になる
)

この例の場合、data1→t1col1、data2→t1col2、data3→t1col3（ここまでは同じ）、data1→t2col1、data2→t2col2、となる。

それぞれのINTO TABLEの後ろにWHENを付けると、その条件を満たしたときだけデータ移送が行われる。[2007-12-28]
全てのWHENで否定されると廃棄データ行きとなる。

実行

sqlldrの引数にコントロールファイルの名前を指定して実行する。

Windowsの場合、バッチファイルを作っておくと便利。このファイルをダブルクリックするとロードが実行される。ネットワークドライブ上では駄目っぽかったけど。

例）emp_load.bat：

C:\oracle\ora92\BIN\SQLLDR ユーザー/パスワード@SID control=ctl/emp.ctl
pause

実行すると、バッチファイルと同じディレクトリにログファイルが出来る。

C:\sample>tree /f
フォルダ パスの一覧
ボリューム シリアル番号は 71XYZ346 BYYY:9ZZZ です
C:.
│  emp.bad
│  emp.log
│  emp_load.bat
│
├─ctl
│      emp.ctl
│
└─data
        emp.csv

#SQL*Loader制御ファイル・リファレンス

posted @ 2013-01-15 10:45 jyogou 阅读(295) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部