Synapse Link for Cosmos DB を SQL Ondemand (Serverless SQL Pool) で操作する場合のメモを。
SQL Ondemand なのか、Serverless SQL Pool なのかがよくわからないので、どちらでもヒットするようにしています (遠い目)
ドキュメントとしては次の内容をベースとしています。
- Azure Synapse Analytics の SQL オンデマンド (プレビュー) のベスト プラクティス
- Azure Synapse Link for Azure Cosmos DB についてよく寄せられる質問
スキーマ推論によるスキーマの確認
Synapse Link で Cosmos DB のデータを検索した場合、自動的にスキーマ推論が行われ、Azure Cosmos DB から SQL 型へのマッピング のデータ型にマッピングされます。
スキーマ推論によってどのようなスキーマとして認識されるのかについては sp_describe_first_results_set で確認ができます。
EXEC sp_describe_first_result_set N'
SELECT
*
FROM
OPENROWSET (
''CosmosDB'',
N''<接続文字列>'',
<コンテナー名>
) AS t1
'
文字列データ型ついては、基本的に varchar(8000) としてマッピングが行われます。
WITH 句で列リストを設定することで、取得する項目とデータ型を明示的に設定することができます。
SELECT TOP 10
*
FROM
OPENROWSET (
'CosmosDB',
N'<接続文字列>',
<コンテナー名>
)
WITH (
id varchar(36),
time varchar(30),
_rid varchar(30),
_etag varchar(38),
_ts bigint,
clientIp varchar(100),
securityProtocol varchar(10),
userAgent varchar(8000),
httpVersion float,
httpStatusDetails varchar(3),
requestUri varchar(8000),
httpMethod varchar(10),
requestBytes bigint,
responseBytes bigint,
httpStatusCode varchar(3),
cacheStatus varchar(20)
) AS T
適切な長さのデータ型へのマッピングはパフォーマンスに影響を与えるという記述がありますので、文字列長が分かっているのであれば、適切な長さに変換するのがよさそうです。
日付型の検索
Cosmos の日付データは ISO 8601 形式の文字列 (YYY-MM-DDThh:mm:ss.fffffffZ) として認識が行われます。
残念ながら WITH 句で日付型に変換はできませんので、WHERE 句で CAST して検索を行う形になるかと思います。
SELECT TOP 10
*
FROM
OPENROWSET (
'CosmosDB',
N'<接続文字列>',
<コンテナー名>
)
WITH (
id varchar(36),
time varchar(30),
_rid varchar(30),
_etag varchar(38),
_ts bigint,
clientIp varchar(100),
securityProtocol varchar(10),
userAgent varchar(8000),
httpVersion float,
httpStatusDetails varchar(3),
requestUri varchar(8000),
httpMethod varchar(10),
requestBytes bigint,
responseBytes bigint,
httpStatusCode varchar(3),
cacheStatus varchar(20)
) AS T
WHERE CAST(time AS datetime2) <= '2020-03-23 09:00:00'
分析ストアについては、列指向のストアであり、範囲検索には対応していないのかなと思いますが、性能についてはちょっと気になりますね。
データ仮想化による複数データの活用
Synapse Link for Cosmos DB を使用すると Cosmos DB のデータを SQL でアクセスできるようになります。
これは、SQL Ondemand により、データ仮想化を行っていると考えることができます。
SQL Ondemand がアクセスできるデータソースとしては、次のようなものがあります。
- 共有メタデータにより Spark Pool と共有しているデータ
- Synapse Link for Cosmos DB により参照している分析ストア
- Azure BLOB Storage / Azure Data Lake Storage Gen2 に格納しているデータ
これらのデータを組み合わせて検索を行うことができます。
(このようなクエリを使用する場合は、ビューを使った方が可読性はよいと思いますが)
SELECT
*
FROM
(
SELECT
*
FROM
OPENROWSET (
'CosmosDB',
N'<接続文字列>',
<コンテナー名>
)
WITH (
id varchar(36),
time varchar(30),
_rid varchar(30),
_etag varchar(38),
_ts bigint,
clientIp varchar(100),
securityProtocol varchar(10),
userAgent varchar(8000),
httpVersion float,
httpStatusDetails varchar(3),
requestUri varchar(8000),
httpMethod varchar(10),
requestBytes bigint,
responseBytes bigint,
httpStatusCode varchar(3),
cacheStatus varchar(20)
) AS T
WHERE CAST(time AS datetime2) <= '2020-03-23 09:00:00'
) Cosmos
INNER JOIN
(
SELECT TOP 10
*
FROM
OPENROWSET(
BULK '/accesslog/result.txt',
FORMAT='csv',
PARSER_VERSION = '2.0',
DATA_SOURCE = '<外部データソース>'
) AS T
WHERE C6 = 'TLS 1.2'
) AS BLOB
ON blob.C5 = Cosmos.clientIp
このクエリは、Cosmos DB と BLOB ストレージのデータを組み合わせて検索を行ったものとなります。
SQL Ondemand を使用することで、このような異種データソースを組み合わせたクエリの実行もできます。
もちろん、同一のデータソースを組み合わせることもできますので、Cosmos DB の複数のコンテナー間で JOIN してクエリを実行というようなことも可能です。
Parquet File の活用
SQL Ondemand では、CREATE EXTERNAL TABLE AS SELECT (CETAS) をサポートしています。
これは、Synapse Link も同様で、次のようなクエリを実行できるということになります。
CREATE EXTERNAL TABLE CosmosResults
WITH(
LOCATION = '/results/cosmos',
DATA_SOURCE = <データソース名>,
FILE_FORMAT = Parquet
)
AS
SELECT
*
FROM OPENROWSET (
'CosmosDB',
N'<接続文字列>',
<コンテナー名>)
AS T
GO
これにより、SQL を実行することで、Azure Storage 上に Cosmos DB のデータをエクスポートすることができます。
分析ストアの RU については、ドキュメントに記載されており、トランザクションストアの RU には影響されずに検索を行うことができます。
Azure Cosmos DB トランザクション ストアにプロビジョニングされた RU に影響はありますか?
Azure Cosmos DB では、トランザクション ワークロードと分析ワークロード間のパフォーマンスの分離が保証されています。 コンテナーで分析ストアを有効にしても、Azure Cosmos DB トランザクション ストアにプロビジョニングされた RU には影響しません。 分析ストアのトランザクション (読み取りおよび書き込み) とストレージのコストは個別に課金されます。 詳細については、Azure Cosmos DB 分析ストアの価格に関するセクションを参照してください。
ただし、読み取りのトランザクションのコストがかかるようですので、頻繁に検索を行うような場合は、中間結果を Azure ストレージに保存しておくことで、リアルタイム性は低下しますが、コストや効率が良くなるケースがあるかもしれません。(料金計算していないですが。)
分析ストアも Azure ストレージ上に Parquet でデータを保存していそうな感じですので、任意の Azure ストレージにエクスポートした場合との検索性能の違いは気になりますが.
上記のクエリは単一のクエリの結果をエクスポートしていますが、これは JOIN をしたクエリ等を書くことももちろんできますので、異種データソース間を結合したクエリにより、外部テーブルにエクスポートを行うことで、複数回の検索を効率的に実施することも考えられるかと。
Spark / SQL Pool から Parquet のファイルを活用することも可能かと思います。
現状、SQL Pool からは、Cosmos DB のデータは直接参照できないかと思いますが、SQL Ondemand の CETAS で Cosmos DB のデータをエクスポートして、エクスポート先のストレージのコンテナーを SQL Pool から参照することで Cosmos DB のデータ参照を行うことも可能なのではないでしょうか。