Oracle Database 12cでのSQL*LoaderのExpress Modeによるロード

Oracle ホワイト ペーパー 2013 年 8 月 Oracle Database 12c での SQL*Loader の Express Mode によるロード

はじめに... 3 Express Modeを使用するメリット... 3 CSVファイルの概要... 3 CSVファイルは ファイル内の各レコードに表の1 行のデータが含まれるテキスト ファイルである... 3 各レコードは改行で終了する... 3 文字 ( 通常 カンマ ) により レコードの各フィールドの値が終了する... 4 CSVファイルで他の文字を使用して フィールド値を囲むことができる... 4 囲み文字の使用は オプションにすることも 必須にすることもできる... 4 " 区切り " 構文が使用されている場合は フィールドに囲み文字を含めることができる... 4 Express Modeによるロード... 4 SQL*LoaderのExpress Modeのカスタマイズ... 5 フィールドの終了文字の変更... 5 データファイルにおけるフィールドの順序の指定... 5 異なるデータファイルの使用... 6 囲み文字の使用... 7 改行を含むフィールドに対する囲み文字の使用... 7 その他のカスタマイズ... 9 SQL*LoaderのExpress Modeのログ ファイル... 10 結論... 10

はじめに データファイルをロードするために SQL*Loaderによって使用される制御ファイルを生成することは SQL*Loaderのユーザーに共通の課題です SQL*Loaderにはさまざまなタイプのデータファイルをロードできる柔軟性が備わっていますが この柔軟性により SQL*Loaderの制御ファイルを作成することが想定よりも難しくなる場合があります データファイルにフィールドが増えると 制御ファイルの作成時にエラーが発生する可能性も上がります 幸い ほとんどのデータファイルはカンマ区切り値 (CSV) 形式となっています Oracle Database 12c では SQL*LoaderにExpress Modeと呼ばれる新機能が導入されており これを使用すると CSVファイルのロードが迅速かつ簡単になります Express Modeを使用する場合は ロードするほとんどの CSVファイルに対して 制御ファイルを作成する必要がありません 代わりに SQL*Loaderコマンドラインでわずかなパラメータのみを使用して CSVファイルをロードできます このホワイト ペーパーでは Express Modeの開始方法について説明します Express Mode を使用するメリット SQL*LoaderのExpress Modeのおもなメリットは SQL*Loader 制御ファイルを作成してテストする必要がなくなることにより 時間と労力が節減されることです 制御ファイルの代わりに いくつかのパラメータを使用した単一のSQL*Loaderコマンドを指定して ロードが開始されます もう1つのExpress Modeのメリットは 最速のメカニズムを使用したデータファイルのロードが試みられることです このメカニズムでは APPENDヒントを含むパラレル挿入を使用した外部表が採用されています INSERT 文にAPPENDヒントを使用すると データベースで表データのロードにダイレクト パスが使用されるようになります 挿入をパラレルで実行することは 一度に複数のプロセスでデータファイルからデータをロードでき ロードにかかる時間を短縮できることを意味します Express Modeで特定のロード操作に使用できない外部表が検出されると 2 番目に高速なメカニズムであるSQL*Loaderのダイレクト パス ロードにフォールバックされます CSV ファイルの概要 SQL*LoaderのExpress Modeでは CSVファイルからのデータのみがロードされます CSVファイルには次の特徴があります CSV ファイルは ファイル内の各レコードに表の 1 行のデータが含まれるテキスト ファイルである 各レコードのフィールドは すべてのレコードで同じ順序になります 各レコードは改行で終了する UNIX 系オペレーティング システムでは 改行はラインフィード文字になります Windows では 改行はキャリッジ リターンとラインフィード文字になります 3

