Synapse Analytics のサーバーレス SQL プールのコスト対象となるデータ処理数の基本的な考え方 at SE の雑記

Synapse Analytics のサーバーレス SQL プールは、メタデータ以外のデータは保持せず、検索対象となるデータは外部ストレージ (サービス) が対象となります。

そのため、利用時のコストは「データ処理数」となり、クエリ実行時には少量データの処理に対しても、最低 10MB の料金がかかることになります。(DMV に対しての検索はコストは発生していないようではあります)

検証で使用している場合は、2023年6月30日までの無料枠である、1TB 以内に収まるかと思いますが、実運用で大量データを使用する場合、クエリでどの程度処理が行われるかを把握することは重要となります。

本投稿では、サーバーレス SQL プールのコスト対象となるデータ処理数の基本的な考え方についてまとめておきたいと思います。
今回の対象は Azure ストレージからの情報取得となっており、Synapse Link については調査できていません。

今回は手元の CSV ファイルを使用して検証をしましたが、検証のために、手元にある程度のボリュームのまとまったデータがない場合は、Azure Open Datasets で公開されているデータを使用してみてもよいかもしれません。

Contents

1 データ処理数の基本的な考え方
- 1.1 Total size of data scanned
- 1.2 Total size of data moved
2 データ処理量の確認方法
3 データ処理量の制限
4 まとめ

データ処理数の基本的な考え方

基本的なコストの考え方については、Azure Synapse Analytics でのサーバーレス SQL プールのコスト管理に記載されています。

データ処理数については次の 3 種類が対象となります。(このコストはサーバーレス SQL プールのみの話で、外部サービスにアクセスする際には、外部サービス側のコストも発生します)

ストレージから読み取られたデータの量
中間結果におけるデータの量
ストレージに書き込まれたデータの量

サーバーレス SQL プールに対して SSMS 等でクエリを実行した場合、メッセージに次のような出力があります。

Statement ID: {A3AA1C09-A0D5-42B6-99A8-D84917AF0085} | 
Query hash: 0x41FFE8849257F11A | 
Distributed request ID: {35B718DE-AEB8-4F85-BF50-51722E3137B4}. 

Total size of data scanned is 306 megabytes, 
total size of data moved is 19 megabytes, 
total size of data written is 0 megabytes.

この結果が上述の 3 種類に対応しています。

ストレージから読み取られたデータの量: Total size of data scanned
中間結果におけるデータの量: total size of data moved
ストレージに書き込まれたデータの量: total size of data written

検索については「1.」「2.」が発生し、CETAS のような書き込みを行った場合は、「3.」も追加で処理が発生します。

この 1～3 の処理サイズの合計がコストにつながるコストとなり、最小のデータ操作でも 10MB の処理量が計上されることになります。

検索の処理対象となる「Total size of data scanned」「Total size of data moved」については、処理効率化に対していくつか意識しておくポイントがあるようです。

Total size of data scanned

この処理量は、ストレージからの読み取られたデータ量となります。Azure ストレージからデータを読み込む場合は、

OPENROWSET でディレクトリを指定する際に、ワイルドカードを使用する箇所をできるだけ絞り込む
OPENROWSET で絞り込めない場合は、ファイルのメタデータ (filepath / filename) を使用して絞り込む

というようなことを意識し、処理対象となるファイルをできるだけ少なくすることでコストを抑えることができます。これらの設定を調整して Total size of data scanned がどのような変化をするかを確認することで、処理対象となるファイルが絞り込めているかを把握することができます。

OPENROWSET でワイルドカードを指定し、ファイルのメタデータでさらに絞り込むということもできますが、ファイルのメタデータの読み込みの影響を抑えるため、OPENROWSET のワイルドカード指定を使用する範囲もできるだけ絞り込めておけるとよいようです。(ストレージのディレクトリ構造は OPENROWSET のワイルドカード指定を意識した階層とする)

実動作の検証はできていないのですが、Parquet ファイルに対しての検索については、文字列データ型の検索については、varchar / Latin1_General_100_BIN2_UTF8 を使用している場合、フィルター述語のプッシュダウンにより、検索対象となるファイルを除外することができるようですので、Parquet ファイルに対しての文字列検索は特定の照合順序の利用を意識しておく津陽があるようです。

外部ストレージに対しての処理は「ファイル単位の処理」が基本となりますので、配置するディレクトリとファイルの関係 / ファイルのメタデータで絞り込むことができるかというのは意識しておく必要があるのではないでしょうか。

