S-JIS[2004-10-02/2013-09-10] 変更履歴

SQL*Loader

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

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

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


コントロールファイル

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

例)emp.ctl:

OPTIONS(LOAD=100,SKIP=1,ERRORS=-1,ROWS=10)
LOAD DATA
INFILE 'data/emp.csv'
BADFILE 'emp.bad'
APPEND
PRESERVE BLANKS
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 'ファイル名' "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できる権限が必要。参照整合性制約を設定している場合は、それをオフにしておくべき。
PRESERVE BLANKS CSVファイル内のカンマの前後の空白を除去しない(空白を残す)。[2013-09-10]  



INTO TABLE データを入れるテーブルを指定する。
複数の出力先を指定することも可能。[2007-12-28]
 
WHEN データを入れる条件。SQLのWHERE句と似た書き方。しかし=,<>!=)とANDしか使えない。[/2007-12-28]
この条件によって廃棄されたデータは、廃棄ファイルに出力される。[2005-03-18]
例:「WHEN 列名=値」「WHEN (列名!=値) AND (列名<>値)」
例:「WHEN (桁位置) = 値」 [2007-12-28]
WHEN (1) = 'ABC'」…行の一番左が“ABC”のデータが対象
FIELDS 可変長の場合、項目の区切り方を指定する。 項目毎の区切り
TERMINATED BY データを区切る文字を指定。 カンマ区切りの場合は「TERMINATED BY ","
タブ区切りにしたい場合は「TERMINATED BY X'09'
空白(スペース・タブ・改行を複数)区切りにしたい場合は「TERMINATED BY WHITESPACE[2013-09-10]
固定長ファイルの場合は不要
OPTIONALLY ENCLOSED BY データを囲む文字を指定。囲まない場合は不要。 ダブルクォーテーションで囲む場合は「OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS この指定があると、データの無い項目にNULLを入れる。  
(項目 …) データ移送先テーブルの項目名を記述する。(フィールドリスト ファイル内のデータの並び順に合わせて列挙する。

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


データファイル(入力データ)

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

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

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


INFILE(・BADFILEDISCARDFILE)を複数書くことにより、複数のデータファイルを読み込むことが出来る。[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

Oracle目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま