CData Software Blog

クラウド連携のCData Software の技術ブログです。

SQL Server から kintone へストアドプロシージャで自動連携:SQL Gateway

f:id:sennanvolar44:20210415095740p:plain こんにちは、テクニカルサポートエンジニア の宮本(@miyamon44)です。

今回は SQL Server の CDC 機能+ストアドプロシージャを使い、リンクサーバー経由で kintone のデータを定期的に更新する方法をご紹介します。

シナリオ

やりたいことは、SQL Server にある既存テーブルの更新された値を、kintone に自動的に取り込むことを行います。
構成は、kintone と SQL Server の間に SQL Gateway を配置し、SQL Gateway からデータソースへのアクセスは HTTP リクエスト、SQL Gateway からクライアントへはTDSプロトコルSQLServer)でアクセスできるようにします。
f:id:sennanvolar44:20210414163008p:plain

SQL Gatewayとは

Salesforce や Kintone、スマレジなど、CData が対応しているデータソースに MySQLSQLServer の I/F でアクセスすることができるツールとなります。

https://www.cdata.com/jp/sqlgateway/ f:id:sennanvolar44:20200610170647p:plain

これを使うことで、SQLServer のリンクサーバー経由や MySQL へ接続できるツール、サービスなどからも SaaS にアクセスすることが可能になります。 もちろん参照だけではなく更新もできます!

手順

やることは大枠でこちらの内容です。

いろいろ書いてますが特に難しいところはなく、淡々と設定できると思います。
では、さっそくやっていきましょう。

CData ODBC Driver のインストール&DSN設定

実は CData ODBC Driver をインストールすると SQL Gateway も併せてインストールされるようになっています。

今回は kintone ODBC Driver を利用しますが、他のデータソースでも利用可能ですので、その際は接続設定やテーブル名などを読み替えていただければ大丈夫です。では以下のリンクから kintone をクリックしてインストーラーをダウンロードします。
https://www.cdata.com/jp/odbc/

f:id:sennanvolar44:20210414163134p:plain

ダウンロードボタンをクリックして kintone ODBC Driver をダウンロードします。
f:id:sennanvolar44:20210414163300p:plain

ダウンロードした kintoneODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。

インストール後にDSN 設定画面が開きますので、kintone の接続情報を設定します。接続テストが成功したらそのままOKボタンを押して設定情報を保存します。
f:id:sennanvolar44:20210414163956p:plain

ここまでで、CData kintone ODBC Driver の設定が完了しました。
続いては SQL Gateway の設定に入っていきます。

SQL GatewayTDS プロトコル IF を作成

Windows メニューにある SQL Gateway クリックして起動します。
f:id:sennanvolar44:20210405162229p:plain

SQL Gateway が起動されたらこのようなコンソール画面が表示されます。
最初に「サービス」タブから追加ボタンをクリックします。
f:id:sennanvolar44:20210405165832p:plain

以下の内容で設定します。

  • サービス名=任意、
  • IF=サービス名下のラジオボタンTDS(SQL Server) を選択
  • データソース=先ほど設定したDSN
  • ポート=未使用のもの

f:id:sennanvolar44:20210414164133p:plain

外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。
f:id:sennanvolar44:20210405171037p:plain

作成後は「変更を保存」→「開始」の順でボタンをクリックします。サービスが正常に開始されると、このように緑色のランプが点灯します。
f:id:sennanvolar44:20210414164241p:plain

これで データソース~SQL Gateway 間の接続設定と、TDS プロトコルでのインタフェース設定が完了しました。

リンクサーバーの作成

次に SQL ServerSQL Gateway つなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」→「リンクサーバー」で右クリックでリンクサーバーの新規作成を行います。

f:id:sennanvolar44:20210414164614p:plain

セキュリティに移動して、SQL Gateway で作成したユーザー情報を入力して設定は完了です。
f:id:sennanvolar44:20210405181742p:plain

これで kintone のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。
f:id:sennanvolar44:20210414164753p:plain

SQL Server の設定(CDC)

まずは CDC(Change Data Capture) 機能をオンにしていきます。ちなみにこの CDC とは、各テーブルのデータの変更情報を保持してくれる機能です。
※ちなみに CDC は Standard Edition 以上、もしくは Developer Edition で利用可能です。

docs.microsoft.com

今回はこの CDC 機能を使って変更データを抽出し、kintone に連携していきます。

CDC 機能を利用するデータベースを指定して以下SQLを実行します。

USE cdata;
GO 
EXECUTE sys.sp_cdc_enable_db;  
GO

CDC の対象とするテーブルを設定します。

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'顧客データ_東北支店',  
@role_name     = N'cdc_role',  
@supports_net_changes = 1
GO  