CSV / JSON についてはファイルサイズが大きくなる傾向があり、処理データ量が増加しますので、頻繁に検索をする情報は、出来るだけ Parquet ファイルに変換することも重要ではないでしょうか。

Total size of data moved

これは、最終的にクライアントに返すデータの生成に影響するものとなりますが、「不要な列をクエリに含めない」ことが重要となります。

クエリには必要な列のみを含めるようにすることで、このデータ処理量を抑えることができますので、検証時以外は、「SELECT *」は使用せず、必要な列リストのみを指定するようにします

データ処理量の確認方法

データ処理量の確認方法についてはいくつかの確認方法があります。

Azure Synapse Studio (Synapse Workspace)

Synapse Studio にアクセスし、「Monitor」->「SQL Requests」からクエリ単位の処理量を確認することができます。

「処理済みデータ」に関しては上述の 1～3 までが合算されているようでした。

「Manage」->「SQL pools」のコスト管理から、合算されているものが確認でき、後述のデータ処理量の制限もこの画面から設定することができます。

T-SQL

データ処理量は T-SQL でも確認することができ T-SQL におけるサーバーレス SQL プールのコスト管理の構成に記載されています。

サーバーレス SQL プールでは、「sys.dm_external_data_processed」という DMV が提供されており、この DMV を検索することで、日 / 週 / 月の処理量を確認することができます。

このデータのもとは、「sys.dm_exec_requests_history」という DMV となっているような気がしています。

この DMV を検索すると、Synapse Studio で確認した SQL Request 相当の情報を取得することができます。

前述した「1～3」の各処理量はわからず、合算されたものが data_processed_mb となっているようで、「1～3」を個別に確認したい場合は、SSMS 等でクエリを実行し、メッセージを確認する必要があるようですね。

サーバーレス SQL プールは使用できる DMV が制限されており、現状は次のような DMV が使用できるようです。

sys.dm_database_encryption_keys
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_hadr_cluster
sys.dm_hadr_database_replica_states
sys.dm_os_host_info
sys.dm_exec_requests_history
sys.dm_external_data_processed
sys.dm_request_phases
sys.dm_request_phases_exec_task_stats
sys.dm_request_phases_task_group_stats

サーバーレス SQL プールへのクエリ実行については SQL Server ベースの環境の情報取得の定番である「sys.dm_exec_*」以外に「sys.dm_exec_requests_*」というものが提供されています。

クエリの詳細情報を取得する場合、前述の「sys.dm_exec_requests_history」以外に、「sys.dm_request_phases」「sys.dm_request_phases_exec_task_stats」「sys.dm_request_phases_task_group_stats」を確認することも有益なようですので、クエリの調査を行う場合は、サーバーレス SQL プール固有の DMV の存在も覚えておくとよいかと。

Azure Monitor

Azure Monitor でも Synapse Analytics 用のメトリックが提供されています。

サーバーレス SQL プールのデータ処理量については、「BuiltinSqlPoolDataProcessedBytes」で収集されていますので、Azure Monitor からデータの取得をできる方法であれば次のように情報を確認することができます。

データ処理量の制限

BigQuery であれば「–dry_run」フラグがあり、実際の実行前に処理量を見積もることができますが、サーバーレス SQL プールには見積もるための方法はないかと思います。

大量のデータ検索によりコストが膨大になることを抑えるためには T-SQL におけるサーバーレス SQL プールのコスト管理の構成に記載されている sp_set_data_processed_limit を使用した daily / weekly / monthly で TB 単位で処理量の制限を行う方法が現時点での対応方法となるのではないでしょうか。

こちらの制限は「制限を超えた以降に実行されたクエリ」に対してかかるものとなるため、「実行中のクエリで大量のデータにアクセスされた場合」については制限することができないため、制限を超える要因になったクエリを強制的に終了するということはできないようです。(処理量に計上されるのがクエリが終了したタイミングだからなのかもしれませんね)

サーバーレス SQL プールでも KILL コマンドは使用することができるので、長時間実行されているクエリがある場合には KILL で強制的に終了させるような仕組みの実装を検討する必要があるかもしれません。

まとめ

サーバーレス SQL プールは、データは外部サービスに格納されているものを利用するという特性上、「データ処理量」がコストに大きく影響します。

クエリ単位のデータ処理量 / 現在のデータ処理量を確認するための方法がいくつか提供されていますので、自分が実行したクエリでどの程度処理が行われているかを意識することが重要ではないでしょうか。

SE の雑記