SQL LoaderはOracleユーティリティ 制御ファイル定義でお手軽データロード可能
  • facebookページ
  • twitterページ
  • 2020.05.22

    SQL LoaderはOracleユーティリティ 制御ファイル定義でお手軽データロード可能

    Oracleのデータインポートユーティリティ、SQL*Loaderについてまとめています。

    SQL*Loaderは、Oracleにデータをインポートするためのユーティリティ

    ファイル構造を定義することで、多彩なフォーマットのファイルインポートが可能です。実行するには、sqlldrコマンドを使用します。例えば以下のコマンドは、ユーザID scottを使用して、def.ctlというファイル構造定義ファイルを指定して、データのインポートをおこないます。実行時のログは、Import.logファイルに出力します。

    SQL DeveloperにもSQL*Loader形式でエクスポートするオプションが用意されています。

    【関連記事】
    SQL Developerを使ってみよう!資格取得の学習にも使える便利なツール

    SQL*Loader制御ファイルで、データの構造を指定する

    SQL Loaderを使うことで、たとえばメインフレーム系から出力したデータファイルを容易にOracleに取り込むなど、他システムとの連携が簡単になります。

    以下は固定長を指定する場合の制御ファイルです。ファイルexample.datを11桁の固定長データとして、テーブル名exampleに取り込みます。

    カンマで区切られたデータを、第一カラム、第二カラムとしてロードします。1行目は、ty,.までが11バイトで、以降は2行目として扱われます。

    以下は、可変長を指定する制御ファイルです。ファイルexample.datを最初の3バイトでレコード長を決めています。

    1行目は、レコード長9バイトで「hello」と「cd」、レコード長10バイトで「world」と「im(+1バイトの改行文字)」、2行目は12バイトで「my」と「name is(+1バイトの改行文字)」となります。

    以下は、ストリーム・レコード形式の制御ファイルです。終了文字が出現することで1つのレコードとして認識します。以下の例は、ファイルexample.datに「|(+改行文字)」が出現したら1レコードとみなす例です。1行目では「hello」と「world」、2行目では「james」と「bond」がそれぞれcol1とcol2に入ります。

    参考)Oracle 公式ドキュメント SQL*Loaderの概念

    バイナリ形式をロードするための制御ファイル

    カンマなどのデリミタ文字を使わず、レコード先頭からのバイト数でカラムを定義することもでき、バイナリファイルを扱う際に有効です。

    以下の制御ファイルは、ファイルsample.datから対応するデータの物理位置を指定し、テーブルdepartmentsに対応するカラムのデータをロードします。dept_mgrはCOLUMN OBJECTでname、age、emp_idで構成されています。

    参考)Oracle公式ドキュメント オブジェクト、LOBおよびコレクションのロード

    制御ファイルとOracleによるデータフォーマットのチェック

    SQL*Loaderの制御ファイルに沿わないデータは拒否されます。例えば、デリミタで区切られたあるべきフィールドが存在しない場合、フィールドがカラム定義で指定した最大長を超えている場合などです。拒否されたレコードは不良ファイル(拡張子.bad)に書き込まれます。

    続いて、Oracle Databaseの表に行として挿入される際に、不適切なデータは拒否されます。データ型やデータ長が異なったり、SQL*Loaderの制御ファイル定義がOracle Databaseとマッチしていなかった場合、キーが重複している場合、NULL不可フィールドがNULLの場合などがこれにあたります。

    なお、Oracle Databaseに拒否されたデータも不良ファイルに書き込まれます。

    外部表を使ったロードとSQL*Loaderの違い

    SQL*Loaderで直接データベースにデータをインポートせず、外部表を使ってインポートすることが可能です。外部表は、データベース上には存在しないが実在のテーブルのように見えます。データへのアクセスは、ORACLE_LOADER、OACLE_DATAPUMPといったアクセスドライバを利用しておこないます。

    外部表の最大のメリットは、パラレル処理が可能なことで大きなファイルをロードする際の時間短縮に有効です。ただし、SJIS、EUCコードのデータの場合、パラレル処理ができないという制約があります。

    SQL*Loaderにはパラレル処理機能はありませんが、ファイルを分割することでパラレル処理の実現が可能です。

    使い分けとしては、バッチ処理などに組み込む定常的な処理は外部表ロードを使い、定常的なローディング処理には手早く対応できるSQL*Loaderを使うのが有効です。

    まとめ

    ポテパンダの一言メモ
    • SQL*Loaderは、ファイルを制御ファイルで構造定義してデータベースにロードするユーティリティ
    • 制御ファイルの記述によって、固定長、可変長、終了文字を使ったストリームレコード形式、バイナリ形式のファイルが扱える
    • SQL*Loaderをバッチ処理などに組み込むことも可能だが、大量データは外部表を使ったロードの方が速い