15 データ・ウェアハウスにおける抽出

この章では、抽出について説明します。抽出とは、業務系システムからデータを取り出してデータ・ウェアハウスまたはステージング・システムに移動するプロセスです。内容は次のとおりです。

データ・ウェアハウスにおける抽出の概要

抽出とは、データ・ウェアハウス環境で使用できるように、ソース・システムからデータを抽出する操作です。これは、ETLプロセスの最初のステップです。抽出後のデータは、変換してデータ・ウェアハウスにロードできます。

通常、データ・ウェアハウスのソース・システムになるのは、トランザクション処理データベース・アプリケーションです。たとえば、販売分析データ・ウェアハウスのソース・システムの例としては、現在の受注状況をすべて記録する注文入力システムがあります。

抽出プロセスの設計および作成は、通常はETLプロセスおよびデータ・ウェアハウスのプロセス全体の中で、最も時間がかかる作業の1つです。ソース・システムが非常に複雑で、詳細にドキュメント化されていないため、どのデータを抽出する必要があるかを決定できない場合もあります。通常、データは1度だけ抽出されるのではなく、変更があったすべてのデータをデータ・ウェアハウスに提供して最新の状態に保つために、定期的に何回も抽出されます。さらに、データ・ウェアハウスの抽出プロセスの必要性に応えようとしても、通常ソース・システムは変更できず、そのパフォーマンスまたは可用性を調整することもできません。

これらは、抽出およびETL全体で考慮する必要がある重要な考慮点です。ただし、この章では、各種のソースおよび抽出方法に関する技術的な考慮点について重点的に説明します。ここでは、データ・ウェアハウス・チームが抽出対象のデータをすでに識別していることを前提に、ソース・データベースからデータを抽出する一般的なテクニックについて説明します。

このプロセスの設計では、主に次の2つのカテゴリに関する意思決定を行うことになります。

どの抽出方法を選択するか

これは、ソース・システム、転送プロセスおよびウェアハウスのリフレッシュの所要時間に影響します。
抽出されたデータを後続の処理にどんな方法で提供するか

これは、転送方法と、データのクレンジングおよび変換のニーズに影響します。

データ・ウェアハウスにおける抽出方法の概要

どの抽出方法を選択する必要があるかは、ソース・システムに大きく左右され、ターゲットとなるデータ・ウェアハウス環境でのビジネス・ニーズも考慮する必要があります。処理負荷の増大やパフォーマンスへの影響を考えると、データの増分抽出機能を強化するために新たなロジックをソース・システムに追加するというのはあまり現実的な方法ではありません。顧客には既製のアプリケーション・システムに対する追加が許されていない場合さえもあります。

この項には次のトピックが含まれます:

論理的抽出方法

論理的抽出には、次の2種類があります。

全体抽出

データ全体がソース・システムから抽出されます。この抽出では、ソース・システムで現在使用可能なデータがすべて反映されるため、前回の抽出成功以降にデータソースに対して行われた変更を追跡する必要がありません。ソース・データはそのまま提供され、ソース側では追加の論理情報(タイムスタンプなど)は不要です。全体抽出の例には、特定の表のエクスポート・ファイルや、ソース表全体をスキャンするリモートSQL文があります。

増分抽出

特定の時点で、履歴で適切に定義されたイベント以降に変更があったデータのみが抽出されます。このイベントは、最終抽出時刻でも、会計期間の最終記帳日のように複雑なビジネス・イベントでもかまいません。このデルタ変更を識別するには、この特定の時間事象以降に変更があった情報をすべて識別する機能が必要です。この情報は、最終変更日時のタイムスタンプを反映するアプリケーション列のようなソース・データ自体、または適切な追加メカニズムにより起点となったトランザクションに加え、変更が追跡されるチェンジ・テーブルから得ることができます。ほとんどの場合は、後者の方法を使用すると、ソース・システムに抽出のロジックを追加することになります。

抽出プロセスに対して、なんらかの形で変更をキャプチャするテクニックを使用しているデータ・ウェアハウスはあまりありません。そのかわり、ソース・システムからすべての表をデータ・ウェアハウスまたはステージング・エリアに抽出し、ソース・システムからの前回の抽出と比較して変更データを識別します。この方法では、ソース・システムが重大な影響を受けることはありませんが、データ・ボリュームが非常に大きい場合は、特に、データ・ウェアハウスの処理に対しては、多大な負荷になります。

