この記事について

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

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

Cosmos DB へクエリ実行

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

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

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

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

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

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

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

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

わかったこと

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

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

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

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

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

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

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

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

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

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

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

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

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

照合順序の変更

CREATE DATABASE synapse_demo_20201015
COLLATE Latin1_General_100_BIN2_UTF8;

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

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

DECLARE 変数は使えない

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

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

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