SQL Server にkintone リンクサーバーを作成、PowerBIやTableau からデータを参照

#はじめに
SQL Server のリンクサーバー機能って便利ですよね。
SQL Server のインターフェースで他のDB とかODBC データソースを叩きにいけます。

この記事ではkintone のデータをSQL Server のリンクサーバーとして設定します。
CData SQL Broker というサーバーツールを使って、ODBC for kintone DSN をSQL Server のリンクサーバーとして設定できます。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/3cc64a2b-2017-83db-cc6f-e5a8cc44227f.png)



利用ケース：
・SQL Server に接続しているアプリケーションでkintone データを利用したいがデータ接続を増やしたくない
・kintone データをSQL Server のデータや他のリンクサーバーのデータをJOIN などして使いたい
・利用したいミドルウェア・BI ツールなどにODBC やJDBC の汎用のインターフェースがないが、SQL Server には接続できる

#環境・利用アプリ
・Windows 10
・SQL Server 2017 + SQL Server Management Studio
・kintone （評価版あり）[kintone評価版アカウント取得、および、サンプルアプリケーションの作成方法](https://qiita.com/kuwazzy/items/416b9c884892016bcc9f)
・[CData ODBC Driver for kintone](https://www.cdata.com/jp/drivers/kintone/odbc/)（評価版あり）
・[CData SQL Broker](https://www.cdata.com/jp/sqlbroker/)


#ODBC Driver for kintone のインストールとkintone 接続



##ODBC Driver for kintone のインストール
[https://www.cdata.com/jp/drivers/kintone/odbc/](https://www.cdata.com/jp/drivers/kintone/odbc/) からODBC Driver をダウンロードします。30日の無償版があります。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/2309f6bc-b18a-af78-1ca3-a0340a9ed79f.png)


.exe ファイルをクリックして、インストーラーを起動し、インストールを行います。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/403186f5-5309-00a9-09c2-e388077c872f.png)

##kintone Driver のDSN 設定
インストールを完了するとkintone Driver のDSN 構成画面が開きます。

URL、User、Password はkintone に接続するためのプロパティです。

| プロパティ      | 内容 |   
|:-----------------|:------------------|
| Url             | kintone のURL |
| User           | kintone のユーザー名 |
| Password             | kintone のパスワード |

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/ca57f3ac-6beb-6318-3df1-503979fffe14.png)

#SQL Broker にkintone ODBC DSN を設定
CData ODBC Drivers にSQL Broker が内包されています。
「スタート」画面から「SQL Broker」を起動します。
起動すると右下のインジケータにSQL Broker のロゴが表示されます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/422e4ea6-645c-b8f6-5303-f8af19221f46.png)

アプリケーションコンソールが開かない場合には、「Open Application」をクリックしてください。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/4d3aec42-d4cc-ecff-779d-708bfab87be1.png)

##ODBC データソース設定
アプリケーションコンソールの「Service」タブを開いて、「Add」をクリックして、新しいODBC データソースを設定します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/b1b2a768-94f5-83ae-e83f-2f1e4bea3865.png)

kintone ODBC の設定を行います。

| 項目       |内容 |
|:-----------------|:------------------|
| Service Name     | 任意 |
| リモーティングDB 選択 | SQL Server を選択 |
| Data Source      | ドロップダウンで選択 | 
| Port         | 使用していないポートを選択 |

「OK」を押して、サービス設定します。その後「Save Changes」で設定を保存します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/de5449be-009e-cda8-67e6-4a967856d399.png)

##ユーザー登録
次に「Users」タブ→「Add」からユーザーを登録します。
ユーザー毎にFull アクセスか、Readonly か、None の権限を選択できます。
複数のODBC データソースがある場合には、データソース毎に権限の設定が可能です。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/beb586a2-70aa-a778-012c-2037d829e15e.png)

「OK」を押して、ユーザー登録を完了します。
その後「Save Changes」で設定を保存します。


SQL Broker の「Service」タブで「Start」ボタンを押して、サービスを起動します。サービスの左側の〇が緑色になれば、サービスは起動中です。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/c0435142-045c-9fc6-05b6-389382b24b11.png)

#SQL Server でのリンクサーバー設定
SQL Server Management Studio (SSMS) を開きます。
オブジェクトエクスプローラーの「サーバーオブジェクト」で「リンクサーバー」を右クリックして、「新しいリンクサーバー」をクリックします。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/df5d2817-a5ce-9404-18d8-4c70d651545c.png)

新しいリンクサーバーの設定画面の「全般」では、以下を入力します。

