CData Japanテクニカルディレクター桑島です。

本記事ではCDataSyncの新バージョン(V21)で追加された変換機能についてご紹介します。変換機能の追加によって、データウェアハウスにデータをロードした後のデータマートの作成処理やデータウェアハウスをクリーンに保つための処理などがCDataSyncだけで実現できるようになりました。

• ELTのTとは
• 変換機能とは
• 利用ユースケース
  • データマートの作成
  • スナップショットの作成
  • データウェアハウスのクリーニング
• まとめ

ELTのTとは

データウェアハウスにデータを収集して活用できる状態にするには、ETL/ELTと呼ばれる手法やツールを利用します。ETL/ELTのアルファベット内のTは、Transformationの略で変換です。本プロセス内ではデータ利用者側からみてデータを利用しやすいように必要に応じて加工・編集を行います。ELTとはつまり、データウェアハウスにデータをロードしてからデータウェアハウス内の処理として変換を行う方式となります。

※下記ご紹介スライドの14ページ目より

ETLとELTの違いは処理の順番の違いで詳細はこちらの資料をご覧ください。

speakerdeck.com

変換機能とは

CDataSyncのV2021では、このTに相当する変換機能が追加されました。実際の管理コンソールではメニューバーのジョブの右隣の変換タブからアクセスできます。

CData Syncの従来からあるジョブ機能と似たようなUIですが、こちらで追加できるのは、CData Syncの同期先のデータソース、すなわち、以下のようなRDB/DWHに対してのクエリ（SQL）となります。

変換処理の追加は、右上の「＋変換を追加...」から行います。接続には既に接続タブで設定済みの同期先のデータソースが選択できます。

追加した変換処理のジョブ設定 > タスク 内の「設定」ボタンからクエリ（SQL）を追加します。ここでは、INSERT/UPDATE/DELETEといったDML、そして、CREATE TABLE / VIEWなどのDDLを複数定義することができます。

変換処理は、従来のジョブと同様にスケジュールやAPIトリガーで実行することができます。

利用ユースケース

この変換機能を利用することでデータウェアハウスにデータをロードした後の処理として以下のような処理をCData Syncで実現することができるようになります。こちらは一例ですので、実際には、一括してあるテーブルの項目を一括更新するなど様々なユースケースがあるでしょう。

• データマートの作成
• スナップショットの作成
• データウェアハウスのクリーニング

データマートの作成

データウェアハウスに、パフォーマンスやユーザビリティ、BIツールなどの可視化ツールからの利用を考慮して集計テーブルを作成するシーンです。これらの集計テーブルはデータマートと呼ばれたりもします。

例えば以下のSQLでは、Account（取引先）のテーブルをもとに、件数と金額を集計したテーブル（Account_Aggregate）を作成しています。

INSERT INTO sync.Account_Aggregate SELECT Type, COUNT(id) AS total_count, SUM(AnnualRevenue) AS AnnualRevenue FROM sync.Account group by Type;

これにより、ユーザは、事前集計済みのテーブルにアクセスすることで都度データベース側の集計処理を必要とせず、集計データにアクセスすることができるようになります。この変換処理を実際に運用するときには、以下のように、テーブルがなければ作成（1行目）、再実行可能なように一度テーブル内のデータを削除（2行目）してからINSERT（3行目）するのが良いでしょう。

CREATE TABLE IF NOT EXISTS sync.Account_Aggregate AS SELECT Type, COUNT(id) AS total_count, SUM(AnnualRevenue) AS AnnualRevenue FROM sync.Account group by Type LIMIT 0;
TRUNCATE TABLE sync.Account_Aggregate;
INSERT INTO sync.Account_Aggregate SELECT Type, COUNT(id) AS total_count, SUM(AnnualRevenue) AS AnnualRevenue FROM sync.Account group by Type;

変換処理のジョブ設定は以下の通りです。

スナップショットの作成

スナップショットとは、例えば、営業活動の案件パイプライン管理などでよく利用される週次や月次の推移を見るために、その時時点（これをスナップショットと呼ぶ）のデータを取得しておくことです。実際のデータ構造は以下のように保持します。この例では、A0001という顧客のstageがC→B→Aと遷移し、Opportunity（見込み金額）が案件クローズに近づいた5/27では120→100に変更されたことがわかります。

SFAやCRMアプリケーションは、最新データしか保持していないことが多いので、このようなスナップショットデータをデータウェアハウス側で履歴保持しておくというケースがあり、CData Syncの変換機能はこのようなユースケースについても以下のようなクエリで実現することができます。このクエリの例はMySQLの構文ですが「current_date()」というこのクエリを実行した時の日付を取得して「snapshot_date」にセットしています。

INSERT INTO sync.Account_Snapshot SELECT current_date() AS snapshot_date, Account.* FROM sync.Account;

この変換処理を実際に運用するときには、以下のように、テーブルがなければ作成（1行目）、再実行可能なように一度同じ日付のデータを削除（2行目）してからINSERT（3行目）するのが良いでしょう。

CREATE TABLE IF NOT EXISTS sync.Account_Snapshot AS SELECT current_date() AS snapshot_date, Account.* FROM sync.Account LIMIT 0;
DELETE FROM sync.Account_Snapshot WHERE snapshot_date = current_date();
INSERT INTO sync.Account_Snapshot SELECT current_date() AS snapshot_date, Account.* FROM sync.Account;

変換処理のジョブ設定は以下の通りです。

データウェアハウスのクリーニング

データウェアハウスは、データの履歴を保持するというのが大きな役割の一つです。しかし、ポリシーやルール（例えば3年間取引なない顧客データは削除する等）、データ容量の枯渇やサービス利用料金の削減など様々な理由でデータのライフサイクル内のデータのクリーニングを検討する必要があるでしょう。CData Syncの変換機能はこのようなユースケースについても以下のようなクエリで実現することができます。このクエリの例はMySQLの構文ですが、1年前のスナップショットデータを一括して削除することができます。このジョブを週次や月次のスケジュールとしてセットしておくことで定期的にデータウェアハウスのデータをクリーニングすることができます。

DELETE FROM sync.Account_Snapshot WHERE DATE_ADD(snapshot_date, INTERVAL 1 YEAR) < current_date();

変換処理のジョブ設定は以下の通りです。

まとめ

本記事では、CDataSync新バージョンの目玉機能ともいえる変換機能について3つのユースケースを例にご紹介しましたが、本機能以外にも多くの製品アップデートがありましたのでリリース情報はこちらをご覧ください。

www.cdata.com

また、CDataSync製品はこちらのサイトから30日間無償でご利用できる評価版もございますのでデータパイプラインの構築に取り組む予定のある方は是非お試しください。

www.cdata.com

関連コンテンツ