Azure Synapse Link for Azure Cosmos DB に T-SQL 対応が来たので早速試してみる

# この記事について

Microsoft Ignite 2020 にてアップデート案内があった、[T-SQL queries and real-time BI dashboards with Azure Synapse Link][1] で、T-SQL での Azure Synapse Link 対応がパブリックプレビューに入ったので試してみます。

- アナウンス: [Azure Synapse Link for Azure Cosmos DB: SQL serverless runtime support in preview][4]
- 公式ブログ: [Ignite 2020: New serverless and analytics capabilities announced][1]
- 日本語解説: [Microsoft Ignite 2020 で発表された Azure Cosmos DB 関連のアップデート][2]


# Cosmos DB へクエリ実行

Azure Synapse ワークスペースにログインします。
Azure Synapse ワークスペースの作成については、以下の記事を参照してください。

- [2020年から始めるAzure Cosmos DB - Azure Synapse Link を試してみる][3]

[Develop] 画面より、[SQL script] を選択します。

![スクリーンショット 2020-10-15 3.57.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/5c0f9512-8280-5314-ea01-b98237b4f8ba.png)

[Connect to] で **SQL on-demand** が選択されていることを確認します。
[use database] は master のままで OK です。

![スクリーンショット 2020-10-15 4.05.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/b64b249d-86fb-cbd8-b4e1-44bec6dc410f.png)

Cosmos DB はすでに作成してあるものを使用します。
Azure Synapse Link、および分析ストアは有効化してコンテナーを作成してください。

```sql
SELECT *
FROM OPENROWSET( 
  N'CosmosDB',
  N'account=<Cosmos DB アカウント名>;database=<Cosmos DB データベース名>;region=<リージョン名>;key=<プライマリまたはセカンダリのパスワード>',
  <Azure Synapse Link が有効化され、分析ストアが存在しているコンテナー名>) as XXX
```

regionですが、West US 2 の場合は westus2 と言った形で、小文字の方を使用します。
実際に取得してみた例がこちらです。

![スクリーンショット 2020-10-15 5.03.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/bb2fe088-45a5-23de-a5e6-ee6fd660affe.png)

Cosmos DB の分析ストアに対してクエリが発行され、データが取得できていることがわかります。
分析ストアを有効にしていない場合は、以下のようなエラーが出ます。データベース名やコンテナー名の誤りは別として、`or analytical storage has not been enabled.`というように、分析ストアへアクセスしようとしているのがわかります。

![スクリーンショット 2020-10-15 5.05.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/7c358f40-8fa3-7d68-4bc1-94d7f389d05f.png)


# わかったこと

# カラム名はきちんと認識する

Cosmos DB 分析ストアにデータが ETL された際、スキーマ管理でカラム名がきちんと付与されているので、SELECT 句内できちんと識別できているようです。

![スクリーンショット 2020-10-15 5.31.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/bdf523cc-7c75-b853-d2a8-96f27c630ca3.png)

## 階層化されたデータだと扱いが面倒

Cosmos DB 側でデータが階層化されている場合、Synapse Link 経由で取得したデータは、2 階層以降のデータが JSON 文字列で返っています。

![スクリーンショット 2020-10-15 6.29.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/de13b9ba-9ee9-c3c1-4903-6c5afd85d0d0.png)

Cosmos DB のデータはこんな感じです。

![スクリーンショット 2020-10-15 6.27.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/4bbde5c8-0013-636b-950a-7de1d8a8cdba.png)

NoSQL だからこそ、RDB とは違ったデータモデリングを求められ、JSON の階層構造には意識を向けているところだと思います。
Synapse Link で T-SQL 使えるようになったのは良いですが、このそのまま JSON 返ってきちゃう部分は注意しないとだめですね。

## SQL プールの照合順序を変更しないと日本語データは文字化けする

先ほどの画像を見て分かる通り、Cosmos DB コンテナー内にある日本語の文字データが文字化けしています。