物理的抽出方法

選択した論理的抽出方法と、ソース側の機能と制限に応じて、抽出データを2つのメカニズムで物理的に抽出できます。つまり、データをソース・システムからオンラインで抽出する方法と、オフライン構成から抽出する方法があります。オフライン構成は既存のものを使用する場合と、抽出ルーチンで生成する場合があります。

物理的抽出には、次の2つの方法があります。

オンライン抽出

データはソース・システム自体から直接抽出されます。抽出プロセスでは、ソース・システムに直接接続してソース表自体にアクセスするか、データが事前構成済の方法(スナップショット・ログやチェンジ・テーブルなど)で格納されている中間システムに接続できます。なお、中間システムは必ずしもソース・システムと物理的に異なるとは限りません。

オンライン抽出では、分散トランザクションでオリジナルのソース・オブジェクトを使用しているか、準備したソース・オブジェクトを使用しているかを考慮する必要があります。

オフライン抽出

データはソース・システムから直接抽出されるのではなく、オリジナル・ソース・システム外部で明示的にステージングされます。データはすでに既存の仕組み(REDOログ、アーカイブ・ログまたはトランスポータブル表領域など)を持っているか、または抽出ルーチンにより作成されています。

次の仕組みを考慮する必要があります。

フラット・ファイル

定義済の汎用フォーマットのデータ。さらに処理するには、ソース・オブジェクトに関する追加情報が必要です。
ダンプ・ファイル

Oracle固有のフォーマット。選択したユーティリティによって、含んでいるオブジェクトに関する情報がある場合とない場合があります。
REDOログおよびアーカイブ・ログ

情報は特殊な追加ダンプ・ファイルにあります。
トランスポータブル表領域

 Oracleデータベース間で大量のデータを抽出および移動する強力な手段。この機能を使用して、データの抽出および転送を行う例については、データ・ウェアハウスにおける転送を参照してください。他の抽出テクニックに比べてパフォーマンスと管理性が大幅に向上するため、できるかぎりトランスポータブル表領域を使用することをお薦めします。

エクスポート/インポート使用の詳細は、『Oracle Databaseユーティリティ』を参照してください。

チェンジ・トラッキング方法

抽出における重要な考慮点として、増分抽出があります。これはチェンジ・トラッキングとも呼ばれます。業務系システムからデータ・ウェアハウスへのデータ抽出を夜間に行う場合、データ・ウェアハウスに必要なのは、前回の抽出以降に変更されたデータ(過去24時間で変更されたデータ)のみです。また、チェンジ・トラッキングは、ほぼリアルタイムまたはオンタイムのデータ・ウェアハウスを提供するキー有効化テクノロジでもあります。

変更された最新データのみを効率的に識別して抽出できれば、データ抽出ボリュームがわずかで済み、抽出プロセス(およびETLプロセスの下流の処理)を大幅に効率化できます。ただし、多くのソース・システムでは最新の変更データの識別は困難であり、また、システムの操作に影響を及ぼすこともあります。チェンジ・トラッキングは、データ抽出における最も困難な技術課題です。

多くの場合、チェンジ・トラッキングは抽出プロセスの一部として望ましいものであるため、この項では独自開発のチェンジ・キャプチャ機能をOracle Databaseソース・システムに実装するいくつかのテクニックについて説明します。

これらのテクニックはソース・システムの特性に基づいていますが、ソース・システムに変更が必要なこともあります。したがって、これらのテクニックを実装する前に、ソース・システムのユーザーが慎重にそれぞれを評価する必要があります。

これらのテクニックは、それぞれ前述のデータ抽出テクニックと連携して機能します。たとえば、データがファイルにアンロードされているか、分散問合せによってアクセスされている場合は、タイムスタンプを使用できます。

タイムスタンプ

業務系システムの中には、表にタイムスタンプ列を持つものもあります。タイムスタンプは、ある行が最後に変更された日付および時間を示します。業務系システムの表にタイムスタンプを含む列があれば、そのタイムスタンプ列を使用することで最新のデータを簡単に識別できます。たとえば、orders表から今日のデータを抽出するには、次の問合せが有効です。

