Synapse Link for Cosmos DB が Synapse SQL Serverless から使えるようになったので試した

期待していた Synapse Link for Cosmos DB ですが、これまで Spark でしか扱えなかったのでスルーしていましたが、やっと SQL on-demand や Synapse SQL Serverless と呼ばれるサーバーレスモデルから扱えるようになったので気になった部分を試しました。リージョンが限定されてるので注意。

Ignite 2020 の時に再度発表されて、その勢いで Synapse Analytcis Workspace を作ったのに対応していなかったという悲しみがありました。最後の方にこっそり数週間後みたいに書くのは止めてほしいと思います。

Synapse Link と SQL On Demand で Analytical Store 使うぞ！と思ったら "this functionality will become available in the next few weeks." と最後に書いてあって全俺が泣いた

— しばやん (@shibayan) 2020年9月23日

今回も例によって気になった部分だけなので、基本は Cosmos DB の Analytical Store を Synapse Link + Synapse SQL Serverless の組み合わせだけを試しています。

Synapse SQL Serverless はインスタンスの管理が必要なく、課金体系も処理した分だけという分かりやすいものになります。SQL や Spark のプールを用意する方法にもパフォーマンスやスケール面でのメリットはありますが、正直プールの管理とかはしたくないです。

Synapse Analytics Workspace と Synapse Link については世界のムッシュが既にまとめてくれているので、こっちを読んでおくと良いと思います。

Analytical Store を有効にした Cosmos DB を用意する

Synapse Link を使うためには、Synapse Link を有効化した Cosmos DB と Analytical Store を有効化した Container が必要になるので、ポチポチと Azure Portal から作成しました。

この辺りはドキュメントが比較的充実しているので、はまるポイントはないでしょう。

Container の作成よりも中に入れるデータの方が大変なので、適当に Data.gov をうろついて面白そうなデータを探してきました。今回は以下の SFO への航空機着陸データを Cosmos DB に投入してみました。

ドキュメントでは Covid-19 絡みのデータセットですが、飛行機が好きなのでこっちを使います。

何故か Azure Portal 上は Analytical Store を有効化したかの判別方法は、Analytical Storage TTL の設定が出てくるかぐらいしかない気がします。

データは適当に Cosmos DB SDK を使ってバルクで投入しました。Data Factory を使っても良かったのですが、リソースを作る方が面倒だったので C# で解決しています。

適当に 1 つを開いてみると、以下のような形式になっています。月毎に集計されたデータです。

面白そうなプロパティがいくつもあるので、これらに対して Synapse Link を使ってクエリを実行します。

Synapse Link を使ってクエリを実行

Synapse Analytics Workspace に Cosmos DB をデータソースとして追加することは出来ますが、今のところ追加したデータソースからサクッとクエリを各機能が提供されていないので、ADLS に保存された CSV / JSON を読み取るときのように OPENROWSET を直接書いてクエリを実行します。

アクセスキーの設定はなかなかに怠いので、RBAC + Managed Identity を使いたい気持ちになります。

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'account=shibayantest;database=AnalyticalTest;region=westus2;key=ACCESS_KEY',
       AirTraffic) AS documents

このクエリで Analytical Store に自動同期されたデータを対象に出来ます。とりあえずサンプル通り 10 件だけ取ってくるシンプルなクエリなので、Cosmos DB の SQL とあまり差を感じません。

実行すると Cosmos DB に保存されたデータからスキーマが自動的に認識されてデータが返ってきます。Query Acceleration もスキーマは自動認識だったので楽ですね。

Cosmos DB が内部で利用している _ts や _etag も含まれるのが少し邪魔だなとは思います。そして接続先は SQL on-demand になっているので、Pool を用意することなくクエリが実行できています。

次はもうちょっと複雑なクエリを書いてみました。2020 年を対象に航空機のメーカー毎に着陸した回数を集計するものですが、Cosmos DB の SQL では集計関数が不安定なので確実に書けないです。

SELECT
    AircraftManufacturer,
    SUM(LandingCount) AS TotalLandingCount
FROM OPENROWSET( 
       'CosmosDB',
       'account=shibayantest;database=AnalyticalTest;region=westus2;key=ACCESS_KEY',
       AirTraffic) AS documents
WHERE
    ActivityPeriod LIKE '2020%'
GROUP BY
    AircraftManufacturer
ORDER BY
    SUM(LandingCount) DESC

Synapse Link を使うと普通の T-SQL と同じ感覚で書けるので、集計が圧倒的に楽ですね。

このクエリも実行して、今度はチャートとしてレンダリングしてみました。Power BI を使わずともビジュアライズが簡単に出来るのは便利です。

同じように今度は国際線の月毎の合計着陸回数を 10 年間分集計してみます。元データセットには国内線と国際線が混ざっているので、フィルタリングしつつ月毎に集計をすれば良いです。

SELECT
    ActivityPeriod,
    SUM(LandingCount) AS TotalLandingCount
FROM OPENROWSET( 
       'CosmosDB',
       'account=shibayantest;database=AnalyticalTest;region=westus2;key=ACCESS_KEY',
       AirTraffic) as documents
WHERE
    ActivityPeriod > '201006' AND GeoSummary = 'International'
GROUP BY
    ActivityPeriod
ORDER BY
    ActivityPeriod

2020 年 6 月のデータまでが含まれているので、Covid-19 の影響を受けていることが可視化できました。

ここまで色々なクエリを投げて試してみましたが、Monitor からは Analytical Store へのリクエスト数やデータサイズなどを確認することは出来ませんでした。

