Oracle Database10g SQL*Loader

Oracle Database10g SQL*Loader

[[Oracle][Oracle Database10g]]

link

概要

• SQL*Loaderを使用して、外部ファイルのデータをOracleデータベースの表にロードできる
• 一般的なSQL*Loaderセッションでは、SQL*Loaderの動作を制御する制御ファイルと1つ以上のデータ・ファイルが入力用に使用される
• SQL*Loaderの出力先は、データがロードされるOracleデータベース、ログ・ファイル、不良ファイルで、廃棄ファイルに出力される場合もある

SQL*Loaderのパラメータ

• SQL*Loaderは、sqlldrコマンドを指定すると起動します。また、オプションで、セッション特性を確立するパラメータを指定した場合も起動します。
• 常に、値がほとんど変らない同じパラメータを使用する場合は、コマンドラインではなく、次の方法でパラメータを指定すると効率的
  • パラメータ・ファイルとしてグループ化。その後、PARFILEパラメータを使用して、そのパラメータ・ファイルの名前をコマンドラインで指定。
  • 一部のパラメータは、OPTIONS句を使用して、制御ファイル内に指定。

データのロード方法

以下の2つの方法

方法	内容
従来型パス・ロード	データベースの表に対して（1つ以上の）SQL INSERT文が実行されます
ダイレクト・パス・ロード	データ・ブロックをフォーマットし、データ・ブロックを直接データ・ファイルに書き込むため、オーバーヘッドが大幅に削減

従来型パス・ロード

• 従来型パス・ロード（デフォルト）では、SQL INSERT文とバインド配列バッファを使用して、データをデータベース表にロード
• バッファ・リソースに関して他のすべてのプロセスと同等の処理が行われるため、競合が発生
• SQL文が生成され、 Oracleに渡されてから実行されるため、オーバーヘッドが発生
• 挿入が発生すると、常に、Oracleデータベースで空き領域のあるブロック（ディスク内に散在して、部分的に書込み可能なブロック）が検索され、そこにデータが書き込まれる
• 大量データのロード速度を大幅に低下させることがあります。

ダイレクト・パス・ロード

• バインド配列バッファに書き込むかわりに、SQL INSERT文を使用して、バインド配列をOracleデータベースに渡す。
• ダイレクト・パス・ロードは、ダイレクト・パスAPIを使用して、ロードされるデータをサーバーのロード・エンジンに渡す。
• ロード・エンジンは、渡されたデータから列配列構造体を作成
• ロード・エンジンは、列配列構造体を使用してOracleデータ・ブロックをフォーマットし、索引キーを作成します。新しくフォーマットされたデータベース・ブロックを直接データベースに書き込む
• I/Oを伴う処理がオーバーラップするため、ロード・パフォーマンスが向上。

ダイレクト・パス・ロードの指定

• SQL*Loaderをダイレクト・パス・ロード・モードで起動するには、次の形式で、コマンドラインまたはパラメータ・ファイル（使用している場合）のDIRECTパラメータにtrueを設定します。

DIRECT=true

SQL*Loader制御ファイル

• 制御ファイルは、SQL*Loaderが解釈できる言語で記述されたテキスト・ファイル

3つのセクション

セクション	内容
第1セクション	セッション全体の情報
第2セクション	1つ以上のINTO TABLEブロックで構成、それぞれのブロックには、表名、その表の列などの、データがロードされる表についての情報
第3セクション	オプションで、このセクションがある場合は、入力データを記述

制御ファイルの内容

• SQL*LoaderのDDL構文図

コメント

• コメントはファイル中のコマンド部分のどこにでも記述できますが、データの部分には記述できません。

-- This is a comment.

データ・ファイルの指定

• ロードするデータを含むデータ・ファイルを指定するには、INFILEキーワードにファイル名を続け、必要な場合はファイル処理オプション文字列を続ける
• ァイル名が指定されない場合は、デフォルトで制御ファイル名の拡張子を.datにしたものが採用
• ードするデータを制御ファイル内にも記述した場合は、ファイル名にアスタリスク（*）を指定

制御ファイルにデータがある場合

INFILE  *

デフォルトの拡張子.datを持つファイル sampleにデータがある場合

INFILE  sample

フルパスに指定されたファイルdatafile.datにデータがある場合

INFILE 'c:/topdir/subdir/datafile.dat' 

フィールド・リストの内容

位置指定

• POSITIONに、データ・フィールドの位置を指定します

