!!!Oracle Database10g SQL*Loader [Oracle][Oracle Database10g] {{amazon 479810910X}} !!概要 *[SQL*Loader|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/part_ldr.html#113]を使用して、外部ファイルのデータをOracleデータベースの表にロードできる *一般的なSQL*Loaderセッションでは、SQL*Loaderの動作を制御する制御ファイルと1つ以上のデータ・ファイルが入力用に使用される *SQL*Loaderの出力先は、データがロードされるOracleデータベース、ログ・ファイル、不良ファイルで、廃棄ファイルに出力される場合もある {{ref_image ora_sqlldr01.jpg}} !!SQL*Loaderのパラメータ *SQL*Loaderは、sqlldrコマンドを指定すると起動します。また、オプションで、セッション特性を確立するパラメータを指定した場合も起動します。 *常に、値がほとんど変らない同じパラメータを使用する場合は、コマンドラインではなく、次の方法でパラメータを指定すると効率的 **パラメータ・ファイルとしてグループ化。その後、PARFILEパラメータを使用して、そのパラメータ・ファイルの名前をコマンドラインで指定。 **一部のパラメータは、OPTIONS句を使用して、制御ファイル内に指定。 !![データのロード方法|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_modes.html#1298] 以下の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構文図|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/app_ldr_syntax.html#311] !コメント *コメントはファイル中のコマンド部分のどこにでも記述できますが、データの部分には記述できません。 -- This is a comment. ![データ・ファイルの指定|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_control_file.html#5966] *ロードするデータを含むデータ・ファイルを指定するには、INFILEキーワードにファイル名を続け、必要な場合はファイル処理オプション文字列を続ける *ァイル名が指定されない場合は、デフォルトで制御ファイル名の拡張子を.datにしたものが採用 *ードするデータを制御ファイル内にも記述した場合は、ファイル名にアスタリスク（*）を指定 ::制御ファイルにデータがある場合 INFILE * ::デフォルトの拡張子.datを持つファイル sampleにデータがある場合 INFILE sample ::フルパスに指定されたファイルdatafile.datにデータがある場合 INFILE 'c:/topdir/subdir/datafile.dat' ![フィールド・リストの内容|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_field_list.html#3725] ::[位置指定|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_field_list.html#4444] *POSITIONに、データ・フィールドの位置を指定します ( { start | * [+integer] } [{ : | - } end] ) ,パラメータ,内容 ,start,開始位置です。論理レコードの先頭バイト位置は1 ,end,終了位置。start-endと表記することも、start:endと表記することもできます。 ,*,対象となるデータ・フィールドが前のフィールドの直後にあることを示す ,integer,オフセットを使用。前フィールドの終了位置直後の位置から現行のフィールドをオフセット ::[データ型の指定|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_field_list.html#5512] ::[TERMINATED FIELD|http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_field_list.html#10225] *フィールドの開始位置から最初のデリミタ文字までのデータが読み込まれます *TERMINATED BY WHITESPACEを指定すると、最初に空白文字（スペース、タブ、空白、LF、改ページまたは改行）が現れるまでデータが読み込まれます TERMINATED [BY] { WHITESPACE | X'hexstr' | 'string' | EOF } ::ENCLOSEDフィールド *空白以外の文字が検出されるまで、空白文字はスキップされます ENCLOSED [BY] [ 'string' | X'hexstr' ] [AND] [ 'string' | X'hexstr' ] 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; {{ref_image ora_sqlldr02.jpg}} ::ユーザーエージェントを調べてみる select user_agent,count(user_agent) from exam.apache_access_log group by user_agent order by 2 desc {{ref_image ora_sqlldr03.jpg}}