文字 ( 通常 カンマ ) により レコードの各フィールドの値が終了するカンマの使用は必須ではありません コロンや縦線などのその他の文字も 終了文字として一般的に使用されます 便宜上 これらのファイルの終了文字がカンマ以外の場合でも CSV ファイルと呼んでいます レコードの最後のフィールドの後の終了文字は省略可能です CSV ファイルで他の文字を使用して フィールド値を囲むことができる この文字は通常二重引用符ですが 他の文字にすることもできます 値を囲むと 改行またはフィールドの終了文字をフィールドの値の一部に含めることができます 囲み文字の使用は オプションにすることも 必須にすることもできる囲み文字を使用するCSVファイルで すべてのフィールドを囲む必要があるようにすることも 囲み文字をオプションとすることもできます 囲み文字をオプションとして使用する場合は 通常 改行または終了文字を含むフィールド値に対してのみ使用されます " 区切り " 構文が使用されている場合は フィールドに囲み文字を含めることができる CSVファイルで囲み文字を使用し フィールド値に囲み文字が含まれる場合は フィールド値の囲み文字を重複させる必要があります これは " 区切り構文 " と呼ばれます これは フィールドの値の一部として出現する囲み文字と フィールド末尾の囲み文字のマーキングを区別するための方法です Express Mode によるロード Oracle Database 12cでは SQL*Loaderに新しいパラメータとしてTABLEが追加されています このパラメータにより Express Modeが有効化されます TABLEパラメータの値には SQL*Loaderでロードされる表の名前を指定します 指定するパラメータがTABLEのみの場合 SQL*Loaderにより次の処理が行われます 1. 現在のディレクトリから ロードされる表と同じ名前で拡張子 ".dat" が付けられたデータファイルが検索されます データファイルの名前の大文字と小文字は TABLEパラメータに指定した表名の大文字と小文字と同じです 2. データファイルのフィールドの順序は 表の列の順序に一致すると見なされます 3. フィールドはカンマで終了します 囲み文字は使用されません 次に 表 データファイル および表をロードするSQL*Loaderコマンドの簡単な例を示します 表は 次のコマンドで作成されます CREATE TABLE EMP (EMPNO number(4) not null, ENAME varchar2(10), HIREDATE date, DEPTNO number(2)); 4

表のロードに使用されるデータファイルは 表名のempに由来し emp.datになります この例のデータファイルの内容は次のとおりです 7782,Clark,09-Jun-81,10 7839,King,17-Nov-81,12 データファイルのフィールドの順序は 表の列の順序に一致します 次のSQL*Loaderコマンドは データファイルから表をロードします sqlldr userid=scott table=emp この例では 制御ファイルは必要ありません SQL*Loaderコマンドの実行後に 表をFROM 句で指定してSELECT 文を実行すると 次のように表示されます EMPNO ENAME HIREDATE DEPTNO ---------- ---------- --------- ---------- 7782 Clark 09-JUN-81 10 7839 King 17-NOV-81 10 SQL*Loader の Express Mode のカスタマイズ 前述の例のSQL*Loaderコマンドはシンプルでしたが これにはいくつかの仮定が立てられており 常にこれが当てはまるわけではありません たとえば CSVファイルではフィールドの終了文字にカンマ以外の文字が使用される場合や 複数のデータファイルがロードされる場合 ファイル名に表の名前が使用されていない場合があります これらの理由から SQL*LoaderのExpress Modeには他のコマンドライン パラメータが用意されています これにより ロードを変更できます フィールドの終了文字の変更 SQL*LoaderコマンドラインでTERMINATED_BYパラメータを使用すると フィールドの終了文字として使用される文字が指定されます 例のデータファイルでセパレータにカンマではなく縦線が使用されていた場合は 次のようになります 7782 Clark 09-Jun-81 10 7839 King 17-Nov-81 12 このデータファイルのロードに使用するコマンドは 次のとおりです sqlldr userid=scott table=emp terminated_by= データファイルにおけるフィールドの順序の指定 SQL*LoaderのExpress Modeでデータファイルのレコードのフィールドが読み取られる場合 表の列ではデータファイルのフィールドの順序と同じ順序が使用されます ただし データファイルのフィールドの順序と 表の列の順序とが異なるようにすることもできます このようなデータファイルには 新しいFIELD_NAMESパラメータを使用します このパラメータを使用すると データファイルの最初のレコードでデータファイルのフィールドの順序が指定されるように SQL*Loaderが設定されます 5

