大量のデータをロードするときにはSQL*Loaderを使用すると高速に処理が
できるようになります。基本から実際の使い方についてみていきましょう。
SQL*Loaderとは
SQL*LoaderはCSVファイルなどのテキスト化されているデータをロードする為の
ツールです。
例えば、1000万のレコードをテーブルに追加する場合、INSERTを1000万回実行するのは大変ですよね。
またCSVファイルをテーブルに取り込みたいという場合も、INSERT文に変換するとなるとものすごい手間がかかります。こんなときにSQL*Loaderを使用すると、CSVファイルを簡単にDBのテーブルに取り込むことができます。
SQL*Loaderは12cのバージョンからエクスプレスモードというモードが追加され、
さらに簡単にロードが簡単になりました。
今までSQL*Loaderを使用するときは、ロードするための情報を定義するファイル(制御ファイル)が必須でしたが、作成しなくてもロードが可能になりました。
データファイルは実際のロードするデータを記載するファイルです。
各列の値をカンマなどで区切った内容を記載します。
制御ファイルはロードするテーブルや、条件句(WHERE句)により一致したデータのみロードするなどの情報を記載します。
不良ファイルはロードに失敗したデータを格納するファイルです。例えばデータ型が
日付なのに文字データを挿入しようとし失敗した場合、不良ファイルに格納されます。
破棄ファイルは制御ファイルに記載した条件句で一致しなかったデータを格納します。
SQL*Loaderのデータファイルと制御ファイル
では実際に試してみましょう。
テーブルの定義は以下のとおりです。
SQL> desc emp
Name Null? Type
----------------------------- -------- -------------------
ID CHAR(3)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
データファイルと制御ファイルは以下のような内容にしています。
$ cat emp.dat 001,Tom,Tokyo 002,Abel,Saitama 003,King,Osaka $ cat emp.ctl LOAD DATA INFILE 'emp.dat' BADFILE 'emp.bad' DISCARDFILE 'emp.dsc' APPEND INTO TABLE emp WHEN ID != '003' (ID char(3),NAME char(20),ADDRESS char(30))
APPENDはテーブルにレコードが存在しても、ロードするオプションです。デフォルトは空テーブルでないとロードできません。
INTOはロードするテーブルを指定します。複数テーブルを指定することも可能です。
WHENは条件句となっており、IDが003のデータはロードしないようにしています。
それでは実行してみます。
$ sqlldr ora/ora@pdb1 CONTROL=emp.ctl
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Mar 26 14:55:22 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 4
Table EMP:
2 Rows successfully loaded.
Check the log file:
emp.log
for more information about the load.
実行結果は、「2 Rows successfully loaded.」というメッセージが表示されました。
WHEN句を使用しているので、003のレコードは破棄されています。
破棄ファイルは制御ファイル内で指定したDISCARDFILEキーワードで指定したemp.dscのファイルに格納されます。
$ cat emp.dsc
003,King,Osaka
では実際に取り込まれたかどうか確認してみます。
SQL> SELECT * FROM emp;
ID NAME ADDRESS
--- -------------------- ------------------------------
001 ,Tom,Tokyo
002 ,Abel,Saitama
NAME列とADDRESS列に分けて入れたかったのですが、NAME列に、「Tom,Tokyo」という文字が格納されています。
原因はデータファイル内のデータの区切りが認識されていないために発生しています。
これを回避するには、カンマ(,)が区切りであることを教えてあげる必要があります。
FIELDS TERMINATED BY ','
このキーワードを指定することで各列の値を区切ることができます。
このキーワードを制御ファイルに追加し、再度実行してみます。
またCPU時間などを制御することができます。
プロファイルの機能は大きく2つの項目を制御することができます。
$ cat emp.ctl
LOAD DATA
INFILE 'emp.dat'
BADFILE 'emp.bad'
DISCARDFILE 'emp.dsc'
APPEND
INTO TABLE emp
WHEN ID != '003'
FIELDS TERMINATED BY ','
(ID char(3),NAME char(20),ADDRESS char(30))
それでは再度実行してみましょう。先ほどロードされたレコードはDELETEで削除しています。
$ sqlldr ora/ora@pdb1 CONTROL=emp.ctl SQL*Loader: Release 12.1.0.1.0 - Production on Sat Mar 26 15:11:20 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 4 Table EMP: 2 Rows successfully loaded. Check the log file: emp.log for more information about the load. SQL> SELECT * FROM emp; ID NAME ADDRESS --- -------------------- ------------------------------ 001 Tom Tokyo 002 Abel Saitama
今度は各列の値を認識し、正常にロードすることができました。
レコード形式
レコード形式とはロードするデータファイルのどこまでが1行なのかを決めるための形式です。先ほどの実行例でも1行ずつ認識しロードを行ってくれました。
ではどのようにして各レコードを認識するのでしょうか。SQL*Loaderでのレコード形式は以下の形式があります。
・固定レコード形式
・可変レコード形式
・ストリームレコード形式(デフォルト)
固定レコード形式
固定レコード形式はサイズを指定し、そのサイズが1行を表す形式です。
この固定レコード形式の場合、各レコード長は同じ長さである必要があります。
柔軟性はありませんが、その結果、可変長またはストリーム形式よりも高いパフォーマンスを得ることができます。
[構文] 固定レコード形式
INFILE datafile_name "fix n"
INFILE datafile_name "fix n"
「fix n」のnは数値を指定します。この項目を制御ファイルに記載します。
固定レコード形式(制御ファイル)
$ cat emp_fix.ctl
LOAD DATA
INFILE 'emp_fix.dat' "fix 12"
BADFILE 'emp_fix.bad'
DISCARDFILE 'emp_fix.dsc'
APPEND
INTO TABLE emp
FIELDS TERMINATED BY ','
(ID char(3),NAME char(20),ADDRESS char(30))
上記では「FIX 12」としている為、12バイトを1行としてロードします。
固定レコード形式(データファイル)
$ cat emp_fix.dat
001,ABE,SIM
002,SAI,SAM
003,MON,SOM
上記のデータファイルの1レコードは「001,ABE,SIM」この文字列はカンマを含めて11バイトです。 1レコードは12バイトで指定したので1バイト足りないと思うかもしれませんが、1行目の最後に改行が入っています。
改行Unixの場合の改行コードはLF(1バイト),Windowsの場合はCR+LF(2バイト)となります。
今回の環境はLinuxなので、1バイトを合計すると12バイトとなります。
$ sqlldr ora/ora@pdb1 control=emp_fix.ctl SQL*Loader: Release 12.1.0.1.0 - Production on Mon Mar 28 16:07:11 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 3 Table EMP: 3 Rows successfully loaded. Check the log file: emp_fix.log for more information about the l. SQL> SELECT * FROM emp; ZID NAME ADDRESSZ --- -------------------- ------------------------------ 001 ABE SIM 002 SAI SAM 003 MON SOM
ロードできました。固定レコード形式の特徴はサイズが固定されているため、
各レコードのサイズが同じである必要があります。メリットは他の形式と比較
して処理速度が早いことです。
可変レコード形式
可変レコードは各レコード毎にサイズを指定する形式です。
各レコードサイズを別々に指定できるため、固定形式より柔軟です。
[構文] 可変レコード形式
INFILE datafile_name "var n"
INFILE datafile_name "var n"
では実行してみます。
制御ファイル(可変レコード形式)
$ cat emp_fix.ctl
LOAD DATA
INFILE 'emp_fix.dat' "var 3"
BADFILE 'emp_fix.bad'
DISCARDFILE 'emp_fix.dsc'
APPEND
INTO TABLE emp
FIELDS TERMINATED BY ','
(ID char(3),NAME char(20),ADDRESS char(30))
INFILEの部分にvar 3と指定されています。これはデータファイルの先頭3バイトの
数字が各レコードのサイズとなります。
データファイル(可変レコード形式)
$ cat emp_fix.dat
015001,ABEL,Tokyo
017002,KING,Okinawa
015003,TOM,Hokkaido
制御ファイルは"var 3"なので、先頭の3バイトが各レコードのサイズになります。
1レコード目は015となるので、15バイトとが1レコードです。前回と同様改行は
1バイトです。次は017となるので、次のレコード長は17バイトが1レコードと認識します。
$ sqlldr ora/ora@pdb1 control=emp_var.ctl SQL*Loader: Release 12.1.0.1.0 - Production on Mon Mar 28 16:07:11 2016 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 3 Table EMP: 3 Rows successfully loaded. Check the log file: emp_fix.log for more information about the load. SQL> SELECT * FROM emp; ID NAME ADDRESS --- -------------------- ------------------------------ 001 ABEL Tokyo 002 KING Okinawa 003 TOM Hokkaid
ロードできました。可変レコード形式の場合は各レコードの長さを決められるので、
柔軟な値をロードすることができます。
ストリームレコード形式
ストリームレコード形式は各レコードのサイズを指定するのではなく、レコードの改行コードを認識し、改行コードまでを1行としてロードします。
この形式が一番簡単ですが、他の形式と比べるとパフォーマンスは低下します。
またINFILE句にレコード形式の指定がない場合、ストリームレコード形式がデフォルトになります。
UNIXベースのプラットフォームでは、デフォルトで改行文字(LF)が使用されます。
[構文] ストリームレコード形式
INFILE datafile_name "str 改行コード"
INFILE datafile_name "str 改行コード"
構文内の改行コードで使用できる項目は以下の通りです。
\n：ラインフィード(LF)
\t：水平タブ
\f：改ページ
\v：垂直タブ
\r：キャリッジリターン(CR)
データファイル(ストリームレコード形式)
$ cat emp_fix.dat
001,ABEL,Tokyo
002,KING,Okinawa
003,TOM,Hokkaido
上記のデータファイルのように特に各レコードサイズは指定せず、ただレコードの内容を記載するだけです。
以上がレコード形式のご紹介となります。
SQL*Loaderのエクスプレスモード
エクスプレスモードは12cからの新機能であり、制御ファイルを作成しなくても、 対話形式でロードすることができます。必要なファイルはデータファイルのみです。
データファイル名はデフォルトでテーブル名.datのファイルをロードします。
$ sqlldr ora/ora@pdb1 table=emp
SQL*Loader: Release 12.1.0.1.0 - Production on Mon Mar 28 16:52:34 2017
Copyright(c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object
SYS_SQLLDR_XT_TMPDIR_00000 for file emp.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path,
using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting
with direct path, using value of NONEs
Express Mode Load, Table: EMP
Path used: Direct
Load completed - logical record count 4.
Table EMP:
3 Rows successfully loaded.
Check the log file:
emp.log
for more information about the load.
SQL*Loader-816のエラーが出力されていますが、これはデータファイル管理権限、CREATE ANY DIRECTORY権限が存在しないと、CREATE DIRECTORY SQLコマンドが失敗し、デフォルトの外部表のロード方法からダイレクト・パス・ロードに自動的に切り替えられます。ロードが失敗しているわけではありません。
SQL> select * from emp;
ID NAME ADDRESS
--- -------------------- --------------------
001 Tom Hokkaid
002 Abel Tokyo
003 King Okinawa
このようにエクスプレスモードを使用すると制御ファイルがなくてもロードできます。
その他オプションが多数あるので、ロードするデータファイル名を変更したり、
複数のデータファイルを読み込んだりすることもできます。
いかがでしたでしょうか。SQL*Loaderを使用するととても簡単にロードすることが可能になりましたね。