トップ > DBA中級 > 9章
9章 SQL*Loader
それでは本章はSQL*Loaderの使用方法ついてご紹介していきます。
大量のデータをロードするときにはSQL*Loaderを使用すると高速に処理が
できるようになります。基本から実際の使い方についてみていきましょう。

SQL*Loaderとは


SQL*LoaderはCSVファイルなどのテキスト化されているデータをロードする為の
ツールです。

例えば、1000万のレコードをテーブルに追加する場合、INSERTを1000万回実行するのは大変ですよね。
またCSVファイルをテーブルに取り込みたいという場合も、INSERT文に変換するとなるとものすごい手間がかかります。こんなときにSQL*Loaderを使用すると、CSVファイルを簡単にDBのテーブルに取り込むことができます。

SQL*Loaderは12cのバージョンからエクスプレスモードというモードが追加され、
さらに簡単にロードが簡単になりました。
今までSQL*Loaderを使用するときは、ロードするための情報を定義するファイル(制御ファイル)が必須でしたが、作成しなくてもロードが可能になりました。

DataPump

データファイルは実際のロードするデータを記載するファイルです。
各列の値をカンマなどで区切った内容を記載します。

制御ファイルはロードするテーブルや、条件句(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"

「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"

では実行してみます。

制御ファイル(可変レコード形式)

  $ 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 改行コード"

構文内の改行コードで使用できる項目は以下の通りです。
\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を使用するととても簡単にロードすることが可能になりましたね。