次の例では フィールドの順序が表の列の順序と一致しない 表 EMPのロードに使用されるデータファイルemp.datを示しています deptno,empno,ename,hiredate 10,7782,Clark,09-Jun-81 12,7839,King,17-Nov-81 このデータファイルのロードに使用するコマンドは 次のとおりです sqlldr userid=scott table=emp field_names=first FIELD_NAMESパラメータには 複数のデータファイルのロードをカスタマイズするためのその他のオプションがあります この例では FIELD_NAMES=FIRSTにより 最初のデータファイルのみでフィールド名のリストが最初のレコードとして含まれるように SQL*Loaderが設定されます ロードで複数のデータファイルが使用される場合は 他のデータファイルにはフィールド名のリストは含まれません FIELD_NAMES=ALLを指定すると すべてのデータファイルでフィールド名のリストが最初のレコードとして含まれるように SQL*Loaderが設定されます 異なるデータファイルの使用データファイルのデフォルト名は 表の名前に拡張子.datが付けられたものになります 異なる名前のファイルをロードする場合 または複数のファイルをロードする場合は DATAパラメータを使用します DATAパラメータにはLOVを指定できるため ロードの一部として複数のファイルを指定できます Oracle Database 12c 以降では SQL*Loaderでワイルド カード文字もサポートされており ファイル名に対して "*" と "?" を指定できるようになりました "*" ワイルド カード文字は ファイル名の1 つ以上の文字に一致します "?" ワイルド カード文字は 1つの文字のみに一致します これにより 類似の名前を持つ数十または数百のファイルをロードする場合 コマンド文字列が大幅に簡素化されます 表 empにロードする2つのファイルがあるとします 最初のファイルはjan_2012_emp.datです 7782,Clark,09-Jan-12,10 7839,King,17-Jan-12,12 2つ目のファイルはfeb_2012_emp.datです 8109,Baby,12-Feb-12,10 8299,Lee,24-Feb-12,12 SQL*Loaderでは以下の2つのコマンドを使ってこれらのファイルをロードできます sqlldr userid=scott table=emp data=jan_2012_emp.dat,feb_2012_emp.dat sqlldr userid=scott table=emp data=*_2012_emp.dat 6

最初のコマンドは ロードされるファイルがリストされます 2 つ目のコマンドでは data パラメー タでファイル名にワイルド カードを使用しています 囲み文字の使用データファイルでは 囲み文字を使用してフィールドを囲む必要がある場合があります これは 終了文字または改行文字がフィールドの値の一部としてフィールドに含まれている場合があるためです 次の表の定義について考えてみます create table part_info (part_number varchar2(10), part_description varchar2(40) ); ファイルpart_info.datには次が含まれています 10-1002,"size 12 widget, red" 10-1003,"size 12 widget, blue" 表 PART_INFOは次のコマンドを使用してロードできます sqlldr userid=scott table=part_info optionally_enclosed_by= ロードが完了したら 表に次の行が含まれます PART_NUMBE PART_DESCRIPTION ---------- ---------------------------------------- 10-1002 size 12 widget, red 10-1003 size 12 widget, blue フィールド値の一部のみが引用符で囲まれていたため この例ではOPTIONALLY_ENCLOSED_BYが使用されています すべてのフィールドを囲む場合はENCLOSED_BYパラメータを使用します 改行を含むフィールドに対する囲み文字の使用フィールドに改行が含まれる場合も 囲み文字を使用してフィールドを囲むことができます たとえば 次のような大きいテキスト フィールドを含む表の定義があるとします CREATE TABLE EMP_RESUMES (EMPNO EMP_RESUME number(4) not null, varchar2(1000)); 7

表にロードする必要があるデータファイルは次のようになります 7782,"line 1 of resume for 7782 line 2 of resume last line of resume" 7839,"line 1 of resume for 7839 line 2 of resume last line of resume" EMP_RESUMEフィールドが二重引用符で囲まれています このフィールドでは データに改行が組み込まれています データファイルにそのようなフィールドがある場合 SQL*Loaderコマンドで WITH_EMBEDDEDの値を含むCSVパラメータを指定する必要があります フィールド内に改行文字を含むファイルを処理する場合は SQL*Loaderで追加の処理を実行する必要があります この追加の処理により この処理を必要としないデータファイルのロードが大幅に遅くなる場合があります そのため すべてのファイルにこの処理に伴う影響が及ばないように 処理が必要なデータファイルをロードするときに SQL*LoaderでユーザーがCSV=WITH_EMBEDDEDを指定して特殊な処理が行われるようにする必要があります 前述のデータファイルで表 EMP_RESUMESをロードするSQL*Loaderコマンドは 次のようになります sqlldr userid=scott table=emp_resumes csv=with_embedded ロード後に表をFROM 句で指定してSELECT 文を実行すると 次のようにファイルのデータが示されます SQL> select * from emp_resumes; EMPNO ---------- EMP_RESUME --------------------------------------------------------------------- 7782 line 1 of resume for 7782 line 2 of resume last line of resume 7839 line 1 of resume for 7839 line 2 of resume last line of resume 8

