こんにちは、テクニカルサポートエンジニア の宮本(@miyamon44)です。
今回は SQL Server の CDC 機能+ストアドプロシージャを使い、リンクサーバー経由で kintone のデータを定期的に更新する方法をご紹介します。
シナリオ
やりたいことは、SQL Server にある既存テーブルの更新された値を、kintone に自動的に取り込むことを行います。
構成は、kintone と SQL Server の間に SQL Gateway を配置し、SQL Gateway からデータソースへのアクセスは HTTP リクエスト、SQL Gateway からクライアントへはTDSプロトコル(SQLServer)でアクセスできるようにします。
SQL Gatewayとは
Salesforce や Kintone、スマレジなど、CData が対応しているデータソースに MySQL や SQLServer の I/F でアクセスすることができるツールとなります。
https://www.cdata.com/jp/sqlgateway/
これを使うことで、SQLServer のリンクサーバー経由や MySQL へ接続できるツール、サービスなどからも SaaS にアクセスすることが可能になります。 もちろん参照だけではなく更新もできます!
手順
やることは大枠でこちらの内容です。
- [CData ODBC Driver] インストール& DSN 設定
- [SQL Gateway] TDSプロトコル IF を作成
- [SQL Server] リンクサーバーを作成
- [SQL Server] CDC 機能をオンにする
- [SQL Server] 更新用ストアドプロシージャの作成
- [SQL Server] SQL Server エージェントでスケジュール設定
いろいろ書いてますが特に難しいところはなく、淡々と設定できると思います。
では、さっそくやっていきましょう。
CData ODBC Driver のインストール&DSN設定
実は CData ODBC Driver をインストールすると SQL Gateway も併せてインストールされるようになっています。
今回は kintone ODBC Driver を利用しますが、他のデータソースでも利用可能ですので、その際は接続設定やテーブル名などを読み替えていただければ大丈夫です。では以下のリンクから kintone をクリックしてインストーラーをダウンロードします。
https://www.cdata.com/jp/odbc/
ダウンロードボタンをクリックして kintone ODBC Driver をダウンロードします。
ダウンロードした kintoneODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。
インストール後にDSN 設定画面が開きますので、kintone の接続情報を設定します。接続テストが成功したらそのままOKボタンを押して設定情報を保存します。
ここまでで、CData kintone ODBC Driver の設定が完了しました。
続いては SQL Gateway の設定に入っていきます。
SQL Gateway で TDS プロトコル IF を作成
Windows メニューにある SQL Gateway クリックして起動します。
SQL Gateway が起動されたらこのようなコンソール画面が表示されます。
最初に「サービス」タブから追加ボタンをクリックします。
以下の内容で設定します。
- サービス名=任意、
- IF=サービス名下のラジオボタンでTDS(SQL Server) を選択
- データソース=先ほど設定したDSN
- ポート=未使用のもの
外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。
作成後は「変更を保存」→「開始」の順でボタンをクリックします。サービスが正常に開始されると、このように緑色のランプが点灯します。
これで データソース~SQL Gateway 間の接続設定と、TDS プロトコルでのインタフェース設定が完了しました。
リンクサーバーの作成
次に SQL Server と SQL Gateway つなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」→「リンクサーバー」で右クリックでリンクサーバーの新規作成を行います。
- リンクサーバー名:任意
- サーバーの種類:その他
- プロバイダー:SQL Server Native Client
- データソース:SQL Gateway を起動しているインスタンスとポート
「インスタンス,ポート」の書き方。 - カタログ:SQL Gateway で指定したDSN
セキュリティに移動して、SQL Gateway で作成したユーザー情報を入力して設定は完了です。
これで kintone のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。
SQL Server の設定(CDC)
まずは CDC(Change Data Capture) 機能をオンにしていきます。ちなみにこの CDC とは、各テーブルのデータの変更情報を保持してくれる機能です。
※ちなみに CDC は Standard Edition 以上、もしくは Developer Edition で利用可能です。
今回はこの 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
各種パラメータの説明は以下を参照ください。上記以外にも設定できるようです。
sys.sp_cdc_enable_table (Transact-sql) - SQL Server | Microsoft Docs
これで対象 DB のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。
何も変更していない状態では、dbo顧客データ東北支店_CT テーブルのレコードはない状態です。
CDC 対象の顧客データ_東北支店 テーブルにあるメールアドレス列の値を変更してみます。
もう一度、dbo顧客データ東北支店_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。
これで 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 エージェントでストアドプロシージャのスケジュール実行を設定します。
ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。
実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。
設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。
これで設定が完了しました!
SQLServer のデータを更新
以下は変更テーブルの中身です。赤枠内のレコードが リンクサーバーを介して kintone に反映される想定です。
赤枠レコードの内訳は、
- 1行目:削除レコード(__$operation:1)
- 2行目:更新前レコード(__$operation:3)
- 3行目:更新後レコード(__$operation:4)
- 4行目:追加レコード (__$operation:2)
ではこれでAM3:00 に実行されるのを待ってみると、ジョブが実行されました。
リンクサーバーの [Kintone].[顧客管理(営業支援パック)] テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。
これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとに kintone を更新されるようになりました。
おわりに
いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーで kintone に自動連携できるようになりました。
今回は kintone でしたが、以下の記事のように他のデータソースでも同じようにご利用いただけます。
CData ODBC Driver は 30 日間の無償評価版がご利用可能です。是非お試しください!