SELECT * FROM orders 
WHERE TRUNC(CAST(order_date AS date),'dd') = 
  TO_DATE(SYSDATE,'dd-mon-yyyy');

業務系ソース・システムにタイムスタンプ情報がない場合、タイムスタンプを含むようにシステムを変更できるとは限りません。このような変更が必要な場合は、まず、業務系システムの表を変更して、新しくタイムスタンプ列を追加します。次に、特定の行を変更する操作が行われるたびにタイムスタンプ列を更新するトリガーを作成します。

パーティション化

ソース・システムの中には、レンジ・パーティション化を使用しているものもあります。これには、ソース表が日付キーによってパーティション化され、新しいデータが簡単に識別できるようになっているものなどがあります。たとえば、orders表から抽出する場合は、orders表が週別にパーティション化されていれば、現在の週のデータを簡単に識別できます。

トリガー

業務系システムにトリガーを作成することで、最新の更新レコードを追跡できます。これらのトリガーをタイムスタンプ列と連携させて使用し、ある行が最後に変更された正確な日付および時間を識別することもできます。そのためには、データ変更を獲得する必要がある各ソース表にトリガーを作成します。ソース表でDML文が実行されるたびに、このトリガーによってタイムスタンプ列が現在の時間に更新されます。このようにして、行が最後に変更された正確な日付および時間が、タイムスタンプ列に反映されます。

同様に内部化されたトリガー・ベースのテクニックが、Oracleのマテリアライズド・ビュー・ログに使用されています。これらのログは、マテリアライズド・ビューが変更データを識別するために使用し、エンド・ユーザーもアクセスできます。ただし、マテリアライズド・ビュー・ログの形式はドキュメント化されないので、時間が経過すると変化する場合があります。

マテリアライズド・ビュー・ログはトリガーに依存しますが、このデータ変更システムの作成およびメンテナンスが主にデータベースで管理されるという点が効果的です。

トリガー・ベース・テクニックはソース・システムのパフォーマンスに影響する場合があるため、本番ソース・システムに実装する前にその点を慎重に検討する必要があります。

データ・ウェアハウスにおける抽出の例

次の2つのデータ抽出方法があります。

データファイルを使用した抽出

ほとんどのデータベース・システムには、内部データベース・フォーマットからデータをフラット・ファイルにエクスポートまたはアンロードする機能が搭載されています。メインフレーム・システムからの抽出では、COBOLプログラムが使用されることがありますが、データベースの多くは、エクスポートまたはアンロード用のユーティリティを搭載しています。

データの抽出では、必ずしもデータベースの全構造がフラット・ファイルにアンロードされるわけではありません。多くの場合、データベース表全体またはオブジェクト全体をアンロードすることが適切です。前回の抽出以降にソース・システムで行われた変更のような、特定の表のサブセットのみ、または複数の表を結合した結果のみをアンロードする方がより適切な場合もあります。抽出テクニックによって、これらの2つのいずれが適切かが異なります。

ソース・システムがOracleデータベースの場合、データをファイルに抽出するには、次のように何通りかの方法があります。

SQL*Plusによるフラット・ファイルへの抽出

データ抽出の最も基本的なテクニックは、SQL*PlusでSQL問合せを実行して、問合せの出力をファイルに送ることです。たとえば、フラット・ファイルcountry_city.logを抽出するには、次のSQLスクリプトを実行します。このファイルでは、列値の間にデリミタとしてパイプ記号が使用されており、表countriesおよびcustomersからのアメリカの市のリストが含まれています。

SET echo off SET pagesize 0 SPOOL country_city.log
SELECT distinct t1.country_name ||'|'|| t2.cust_city  
FROM countries t1, customers t2 WHERE t1.country_id = t2.country_id 
AND t1.country_name= 'United States of America';
SPOOL off

出力ファイルの抽出フォーマットは、SQL*Plusのシステム変数を使用して指定できます。

この抽出テクニックは、カスタマイズされたフォーマットで結果を格納するのに効果的です。外部表データ・ポンプ・アンロード機能を使用すると、任意のSQL操作の結果を抽出することもできます。前述の例では、結合の結果を抽出します。