その他のカスタマイズ SQL*LoaderのExpress Modeでは ロードをカスタマイズするためのその他のパラメータがいくつかサポートされています 以下を参照してください SQL*LoaderのExpress Modeのドキュメントには これらのオプションに関する詳細情報が記載されています BAD SQL*Loaderがロードできなかったレコードを書き込むファイルです CHARACTERSET データファイルのエンコードに使用されるキャラクタ セットの名前です DATE_FORMAT データファイルの日付の解析時に使用する書式文字列です DEGREE_OF_PARALLELISM 外部表を使用したロード時に使用する並列度です DIRECT ロードで 外部表の代わりにダイレクト パス ロードまたは従来型パス ロードを強制的に使用します EXTERNAL_TABLE ロードで データファイルのロードに外部表を強制的に使用します または 外部表をロードするために使用するSQL 文を実行せずに その文をログ ファイルに書き込むように SQL*Loaderを設定します LOAD ロードするレコードの数です デフォルトではすべてのレコードがロードされます NULLIF SQL*Loaderで該当するフィールドにNULLが挿入されるようにするレコードのフィールドの値です SILENT SQL Loaderのログ ファイルに書き込まれるメッセージを制御します TIMESTAMP_FORMAT データファイルのタイムスタンプ フィールドの解析時に使用する書式文字列です TRIM フィールドの先頭および末尾から空白文字をトリムする方法を指定します 9

SQL*LoaderのExpress Modeのログ ファイル SQL*LoaderのExpress Modeのログ ファイルには その他のSQL*Loaderの処理と同じ情報が含まれますが それ以外の情報も含まれます SQL*LoaderのExpress Modeでは Express Modeが使用されていないかのように 同じロードを実行するSQL*Loader 制御ファイルの内容が書き込まれます Express Modeのカスタマイズ オプションでは適切にロードできない場合に 制御ファイルを作成およびカスタマイズしてデータをロードするための開始点として ログ ファイル内の制御ファイルの情報を使用できます たとえば データファイルの日付フィールドごとに異なるデータ形式が使用されている場合は DATE_FORMATパラメータは役に立ちません この場合 次の手順を実行できます SQL*LoaderのExpress Modeを使用して ログ ファイルに制御ファイルの情報を書き込みます この情報を使用して新しい制御ファイルを作成します 日付フィールドで データの書式文字列を適切な値に変更します このように より複雑なSQL*Loader 制御ファイルを作成する場合 SQL*LoaderのExpress Modeが良い手段になります 結論 SQL*LoaderのExpress Modeにより ほとんどのデータファイルのロードに必要な作業が簡略化されます Express Modeを使用して 非常に一般的なファイル形式であるCSVファイルをロードします Express Modeでは 可能な限り最高速のメカニズムを使用したデータのロードが試みられます また ユーザーはオプションのパラメータを使用して ロードをカスタマイズできます Express Mode を使用すると 迅速にロードを開始および完了できます 10

Oracle Database 12cでのSQL*Loaderの Express Modeによるロード 2013 年 6 月著者 :Jim Stenoish 共著者 :Ray Pfau Roy Swonger Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問い合わせ窓口 : 電話 :+1.650.506.7000 ファクシミリ :+1.650.506.7200 Copyright 2013, Oracle and/or its affiliates. All rights reserved. 本書は情報提供のみを目的としており ここに記載される内容は予告なく変更されることがあります 本文書は その内容に誤りがないことを保証するものではなく また 口頭による明示的保証や法律による黙示的保証を含め 商品性ないし特定目的適合性に関する黙示的保証および条件などのいかなる保証および条件も提供するものではありません オラクルは本文書に関するいかなる法的責任も明確に否認し 本文書によって直接的または間接的に確立される契約義務はないものとします 本文書はオラクルの書面による許可を前もって得ることなく いかなる目的のためにも 電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません OracleおよびJavaはOracleおよびその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標です IntelおよびIntel XeonはIntel Corporationの商標または登録商標です すべてのSPARC 商標はライセンスに基づいて使用されるSPARC International, Inc. の商標または登録商標です AMD Opteron AMDロゴおよびAMD Opteronロゴは Advanced Micro Devicesの商標または登録商標です UNIXは The Open Groupの登録商標です 0612 oracle.com