« iPhoneから読まれてる! | トップページ | 携帯へのスパムメール »

2007年9月23日 (日)

SQL*Loader で BLOBロード

少々前に、大量のBLOBデータをデータベースへロードするためのプログラムをコーディングしている方がいたのだが、そのプログラムの仕様を聞いてみたら、SQL*Loaderでも対応可能な内容だった。SQL*LoaderでBLOBデータをローディングできることは知らなかったのだ。知っていたらもっと楽だったろうに。


今回の例ではOracle10g R1を利用しているのだが、Oracle10g R2でも使い方は同じ。

● 参考:
・Oracle Database ユーティリティ 10g リリース1(10.1)
・Oracle Database ユーティリティ 10g リリース2(10.2)LOBFILEからのLOBデータロード
・Oracle Database ユーティリティ 10g リリース2(10.2)EXPRESSIONパラメータ

ということで、前述の話に比べると少々単純なのだがロードの例を一つ。

● まずは準備。

BLOBを格納する表のキーはユニークな数値なので、シーケンスを利用する。え!? SQL*Loaderでシーケンスが利用できるのか?
できるんです。EXPRESSIONパラメータを利用すれば!(EXPRESSIONパラメータの利用例は随分前にOTN-JのCode Tipsへも投稿してあったっけ。OTN-JのCode Tipsを参照するにはOTN-Jへのユーザ登録(無料)必要です。)

> conn scott/tiger
接続されました。
SCOTT>
SCOTT>
SCOTT> create sequence sq_blob_key
2 start with 1
3 increment by 1
4 maxvalue 999999
5 nocycle
6 nocache;

順序が作成されました。

BLOBデータを登録する表の定義は以下。
(blob用表領域は他のデーブルデータとは別表領域にしている)

SCOTT> l
1 create table blobtab
2 (
3 blob_id number primary key,
4 blob_type varchar(4),
5 content blob
6 )
7 lob(content) store as
8 (
9 tablespace blobspace
10 nocache logging
11* )
SCOTT> /

表が作成されました。

SCOTT>


● SQL*Loaderの制御ファイルとBLOBとして登録するファイルは同一ディレクトリ上へ配置。

ちなみに、Linux/Unix系OSでは、hostコマンドまたは、! を利用すればSQL*Plusからshellコマンドなとを直接実行できる。
SCOTT>  
SCOTT> !ls | grep -E '¥.(ctl|jpg|zip|png|gif)$'
blobload.ctl
discus.gif
generic_connectivity.zip
image001.png
neontetra.jpg

SCOTT>


● SQL*Loaderの制御ファイルの内容

ポイントは、シーケンスを利用するために、expressionパラメータで sq_blob_key.nextval と設定している点と、ex_fname FILLER以降の定義(赤字部分)。
SCOTT> !cat blobload.ctl
LOAD DATA
INFILE 'blobload.dat'
INTO TABLE blobtab
FIELDS TERMINATED BY ','
(
blob_id expression "sq_blob_key.nextval",
blob_type,
ext_fname FILLER CHAR(80),
content LOBFILE(ext_fname) TERMINATED BY EOF
)


● SQL*Loaderの制御ファイルのINFILEパラメータで指定するデータファイルの内容

この例では同一ディレクトリに配置した gif/zip/png/jpgを登録対象とした。
拡張子、ファイル名の順にカンマ区切りでデータファイルを作成。(awkを使えば簡単ですしね)

SCOTT> !ls | grep -E '¥.(zip|jpg|png|gif)$' | awk '{print substr($1,index($1,".")+1)","$1","}' > blobload.dat
SCOTT> !cat blobload.dat
gif,discus.gif,
zip,generic_connectivity.zip,
png,image001.png,
jpg,neontetra.jpg,

SCOTT>
SCOTT>


● 準備完了!

準備が整った。今回はSQL*Loaderの制御ファイル、データファイル、BLOBとして登録するファイルを同一ディレクトリに配置した。
SCOTT> !ls | grep -E '¥.(ctl|dat|jpg|zip|png|gif)$'
blobload.ctl
blobload.dat
discus.gif
generic_connectivity.zip
image001.png
neontetra.jpg

SCOTT>


● SQL*Loaderの実行

SCOTT> !sqlldr userid=scott/tiger@xtiger control=blobload.ctl log=blobload.log

SQL*Loader: Release 10.1.0.3.0 - Production on 土 9月 22 17:12:16 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

コミット・ポイントに達しました。 - 論理レコード件数4

SCOTT>


● SQL*Loaderのログを確認!

SCOTT> !cat blobload.log

SQL*Loader: Release 10.1.0.3.0 - Production on 土 9月 22 17:12:16 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

制御ファイル: blobload.ctl
データ・ファイルblobload.dat
不良ファイル: blobload.bad
廃棄ファイル: 指定なし

(すべて廃棄できます)

ロード数: ALL
スキップ数: 0
許容エラー数: 50
バインド配列: 64行、最大256000バイト
継続文字: 指定なし
使用パス: 従来型

表BLOBTAB、 ロード済 すべての論理レコードから
この表に対する有効な挿入オプション: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BLOB_ID EXPRESSION
列のSQL文字列 : "sq_blob_key.nextval"
BLOB_TYPE FIRST * , CHARACTER
EXT_FNAME NEXT 80 , CHARACTER
(FILLERフィールド)
CONTENT DERIVED * EOF CHARACTER
動的LOBFILE - ファイル名EXT_FNAME


表BLOBTAB:
4行のロードに成功しました。
0行はデータ・エラーのためロードされませんでした。
0行はWHEN句のエラーのためロードされませんでした。
0行はすべてのフィールドがNULLのためロードされませんでした。

バインド配列に割り当てられた領域: 21760バイト(64行)
読取りバッファのバイト数: 1048576

スキップされた論理レコードの合計: 0
読み込まれた論理レコードの合計: 4
拒否された論理レコードの合計: 0
廃棄された論理レコードの合計: 0

実行開始土 9月 22 17:12:16 2007
実行終了土 9月 22 17:12:17 2007

実行時間: 00: 00: 00.80
CPUタイム : 00: 00: 00.06

SCOTT>


● 内容確認。(登録ファイルのサイズとBLOBサイズが同じであることを確認)

SCOTT> select
2 blob_id,
3 blob_type,
4 lengthb(content)
5 from
6 blobtab
7 order by
8 blob_id;

BLOB_ID BLOB LENGTHB(CONTENT)
---------- ---- ----------------
1 gif 9675
2 zip 20070
3 png 2327573
4 jpg 6492

SCOTT>
SCOTT> !ls -l | grep -E '¥.(jpg|zip|png|gif)$'
-rw-r--r-- 1 oracle oinstall 9675 Dec 31 1997 discus.gif
-rw-r--r-- 1 oracle oinstall 20070 Nov 17 2005 generic_connectivity.zip
-rw-r--r-- 1 oracle oinstall 2327573 Jul 21 16:21 image001.png
-rw-r--r-- 1 oracle oinstall 6492 May 4 1999 neontetra.jpg

SCOTT>

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/16547147

この記事へのトラックバック一覧です: SQL*Loader で BLOBロード:

コメント

コメントを書く