この抽出テクニックは、複数の同時SQL*Plusセッションを起動し、各セッションで、抽出対象データの異なる部分を表す別々の問合せを実行することで、パラレル化できます。たとえば、orders表からデータを抽出するとき、そのorders表が月別にレンジ・パーティション化されており、orders_jan1998、orders_feb1998などのパーティションがあるとします。orders表から1年分のデータを抽出するには、12の同時SQL*Plusセッションを起動し、各セッションでパーティションを1つずつ抽出します。このようなセッションを行うSQLスクリプトの例を、次に示します。

SPOOL order_jan.dat
SELECT * FROM orders PARTITION (orders_jan1998);
SPOOL OFF

これらの12のSQL*Plusプロセスによって、データが12の別々のファイルに同時にスプールされます。必要な場合は、抽出後に(オペレーティング・システムのユーティリティを使用して)これらのファイルを連結できます。ターゲットへのロードにSQL*Loaderを使用する場合は、この12のファイルをそのまま使用して12のSQL*Loaderセッションでパラレル・ロードできます。例については、データ・ウェアハウスにおける転送を参照してください。

orders表がパーティション化されていない場合でも、論理的または物理的な基準に基づいて抽出をパラレル化できます。論理的な方法は、次のように列値の論理的な範囲に基づいています。

SELECT ... WHERE order_date
BETWEEN TO_DATE('01-JAN-99') AND TO_DATE('31-JAN-99');

物理的な方法は、値の範囲に基づいています。データ・ディクショナリを参照することで、orders表を構成するOracle Databaseデータ・ブロックを識別できます。この情報を使用して、orders表からデータを抽出するためのROWIDレンジ問合せの集合を導出できます。

SELECT * FROM orders WHERE rowid BETWEEN value1 and value2;

複雑なSQL問合せによる抽出もパラレル化できますが、1つの複雑な問合せを複数のコンポーネントに分割することが困難な場合があります。特に、独立したプロセスを調整してグローバルな一貫性を持つビューを保証するのは困難な場合があります。SQL*Plusの方法と異なり、外部表データ・ポンプ・アンロード機能では透過的にパラレル化できます。

パラレル化のすべてのテクニックは、ソース・システムのCPUおよびI/Oリソースを大量に使用します。抽出テクニックをパラレル化する前に、ソース・システムへの影響を評価する必要があることに注意してください。

OCIまたはPro*Cプログラムによるフラット・ファイルへの抽出

OCIプログラム(または、Pro*CプログラムなどOracle Call Interfaceを使用する他のプログラム)も、データ抽出に使用できます。これらのテクニックによって、通常、SQL*Plusを使用する方法より高いパフォーマンスが得られますが、プログラミングには時間がかかります。SQL*Plusの方法と同様に、OCIプログラムもSQL問合せの結果の抽出に使用できます。また、SQL*Plusの方法で説明したパラレル化のテクニックもOCIプログラムに対して簡単に適用できます。

OCIまたはSQL*Plusを抽出に使用する場合は、データ自体のみでなく追加情報が必要になります。少なくとも、抽出される列に関する情報が必要です。また、抽出フォーマットがわかっていれば役立ちます。これには、個別の列間のセパレータなどがあります。

エクスポート・ユーティリティによるエクスポート・ファイルへの抽出

エクスポート・ユーティリティによって、表(データ含む)をOracle Databaseエクスポート・ファイルにエクスポートできます。SQL問合せの結果を抽出するSQL*PlusおよびOCIの方法とは異なり、エクスポート・ユーティリティではデータベースのオブジェクトを抽出するメカニズムが提供されます。そのため、エクスポート・ユーティリティは前述の2つの方法とは大きく異なる点があります。

エクスポート・ファイルには、データの他にメタデータも含まれます。エクスポート・ファイルには、表の生データのみでなく表を再作成するための情報も含まれ、索引、制約、権限付与、およびその表に関係する他の属性があればそれらも含まれます。
1つのエクスポート・ファイルにはシングル・オブジェクトのサブセットまたは多数のデータベース・オブジェクトが含まれ、スキーマ全体が含まれることもあります。
エクスポート・ユーティリティは、複雑なSQL問合せの結果のエクスポートには直接使用できません。エクスポートは、個々のデータベース・オブジェクトのサブセットの抽出にのみ使用できます。
エクスポート・ユーティリティの出力は、インポート・ユーティリティを使用して処理する必要があります。