f:id:sennanvolar44:20210414171642p:plain
各種パラメータの説明は以下を参照ください。上記以外にも設定できるようです。
sys.sp_cdc_enable_table (Transact-sql) - SQL Server | Microsoft Docs

これで対象 DB のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。
f:id:sennanvolar44:20210414171733p:plain

何も変更していない状態では、dbo顧客データ東北支店_CT テーブルのレコードはない状態です。
f:id:sennanvolar44:20210414171859p:plain

CDC 対象の顧客データ_東北支店 テーブルにあるメールアドレス列の値を変更してみます。
f:id:sennanvolar44:20210414172428p:plain

もう一度、dbo顧客データ東北支店_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。
f:id:sennanvolar44:20210414173611p:plain

これで SQL Server での変更したデータを確認することが出来るようになりました!

ストアドプロシージャの作成

シンプルに CDC テーブルの Name という項目だけを使用して、それをリンクサーバーの [Kintone].[顧客管理(営業支援パック)] テーブルに Insert、Delete、Update で連携するということをやってみます。

CREATE PROCEDURE KintoneSP
AS
BEGIN
SET ANSI_NULLS ON 
SET ANSI_WARNINGS ON

--カーソルの値を取得する変数宣言
DECLARE @Id varchar(50)
DECLARE @email varchar(50)
DECLARE @Operation int

--カーソル定義
DECLARE CUR_1 CURSOR FOR
SELECT a.[RecordId], a.[メールアドレス],a.[__$operation]
FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT] AS a, 
    (SELECT [RecordId] , MAX([__$seqval]) AS seqval
     FROM   [cdata].[cdc].[dbo_顧客データ_東北支店_CT]
     GROUP BY [RecordId]) AS b
WHERE a.[RecordId] = b.[RecordId]
AND a.[__$seqval] = b.seqval
--'3'は更新前レコード
AND a.[__$operation] <> '3'

--カーソルオープン
OPEN CUR_1;
FETCH NEXT FROM CUR_1
INTO @Id,@email,@Operation;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- 削除レコードの場合
    IF @Operation = 1 
        BEGIN
          DELETE FROM [KINTONE_GATEWAY].[CData Kintone Sys].[Kintone].[顧客管理(営業支援パック)] WHERE RecordId=@Id;
        END
    -- 新規レコードの場合
    ELSE IF @Operation = 2
        BEGIN
          INSERT INTO [KINTONE_GATEWAY].[CData Kintone Sys].[Kintone].[顧客管理(営業支援パック)] ([メールアドレス]) Values(@email);
        END
    -- 更新レコードの場合
    ELSE IF @Operation = 4
        BEGIN
          UPDATE [KINTONE_GATEWAY].[CData Kintone Sys].[Kintone].[顧客管理(営業支援パック)] SET [メールアドレス] = @email WHERE RecordId=@Id;
        END

    --次のレコード
    FETCH NEXT FROM CUR_1
    INTO @Id,@email,@Operation;
END

--カーソルクローズ
CLOSE CUR_1;
DEALLOCATE CUR_1;

--CDC変更テーブルのレコード削除
Truncate table [cdc].[dbo_顧客データ_東北支店_CT]

END

スケジュール設定

最後にSQL Server エージェントでストアドプロシージャのスケジュール実行を設定します。
f:id:sennanvolar44:20210415065227p:plain

ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。
f:id:sennanvolar44:20210415065439p:plain

実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。
f:id:sennanvolar44:20210405231840p:plain

設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。
f:id:sennanvolar44:20210415090914p:plain

これで設定が完了しました!

SQLServer のデータを更新

以下は変更テーブルの中身です。赤枠内のレコードが リンクサーバーを介して kintone に反映される想定です。
赤枠レコードの内訳は、

  • 1行目:削除レコード(__$operation:1)
  • 2行目:更新前レコード(__$operation:3)
  • 3行目:更新後レコード(__$operation:4)
  • 4行目:追加レコード (__$operation:2)

f:id:sennanvolar44:20210415094458p:plain

ではこれでAM3:00 に実行されるのを待ってみると、ジョブが実行されました。
f:id:sennanvolar44:20210415094821p:plain

リンクサーバーの [Kintone].[顧客管理(営業支援パック)] テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。
f:id:sennanvolar44:20210415095322p:plain

これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとに kintone を更新されるようになりました。

おわりに

いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーで kintone に自動連携できるようになりました。
今回は kintone でしたが、以下の記事のように他のデータソースでも同じようにご利用いただけます。

www.cdatablog.jp

CData ODBC Driver は 30 日間の無償評価版がご利用可能です。是非お試しください!

www.cdata.com