Synapse Analytics の SQL on-demand(Serverless SQL Pool) と、Azure Storage の Query Acceleration では、Azure Storage 上のファイルに対して、SQL を実行することが可能です。
どちらもファイル対して、SQL を実行する機能ではありますが、これらの機能ではどのような違いがあるのか気になったので簡単にではありますが比較してみました。
ツールによりデータを取得するか、SDK からクエリを実行してデータを取得するかという、そもそものユースケースに違いがありますので、あまり比較しても意味はないかもしれませんが、ざっとした比較では次のようになるかと。
SQL on-demand | Query Acceleration | |
検索対象として利用可能な Azure ストレージ | Azure BLOB ストレージ Azure Data Lake Storage Gen2 |
Azure BLOB ストレージ Azure Data Lake Storage Gen2 (Synapse Link で Cosmos DB に接続可) |
クエリの実行方法 | TDS データは TDS で取得 |
SDK データは Stream オブジェクトで取得 |
サポートする SQL | 一般的な検索の SQL をサポート データソース間の JOIN が可能 |
限定的 な SQL をサポート 単一データソースによる検索 |
データのエクスポート | TDS を利用可能なツール CETAS |
Stream オブジェクトをコードで操作 |
検索対象のファイル | CSV JSON Parquet |
CSV JSON |
一つのクエリで検索可能なファイル | ディレクトリ ワイルドカード |
単一ファイル |
メタデータによる検索するファイルのフィルター | filename() 関数 filepath() 関数 |
メタデータ BLOB インデックスタグ |
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 エンジンが内部的には使用されているのかもしれませんね。
Contents
検索対象として利用可能な 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 ストレージで考えた場合にはどちらも機能に差はないかと思います。
クエリの実行方法
どちらも 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
次のファイルにアクセスすることができます。
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 インデックスタグ や、メタデータの設定を併用することで、検索するファイルのフィルターができるのではないでしょうか。