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(・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