| 項目       |内容 |
|:-----------------|:------------------|
| サーバー種類     | その他データソース |
| プロバイダー | SQL Server Native _Client 11.0 |
| データソース  | この例の場合、localhost, 1433 を入力 | 
| カタログ        | ODBC DSN 名を入力 |

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/ec66f3ca-ea6c-7d3b-5890-d4fa49b4ea43.png)

また、「セキュリティ」では、「このセキュリティコンテキスト」を選択して、リモートログインにSQL Broker にユーザー設定したUser 名とPassword を入力します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/f6e4c2d5-ce8f-9ff5-fb32-ceb7acc3a166.png)

これで、リンクサーバーの設定は終わりですので、「OK」を押して設定を保存します。

#SSMS でデータを見てみる
SSMS のオブジェクトエクスプローラーのリンクサーバー下に「KINTONE」が作成され、「テーブル」下にkintone のデータがアプリ単位でテーブルが生成されます。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/1359cd7a-86c8-d5a6-81f3-5b69c7149618.png)

新しいクエリを選択し、kintone データを取得してみます。

```
SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]
```
このように、API のリクエストではなく通常のSQL 構文でデータを扱うことが可能になっています。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/bb89fcb6-34e9-2b61-fd28-9bf2e20b7fe7.png)

#Visual Studio からkintone リンクサーバーを使う
Visual Studio のサーバーエクスプローラーの「データ接続」を右クリックし、「接続の追加」をクリックして、データソースの選択画面を開きます。データソースの選択画面でデータソースを「Microsoft SQL Server 」に選択して、「続行」を押します。
 
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/fad7002c-8547-3372-f4bd-ffff86faa22f.png)

「接続の追加」画面で、以下を入力します。

| 項目       |内容 |
|:-----------------|:------------------|
| サーバー名     | SQL Server のサーバー名 |
| 認証 | 任意の認証方式 |
| データベース名の選択または入力  | ※master など | 

※リンクサーバーを直接データベース名として入力することはできないようです。なので上位のデータベース名で設定しておきます。

「テスト接続」をして大丈夫であれば、「OK」を押して設定を完了させます。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/2f98a89a-6871-3f3b-13b7-e10efd5ce83d.png)

「新しいクエリ」で、SQL でSELECT 文を書いてデータを取得してみます。
```
SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]
```

このように、通常のSQL Server のリンクサーバーと同じようにVisual Studio 内でkintone データを扱うことが可能になります。他のIDE でもSQL Server にアクセスする方法でkintone データにSQL でアクセスが可能になります。

複数データソースのJOIN などを行う場合には大変便利です。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/0a73cba8-4871-3043-f61c-077dbf792e9a.png)

#Power BI からkintone リンクサーバーを使う

データビジュアライゼーションツールのPower BI からSQL Server インターフェースでkintone リンクサーバーのデータを取得することが可能です。

「データを取得」から「SQL Server 」を選択します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/129722e5-942a-8b82-012c-586274c28b72.png)

| 項目       |内容 |
|:-----------------|:------------------|
| データベース    | master など（リンクサーバーは設定できない） |
| 詳細設定オプション | 展開してSQL 記述 |

```
SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]
```

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/dfadc417-a904-2060-ea84-04ebab8129a5.png)

kintone データがプレビューで確認できます。「読み込み」を押します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/88f79b9f-33bf-dece-bd30-732f5860976e.png)

あとは、通常のDB のテーブルデータと同じようにビジュアライズを作ります。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/6c651e90-0c15-2ca8-fc71-3b8784f4cf84.png)


#Tableau からkintone リンクサーバーを使う

同様に人気のあるデータビジュアライゼーションツールのTableau からもkintone リンクサーバーデータを使ってみましょう。

接続から「詳細」→「Microsoft SQL Server」を選択して、リンクサーバーを含むDB に設定します。「サインイン」で設定を完了します。

| 項目       |内容 |
|:-----------------|:------------------|
| サーバー     | SQL Server のサーバー名 |
| データベース| 入力不要　|
| 認証 | 任意の認証方式 |


![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/b904685b-d10d-0a59-94f5-feec6709e431.png)

データソース画面で、「データベース」からドロップダウンでmaster を選択。
リンクテーブルはテーブルとして表示されないようなので、直接「新しいカスタムSQL」でSQL を書きます。

```
SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]
```

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/6d511baf-21e8-eeec-de19-91a70cc50d53.png)

クエリを実行すると、データが取得されます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/cbd46806-2740-245f-9e5d-3d8a1fe4c780.png)

あとは、通常のSQL Server のテーブルデータと同じように、ビジュアライズが可能です。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/116843/65756d5c-7016-cdb3-c31e-3ca8cf5fe6a6.png)


以上、CData SQL Broker を使った、kintone リンクサーバーの設定方法と使い方でした。