18 データ・ウェアハウスにおけるロードおよび変換
この章は、データ・ウェアハウスの作成および管理に有効な情報について説明します。内容は次のとおりです。
18.1 データ・ウェアハウスにおけるロードおよび変換の概要
データ変換は最も複雑で、抽出、変換およびロード(ETL)プロセスの中で最も処理時間がかかることがあります。データ変換では、単純なデータ変換からかなり複雑なデータのクレンジング・テクニックまで実行できます。変換は、データベース外(フラット・ファイルなど)でも実装されることがありますが、ほとんどの場合はOracleデータベース内で行います。
この章では、Oracle Database内でスケーラブルで効果的なデータ変換を実装するテクニックについて説明します。この章の例は比較的単純です。実際のデータ変換は、通常、はるかに複雑です。ただし、この章で説明した変換テクニックは、実際のデータ変換要件のほとんどを満たしており、その他の方法よりスケーラブルで、少ないプログラミングで済みます。
この章では、データ・ウェアハウスで発生する一般的な変換をすべて説明しているわけではありません。これらの変換を実装するために使用できる基本的なテクニックの種類を示し、最適なテクニックの選択方法を説明します。
18.1.1 データ・ウェアハウス: 変換フロー
アーキテクチャの観点では、データ変換には次の方法があります。
18.1.1.1 データ・ウェアハウスでのマルチステージ・データ変換
ほとんどのデータ・ウェアハウスのデータ変換のロジックは、複数のステップから構成されています。たとえば、sales表に挿入するために新しいレコードを変換する場合、各ディメンション・キーの妥当性チェックを行うには、個別のロジック変換ステップに従う必要がある場合があります。
図18-1は、変換ロジックをグラフィカルに示したものです。
Oracle Databaseを変換エンジンとして使用する場合、一般的な方法では、変換をそれぞれ別々のSQL操作として実装し、各ステップの処理結果を格納するために別々の一時的なステージング表(図18-1の表new_sales_step1
やnew_sales_step2
など)を作成します。また、ロードしてから変換するこの方法では、変換プロセス全体にチェックポイント取得を実行するスキームが提供されます。このスキームによって、プロセスの監視および再起動が簡単になります。ただし、マルチステージングには、領域と時間の必要性が増大するというデメリットもあります。
また、多数の単純なロジック変換を、単一のSQL文または単一のPL/SQLプロシージャに結合することも可能です。このような結合を行うと、各ステップを個別に実行するよりパフォーマンスが向上することがありますが、同時に、個々の変換の変更、追加、削除や、失敗した変換からのリカバリが困難になる場合があります。
18.1.1.2 データ・ウェアハウスでのパイプライン・データ変換
ETLプロセス・フローは動的に変更でき、データベースはETLソリューションに不可欠の部品になります。
新機能により従来は必要だったプロセス・ステップの一部が廃止になりますが、改造してデータ・フローとデータ変換を強化し、よりスケーラブルで中断のないものにできます。タスクは変換してからロードするという(ほとんどのタスクがデータベース外部で実行される)シリアル・プロセスや、ロードしてから変換するというプロセスから拡張され、ロードしながら変換するプロセスへとシフトします。
Oracleでは、ETLシナリオに関連するすべての問題とタスクに対処できるように、多様な新機能が用意されています。データベースは、汎用的なソリューションに対処するのではなく、ツールキット機能を提供するということを理解する必要があります。基礎となるデータベースは、特定の顧客のニーズに合せて最も適切なETLプロセス・フローを有効化する必要があり、技術的な観点での要件や制約がないようにする必要があります。図18-2に、以降で説明する新機能を示します。
18.1.1.3 データ・ウェアハウスのステージング領域
ロードの全体的な速度は、生データをいかに速くステージング領域から読み取ることができるか、データベースのターゲット表に書き込むことができるかにより決まります。生データはできるだけ多くの物理ディスクにステージングし、生データの読取りがロード時のボトルネックにならないようにすることを強くお薦めします。
データをステージングするのに最適な場所はOracle Database File System(DBFS)です。DBFSはSecureFile LOBとしてデータベースに格納されたファイルへのアクセスに使用できるマウント可能なファイルシステムを作成します。DBFSはローカルのファイルシステムのように見える共有ネットワーク・ファイルシステムを提供するという点でNFSと似ています。DBFSはデータ・ウェアハウスとは別のデータベースに作成し、DIRECT_IO
オプションを使用してマウントして、ファイルシステムとの間の生データ・ファイルの移動時にシステム・ページ・キャッシュの競合が起きないようにします。DBFSの設定の詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。
18.1.2 バッチ更新および表のオンライン再定義について
表の一括更新は、EXECUTE_UPDATE
プロシージャを使用して最適化できます。REDOログに更新が記録されないため、パフォーマンスが最適化されます。
DBMS_REDEFINITION.EXECUTE_UPDATE
プロシージャを使用すると、UPDATE
文を直接挿入モードで実行できます。この操作中はREDOが記録されないため、メディア・リカバリを使用して再定義やデータの更新をリカバリできません。リカバリを可能にするには、再定義を開始する前にデータベースまたは表領域のバックアップを実行することをお薦めします。
関連項目:
18.1.3 ETL操作の監視の概要
ETLは複雑になり、パフォーマンスが低下する可能性があるため、Oracle Databaseでは、ETLの計画に含まれるデータベース操作を監視およびレポートできるユーザー・インタフェースを提供しています。
データベース操作は、一連の関連するデータベース・タスクが含まれているユーザー定義の論理オブジェクトです。たとえば、エンド・ユーザーまたはアプリケーション・コードによって定義されたETL処理ジョブです。各データベース操作は、名前および実行IDによって一意に識別でき、複数回実行できます。
データベース操作の監視は、最適とまでいかないジョブのトラブルシューティングに大変有効で、任意のステップにおいて、どこで、どのようにリソースが消費されているのかを特定するのに役立ちます。関連情報の追跡、パフォーマンス・ボトルネックの特定およびデータベース・パフォーマンスの問題の調整のための時間の短縮が可能になります。Oracle Database 12cリリース2 (12.2)以降では、DBMS_SQL_MONITOR.BEGIN_OPERATION
関数にセッションIDおよびシリアル番号を指定することによって、任意のセッションでデータベース操作を開始できます。
18.2 データ・ウェアハウスでのロード・メカニズム
データ・ウェアハウスへのロードには、次のメカニズムを使用できます。
18.2.1 SQL*Loaderを使用したデータ・ウェアハウスのロード
データ変換をデータベース内で行うには、生データがデータベースでアクセス可能になっている必要があります。その方法の1つは、データベースにロードすることです。データをOracleデータ・ウェアハウスに転送するいくつかのテクニックについては、データ・ウェアハウスにおける転送を参照してください。データを転送する最も一般的なテクニックは、フラット・ファイルを使用する方法です。
データをフラット・ファイルからOracleデータ・ウェアハウスへ移動するには、SQL*Loaderを使用します。このデータのロード中に、SQL*Loaderを使用して、基本的なデータ変換の実装を行うこともできます。ダイレクト・パスSQL*Loaderを使用すると、データ型変換や単純なNULL
処理などの基本的なデータ操作は、データのロード中に自動的に解決できます。パフォーマンスの理由から、ほとんどのデータ・ウェアハウスでは、ダイレクト・パス・ロードが選択されます。
従来型パス・ローダーは、ダイレクト・パス・ローダーより広範囲な機能をデータ変換で使用できます。SQL関数は、列の値がロードされるときに、すべての列に適用できます。これによって、データのロード中に、豊富な機能を使用して変換できるようになります。ただし、従来型パス・ローダーはダイレクト・パス・ローダーより低速です。このような理由から、従来型パス・ローダーを使用するのは、主に少量のデータをロードおよび変換する場合にしてください。
データ・ウェアハウスでは、直接パス・モードを使用してバッチ更新を実行することによって、REDOデータを保守するためのオーバーヘッドを回避できます。バッチ更新は、表のオンライン再定義中に表に対して実行できます。
ここでは、SQL*Loader制御ファイルの単純な例を示します。このファイルでは、外部ファイルsh_sales.dat
からsh
サンプル・スキーマのsales
表にデータがロードされます。外部フラット・ファイルsh_sales.dat
は、日次レベルで集計された売上トランザクション・データで構成されています。この外部ファイルのすべての列がsales
表にロードされるわけではありません。この外部ファイルは、sh
サンプル・スキーマの2番目のファクト表をロードするためのソースとしても使用されます。これには、外部表が使用されます。
次に、sales
表をロードする制御ファイル(sh_sales.ctl
)を示します。
LOAD DATA INFILE sh_sales.dat APPEND INTO TABLE sales FIELDS TERMINATED BY "|" (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)
このロードには、次のコマンドを使用できます。
$ sqlldr control=sh_sales.ctl direct=true Username: Password:
SQL*Loader Expressモードの場合、制御ファイルは使用しません。かわりに、表の列定義を使用して入力データ型を特定します。
関連項目:
-
詳細は、『Oracle Databaseユーティリティ』を参照してください。
-
DBMS_REDEFINITION
を使用した一括更新の詳細は、『Oracle Database管理者ガイド』を参照してください。
18.2.2 外部表を使用したデータ・ウェアハウスのロード
外部データソースを処理するもう1つのアプローチは、外部表を使用することです。Oracleの外部表機能では、外部データを直接およびパラレルに問い合せて結合できる仮想表として使用できます。この場合、外部データを最初にデータベースにロードする必要はありません。そのまま、SQL、PL/SQLおよびJavaを使用して外部データにアクセスできます。
外部表を使用すると、ロード・フェーズを変換フェーズとパイプライン化できます。データ・ストリーミングを中断せずに、変換プロセスをロード・プロセスとマージできます。データベースで比較や変換などの処理を行うために、データベース内でデータをステージングする必要はなくなります。たとえば、外部表からのSELECT
と組み合せたダイレクト・パスのINSERT
AS
SELECT
文で、従来型ロードの変換機能を使用できます。Oracle Database 12cより、データベースではバルク・ロード操作の一部として(CTASおよびIAS)、索引の作成時の統計の収集方法に似た表統計の自動収集を行います。データ・ロードの際に統計を収集することによって、スキャン操作の追加を回避し、ユーザーがデータを使用できるようになるとすぐに必要な統計を提供できます。
外部表と通常の表の主な違いは、外部で編成された表は読取り専用であることです。DML操作(UPDATE
/INSERT
/DELETE
)は実行できず、索引も作成できません。
外部表は既存のSQL*Loader機能の大部分に準拠しており、ほとんどの場合において、より優れた機能を提供します。外部表は、外部ソース全体を既存のデータベース・オブジェクトと結合する必要がある環境、またはデータを複雑な方法で変換する必要がある環境で特に有効です。たとえば、SQL*Loaderとは異なり、任意のSQL変換を適用してダイレクト・パス・インサートを使用できます。さらに、ファイル(圧縮されたデータファイルなど)を処理する実行対象のプログラム(zcat
など)を指定し、その出力(圧縮されていないデータファイルなど)をOracle Databaseで利用できるようにすることもできます。つまり、大量の圧縮データをロードする際に、先にそのデータをディスクに展開する必要がないということです。
外部ファイルsh_sales.gz
に表される、売上トランザクション・データ全体の構造を表す外部表sales_transactions_ext
を作成できます。製品部門では、製品と時間単位の原価分析が特に重要です。したがって、sh
スキーマにファクト表cost
を作成します。処理するソース・データは、sales
ファクト表の場合と同じです。ただし、提供される全ディメンション情報を調査するわけではないため、cost
ファクト表のデータはsales
ファクト表のデータより疎い密度になっています。たとえば、すべての異なる物流チャネルが1つに集計されます。
ディメンションの一部は使用されないため、前述のような詳細情報の集計を行わないとcost
ファクト表にデータをロードできません。
外部表フレームワークは、そのソリューションを提供します。SQL*Loaderでは集計を適用する前にデータをロードする必要がありましたが、それとは異なり、次のように単一のSQL DML文中にロードと変換を組み合せることができます。ターゲット表に挿入する前にデータを一時的にステージングする必要はありません。
オブジェクト・ディレクトリがすでに存在し、sh_sales.gz
ファイルを含むディレクトリと不良ファイルおよびログ・ファイルを含むディレクトリを指定する必要があります。
CREATE TABLE sales_transactions_ext (PROD_ID NUMBER, CUST_ID NUMBER, TIME_ID DATE, CHANNEL_ID NUMBER, PROMO_ID NUMBER, QUANTITY_SOLD NUMBER, AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2), UNIT_PRICE NUMBER(10,2)) ORGANIZATION external (TYPE oracle_loader DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII PREPROCESSOR EXECDIR:'zcat' BADFILE log_file_dir:'sh_sales.bad_xt' LOGFILE log_file_dir:'sh_sales.log_xt' FIELDS TERMINATED BY "|" LDRTRIM ( PROD_ID, CUST_ID, TIME_ID DATE(10) "YYYY-MM-DD", CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD, UNIT_COST, UNIT_PRICE)) location ('sh_sales.gz') )REJECT LIMIT UNLIMITED;
これで、外部表をデータベースから使用し、外部データの一部の列にのみアクセスし、データをグルーピングして、costs
ファクト表に挿入できます。
INSERT /*+ APPEND */ INTO COSTS (TIME_ID, PROD_ID, UNIT_COST, UNIT_PRICE) SELECT TIME_ID, PROD_ID, AVG(UNIT_COST), AVG(amount_sold/quantity_sold) FROM sales_transactions_ext GROUP BY time_id, prod_id;
関連項目:
-
外部表の構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
使用例は『Oracle Databaseユーティリティ』を参照してください。
18.2.2.1 DBMS_CLOUDを使用したオブジェクト・ストア・データの外部表の作成
DBMS_CLOUD PL/SQLパッケージを使用すると、データ・ウェアハウスをクラウド内のオブジェクト・ストアに接続できます。
DBMS_CLOUD
は、外部表を作成し、クラウドに格納されているファイルおよびオブジェクトからデータへのアクセスを可能にするAPIを提供します。テキスト・ファイル、Parquetファイル、Avroファイルおよびクラウド内のデータ・ポンプ・ファイルから外部表にデータをロードできます。
オブジェクト・ストアに対する認証は、ユーザー名とパスワードを含む、個別に作成された資格証明オブジェクトを介して取得されます。オブジェクト・ストア管理者は、これらの資格証明を指定し、ストア内のデータにアクセスするための適切な権限をユーザーにプロビジョニングする必要があります。
このパッケージは、Oracle Object Storage、Microsoft Azure Blob StorageおよびAmazon S3からのファイルのロードをサポートしています。
関連項目:
DBMS_CLOUD
APIについて説明しているDatabase PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス。
18.2.3 OCIおよびダイレクト・パスAPIを使用したデータ・ウェアハウスのロード
OCIおよびダイレクト・パスAPIが頻繁に使用されるのは、変換と計算がデータベースの外部で実行され、フラット・ファイルのステージングを必要としない場合です。
18.2.4 エクスポート/インポートを使用したデータ・ウェアハウスのロード
エクスポートとインポートは、データがそのままターゲット・システムに挿入される場合に使用されます。複雑な抽出は実行できません。詳細は、データ・ウェアハウスにおける抽出を参照してください。
18.3 データ・ウェアハウスでの変換メカニズム
18.3.1 SQLを使用したデータの変換
18.3.1.1 CREATE TABLE ... AS SELECTおよびINSERT /*+APPEND*/ AS SELECT
CREATE
TABLE
... AS
SELECT
文(CTAS)は、大規模なデータセットを操作する場合に強力なツールです。後述する例のように、多くのデータ変換は標準SQLで記述でき、CTASにより、SQL問合せを効果的に実行してその問合せ結果を新しいデータベース表に格納する機能が提供されます。INSERT
/*+APPEND
*/ ...AS
SELECT
文の機能は、既存のデータベース表に対して同様の機能を提供します。
データ・ウェアハウス環境では、CTASは、最大のパフォーマンスを得るためにNOLOGGING
モードで、パラレルで実行されます。
簡単で一般的なデータ変換は、データの置換えです。データ置換えによる変換では、1つの列のいくつかまたはすべての値が変更されます。たとえば、sales
表にchannel_id
列があるとします。この列は、指定された売上トランザクションが企業自体の売上(直接販売)によるものか、または販売店(間接販売)によるものかを指定するために使用されます。
データ・ウェアハウスの複数のソース・システムからデータを受け取る場合があるとします。これらのソース・システムのうち、直接販売のみを処理し、間接販売チャネルには関与していないソース・システムがあるとします。データ・ウェアハウスがこのシステムから売上データを最初に受け取ると、すべての売上レコードのsales.channel_id
フィールドはNULL
値になります。これらのNULL
値は、適切なキー値に設定する必要があります。たとえば、ターゲットとなるsales表の文への挿入の一部としてSQL関数を使用すると、この操作を効率的に行うことができます。ソース表sales_activity_direct
の構造は、次のとおりです。
DESC sales_activity_direct Name Null? Type ------------ ----- ---------------- SALES_DATE DATE PRODUCT_ID NUMBER CUSTOMER_ID NUMBER PROMOTION_ID NUMBER AMOUNT NUMBER QUANTITY NUMBER
次のSQL文は、sales_activity_direct
のデータをサンプル・スキーマのsales
表に挿入します。ここでは、売上日時(sales_date)の値を午前0時に切り捨てるSQL関数を使用し、固定チャネルIDとして3を割り当てます。
INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount FROM sales_activity_direct;
18.3.1.2 UPDATEを使用したデータの変換
データ置換を実装するもう1つのテクニックは、UPDATE
文を使用してsales.channel_id
列を変更することです。UPDATE
によって正しい結果が戻されます。ただし、データ置換による変換で多数(またはすべての行)の変更が必要であれば、UPDATE
ではなくCTAS文を使用する方が効率的な場合があります。
18.3.1.3 MERGEを使用したデータの変換
Oracle Databaseのマージ機能では、表または表外の単一表ビューに行を条件付きで更新または挿入できるように、SQLキーワードMERGE
を導入することでSQLが拡張されます。条件は、ON
句に指定します。これは、純粋な大量ロードと並んでデータ・ウェアハウスの同期化における最も一般的な操作です。
例18-1 SQLを使用したマージ操作
次の例では、マージの各種実装について説明します。この例では、ディメンション表productsの新規データをデータ・ウェアハウスに転送し、挿入または更新する必要があるものとします。表products_delta
の構造はproducts
と同じです。
MERGE INTO products t USING products_delta s ON (t.prod_id=s.prod_id) WHEN MATCHED THEN UPDATE SET t.prod_list_price=s.prod_list_price, t.prod_min_price=s.prod_min_price WHEN NOT MATCHED THEN INSERT (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_status, prod_list_price, prod_min_price) VALUES (s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory, s.prod_subcategory_desc, s.prod_category, s.prod_category_desc, s.prod_status, s.prod_list_price, s.prod_min_price);
18.3.1.4 マルチテーブル・インサートを使用したデータの変換
多くの場合、外部データソースは、様々なターゲット・オブジェクトに挿入できるように、論理属性に基づいて分離する必要があります。また、通常、データ・ウェアハウス環境では、同じソース・データが複数のターゲット・オブジェクトに分岐しています。マルチテーブル・インサートでは、この種の変換用に新しいSQL文が用意されており、ビジネス上の変換ルールに応じてデータを複数または1つのターゲットにできます。この挿入は、ビジネス・ルールに基づいて条件付きで行う方法と、無条件で行う方法があります。
これには、複数の表がターゲットとなっている場合に、INSERT
... SELECT
文を使用できるというメリットがあります。これにより、2つの別の方法でのデメリットを回避できます。従来は、n個の独立したINSERT
… SELECT
文を取り扱う必要があり、同じソース・データをn回処理するため、変換による作業負荷もn倍になっていました。または、プロシージャによるアプローチを選択し、挿入の処理方法を行ごとに判断する必要がありました。このソリューションには、SQLで使用可能な高速のアクセス・パスへのダイレクト・アクセスが欠けています。
既存の文を使用する場合と同様に、新しいINSERT
... SELECT
文も、パラレル化してダイレクト・ロード機能とともに使用することでパフォーマンスを改善できます。
例18-2 無条件の挿入
次の文では、sales_activity_direct
に格納されているトランザクションの売上情報が日次で集計され、今日のsales
およびcosts
ファクト表に挿入されます。
INSERT ALL INTO sales VALUES (product_id, customer_id, today, 3, promotion_id, quantity_per_day, amount_per_day) INTO costs VALUES (product_id, today, promotion_id, 3, product_cost, product_price) SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id, s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity) quantity_per_day, p.prod_min_price*0.8 AS product_cost, p.prod_list_price AS product_price FROM sales_activity_direct s, products p WHERE s.product_id = p.prod_id AND TRUNC(sales_date) = TRUNC(SYSDATE) GROUP BY TRUNC(sales_date), s.product_id, s.customer_id, s.promotion_id, p.prod_min_price*0.8, p.prod_list_price;
例18-3 条件付きのALL挿入
次の文では、有効な宣伝を伴うすべての売上トランザクションについて、sales
およびcosts
表に1行が挿入され、ある顧客による複数の同一注文に関する情報が別の表cum_sales_activity
に格納されます。売上トランザクションには、2行を挿入できるものと、まったく挿入できないものがあります。
INSERT ALL WHEN promotion_id IN (SELECT promo_id FROM promotions) THEN INTO sales VALUES (product_id, customer_id, today, 3, promotion_id, quantity_per_day, amount_per_day) INTO costs VALUES (product_id, today, promotion_id, 3, product_cost, product_price) WHEN num_of_orders > 1 THEN INTO cum_sales_activity VALUES (today, product_id, customer_id, promotion_id, quantity_per_day, amount_per_day, num_of_orders) SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id, s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity) quantity_per_day, COUNT(*) num_of_orders, p.prod_min_price*0.8 AS product_cost, p.prod_list_price AS product_price FROM sales_activity_direct s, products p WHERE s.product_id = p.prod_id AND TRUNC(sales_date) = TRUNC(SYSDATE) GROUP BY TRUNC(sales_date), s.product_id, s.customer_id, s.promotion_id, p.prod_min_price*0.8, p.prod_list_price;
例18-4 条件付きのFIRST挿入
次の文では、製品注文の合計数量と重量に従って、適切な出荷リストに挿入されます。例外は大量注文の場合で、重量区分が大きすぎないかぎり急送されます。数量のない注文として表されているこの単純な例では、すべての不適切な注文は別の表に格納されます。ここでは、適切な表large_freight_shipping
、express_shipping
、default_shipping
およびincorrect_sales_order
が存在するものとします。
INSERT FIRST WHEN (sum_quantity_sold > 10 AND prod_weight_class < 5) AND sum_quantity_sold >=1) OR (sum_quantity_sold > 5 AND prod_weight_class > 5) THEN INTO large_freight_shipping VALUES (time_id, cust_id, prod_id, prod_weight_class, sum_quantity_sold) WHEN sum_amount_sold > 1000 AND sum_quantity_sold >=1 THEN INTO express_shipping VALUES (time_id, cust_id, prod_id, prod_weight_class, sum_amount_sold, sum_quantity_sold) WHEN (sum_quantity_sold >=1) THEN INTO default_shipping VALUES (time_id, cust_id, prod_id, sum_quantity_sold) ELSE INTO incorrect_sales_order VALUES (time_id, cust_id, prod_id) SELECT s.time_id, s.cust_id, s.prod_id, p.prod_weight_class, SUM(amount_sold) AS sum_amount_sold, SUM(quantity_sold) AS sum_quantity_sold FROM sales s, products p WHERE s.prod_id = p.prod_id AND s.time_id = TRUNC(SYSDATE) GROUP BY s.time_id, s.cust_id, s.prod_id, p.prod_weight_class;
例18-5 条件付き挿入と無条件の挿入の混在型
次の例では、新規顧客がcustomers
表に挿入され、cust_credit_limit
が4501以上のすべての新規顧客がさらなる販促対象として別個の表に格納されます。
INSERT FIRST WHEN cust_credit_limit >= 4500 THEN INTO customers INTO customers_special VALUES (cust_id, cust_credit_limit) ELSE INTO customers SELECT * FROM customers_new;
関連項目:
MERGE
操作の詳細は、マテリアライズド・ビューのリフレッシュを参照してください。
18.3.2 PL/SQLを使用したデータの変換
データ・ウェアハウス環境では、PL/SQLなどの手続き型言語を使用して、複雑な変換をOracle Databaseで実装できます。CTASが表全体を操作し、パラレル化を強調するのに対して、PL/SQLでは、行ベースの方法で、非常に高度な変換規則に対応できます。たとえば、PL/SQLプロシージャを使用して、複数のカーソルをオープンして複数のソース表からデータを読み取り、複雑なビジネス・ルールを使用してこのデータを結合できます。これによって、変換されたデータを1つ以上のターゲット表に挿入できます。標準SQL文を使用して、同じ手順の操作を表現するのは困難または不可能です。
手続き型言語を使用すると、複雑なETL処理内で特定の変換(または多数の変換ステップ)をカプセル化し、中間のステージング・エリアからデータを読み取り、出力として新しい表オブジェクトを生成できます。以前に生成された変換の入力表と後続の変換には、この特定の変換により生成される表が使用されます。また、ETLプロセス全体でこのようにカプセル化された変換ステップをシームレスに統合できるため、相互の行セットがストリーム化され、中間的なステージングが不要になります。テーブル・ファンクションを使用すると、このような動作を実装できます。
18.3.3 テーブル・ファンクションを使用したデータの変換
テーブル・ファンクションにより、PL/SQL、CまたはJavaで実装された変換のパイプライン実行またはパラレル実行がサポートされます。 前述のシナリオは、中間的なステージング表を使用せずに実行でき、各種変換ステップでのデータ・フローは中断されません。テーブル・ファンクションに関する詳細情報は、テーブル・ファンクションに記載されています。
18.3.3.1 テーブル・ファンクション
テーブル・ファンクションは、出力として行セットを生成できる関数として定義されます。また、テーブル・ファンクションは入力として行セットを使用できます。Oracle9iまでのPL/SQLファンクションには、次のようなデメリットがありました。
-
入力としてカーソルを使用できません。
-
パラレル化またはパイプライン化できません。
現在、ファンクションにこのような制限はありません。テーブル・ファンクションでは、次のことができるため、データベース機能が拡張されます。
-
1つの関数から複数行を戻すことができます。
-
SQL副問合せ(複数行の選択)の結果を関数に直接渡すことができます。
-
関数に入力としてカーソルを使用できます。
-
関数をパラレル化できます。
-
結果セットが作成されるとすぐに次の処理に段階的に渡します。これは段階的パイプライン処理と呼ばれます。
テーブル・ファンクションは、ネイティブなPL/SQLインタフェースを使用してPL/SQLで定義するか、Oracle Data Cartridge Interface(ODCI)を使用してJavaまたはCで定義できます。
関連項目:
-
詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
行セットを入力してSUM操作の実行後に行セットを出力する典型的な集計を、図18-3
に示します。
この操作のための疑似コードは次のようになります。
INSERT INTO Out SELECT * FROM ("Table Function"(SELECT * FROM In));
このテーブル・ファンクションは、In
でのSELECT
の結果を入力として使用し、レコード・セットを異なるフォーマットでダイレクト挿入用の出力としてOut
に渡します。
また、テーブル・ファンクションでは、データをアトミック・トランザクションの適用範囲内で分岐させることができます。これは、効率的なロギング・メカニズムや、他の独立した変換の分岐など、様々な場合に使用できます。このような使用例では、単一のステージング表が必要です。
このための疑似コードは次のようになります。
INSERT INTO target SELECT * FROM (tf2(SELECT * FROM (tf1(SELECT * FROM source))));
この場合は、target
に挿入され、アトミック・トランザクションの適用範囲内でtf1
の一部としてStage
Table
1
にも挿入されます。
INSERT INTO target SELECT * FROM tf3(SELT * FROM stage_table1);
関連項目:
-
テーブル・ファンクションの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQL以外の言語で実装されるテーブル・ファンクションの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
テーブル・ファンクションの例を実行する前に作成するオブジェクト
次の例は、テーブル・ファンクションの基本を示しており、これらの関数内に実装された複雑なビジネス・ルールは使用されていません。あくまでも具体例を示すことが目的であり、すべてPL/SQLで実装されています。
テーブル・ファンクションはレコード・セットを戻し、入力としてカーソルを使用できます。この例を使用する前に、sh
サンプル・スキーマの他に次のデータベース・オブジェクトを設定する必要があります。
CREATE TYPE product_t AS OBJECT ( prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50) , prod_category_desc VARCHAR2(2000) , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) , prod_list_price NUMBER(8,2) , prod_min_price NUMBER(8,2) ); / CREATE TYPE product_t_table AS TABLE OF product_t; / COMMIT; CREATE OR REPLACE PACKAGE cursor_PKG AS TYPE product_t_rec IS RECORD ( prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50) , prod_category_desc VARCHAR2(2000) , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) , prod_list_price NUMBER(8,2) , prod_min_price NUMBER(8,2)); TYPE product_t_rectab IS TABLE OF product_t_rec; TYPE strong_refcur_t IS REF CURSOR RETURN product_t_rec; TYPE refcur_t IS REF CURSOR; END; / REM artificial help table, used later CREATE TABLE obsolete_products_errors (prod_id NUMBER, msg VARCHAR2(2000));
例18-6 テーブル・ファンクションの例: 基本的な例
この例は、prod_category
Electronicsを除くすべての廃止製品を表示する単純なフィルタ処理です。このテーブル・ファンクションは、結果セットとしてレコード・セットを戻し、入力として弱い型指定を持つREF
CURSOR
を使用しています。
CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t) RETURN product_t_table IS prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; objset product_t_table := product_t_table(); i NUMBER := 0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched -- Category Electronics is not meant to be obsolete and will be suppressed IF prod_status='obsolete' AND prod_category != 'Electronics' THEN -- append to collection i:=i+1; objset.extend; objset(i):=product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price); END IF; END LOOP; CLOSE cur; RETURN objset; END; /
このテーブル・ファンクションをSQL文に使用すると、次の結果が表示されます。ここでは、出力用にSQLの機能を追加使用しています。
SELECT DISTINCT UPPER(prod_category), prod_status FROM TABLE(obsolete_products( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products)));
例18-7 テーブル・ファンクションの例: REF CURSORを使用したフィルタ処理
この例は、例18-6と同じフィルタ処理を実装しています。2つの主な違いは次のとおりです。
-
この例では、強い型指定を持つ
REF
CURSOR
を入力として使用しており、次に示す例の1つのように、そのカーソルのオブジェクトに基づいてパラレル化できます。 -
テーブル・ファンクションは、レコードの作成直後に段階的に結果セットを戻します。
CREATE OR REPLACE FUNCTION obsolete_products_pipe(cur cursor_pkg.strong_refcur_t) RETURN product_t_table PIPELINED PARALLEL_ENABLE (PARTITION cur BY ANY) IS prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched IF prod_status='obsolete' AND prod_category !='Electronics' THEN PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)); END IF; END LOOP; CLOSE cur; RETURN; END; /
次のようなテーブル・ファンクションを使用できます。
SELECT DISTINCT prod_category, DECODE(prod_status,'obsolete','NO LONGER AVAILABLE','N/A') FROM TABLE(obsolete_products_pipe( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products)));
ここで、入力表productsの並列度を変更し、再度同じ文を発行します。
ALTER TABLE products PARALLEL 4;
セッション統計は、文がパラレル化されていることを示します。
SELECT * FROM V$PQ_SESSTAT WHERE statistic='Queries Parallelized'; STATISTIC LAST_QUERY SESSION_TOTAL -------------------- ---------- ------------- Queries Parallelized 1 3 1 row selected.
例18-8 テーブル・ファンクションの例: 永続表への結果の移入
テーブル・ファンクションでは、結果を永続表の構造に分岐させることもできます。この例では、誤ってステータスobsolete
に設定されている特定のprod_category
(デフォルトはElectronics)の製品を除き、それ以外のすべての廃止製品が関数によってフィルタ処理されます。テーブル・ファンクションの結果セットは、他のすべての廃止製品カテゴリで構成されます。検出された間違ったprod_id
は、別個の表構造obsolete_products_error
に格納されます。テーブル・ファンクションが自律型トランザクションの一部である場合、サブプログラム呼出しでのエラーを回避するために、各PIPE
ROW
文の前にCOMMIT
またはROLLBACK
を実行する必要があることに注意してください。その結果セットは、他のすべての廃止製品カテゴリで構成されます。さらに、通常の変数をテーブル・ファンクションとともに使用する方法を示します。
CREATE OR REPLACE FUNCTION obsolete_products_dml(cur cursor_pkg.strong_refcur_t, prod_cat varchar2 DEFAULT 'Electronics') RETURN product_t_table PIPELINED PARALLEL_ENABLE (PARTITION cur BY ANY) IS PRAGMA AUTONOMOUS_TRANSACTION; prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched IF prod_status='obsolete' THEN IF prod_category=prod_cat THEN INSERT INTO obsolete_products_errors VALUES (prod_id, 'correction: category '||UPPER(prod_cat)||' still available'); COMMIT; ELSE PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)); END IF; END IF; END LOOP; CLOSE cur; RETURN; END; /
次の問合せでは、誤ってステータスobsolete
に設定されているprod_category
Electronicsを除き、すべての廃止製品グループが示されます。
SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products)));
このように、prod_category
Electronicsには、誤って廃止された製品があります。
SELECT DISTINCT msg FROM obsolete_products_errors;
2番目の入力変数を利用すると、Electronics以外の対象となる別の製品グループを指定できます。
SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products),'Photo'));
テーブル・ファンクションは通常の表と同様に使用できるため、次のようにネストできます。
SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml(CURSOR(SELECT * FROM TABLE(obsolete_products_pipe(CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products))))));
Oracle DatabaseのETLの最大のメリットはツールキット機能です。この機能を後述の機能と組み合せると、ETL処理を改善してスピードアップできます。たとえば、入力として外部表を使用し、それを既存の表と結合し、パラレル化されたテーブル・ファンクションの入力に使用して、複雑なビジネス・ロジックを処理できます。このテーブル・ファンクションはMERGE
操作の入力ソースとして使用できるため、フラット・ファイルとして提供されたデータ・ウェアハウス用の新規情報をストリーム化し、ETLプロセスを通じて単一の文中で処理できます。
18.4 エラーのロギングおよび処理のメカニズム
外部ソースなど、特に様々なソースからのデータを処理する場合、データをロードおよび変換する際に、制約に従っていないデータに遭遇する場合がよくあります。この不適切なデータによって、長時間のロードや変換の操作が中断させられると、多くの時間とリソースが無駄になります。
次のトピックでは、エラーの2つの主な原因とその対処方法について説明します。
18.4.1 ビジネス・ルールの違反
論理的に制約に従っていないデータは、データを使用する前に認識されているビジネス・ルールに違反します。ほとんどの場合、この種のエラー処理は、ロードまたは変換プロセスに組み込まれます。ただし、すべてのレコードに対するエラー識別にかかるコストが膨大となり、かつデータ・ルール違反であってもビジネス・ルールが適用される可能性があるような状況(数百の列をテストして、それらがNOT
NULL
かどうかを確認するなど)では、プログラマは多くの場合、既知の論理エラーの場合でも一般的に処理する方を選択します。この例は、データ・エラーのシナリオに示しています。
論理的なルールの組込みは、簡単な場合(データの入力ストリームに対してフィルタ条件を適用するなど)もあれば、複雑な場合(不適切なデータを異なる変換ワークフローに送り込むなど)もあります。次に、例をいくつか示します。
18.4.2 データ・ルールの違反(データ・エラー)
論理エラーと異なり、データ・ルールの違反は、ロード・プロセスや変換プロセスでは通常想定していません。操作では処理されないこのような想定外のデータ・ルールの違反(データ・エラー)によって、処理は失敗します。データ・ルールの違反は、データベース内で発生し、文を失敗させるエラー状況です。この例として、データ型の変換エラーや制約違反があります。
かつてのSQLでは、大量処理の一部として行レベルでデータ・エラーを処理する方法がありませんでした。データベース内のデータ・エラーを処理する唯一の手段は、PL/SQLを使用することでした。現在では、DML操作の続行中でもデータ・エラーのログを特別なエラー表に記録できるようになりました。SQL関数を使用してデータ変換エラーを処理できるようにもなりました。
次の項では、様々な例外処理戦略について簡単に説明します。
18.4.2.1 SQLを使用したデータ・エラーの処理
データ変換プロセスで使用される外部データは場合によって適切でなく、その結果、データ変換エラーが発生することがあります。特定のSQL関数を使用して、データ変換エラーを処理できます。
データ変換エラーを処理するSQL関数を使用するには、COMPATIBLE
パラメータを12.2に設定する必要があります。
SQL関数を使用したデータ変換エラーの処理には、次の方法を使用できます。
例18-9 VALIDATE_CONVERSIONおよびCASTを使用したデータ変換エラーの処理
TMP_PRODUCTS
表からPRODUCTS
表にデータをロードするとします。列の数と名前はどちらの表でも同じですが、prod_id
列のデータ型が異なります。PRODUCTS
表のprod_id
列のデータ型はNUMBER
です。TMP_PRODUCTS
表のprod_id
列のデータは数値ですが、データ型はVARCHAR2
です。PRODUCTS
表へのデータのロード中に、prod_id
列のデータ型変換エラーを処理するには、適切でないprod_id
値を含む行をフィルタ処理して除外するか、NUMBER
に変換できないprod_id
値にデフォルト値を割り当てます。
次のコマンドは、TMP_PRODUCTS
表からPRODUCTS
表にデータをロードします。tmp_products.prod_id
を数値に正常に変換できる行のみが挿入されます。
INSERT INTO PRODUCTS
(SELECT prod_id, prod_name, prod_desc, prod_category_id, prod_category_name,
prod_category_desc, prod_list_price
FROM tmp_products
WHERE VALIDATE_CONVERSION(prod_id AS NUMBER)=1);
CAST
関数を使用すると、データ型変換エラーの原因となるprod_id
値を処理できます。次のINSERT
コマンドは、TMP_PRODUCTS
表からPRODUCTS
表にデータをロードします。prod_id
で使用されているCAST
関数によって、データ型変換エラーが発生した場合にデフォルト値0がprod_id
に割り当てられます。これにより、データ型変換エラーが原因でロード操作が失敗することがなくなります。
INSERT INTO PRODUCTS
(SELECT CAST(prod_id AS NUMBER DEFAULT 0 ON CONVERSION ERROR), prod_name,
prod_desc, prod_category_id, prod_category_name, prod_category_desc,
prod_list_price
FROM tmp_products);
関連項目:
CAST
関数およびVALIDATE_CONVERSION
関数、およびそれらでサポートされるデータ型の詳細は、Oracle Database SQL言語リファレンスを参照してください。
18.4.2.2 PL/SQLを使用したデータ・エラーの処理
次の文は、PL/SQLを使用してエラー処理を行う方法の例を示しています。ここでは、すべてのエラーを捕捉するために、プロシージャによるレコードレベルの処理を使用する必要があります。この文は、エラー・ロギング表を使用したデータ・エラーの処理で説明している文とほぼ同じです。
DECLARE errm number default 0; BEGIN FOR crec IN (SELECT product_id, customer_id, TRUNC(sales_date) sd, promotion_id, quantity, amount FROM sales_activity_direct) loop BEGIN INSERT INTO sales VALUES (crec.product_id, crec.customer_id, crec.sd, 3, crec.promotion_id, crec.quantity, crec.amount); exception WHEN others then errm := sqlerrm; INSERT INTO sales_activity_error VALUES (errm, crec.product_id, crec.customer_id, crec.sd, crec.promotion_id, crec.quantity, crec.amount); END; END loop; END; /
18.4.2.3 エラー・ロギング表を使用したデータ・エラーの処理
DMLエラー・ロギングによって既存のDML機能が拡張され、ユーザーがエラー・ロギング表の名前を指定すると、DML操作中に検出されたエラーがOracle Databaseによってその表に記録されるようになりました。これにより、どのようなエラーが検出されてもDML操作を完了でき、後でエラーのある行において対処措置をとることができます。
このDMLエラー・ロギング表は、ターゲット列に起こり得るエラーを格納可能なデータ型を使用した、DML操作のターゲット表の列のすべてまたはサブセットを表すユーザー定義の列のセット、およびいくつかの必須の制御列から構成されています。たとえば、ターゲット表のNUMBER
列のTO_NUM
データ型変換エラーを格納するには、エラー・ロギング表にVARCHAR2
データ型が必要となります。DMLエラー・ロギング表を作成するには、DBMS_ERRLOG
パッケージを使用する必要があります。このパッケージおよびロギング表の構造の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
DMLターゲット表とエラー・ロギング表の間の列名のマッピングによって、制御列以外のどの列がDML操作でロギングされるかが決まります。
次の文は、DMLエラー・ロギングによってSQLを使用したデータの変換の例を強化する方法を示します。
INSERT /*+ APPEND PARALLEL */ INTO sales SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount FROM sales_activity_direct LOG ERRORS INTO sales_activity_errors('load_20040802') REJECT LIMIT UNLIMITED
すべてのデータ・エラーのログが、オプションのタグload_20040802
で識別される表sales_activity_errors
に記録されます。このINSERT
文は、データ・エラーが存在する場合でも成功します。この文を使用する前にDMLエラー・ロギング表を作成しておく必要がある点に注意してください。
REJECT
LIMIT
X
を指定していた場合、この文は失敗し、エラー・メッセージ「エラーX=1
」が示されます。このエラー・メッセージは、拒否制限(REJECT LIMIT)によって異なる場合があります。文が失敗した場合、DML文のみがロールバックされ、DMLエラー・ロギング表への挿入操作はロールバックされません。エラー・ロギング表には、X+1個の行が含まれます。
DMLエラー・ロギング表は、実行ユーザーとは異なるスキーマに配置できますが、その場合、表の完全な名前を指定する必要があります。状況に応じてDMLエラー・ロギング表の名前を省略することもできます。その場合、DBMS_ERRLOG
パッケージによって生成される表のデフォルト名が使用されます。
Oracle Databaseは、DML操作中に次のエラーを記録します。
-
大きすぎる列値
-
制約違反(
NOT
NULL
制約、一意制約、参照制約およびチェック制約) -
トリガー実行中に発生したエラー
-
副問合せの列と表の対応列との間の型変換で発生したエラー
-
パーティション・マッピング・エラー
次の状態では、文の実行が失敗しロールバックは実行されますが、エラー・ロギング機能は起動されません。
-
遅延制約の違反
-
領域不足エラー
-
一意制約や索引の違反を招くすべての
INSERT
操作(INSERT
またはMERGE
) -
一意制約や索引の違反を招くすべての
UPDATE
操作(UPDATE
またはMERGE
)
また、LONG
、LOB
またはオブジェクト型の列については、エラー・ロギング表においてエラーを追跡できません。エラー・ロギングを使用する際の制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
DMLエラー・ロギングは、あらゆる種類のDML操作に適用できます。次の項では、これらの例について説明します。
外部ロード・ユーティリティとしてのSQL*Loaderにも、データ・エラーのロギング機能がありますが、データベース内部に統合されたETL処理のメリットがありません。
18.5 ロードおよび変換の使用例
18.5.1 キー参照のシナリオ
典型的な変換として、キー参照があります。たとえば、売上トランザクション・データが小売データ・ウェアハウスにロードされているとします。データ・ウェアハウスのsales
表にはproduct_id
列がありますが、ソース・システムから抽出された売上トランザクション・データには、製品IDではなくUPCコード(Uniform Price Codes)があります。そのため、新しい売上トランザクション・データをsales
表に挿入できるようにするには、最初にUPCコードを製品IDに変換する必要があります。
この変換を実行するには、product_id
値をUPCコードに関係付ける参照表が必要です。この表は、product
ディメンション表か、またはこの変換をサポートするために特別に作成された、データ・ウェアハウスにある別の表です。この例では、product_id
列およびupc_code
列を持つ、product
という表があると想定しています。
このデータ置換による変換は、次のCTAS文を使用して実装できます。
CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS SELECT sales_transaction_id, product.product_id sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount FROM temp_sales_step1, product WHERE temp_sales_step1.upc_code = product.upc_code;
このCTAS文は、有効な各UPCコードを、有効なproduct_id
値に変換します。各UPCコードが有効であることをETLプロセスが保証している場合は、この文のみで変換全体を実装できます。
18.5.2 ビジネス・ルール違反のシナリオ
前述の例で、有効なUPCコードが付いていない新規の売上データ(論理データ・エラー)も処理する必要がある場合は、次のようにCTAS文を追加使用して無効な行を識別できます。
CREATE TABLE temp_sales_step1_invalid NOLOGGING PARALLEL AS SELECT * FROM temp_sales_step1 s WHERE NOT EXISTS (SELECT 1 FROM product p WHERE p.upc_code=s.upc_code);
無効なデータは別の表temp_sales_step1_invalid
に格納され、ETLプロセスで別々に処理できます。
無効なデータを処理する別の方法として、次の文に示すように、元のCTASを変更して外部結合を使用します。
CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS SELECT sales_transaction_id, product.product_id sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount FROM temp_sales_step1, product WHERE temp_sales_step1.upc_code = product.upc_code (+);
外部結合を使用すると、無効なUPCコードを含んでいた売上トランザクションに、NULL
のproduct_id
が割り当てられます。これらのトランザクションは後で処理できます。または、NULL
のproduct_id
で値を別の表に分離するマルチテーブル・インサートも使用できます。これは、予想されたエラー数がデータ全体の量と比較して相対的に少ないときに有効なアプローチとなる場合があります。後続の処理では、大きなターゲット表を扱う必要がなくなり、小さな表のみを扱うことになります。
INSERT /*+ APPEND PARALLEL */ FIRST WHEN sales_product_id IS NOT NULL THEN INTO temp_sales_step2 VALUES (sales_transaction_id, sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount) ELSE INTO temp_sales_step1_invalid VALUES (sales_transaction_id, sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount) SELECT sales_transaction_id, product.product_id sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount FROM temp_sales_step1, product WHERE temp_sales_step1.upc_code = product.upc_code (+);
このソリューションでは、空の表temp_sales_step2
およびtemp_sales_step1_invalid
があらかじめ存在している必要がある点に注意してください。
無効なUPCコードを処理するには、他にも方法があります。あるデータ・ウェアハウスではNULL値のproduct_id
をsales
表に挿入するように選択されます。また、無効なUPCコードがすべて処理されるまで、バッチ全体のどの新規データもsales
表に挿入できないデータ・ウェアハウスもあります。どの方法が適切かは、データ・ウェアハウスのビジネス要件によって決まります。特定の要件に関係なく、例外処理は、変換と同じく基本的なSQLによって処理されます。
18.5.3 データ・エラーのシナリオ
データの質が不明の場合、ビジネス・ルール違反のシナリオで示した例を、次に示すように想定外のデータ・エラー(データ型の変換エラーなど)を処理するように強化できます。
INSERT /*+ APPEND PARALLEL */ FIRST WHEN sales_product_id IS NOT NULL THEN INTO temp_sales_step2 VALUES (sales_transaction_id, sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount) LOG ERRORS INTO sales_step2_errors('load_20040804') REJECT LIMIT UNLIMITED ELSE INTO temp_sales_step1_invalid VALUES (sales_transaction_id, sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount) LOG ERRORS INTO sales_step2_errors('load_20040804') REJECT LIMIT UNLIMITED SELECT sales_transaction_id, product.product_id sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount FROM temp_sales_step1, product WHERE temp_sales_step1.upc_code = product.upc_code (+);
この文は、表temp_sales_step1_invalid
に有効な製品UPCコードが存在しないという論理データ・エラー、およびsales_step2_errors
という名前のDMLエラー・ロギング表において可能性のあるその他のすべてのエラーを追跡します。エラー・ロギング表は、複数のDML操作で使用できます。
または、NOT
NULL
制約を使用して、データベース・レベルで有効なUPCコードが存在するというビジネス・ルールを実施する方法もあります。外部結合を使用すると、有効なUPCコードを持たないすべての注文はNULL
値にマップされ、データ・エラーとして扱われます。次の文では、これらのエラーを追跡するために、このDMLエラー・ロギング機能が使用されています。
INSERT /*+ APPEND PARALLEL */ INTO temp_sales_step2 VALUES (sales_transaction_id, sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount) SELECT sales_transaction_id, product.product_id sales_product_id, sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold, sales_dollar_amount FROM temp_sales_step1, product WHERE temp_sales_step1.upc_code = product.upc_code (+) LOG ERRORS INTO sales_step2_errors('load_20040804') REJECT LIMIT UNLIMITED;
エラー・ロギング表には、DML操作の失敗の原因となったすべてのレコードが格納されます。この内容を利用して、あらゆるエラーを分析および修正できます。エラー・ロギング表の内容は、DML操作自体が成功したかどうかに関わりなく、すべてのDML操作に対して保持されます。拒否制限(REJECT LIMIT)に達したため、次のSQL文が失敗したものと仮定します。
SQL> INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall 2 SELECT * FROM sales_activity_direct 3 LOG ERRORS INTO err$_sales_overall ('load_test2') 4 REJECT LIMIT 10; SELECT * FROM sales_activity_direct * ERROR at line 2: ORA-01722: invalid number
エラー・ロギング表の名前err$_sales_overall
は、DBMS_ERRLOG
パッケージを使用して導出されたデフォルトの名前です。詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
生成されたエラー・メッセージは、最初のエラー制限に達した後で発生しています。次のエラー(番号11)は、エラーを生じさせたものです。表示されているエラー・メッセージは、制限を超過したエラーを基準としているため、たとえば、9番目のエラーは、11番目のエラーとは異なる場合があります。
ターゲット表sales_overall
には入力されているレコードがありません(空の表だったと考えられる)が、エラー・ロギング表には、11個(REJECT
LIMIT
+
1
)の行が含まれます。
SQL> SELECT COUNT(*) FROM sales_overall; COUNT(*) ---------- 0 SQL> SELECT COUNT(*) FROM err$_sales_overall; COUNT(*) ---------- 11
DMLエラー・ロギング表は、すべてのエラー・ロギング表に必須であるいくつかの固定の制御列で構成されます。Oracleエラー番号の他に、エラー・メッセージも格納されます。多くの場合、エラー・メッセージにより、データ・エラーの根本的原因を分析および解決するための詳細な情報が示されます。次のDMLエラー・ロギング表のSQL出力は、この違いを示しています。2番目の出力には、NOT
NULL
違反によって拒否された行の追加情報が含まれています。
SQL> SELECT DISTINCT ora_err_number$ FROM err$_sales_overall; ORA_ERR_NUMBER$ --------------- 1400 1722 1830 1847 SQL> SELECT DISTINCT ora_err_number$, ora_err_mesg$ FROM err$_sales_overall; ORA_ERR_NUMBER$ ORA_ERR_MESG$ 1400 ORA-01400: cannot insert NULL into ("SH"."SALES_OVERALL"."CUST_ID") 1400 ORA-01400: cannot insert NULL into ("SH"."SALES_OVERALL"."PROD_ID") 1722 ORA-01722: invalid number 1830 ORA-01830: date format picture ends before converting entire input string 1847 ORA-01847: day of month must be between 1 and last day of month
関連項目:
制御列の詳細は、『Oracle Database管理者ガイド』を参照してください。
18.5.4 ピボットのシナリオ
データ・ウェアハウスは、多数の異なるソースからデータを受け取ることができます。これらのソース・システムには、リレーショナル・データベースではないものもあり、データ・ウェアハウスとは大きく異なるフォーマットでデータが格納されている場合があります。たとえば、売上レコードの集合を、次のフォームの非リレーショナル・データベースから受け取ったとします。
product_id, customer_id, weekly_start_date, sales_sun, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
入力表は次のようになります。
SELECT * FROM sales_input_table;
PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT ---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ---------- 111 222 01-OCT-00 100 200 300 400 500 600 700 222 333 08-OCT-00 200 300 400 500 600 700 800 333 444 15-OCT-00 300 400 500 600 700 800 900
データ・ウェアハウスでは、次のような一般的なリレーショナル形式でsh
サンプル・スキーマのファクト表sales
にこれらのレコードを格納します。
prod_id, cust_id, time_id, amount_sold
ノート:
この例では、簡潔にするために表の多数の列を無視しているため、sales
表の多数の制約が使用禁止になっています。
これには、入力ストリームの各レコードが、データ・ウェアハウスのsales
表の7つのレコードに変換されるように変換処理を作成する必要があります。通常、この操作はピボットと呼ばれ、Oracle Databaseでは複数の方法でこの操作を行えます。
前述の例の結果は、次のようになります。
SELECT prod_id, cust_id, time_id, amount_sold FROM sales; PROD_ID CUST_ID TIME_ID AMOUNT_SOLD ---------- ---------- --------- ----------- 111 222 01-OCT-00 100 111 222 02-OCT-00 200 111 222 03-OCT-00 300 111 222 04-OCT-00 400 111 222 05-OCT-00 500 111 222 06-OCT-00 600 111 222 07-OCT-00 700 222 333 08-OCT-00 200 222 333 09-OCT-00 300 222 333 10-OCT-00 400 222 333 11-OCT-00 500 222 333 12-OCT-00 600 222 333 13-OCT-00 700 222 333 14-OCT-00 800 333 444 15-OCT-00 300 333 444 16-OCT-00 400 333 444 17-OCT-00 500 333 444 18-OCT-00 600 333 444 19-OCT-00 700 333 444 20-OCT-00 800 333 444 21-OCT-00 900
例18-10 ピボットの例
次の例では、マルチテーブル・インサート構文を使用して、デモ表sh.sales
に異なる構造を持つ入力表からデータを挿入しています。マルチテーブルINSERT
文は次のようになります。
INSERT ALL INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date, sales_sun) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+1, sales_mon) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+2, sales_tue) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+3, sales_wed) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+4, sales_thu) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+5, sales_fri) INTO sales (prod_id, cust_id, time_id, amount_sold) VALUES (product_id, customer_id, weekly_start_date+6, sales_sat) SELECT product_id, customer_id, weekly_start_date, sales_sun, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat FROM sales_input_table;
この文では、ソース表が1回のみスキャンされ、毎日の適切なデータが挿入されます。
関連項目:
-
ピボットの詳細は、ピボット操作を参照してください。
-
pivot_clauseの構文については、『Oracle Database SQL言語リファレンス』
を参照してください。