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 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.3.18] | 例:「CHARACTERSET JA16SJIS」 JA16EUC、UTF8、JA16DBCS(EBCDIC)等 | |
INFILE | 入力データであるCSVファイルや固定長ファイル | sql*loaderを実行したディレクトリからの相対パスでディレクトリを指定可能。 | |
BADFILE | 何らかのエラーがあってDBに入れられないデータがあった場合、そのデータがこのファイルに出力される。 | ||
DISCARDFILE | WHENによってロード対象外となった廃棄データが、このファイルに出力される。[2005.3.18] | ||
INSERT APPEND REPLACE TRUNCATE | 以下のいずれかのモードを指定する。 | ||
INSERT | 新規にデータをロードする。 | テーブルは空である必要がある。既にデータがある場合はエラーとなる。(重複しないデータであっても!) | |
APPEND | データを追加する。 | 既にデータがある場合は、duplicateしないデータだけが追加される。 | |
REPLACE | テーブルの内容を全て削除し、新規にデータをロードする。 | 削除は、DELETEに相当。 | |
TRUNCATE | 削除は、TRUNCATEに相当。truncateできる権限が必要。参照整合性制約を設定している場合は、それをオフにしておくべき。 | ||
INTO TABLE | データを入れるテーブル | ||
WHEN | データを入れる条件。SQLのWHERE句と同様の書き方。 この条件によって廃棄されたデータは、廃棄ファイルに出力される。[2005.3.18] | 例:「WHEN 列名=値」「WHEN (列名>=値) AND (列名<=値)」 | |
FIELDS | 項目の区切り方の指定。 | ||
TERMINATED BY | データを区切る文字を指定。 | カンマ区切りの場合は「TERMINATED BY ","」 タブ区切りにしたい場合は「TERMINATED BY X'09'」 固定長ファイルの場合は不要 | |
OPTIONALLY ENCLOSED BY | データを囲む文字を指定。囲まない場合は不要。 | ダブルクォーテーションで囲む場合は「OPTIONALLY ENCLOSED BY '"'」 | |
TRAILING NULLCOLS | この指定があると、データの無い項目にNULLを入れる。 |
コントロールファイルの中で、「--」で始まっている行は コメント扱いになる。
各項目の後ろには、関数を書いて演算をすることも出来る。
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
データファイル
固定長ファイルも可能だが、よく使われるのはCSV形式のファイルだと思う。
データファイルの名前は、コントロールファイル内に記述する。
コントロールファイルで指定した項目数よりCSVファイル側の項目数が多い場合は、無視されるだけで問題ない。
Windowsのテキストファイルの場合、ファイルの最後にEOFのコードが付いている場合がある。
この行は(属性が不一致であれば)エラーとなり、ロードされない。(badファイルに出力される)
実行
sqlldrの引数にコントロールファイルの名前を指定して実行する。
Windowsの場合、バッチファイルを作っておくと便利。このファイルをダブルクリックするとロードが実行される。ネットワークドライブ上では駄目っぽかったけど。
例)emp_load.bat:
C:/oracle/ora92/BIN/SQLLDR ユーザー/パスワード@SID control=ctl/emp.ctl pause
実行すると、バッチファイルと同じディレクトリにログファイルが出来る。
C:/sample>tree /f フォルダ パスの一覧 ボリューム シリアル番号は 71EFE346 B876:91FD です C:. │ emp.bad │ emp.log │ emp_load.bat │ ├─ctl │ emp.ctl │ └─data emp.csv