SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Synapse Analytics の SQL on-demand の DB を跨いだクエリの実行について

without comments

Synapse Analytics (Workspace) の SQL on-demand (Serverless SQL Pool) では、次の形態の DB を利用することができます。

  • SQL on-demand 用のデータベース
    • OPENROWSET により、Data Lake / Synapse Link のデータを検索
    • SQL on-demand のメタデータオブジェクトを格納するためのデータベース
  • 共有データベース
    • Spark Pool と SQL on-demand でテーブルを共有するためのデータベース

従来の、Azure SQL Database では「論理サーバー」を使用した環境の場合、サーバーはエンドポイントとなっていました。
エンドポイント内に複数のデータベースを作成することはできるのですが、各データベースは独立したリソースで動作しているため、データベースを跨いだクエリの実行にはかなり制限がありました。

SQL on-demand の場合、Azure SQL Database の論理サーバーモデルとは異なり、データベースを跨いだクエリの実行ができるようです。

Azure Synapse SQL でサポートされる Transact-SQL 機能 で、サポートされる T-SQL の機能が記載されているのですが、SQL on-demand では、データベース間のクエリはサポートとなっています。

image

 

データベースを跨いだクエリの実行

検証してみたところ、次のようなクエリを実行することができました。

image

このクエリは 2 つのテーブルを結合 (JOIN) しています。

現在のデータベースは「SynapseLink」というデータベースになっており、このデータベースは UTF8 の照合順序 (Synapse Link で Cosmos DB の文字列型を参照する際に、文字化けをさせずに表示させるために必須となる照合順序) を設定しているデータベースとなっています。

このデータベースで OPENROWSET を実行して、Synapse Link でCosmos DB のデータの参照を行っています。
このベースクエリに対して INNER JOIN を行っているのは「sparkdb」という異なるデータベースの「parquetdata」というテーブルとなります。

従来の Azure SQL Database ではテーブル名に「3 パート名 (データベース名.スキーマ名.テーブル名」は指定することができず、別の データベースのデータについては、Elastic Query のような機能を使用して、外部テーブルとして参照を行う必要がありました。

Synapse の SQL on-demand については、Elastic Query を使用することはできないのですが、SQL on-demand 内の他のデータベースのテーブルについては 3 パート名の形式で直接参照を行うことができるようです。

INNER JOIN で参照している「sparkdb.dbo.parquetdata」は共有メタデータ テーブルを使用しています。つまり、Synapse の Spark Pool で作成したテーブルです。

このクエリは「Cosmos DB のコンテナー」と「Spark SQL のテーブル」を JOIN して実行しているということになります。(Spark Pool と共有メタデータ テーブルとして参照を行うためのデータベースについては、「Latin1_General_100_BIN2_UTF8」の照合順序が使用されていますので、JOIN をする際には、照合順序の互換性維持のために、明示的な照合順序の指定について確認をしておく必要があります)

SQL on-demand はデータベースを跨いだクエリが実行できていますので、このように Synapse Analytics の様々な機能で参照可能なテーブルを組み合わせてクエリを実行することができます。

同一データベース内で認識できるデータの活用

「様々な機能で参照可能なテーブルを組み合わせたクエリの実行」は「データベースを跨いだクエリ」に限定する話ではなりません。

  • 複数の Cosmos DB のコンテナー間のデータを JOIN してクエリを実行
  • Cosmos DB と Data Lake 内の CSV データを JOIN してクエリを実行

というように同一の DB についても複数の種別のデータ (異種データ) を組み合わせて単一のクエリにより分析を行うことが可能です。

SQL Server から SQL on-demand のデータの活用

SQL on-demand の接続には、「SQL on-demand Endpoint」(~.sqlazuresynapse.net) を使用して接続を行いますが、個のエンドポイントは SQL Server の TDS によるデータアクセスを行うためのインタフェースとなります。

つまり、SQL Server への接続をサポートしている様々な機能で、SQL on-demand に対してクエリを実行できるということになります。

SQL on-demand に対しての接続ですが、「SQL Server のリンクサーバー」としても接続が行えるようです。
image

SQL on-demand に、ビューを作成して、それを 4 パート名 (リンクサーバー名.データベース名.スキーマ名.オブジェクト名) で参照することや、

SELECT 
    *
FROM
    [<リンクサーバー名>].[SynapseLink].[dbo].[v_cosmos]

 

OPENQUERY を使用して参照すること、

SELECT
    *
FROM
    OPENQUERY([<リンクサーバー名>], 'SELECT top 100 
    accesslog.*
FROM OPENROWSET(
    ''CosmosDB'',
    ''<接続文字列>'',
    accesslog
) AS accesslog
')

 

などができるようです。

リンクサーバー経由のクエリは、同時実行性や実行効率の制御が難しいケースがありますが、SQL on-demand が SQL Server の接続インタフェースを持っていることで、このようにリモートの SQL Server のようなデータ参照ができるようになりますので、TDS が利用できることでデー活用の幅が広がるケースがあるのではないでしょうか。

Written by Masayuki.Ozawa

11月 3rd, 2020 at 10:27 pm

Posted in Synapse Analytics

Tagged with

Leave a Reply