Oracle Databaseでは、下位互換性用の元のエクスポート/インポート・ユーティリティ、および高パフォーマンスでスケーラブルなパラレル抽出のためのデータ・ポンプ・エクスポート/インポート・インフラストラクチャを提供しています。詳細は、『Oracle Databaseユーティリティ』を参照してください。

外部表によるエクスポート・ファイルへの抽出

エクスポート・ユーティリティの他に、外部表を使用して任意のSELECT操作の結果を抽出できます。データは、プラットフォームに依存しないOracle内部データ・ポンプ形式で格納され、ターゲット・システムで通常の外部表として処理できます。

次の例では、パラレルの結合操作の結果を、指定した4つのファイルに抽出します。データ抽出用に使用できる外部表のタイプは、Oracle内部形式ORACLE_DATAPUMPのみです。

CREATE DIRECTORY def_dir AS '/net/dlsun48/private/hbaer/WORK/FEATURES/et';
DROP TABLE extract_cust;
CREATE TABLE extract_cust
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY def_dir ACCESS PARAMETERS
(NOBADFILE NOLOGFILE)
LOCATION ('extract_cust1.exp', 'extract_cust2.exp', 'extract_cust3.exp',
          'extract_cust4.exp'))
PARALLEL 4 REJECT LIMIT UNLIMITED AS
SELECT  c.*, co.country_name, co.country_subregion, co.country_region 
FROM customers c, countries co where co.country_id=c.country_id;

指定した抽出ファイルの合計数により、書込み操作の最大並列度が制限されます。抽出をパラレル化しても、文のSELECT部分は自動的にパラレル化されないことに注意してください。

エクスポート/インポートを使用する場合とは異なり、外部表データ・ポンプ・アンロードを使用すると、外部表のメタデータは作成されたファイルには含まれません。外部表の適切なメタデータを抽出するには、次の文に示すとおりDBMS_METADATAパッケージを使用します。

SET LONG 2000
SELECT DBMS_METADATA.GET_DDL('TABLE','EXTRACT_CUST') FROM DUAL;

分散処理による抽出

分散問合せテクノロジを使用すると、1つのOracleデータベースから、他のOracleデータベースやOracleゲートウェイ・テクノロジで接続されている従来型システムなど、各種のソース・システムにある表へ直接問い合せることができます。具体的には、データ・ウェアハウスまたはステージング・データベースから、接続されているソース・システムにある表やデータに直接アクセスできます。ゲートウェイは、分散問合せテクノロジのもう1つの形式です。ゲートウェイでは、Oracleデータベース(データ・ウェアハウスなど)からリモートの非Oracleデータベースに格納されているデータベース表にアクセスできます。2つのOracleデータベース間でデータを移動するには、これが最も簡単な方法です。抽出と変換のプロセスを1ステップで行うことができるうえ、プログラミングに必要な時間も最小限に抑えられます。ただし、これが常に実現可能であるとは限りません。

部署名を含む従業員名リストをソース・データベースから抽出し、それをデータ・ウェアハウスに格納するとします。Oracle Net接続および分散問合せテクノロジを使用すると、この作業は、次の1つのSQL文で実現できます。

CREATE TABLE country_city AS SELECT distinct t1.country_name, t2.cust_city
FROM countries@source_db t1, customers@source_db t2
WHERE t1.country_id = t2.country_id 
AND t1.country_name='United States of America';

この文により、データ・マートにローカル表country_cityが作成され、ソース・システム上の表countriesおよびcustomersからデータが移入されます。

このテクニックは少量のデータ移動には理想的です。ただし、データは単一のOracle Net接続を介してソース・システムからデータ・ウェアハウスに転送されます。そのため、このテクニックのスケーラビリティには限界があります。データが大量になると、ファイル・ベースのデータ抽出および転送テクニックの方が、よりスケーラブルで適切であることがあります。