SE の雑記

Synapse Analytics の SQL on-demand(Serverless SQL Pool) と、Azure Storage の Query Acceleration では、Azure Storage 上のファイルに対して、SQL を実行することが可能です。

どちらもファイル対して、SQL を実行する機能ではありますが、これらの機能ではどのような違いがあるのか気になったので簡単にではありますが比較してみました。

ツールによりデータを取得するか、SDK からクエリを実行してデータを取得するかという、そもそものユースケースに違いがありますので、あまり比較しても意味はないかもしれませんが、ざっとした比較では次のようになるかと。

Ignite 2020 の What’s New in Azure Storage では、Query Acceleration に関しては、次のような解説が行われていました。

「Deeply integrated into Azure Synapse Analytics for improved performance and cost」と説明がされています。
具体的にどのように Synaspe Anaytics と統合されているのかまでは解説されていないのですが、Synapse Analytics で使用されている Polaris という分散 SQL エンジンが内部的には使用されているのかもしれませんね。

検索対象として利用可能な Azure ストレージ

どちらも、検索対象としては、次の 2 種類が利用できます。

• Azure BLOB ストレージ
• Azure Data Lake Storage Gen2 (ADL Gen2)

SQL on-demandでアクセス可能なストレージについては、サポートされているストレージと承認の種類 に次のように記載されています。

Query Acceleration については、概要 で文言で記載されています。

クエリ アクセラレーション機能は、Data Lake Storage (階層型名前空間が有効になっているストレージ アカウント) に限定されません。 クエリ アクセラレーションは、階層型名前空間が有効になっていないストレージ アカウントの BLOB と完全に互換性があります。 これはつまり、ストレージ アカウントに BLOB として既に格納されているデータを処理する場合でも、ネットワーク待機時間とコンピューティング コストを同様に削減できます。

どちらも BLOB ストレージと、ADL Gen2 をサポートしていますので、現在の Azure で一般的に使用されている BLOB ストレージであれば、どちらの機能を使用してもアクセスすることが可能です。

SQL on-demand の場合は、追加で次のデータソースにもアクセスすることができますが、Azure ストレージで考えた場合にはどちらも機能に差はないかと思います。

• Synapse Link for Cosmos DB
• 共有メタデータによる Spark で作成したテーブルの参照

クエリの実行方法

どちらも SQL を実行することによる検索をサポートしていますが、クエリの実行方法は異なります。

SQL on-demand

プロトコルとしては TDS を使用していますので、SQL Server に接続可能なツールであれば、クエリを実行することが可能です。

そのため、SSMS や、Power BI から接続して、SQL によりデータの取得を行うことが可能です。

Query Acceleration

SDK 経由でアクセスすることになるかと。

データについては Stream オブジェクトとして取得することになりますので、外部の分析ツールからのアクセスではなく、プログラムからクエリを実行してアクセスを行うのが基本的な方法となるのではないでしょうか。

データのエクスポート

取得したデータを外部連携のためにエクスポートするための方法はどうなるでしょうか。

SQL on-demand

TDS でアクセスが可能ですので、SQL Server の接続方法をサポートしているツールからであればアクセスが可能です。
bcp でデータをエクスポートすることもできますし、CETAS (CREATE EXTERNAL TABLE AS SELECT) を使用して外部テーブルとしてエクスポートすることも可能です。

Query Acceleration

Stream オブジェクトとして取得したデータをコード内で操作する必要があります。
PowerShell の場合は、Result ファイルとして結果を取得することになりますので、Stream オブジェクトをそのまま取り扱わなくても、データをエクスポートできます。(-ResultFile オプションが必須なので、結果はファイルとしてしか出力できないようなのですが)

サポートする SQL

SQL on-demand

一般的な SQL の構文をサポートしていますので、GROUP BY 等も柔軟に使用することが可能です。

SELECT 
    T.filename(),
    COUNT(*)
