IICS CDIでOracle接続を試す（マッピング編）
こんにちは。データアナリティクス事業本部の松村です。
前回は、Informatica Intelligent Cloud Services（以降IICS）のデータ統合サービスであるCloud Data Integration（以降CDI）において、Oracleコネクタの設定方法を調べてみました。今回はマッピングで使えるOracleコネクタの便利な機能について紹介します。
Informatica Intelligent Cloud Services Cloud Data IntegrationでOracle接続を試す（設定編）
シノニムおけるリレーション追従
マッピングにおいて、使用するコネクタによってはソースタイプで『複数のオブジェクト』というものが選択できます。これは複数のデータソースオブジェクトを結合してデータ取得する必要があるときに、ウィザードに近い形で結合条件を設定できるオプションです。
このとき、オブジェクト間にリレーション（外部キー）があると、それを候補として表示してくれるのですが、シノニムに対しても働いてくれます。実体のテーブルが他のスキーマにあってもOKです。
複数オブジェクトを選択するときは、まずは中心になるオブジェクトを選択します。このスキーマのオブジェクトはすべてシノニムです。
次に関連オブジェクトを選択します。前の画面でSYNONYMと表示されていたオブジェクトが関連オブジェクトとして表示され、結合条件も自動で判定してくれています。
この恩恵にあずかれるのは実体のテーブルにリレーションが設定されている場合に限りますが、嬉しい機能です。
ビューにおいては、たとえ単一のテーブルを参照するものであってもリレーション追従はしないようです。
ターゲットの一括ロード（INSERT）
ターゲットの一括ロード機能により、非常に高速にデータのINSERTを行うことができます。これはOracleデータベースのダイレクト・パス・ロード機能により実現されています。
（手順は省略しますが、Oracleデータベースの統合監査機能により、ダイレクト・パスAPIが呼び出されていることを確認しました。）
使い方は、『ターゲットの一括ロードの有効化』をチェックするだけです。簡単ですね。
これを指定した場合としない場合で、どのぐらい性能に差が出るかを見てみました。OracleデータベースのサンプルスキーマBIのSALESシノニム（データ件数918,843件）をSALES_COPYテーブルにコピーしています。
（注：性能は環境や実行時の周辺要因にも左右されますので、あくまで参考としてご覧ください。）
通常のINSERT
まずは普通にINSERTしてみます。
結果を見てみると、5分強かかっています。
ターゲットの一括ロードによるINSERT
次に『ターゲットの一括ロードの有効化』をチェックしました。
わずか15秒で処理が完了しています。圧倒的に速くなりましたね。
応用
ターゲットの一括ロードは残念ながらInsert操作でしか行うことができません。
その他の操作ではチェックボックス自体が表示されません。
しかし、ターゲット更新後に、『Post SQL』に設定した任意のSQLを実行することができますので、これを応用してUpdateやDelete操作と同じことをもっと速く処理することができます。具体的には、作業用テーブルをターゲットにして一括ロードを行い、Post SQLで作業用テーブルを使って相関UPDATE/DELETE、もしくはMERGEを実行すれば、Oracleサーバーとのラウンドトリップをぐっと減らすことができます。
では実際にやってみましょう。普通にUpsert操作により更新した場合と、一括ロードによるInsert操作とMERGE文の組み合わせで更新した場合とで性能の違いを見てみます。こちらも比較結果は参考程度にご覧ください。
Upsert
OracleデータベースのサンプルスキーマBIのCUSTOMERSシノニム（データ件数55,500件）を、同じ構造を持ったテーブルCUSTOMERS_COPYにUpsertで操作で登録します。UpsertのキーはCUST_IDで、これを『更新カラム』に指定します。
CUSTOMERSシノニムの中身はこのようになっています。
CUSTOMERS_COPYテーブルには、CUSTOMERSからCUST_IDが奇数のデータを抽出、かつ姓と名を一律'unknown'に変更して登録してあります。
これで、CUSTOMERSのデータのうち、CUST_IDが奇数のものはUPDATE、偶数のものはINSERTが実行されます。
これを実行したところ、所要時間は33分強でした。Upsert操作は、内部的には一旦ソースのデータをすべて使用して1行ずつUPDATEを実行、そのときの更新結果が0行だったデータを後からINSERT、という動作になっており、非常に多くのラウンドトリップが発生します。そのためどうしても時間がかかります。
更新結果は問題ありません。CUSTOMERS_COPYテーブルの内容がCUSTOMERSシノニムと同じになりました。
一括ロードによるInsertとMERGE文の組み合わせ
今度は、一旦作業用テーブルのCUSTOMERS_WORKにInsertしてから、Post SQLでMERGE文を実行します。
Insertはターゲットの一括ロードにより行います。
実行前にCUSTOMERS_WORKにデータが残っていると更新結果がおかしくなりますので、事前にTRUNCATEするオプションを忘れずに指定しておきます。
CUSTOMERS_WORKをGLOBAL TEMPORARY TABLEにできれば、事前のTRUNCATEも必要なくすっきりするのですが、それはできません。Post SQLを実行する前に一度データベースとのセッションが切断されるため、GLOBAL TEMPORARY TABLEではデータが消去されてしまうためです。このセッションの切断については、FAQに記載があります。
FAQ's on Pre SQL/Post SQL advanced property with Oracle Source/Target in IICS
少し横道にそれました。Post SQLにMERGE文を書いておきます。
スクリーンショットではMERGE文のごく一部しか見えないので、以下に全文を載せておきます。
MERGE INTO CUSTOMERS_COPY
USING CUSTOMERS_WORK WORK
ON (CUSTOMERS_COPY.CUST_ID = WORK.CUST_ID)
WHEN MATCHED THEN
UPDATE SET
CUST_FIRST_NAME = WORK.CUST_FIRST_NAME,
CUST_LAST_NAME = WORK.CUST_LAST_NAME,
CUST_GENDER = WORK.CUST_GENDER,
CUST_YEAR_OF_BIRTH = WORK.CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS = WORK.CUST_MARITAL_STATUS,
CUST_STREET_ADDRESS = WORK.CUST_STREET_ADDRESS,
CUST_POSTAL_CODE = WORK.CUST_POSTAL_CODE,
CUST_CITY = WORK.CUST_CITY,
CUST_CITY_ID = WORK.CUST_CITY_ID,
CUST_STATE_PROVINCE = WORK.CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID = WORK.CUST_STATE_PROVINCE_ID,
COUNTRY_ID = WORK.COUNTRY_ID,
CUST_MAIN_PHONE_NUMBER = WORK.CUST_MAIN_PHONE_NUMBER,
CUST_INCOME_LEVEL = WORK.CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT = WORK.CUST_CREDIT_LIMIT,
CUST_EMAIL = WORK.CUST_EMAIL,
CUST_TOTAL = WORK.CUST_TOTAL,
CUST_TOTAL_ID = WORK.CUST_TOTAL_ID,
CUST_SRC_ID = WORK.CUST_SRC_ID,
CUST_EFF_FROM = WORK.CUST_EFF_FROM,
CUST_EFF_TO = WORK.CUST_EFF_TO,
CUST_VALID = WORK.CUST_VALID
WHEN NOT MATCHED THEN
INSERT (
CUST_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_GENDER,
CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS,
CUST_STREET_ADDRESS,
CUST_POSTAL_CODE,
CUST_CITY,
CUST_CITY_ID,
CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID,
COUNTRY_ID,
CUST_MAIN_PHONE_NUMBER,
CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT,
CUST_EMAIL,
CUST_TOTAL,
CUST_TOTAL_ID,
CUST_SRC_ID,
CUST_EFF_FROM,
CUST_EFF_TO,
CUST_VALID
)
VALUES (
WORK.CUST_ID,
WORK.CUST_FIRST_NAME,
WORK.CUST_LAST_NAME,
WORK.CUST_GENDER,
WORK.CUST_YEAR_OF_BIRTH,
WORK.CUST_MARITAL_STATUS,
WORK.CUST_STREET_ADDRESS,
WORK.CUST_POSTAL_CODE,
WORK.CUST_CITY,
WORK.CUST_CITY_ID,
WORK.CUST_STATE_PROVINCE,
WORK.CUST_STATE_PROVINCE_ID,
WORK.COUNTRY_ID,
WORK.CUST_MAIN_PHONE_NUMBER,
WORK.CUST_INCOME_LEVEL,
WORK.CUST_CREDIT_LIMIT,
WORK.CUST_EMAIL,
WORK.CUST_TOTAL,
WORK.CUST_TOTAL_ID,
WORK.CUST_SRC_ID,
WORK.CUST_EFF_FROM,
WORK.CUST_EFF_TO,
WORK.CUST_VALID
);
TRUNCATE TABLE CUSTOMERS_WORK;
CUSTOMERS_COPYテーブルの中身は、Upsert処理による更新前と同じ状態に戻しておきます。
実行したところ、なんと9秒で処理が完了してしまいました。作業用テーブルやMERGE文を作るのがちょっと大変でしたが、それに見合う性能向上です。
更新結果も問題ありません。
性能的には大幅に向上しましたが、これと同じことを実施するためには、一時的とはいえ作業用テーブルの分だけ余分に表領域を消費するという点に気をつけてください。
注意
最初に書いたとおり、ターゲットの一括ロード機能は、Oracleデータベースのダイレクト・パス・ロード機能を使用しています。したがって注意点もそれに準じます。詳しくは以下のOracleデータベースのマニュアルをご覧ください。
最初の方の『従来型パス・ロード』は、別の方式なので読み飛ばして構いません。
逆に『索引メンテナンスの回避』『ダイレクト・ロード、整合性制約およびトリガー』あたりはひととおり目を通すことをおすすめします。特に後者は、制約やトリガーが一時的に無効になる件について触れられていますので、必読と言えるでしょう。
（注意）CHAR/VARCHAR2におけるマルチバイト文字
便利な機能ではありませんが、注意が必要なので書いておきます。
OracleデータベースでCHAR型/VARCHAR2型の列にマルチバイト文字を格納している場合、そのままだと読み書きにするときに文字化けが発生してしまいます。こちらをご覧ください。
このページではUTF-8について触れられていますが、Shift-JIS等他のキャラクタセットでも同じです。
This issue occurs when datatype of the field is varchar as it stores ASCII data.
の意味について画面で説明します。ソースまたはターゲットのプロパティで『フィールド』を開き、オプションから『メタデータの編集』を選択します。
するとこのように表示される項目が増えます。『ネイティブタイプ』にvarcharと表示されていますが、これがマルチバイトに対応していないので文字化けが発生するというわけです。
文字化けに対処するためには、このようにネイティブタイプをnvarcharに変更します。
CHARやVARCHAR2でマルチバイト文字が入ってくる列に対しては、ソース／ターゲットともにすべてこの対応をする必要があります。対象項目がたくさんあるとちょっと大変ですね。
ソースであれば以下のようにソースタイプを『クエリ』にして、TO_NCHAR関数を噛ませるか、同等のビューを作っておいてそれをソースオブジェクトにすれば、最初からネイティブタイプはnvarcharにマッピングしてくれます。
ターゲットの方はひとつずつ画面で変更していくしかありません。
新しくCDIでマッピングを作成するときは、ターゲットになるテーブルも新規に作成することが多いと思いますので、テーブル設計の時点で、マルチバイトありの列はNCHARやNVARCHAR2で定義してしまうのもひとつの手段でしょう。
もしくは、Oracleデータベースの文字コードがUTF-8、NLS_CHARACTERSETでいうとAL32UTF8もしくはUTF8の場合に限り、Secure Agentの設定で、CHAR型やVARCHAR2型を自動的にネイティブタイプのncharやnvarcharにマッピングさせることができます。
方法は基本的にこちらにある通りですが、画面を交えて説明します。
まずは管理者のメニューから『ランタイム環境』を選択します。次にSecure Agentをクリックして詳細画面を開きます。
『編集』ボタンをクリックします。
『カスタム属性の詳細』に以下の通り入力します。入力したら『保存』をクリックします。
- サービス :
Data Integration Server - タイプ :
Tomcat - サブタイプ : ブランク
- 名前 :
oracle.utf8.charset.check - 値 :
true
保存すると、自動的にData Integration Serverが再起動します。Secure Agentの詳細画面をもう一度開くと、Data Integration Serverの新しいバージョンが『起動中』と表示されています。
各サービスのバージョン番号の末尾は、設定変更のたびにインクリメントされる仕様で、このときはサービスのコンポーネントが更新されているわけではありません。
『ステータスの更新』を何度かクリックしていると、そのうち新しいバージョンのData Integration Serverのステータスが『稼働中』に切り替わり、先ほどまで稼働中だった古いバージョンのものは表示されなくなります。これで設定変更内容が反映され、Oracleデータベース上でCHARやVARCHAR2の列が、CDIのデザイナ上で自動的にncharやnvarcharにマッピングされるようになります。
繰り返しますが、この設定が有効なのはOracleデータベースのキャラクタセットがAL32UTF8やUTF8の場合に限られます。それ以外、例えばJA16SJISTILDEなどの場合、この設定を行っても、CHARやVARCHAR2の列は依然としてCDI上でcharやvarcharにマッピングされます。
プッシュダウン
Oracleコネクタに限りませんが、コネクタの中には、『プッシュダウン』というパフォーマンスを向上させる機能を持っているものがあります。簡単に言うと、マッピングでトランスフォーメーションを使って作成した処理を、可能な限りSQLに変換してデータベース側で実行し、SQL変換できない処理だけをSecure Agentで実行する、というものです。
これについてはもっと検証が必要なので、またの機会にご紹介したいと思います。
最後に
Oracleコネクタの便利な機能について見てみました。これからも他のコネクタやプッシュダウン機能など、IICSの機能を紹介していきますので、ご期待ください。
