SQL＊Loaderの使い方

SQL＊Loaderの使い方

SQL*Loaderの使い方です。コマンドは以下のようにします。

sqlldr ユーザID/パスワード control=コントロール.ctl data=インタフェースファイル.csv bad=badファイル.bad log=ログファイル.log direct=TRUE

controlでコントロールファイルを指定します。

dataでインタフェースファイルを指定しますがこのデータがテーブルにinsertするデータになります。

badで指定するファイルはinsertに失敗したデータがログとして出力されます。

logで指定するファイルはログファイルです。

direct=TRUEと指定すると、SQL*Loaderの処理が早くなるので普通は記述しますが、いくつか制約もあります。

• クラスタ化された表には使用できない
• データロード中、表ロックがかかる
• データロード時、外部キー制約、チェック制約が無視される

そのため、PKを指定しても重複したデータが登録できてしまいます。SQLエディタ（A5M2など）で重複データを削除しようとすると、ORA-01502エラーが発生するのでtruncateするしかありません。

コントロールファイルについてですが、固定長の場合は以下のように記述します。

OPTIONS(SKIP=0,ERRORS=50000,ROWS=1000)
LOAD DATA
TRUNCATE
INTO TABLE テーブル名
(
カラム名1 POSITION(1:10) CHAR,
カラム名2 POSITION(11:12) CHAR,
カラム名3 CONSTANT '1',
カラム名4 DECIMAL EXTERNAL "DECODE(:カラム名4,'00','0','1')"
)

SKIP=0でインタフェースファイルの先頭行からデータとして読み込みます。

ERRORSでエラーデータの最大許容量を指定します。

ROWSでコミット単位を指定します。

その後にLOAD DATAと記述します。

TRUNCATEと書くと一旦テーブルをTRUNCATEしてからインタフェースファイルのデータを読み込みます。TRUNCATE以外にもあります。

INTO TABLE テーブル名

上記でインサートするテーブル名を指定します。

POSITION(1:10)でインタフェースファイルの1バイト目から10バイト目までをカラム名1に入れるという意味です。

定数をいれたい場合はCONSTANTキーワードを使用します。

数値（NUMBER）を指定したい場合はDECIMAL EXTERNALを指定します。

CSV形式の場合は以下のように記述します。

OPTIONS(SKIP=0,ERRORS=50000,ROWS=1000)
LOAD DATA
TRUNCATE
INTO TABLE テーブル名
FIELDS TERMINATED BY ','
(
カラム名1 CHAR,
カラム名2 TO_NUMBER(:カラム名2),
カラム名3 CONSTANT '1',
カラム名4 SYSDATE
)

カンマ区切りの場合は、以下の1文を記述します。

FIELDS TERMINATED BY ','

SQL*Loaderを実行すると以下のエラーが出る場合があります。

ORA-01009　必須パラメータがありません。

原因はいくつかありますが、定数を空にするとこのエラーが発生します。これはカラム名にNULLを設定したい場合に下記のように記述したらORA-01009が発生します。NULLを設定したい場合はカラム名自体記述する必要がありません。

カラム名 CONSTANT ''

CSV形式で、全ての列をテーブルにロードするのではなく、ある特定の列のみロードしたい場合があります。

例えば以下のようなCSVがあるとします。

K01,100,0

全部で3列のCSVですが、3列目を読み飛ばしてテーブルにロードしたいとします。

そのような場合はFILLERを使います。以下、コントロールファイルです。

OPTIONS(SKIP=0,ERRORS=50000,ROWS=1000)
LOAD DATA
TRUNCATE
INTO TABLE テーブル名
FIELDS TERMINATED BY ','
(
カラム名1 CHAR,
カラム名2 CHAR,
"dummy"   FILLER
)

“dummy”としていますが、”a”でも良いですし、ダブルクォーテーションで囲む必要もありません。但し、テーブルに存在する列名を指定すると以下エラーが発生しますので、”dummy”などのありえないカラム名が良いです。

SQL*Loader-404?列～～～が「テーブル名」のINTO TABLE文に複数個存在します。

列を読み飛ばしたい場合はFILLERを使うということを覚えておくと便利です。

SQL*Loaderでなるべく複数のテーブルに取り込まない

SQL*Loaderは大量データを取り込む時に高速で取り込めるため便利なツールです。

一般的には他システムからきたIFファイル（csvまたは固定長）を取り込む場合にシェルでSQL*Loaderを使うと思います。

FILLERを使って1列目と3列目をテーブルAに取り込み、2列目と4列目をテーブルBに取り込むような場合は設計を見直すべきで、一旦SQL*LoaderでテーブルCに取り込んでから別のプログラムでテーブルAに取り込み、また別のプログラムでテーブルBに取り込むべきです。

このようなCSV形式のデータが他システムから来た場合、料金に「あ」と入っているため不正なデータ（1行）です。しかし先ほどの設計でいくと、2列目と4列目をテーブルBに取り込む場合は正常データとして取り込んでしまいます。

そのため、SQL*Loaderで取り込む場合は一旦一つのテーブルに取り込むべきだと思います。

SQL*Loaderで指定した条件を取り込む

WHENを使用すると、指定した条件のデータのみを取り込むことができます。

INTO TABLE テーブル名のあとにWHENを指定します。

WHEN RYOKIN IS NOT NULL

というようにIS NOT NULLは指定できません。この場合以下のようにします。

WHEN RYOKIN != BLANKS

逆の場合は以下のように記述します。

WHEN RYOKIN = BLANKS
WHEN RYOKIN = ''

但し、この場合はなぜか上手くデータが取得できません。= BLANKSのデータを取得することはできないようです。

WHEN RYOKIN = '100'

というように空以外であれば、＝は効きます。

SQL*Loaderで取り込むファイルはシェルのforでループしない

ちょっとSQLLoaderの使い方とは話がそれますが、SQLLoaderは基本的に大量データをインサートするために使うツールです。

その為、シェルでファイルを1行ずつfor文でループし、バイトチェックなどをすることは絶対にやってはいけません。（シェルのループは時間がかかるため）

どうしてもバイトチェックやCSVの項目数チェックを行いたい場合はawkを使います。

以下、参考までに書きます。$?は成功なら0、失敗なら1を返します。

cat a.csv | awk 'BEGIN{FS=","} {if(NF!=12) exit 1}'

インタフェースファイルがダブルクォーテーションで囲まれている場合

ダブルクォーテーションなどの区切り文字で各項目が括られてくるインタフェースファイルの場合は、コントロールファイルに、OPTIONALLY ENCLOSED BY ‘”‘を指定します。

OPTIONS(SKIP=0,ERRORS=50000,ROWS=1000)
LOAD DATA
TRUNCATE
INTO TABLE テーブル名
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
カラム名1 CHAR,
カラム名2 CHAR,
"dummy"   FILLER
)

TRUNCATEモード

テーブルにロードする前にTRUNCATEしてからロードします。

APPENDモード

テーブルに既存データの有無に関係なくロードします。

ダイレクトパスロード＝TRUEにすると主キーが重複したデータが入る可能性があります。

ダイレクトパスロード=FALSEにすると重複したデータはbadファイルに出力されます。

高橋

KHI入社して退社。今はCONFRAGEで正社員です。関西で140-170/80～120万から受け付けております^^
得意技はJS(ES6),Java,AWSの大体のリソースです
コメントはやさしくお願いいたします^^
座右の銘は、「狭き門より入れ」「願わくは、我に七難八苦を与えたまえ」です^^