課金に影響する部分なので、何らかの確認方法が欲しいですが Preview 中の制約っぽいです。

View を作成してからクエリを実行

正直なところ毎回 OPENROWSET を使うのは面倒だと思っていましたが、Power BI から利用する方法が書いてあるドキュメントには View を作成して使う方法が紹介されていたので、これを使ってみました。

View の作成のためにはまず Database から作成しないといけないので、以下のようなクエリを流して Database と同時に Synapse Link を使う View を作成しました。

CREATE DATABASE AirTrafficCosmosDB
GO

USE AirTrafficCosmosDB
GO

CREATE VIEW AirTraffic
AS  
SELECT *
FROM OPENROWSET( 
       'CosmosDB',
       'account=shibayantest;database=AnalyticalTest;region=westus2;key=ACCESS_KEY',
       AirTraffic) AS documents
GO

一度 View を作成して Database を master から作成したものに切り替えれば、後は簡単な FROM 指定でクエリを書けるようになります。接続文字列的なものがクエリに出てこないので分かりやすくなりました。

SELECT
    ActivityPeriod,
    GeoRegion,
    SUM(LandingCount) AS TotalLandingCount
FROM
    AirTraffic
GROUP BY
    ActivityPeriod,
    GeoRegion
ORDER BY
    ActivityPeriod DESC

当然ながら実行結果は OPENROWSET を直接使ったものと同じです。当たり前すぎて面白みはありません。

View では不要なカラムの除外も出来るので、基本は作成しておくのが良さそうだと感じました。

日本語が文字化けするのを直す

これまで使ってきたデータセットはアルファベットと数字しか出てこないため問題になりませんでしたが、Cosmos DB は UTF-8 な JSON を扱うストレージなので、当然ながら日本語などの非 ASCII 文字がデータに含まれている可能性があります。

試しに Hack Azure で使った適当なデータセットを使ってみると、日本語は見事に化けました。

これだから ASCII 圏はという感じがしますが、ドキュメントにも記載のあるようにデフォルトの照合順序が UTF-8 向けになっていないのが原因らしいので、指示に従って LATIN1_GENERAL_100_CI_AS_SC_UTF8 に変更しておきます。

If you see unexpected characters in your text like Mélade instead of Mélade then your database collation is not set to UTF8 collation. Change collation of the database to some UTF8 collation using some SQL statement like ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8.

Query Azure Cosmos DB data using SQL serverless in Azure Synapse Link (preview) - Azure Synapse Analytics | Microsoft Docs

ちなみに master に対しては実行できないので Database を作成してから実行する必要があります。以下のようなクエリを流して Database の照合順序を UTF-8 向けに変更しました。

CREATE DATABASE TodoItemCosmosDB
GO

USE TodoItemCosmosDB
GO

ALTER DATABASE TodoItemCosmosDB COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8
GO

これで再度クエリを実行すると、日本語が化けることなく返ってくるようになりました。

もっと日本語に適した照合順序はあると思いますが、基本は UTF-8 向けのものを選べば問題なさそうです。この辺りはきっとムッシュが書いてくれると思うので期待しています。

追記

安心と信頼のムッシュが書いてくれました。日本語を扱う前にはまずこちらを確認しましょう。

SQL の照合順序周りは難しいなと感じる日々です。全て UTF-8 にすると楽できる世界になって欲しい。

ネストされたデータの扱いは少しめんどくさい

非リレーショナルで JSON を採用した Cosmos DB では、1 つのドキュメントにオブジェクトがネストされていることが多々あります。というかデータモデリングの時に大体はネストする構造を選ぶはずです。

そのようなデータに対してクエリを投げると、ネストされた部分は JSON のまま返ってきました。これは Synapse Link だからという訳ではなく、JSON を読み込んだ時にも普通に発生するようです。

対応方法としては OPENROWSET 時に WITH を使ってスキーマと該当するプロパティのパスを指定したり、JSON_VALUE などの関数でパスを展開したりと色々な方法があるようです。

1,2 個のプロパティだけ必要な場合は JSON_VALUE、もっと多くのプロパティが必要なら WITH でスキーマ定義、ネストされた配列を使う場合は OPENJSON と CROSS APPLY というような使い分けになりそうです。

今回は 1 つのプロパティだけが欲しかったので JSON_VALUE でサクッと終わらせました。

SELECT TOP 10
    Title,
    Body,
    JSON_VALUE([User], '$.id') AS UserId
FROM OPENROWSET( 
       'CosmosDB',
       'account=shibayantest;database=AnalyticalTest;region=westus2;key=ACCESS_KEY',
       TodoItem) AS documents

実行すると JSON から指定したパスのプロパティだけが返ってくることが確認できます。

JSON なストレージから RDB へのマッピングが現実的にしんどいのは容易に想像がつくので、Synapse Link を使うことが分かっている場合にはデータモデリング時に考慮した方が良さそうです。

価格について

最後に気になる価格ですが、Cosmos DB の Analytical Store に関してはデータ容量と読み書きの 1 万操作ごとの金額が記載されていますが、現在はモニタリング方法が無いので金額を推定することすら困難です。

Synapse Analytics の SQL Serverless も現在は ADLS 向けの記載になっているので、Synapse Link での扱いは分からないままです。恐らくは処理した分だけの課金でしょうが単価は変わるかもしれません。

ADLS を使うよりは Synapse Link 経由で Cosmos DB を使う方が高くなるのは間違いないでしょうが、もうちょっと金額に関しては待つ必要がありますね。かなり使い勝手は良かったので期待しています。