( { start | * [ - } end +integer] } [{ : ] )

パラメータ	内容
start	開始位置です。論理レコードの先頭バイト位置は1
end	終了位置。start-endと表記することも、start:endと表記することもできます。
*	対象となるデータ・フィールドが前のフィールドの直後にあることを示す
integer	オフセットを使用。前フィールドの終了位置直後の位置から現行のフィールドをオフセット

TERMINATED FIELD

• フィールドの開始位置から最初のデリミタ文字までのデータが読み込まれます
• TERMINATED BY WHITESPACEを指定すると、最初に空白文字（スペース、タブ、空白、LF、改ページまたは改行）が現れるまでデータが読み込まれます

TERMINATED [BY] { WHITESPACE | X'hexstr' | 'string' | EOF }

ENCLOSEDフィールド

• 空白以外の文字が検出されるまで、空白文字はスキップされます

ENCLOSED [ X'hexstr'  BY] [ 'string' | X'hexstr' ] [AND] [ 'string' ]

TERMINATED BY ','                      a data string, 
ENCLOSED BY '"'                        "a data string" 
TERMINATED BY ',' ENCLOSED BY '"'      "a data string", 
ENCLOSED BY '(' AND ')'                (a data string) 

入力データおよびデータ・ファイル

• 制御ファイルに指定された1つ以上のファイルなどから、SQL*Loaderにデータが読み込まれます
• レコード形式は、INFILEパラメータを使用して制御ファイルに指定することができる。デフォルトはストリーム・レコード形式

データファイル形式

• 固定レコード形式
• 可変レコード形式
• ストリーム・レコード形式

固定レコード形式

INFILE datafile_name "fix n"

可変レコード形式

INFILE "datafile_name" "var n"

ストリーム・レコード形式

INFILE datafile_name ["str terminator_string"]

SQL*Loaderの事例

• Oracle Databaseのインストール時に、$ORACLE_HOME/rdbms/demoディレクトリに事例ファイルがインストールされる

通常、各事例は次の種類のファイルで構成

• 制御ファイル（ulcase5.ctlなど）
• データ・ファイル（ulcase5.datなど）
• セットアップ・ファイル（ulcase5.sqlなど）

ストリームレコード形式のロード

テーブルの作成

• 適当にテーブルを作成する

SQL> show user
ユーザーは"EXAM"です。
SQL> create table apache_access_log (
  2    ip_address     char(20)     ,
  3    user_inf       char(20)     ,
  4    user_id        char(20)     ,
  5    req_date       char(60)     ,
  6    request        varchar2(512),
  7    status_cd      char(10)     ,
  8    res_size       char(10)     ,
  9    referer        varchar2(512),
 10    user_agent     varchar2(512)
 11  )
 12 /

表が作成されました。

制御ファイルの作成

• apache_access_log.ctl

-- appache access logfile load sample control file
load data                            -- 新しくデータロードが開始される
infile  'access_log'                 -- ロードするデータが入っているファイル名
badfile 'access_log.bad'             -- 拒否レコードが書き込まれるファイル名
discardfile 'access_log.dsc'         -- 廃棄レコードが書き込まれるファイル名
insert                               -- すでにデータが存在する場合のオプション APPEND,REPLACE,TRUNCATE
into table exam.apache_access_log    -- 
(  ip_address   position(*) char terminated by whitespace                 
 , user_inf     position(*) char terminated by whitespace
 , user_id      position(*) char terminated by whitespace  
 , req_date     position(*) char enclosed by '[' and ']'         
 , request      position(*) char enclosed by '"' and '"'
 , status_cd    position(*) char terminated by whitespace
 , res_size     position(*) char terminated by whitespace
 , referer      position(*) char enclosed by '"' and '"'
 , user_agent   position(*) char enclosed by '"' and '"'
)

実行

• apache のアクセスログを、access_log.datに変更して、以下を実行

$sqlldr exam/abc123 apache_access_log.ctl 

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jul 11 22:05:04 2009 

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

Commit point reached - logical record count 64
Commit point reached - logical record count 128
                :

• ダイレクト・パス・ロードで実行の例

$ sqlldr exam/abc123 apache_access_log.ctl  direct=true

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 12 00:18:28 2009

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

Load completed - logical record count 5872.

取り込まれた

select * from exam.apache_access_log;

ユーザーエージェントを調べてみる

select user_agent,count(user_agent) from exam.apache_access_log
group by user_agent 
order by 2 desc