FROM 
    OPENROWSET(
        BULK '/tpch/lineitem/', 
        FORMAT='CSV', 
        PARSER_VERSION = '2.0', 
        FIELDTERMINATOR ='|',
        DATA_SOURCE = 'adfteststorage'
    ) AS T
GROUP BY 
    T.filename()

また、SQL on-demandでアクセス可能なデータソース間で JOIN することも可能ですので、SQL Server ライクなクエリを記述することはできるかと。

Query Acceleration

クエリ アクセラレーション SQL 言語リファレンス で利用可能な SQL について記載されています。

限定的なサポートとなっており、GROUP BY などはサポートしていませんので、集計については限定的な対応となります。

集計式 (MAX 等) については数字型のみのサポートになっていそうで、文字列型に MAX を実行してみたところ、「Not a numeric type.」になってしまいました。

検索対象のファイル

Azure ストレージについては機能差はありませんでしたが、アクセスできるファイルはどうでしょうか？

SQL on-demand

次のファイルにアクセスすることができます。

• CSV
• JSON
• Parquet

Query Acceleration

概要 に記載されている、次のファイルにアクセスすることができます。

• CSV
• JSON

クエリ アクセラレーションでは、各要求への入力として CSV および JSON 形式のデータがサポートされます。

SQL Ondemad では、Parquet がサポートされていますが、Query Acceleration では、サポートされていないようです。

Synapse の場合は、SQL Pool / Spark Pool / Pipeline で Parquet ファイルを活用することが多々あるかと思いますが、Query Acceleration のユースケースで Parquet ファイルがサポートされていないことによる影響はあまりないかもしれないですね。

一つのクエリで検索可能なファイル

SQL on-demand

フォルダーや複数のファイルに対しての検索 がサポートされていますので、次のようにディレクトリ配下のファイルのアクセスを単一のクエリで行うことや、一部をワイルドカードで検索することができます。

SELECT 
    COUNT(* )
FROM 
    OPENROWSET(
        BULK '/tpch/lineitem/', 
        FORMAT='CSV', 
        PARSER_VERSION = '2.0', 
        FIELDTERMINATOR ='|',
        DATA_SOURCE = 'adfteststorage'
    ) AS T

Query Acceleration

ワイルドカードの指定ができないため、どのファイルを検索対象とするかの指定が必要になるかと思います。

$ctx = New-AzStorageContext -ConnectionString ""

$container = "tpch"
$blob = "lineitem/lineitem.tbl.1"
$query = "SELECT COUNT(*) FROM BlobStorage "
Get-AzStorageBlobQueryResult -Context $ctx -Container $container -blob $blob -QueryString $query -ResultFile C:\temp\results.txt -Force
Get-Content C:\temp\results.txt 

複数のファイルを横断的に検索するのは、SQL on-demandのほうが得意そうですね。

メタデータによる検索するファイルのフィルター

コンテナーに複数のファイルが格納されている場合に、特定のメタデータの情報を持つファイルを検索したい場合の方法はどのようになるでしょうか。

SQL on-demand

ファイルのメタデータにアクセスする という機能が提供されています。

特定のパス配下や、ワイルドーカード指定のほかに、ファイル名とファイルパスを条件に対象を絞り込むこともできます。

SELECT TOP 10 
    T.filename(),
    T.filepath(),
    *
FROM 
    OPENROWSET(
        BULK '/tpch/lineitem/', 
        FORMAT='CSV', 
        PARSER_VERSION = '2.0', 
        FIELDTERMINATOR ='|',
        DATA_SOURCE = 'adfteststorage'
    ) AS T
WHERE
    T.filename() = 'lineitem.tbl.4'

コンテナー / ディレクトリの命名規則を定めておけば、メタデータによるフィルタリングは不要かもしれませんが、ファイル名 / パスをメタデータとして使用することもd系ます。

Query Acceleration

Query Acceleration の機能ではなく、BLOB インデックスタグ や、メタデータの設定を併用することで、検索するファイルのフィルターができるのではないでしょうか。