![スクリーンショット 2020-10-15 5.08.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/f49ced6b-c243-64e3-67c8-f160f0aed343.png)

~~Azure Synapse ワークスペースを West US 2 で作成しているから、SQL プール側で、いわゆる SQL Server の照合順序的なものが悪さをしているのでしょうかね。。気になるところです。~~
~~Spark の時は、問題なく取れたんですけどね。。フィードバックしておきました。~~

原因は、SQL プール (オンデマンド) の照合順序でした。Microsoft Docs にもきちんと記載がありました。

- [Azure Synapse Link (プレビュー) で SQL オンデマンドを使用して Azure Cosmos DB のデータのクエリを実行する][doc-5]

Synapse ワークスペースを作成した際、私の環境の master データベース (サーバーの)デフォルトの照合順序は *SQL_Latin1_General_CP1_CI_AS* になっていました。

![スクリーンショット 2020-10-15 17.20.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/58be2f7e-100d-bcee-3de9-4d07299ed453.png)

これでは日本語が文字化けしてしまいます。そのため、master ではない別のデータベースを作成し、照合順序を UTF8 に対応したものに設定して、再度実行してみます。

```sql:照合順序の変更
CREATE DATABASE synapse_demo_20201015
COLLATE Latin1_General_100_BIN2_UTF8;
```

これで作ったデータベースを使用して、再度実行すると、日本語も問題なく取得できるようになります。

![スクリーンショット 2020-10-15 17.41.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/84258dc3-e017-642c-ef67-60a8269ae037.png)

![スクリーンショット 2020-10-15 17.36.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/2508b21d-553d-2fca-b024-fb44ab78a18a.png)

なお。SQL プールの照合順序についての記載はこちら。

- [Synapse SQL でのデータベースの照合順序のサポート][doc-3]
- [Azure Synapse Analytics SQL プールのデータベースの照合順序のサポート][doc-4]


## DECLARE 変数は使えない

Cosmos DB の接続文字列など、正直クエリに直接埋め込みたくはない気持ちがあります。
(もしかしたら私がミスしているかもしれませんが) DECLARE で変数化して OPENROWSET 使えるかなーと思って試してみましたが、やはりダメでした。
Microsoft Docs のように、クエリ本文にベタ書きするしか無いようです。
ここは、Spark 側では Linked Services に登録することで、接続文字列などを秘匿化できていたのもあり、少し残念ポイントです。

![スクリーンショット 2020-10-15 5.24.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348116/ce8dcc46-ab4d-a17c-6536-a4ea02c0bf91.png)


引き続き、検証してわかったことはこちらに追記していく予定です。


# 参考資料

- [Azure Synapse Link (プレビュー) で SQL オンデマンドを使用して Azure Cosmos DB のデータのクエリを実行する][doc-1]
- [Power BI および Synapse SQL サーバーレスを使用して Synapse Link で Azure Cosmos DB を分析する (プレビュー)][doc-2]


---
[1]:https://devblogs.microsoft.com/cosmosdb/ignite-2020-new-serverless-and-analytics-capabilities-announced/#t-sql-queries-and-real-time-bi-dashboards-with-azure-synapse-link
[2]:https://mappie-kochi.hatenablog.jp/entry/2020/09/23/021641
[3]:https://qiita.com/ymasaoka/items/3caac5045f0e5349504f
[4]:https://azure.microsoft.com/en-us/updates/azure-synapse-link-for-azure-cosmos-db-sql-serverless-runtime-support-in-preview/
[5]:https://qiita.com/ymasaoka/items/48539b8994b357fba0be

[doc-1]:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?toc=/azure/cosmos-db/toc.json&bc=/azure/cosmos-db/breadcrumb/toc.json
[doc-2]:https://docs.microsoft.com/ja-jp/azure/cosmos-db/synapse-link-power-bi
[doc-3]:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql/reference-collation-types
[doc-4]:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-reference-collation-types
[doc-5]:https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql/query-cosmos-db-analytical-store