はじめに
CData Sync V25.1でkintoneへのリバースETL機能が追加されました。詳細は
こちらの記事をご参照ください。kintoneへのリバースETL機能を利用すると、各種データベースからkintoneへのデータの書き戻しが可能です。本記事では複数のkintoneアプリ内のデータをCData SyncのレプリケーションジョブでSQL Serverに複製、SQL Server側で集計した結果を、kintoneの別なアプリにリバースETL機能で書き戻すシナリオでの活用方法をご紹介します。
事前準備
- CData Sync(V25.1以上) ※30日間の無償評価版あり
- kintone
- Microsoft SQL Server(Azure SQL Databaseでも可)
-
- 集計対象となるkintoneのアプリ（本例では、サンプルの案件管理アプリを元に「Web販売」「対面販売」「代理店販売」の３つのアプリ）
CREATE TABLE 案件管理_集計
(
日時 DATETIME NOT NULL,
対面販売 DECIMAL(15,0),
代理店販売 DECIMAL(15,0),
Web販売 DECIMAL(15,0),
合計 DECIMAL(15,0),
PRIMARY KEY (日時)
);
手順
kintoneからSQL Serverへのレプリケーションジョブの作成・実行
CData Syncの管理コンソールに入り、「接続」の「+接続の追加」ボタンをクリックして「データソース」タブより「kintone」、「同期先」タブより「SQL Server」の接続を作成します。
次に「ジョブ」を開き、「+ジョブを追加」ボタンをクリックして、kintoneからSQL Serverにレプリケーションするジョブ（本例では「kintone2SQL」）を作成します。ジョブのタスク（kintoneアプリ）には、事前準備で作成した３つのアプリを指定します。
ジョブを実行するとSQL Server側にアプリ名と同名のテーブルが作成され、データが複製されます。
※上記画面はAzure SQL Databaseのクエリエディタから確認しています
SQL Serverで集計クエリを実行
CData Syncの管理コンソールに入り、「変換」の「+変換の追加」ボタンをクリックして「新しい変換」>「名前：本例では集計」「種類：SQL」「同期先：SQL Server」を選択して「変換の追加」を行います。
作成した変換ジョブを選択して「クエリ」タブよりSQL Server上で実行したいクエリを登録します。
本記事ではSQL Server上の「案件管理_集計」テーブル内のデータを削除し、その後に各アプリの複製テーブル（例：案件管理_対面販売、案件管理_代理店販売、案件管理_Web販売）の集計データを「案件管理_集計」テーブルにINSERTするクエリを登録しました。
INSERT INTO
案件管理_集計
SELECT
DATEADD(hour, +9, SYSDATETIME()) AS 日時,
T.対面販売,
D.代理店販売,
W.Web販売,
T.対面販売 + D.代理店販売 + W.Web販売 AS 合計
FROM
(SELECT SUM(CONVERT(decimal,小計)) AS 対面販売 FROM 案件管理_対面販売) T
CROSS JOIN
(SELECT SUM(CONVERT(decimal,小計)) AS 代理店販売 FROM 案件管理_代理店販売) D
CROSS JOIN
(SELECT SUM(CONVERT(decimal,小計)) AS Web販売 FROM 案件管理_Web販売) W
;
登録したら変換ジョブを実行してみます。ジョブが正常終了したらSQL Server上の「案件管理_集計」テーブル内のデータを参照して集計されたデータが登録されていることを確認します。
作成した変換ジョブは、「kintoneからSQL Serverへのレプリケーションジョブの作成・実行」で作成したレプリケーションジョブの後続ジョブとして実行することが可能です。
SQL ServerからkintoneへのリバースETLジョブの作成・実行
CData Syncの管理コンソールに入り、「接続」の「+接続の追加」ボタンをクリックして「データソース」タブより「SQL Server」の接続を作成します。なお、kintoneについてはデータソースと同期先を共有のコネクションとしてご利用いただけます。
次に「ジョブ」を開き、「+ジョブを追加」ボタンをクリックして、SQL ServerからkintoneへのリバースETLジョブ（本例では「SQL2kintone」）を作成します。転送モードは集計結果のレコードを追加したいのでInsertを選択します。
テーブルを設定画面にてデータソース（SQL Serverの集計テーブル、本例では「案件管理_集計」）と同期先（kintoneの集計結果用アプリ、本例では「案件管理_集計」）を選択します。
データソースとなるSQL Serverと同期先のkintoneアプリの項目マッピングのカラムを追加してジョブを追加します。
追加したジョブを実行します。
ジョブが正常実行できたら、kintoneの集計結果用アプリ、本例では「案件管理_集計」を開いてみましょう。レコードが追加されて各kintoneアプリのデータが集計されて一つの別なkintoneアプリに反映することができました。
本手順では手動でジョブを実行しましたが、作成したジョブは毎日夜の0時に実行、1時間毎に実行といったようにスケジュール登録して集計作業を自動化することも可能です。
まとめ
本記事では複数のkintoneアプリ内のデータをCData SyncのレプリケーションジョブでSQL Serverに複製、SQL Server側で集計した結果をkintoneの別なアプリにリバースETL機能で書き戻すシナリオでの活用方法をご紹介しました。本例では全てkintone内のアプリをデータソースとして利用しましたが、同じ方法で、複数のSaaSアプリケーション（SalesforceやHubspot、Googleスプレッドシート）のデータを集計してkintoneに集約することも可能です。
