Synapse Analytics Serverless SQL PoolでDWHに不可欠な"分散"、"パーティション"の作用を実践する

はじめに

各社のDWH製品で、「データが"分散"されることで、並列処理による大規模クエリを実現します」
という文言を聞いたことがないでしょうか？

この文言通り大規模な分析クエリは並列処理により実現されるわけですが、並列処理にはそのためのデータの持ち方として「分散」という考え方や、不要なデータをスキャンしないための「パーティション」という機能が存在します。

今回は、データレイク上で実際にファイルの保持方法を変えることでこれらの考え方を反映し、パフォーマンスの違いを検証します。
Synapse Analytics Serverless SQL Poolを利用すると、データレイク上のファイルに対するクエリ発行とスキャンデータ量の把握ができるのでこれを利用して疑似的に分散とパーティションの効果を見ていきます。

前提

データについて

今回のために生成した以下のテーブルを利用します。

• データ量：10億件
• フォーマット：Parquet（snappy圧縮）
• テーブル定義:
  • 製品コード:100種
  • 年:2000～2020年（均等に割り振り）
  • 売上区分:a,b,c,dの4種ありますが、ほとんどが、区分=aのデータになります
  • 売上数量:適当な整数値

検証シナリオ

ファイルの配置パターン

ケース1 単一ファイル（分散、パーティションをしない）

まずは、すべてのデータが分割されず、単一の巨大なファイルだとパフォーマンスはどうなるでしょうか？
従来のRDBMS(Shared Everything)はこの方式でデータを持つ場合が多かったとお考え下さい。

準備したファイルは以下のような状態です。
4.7GBのファイルが一つだけあります。

また、クエリするためのServerless SQL PoolでのView定義は以下の通り。

sql

CREATE VIEW single_sales
as
SELECT
     *
FROM
    OPENROWSET(
        BULK 'https://<ストレージ名>.dfs.core.windows.net/xxx/sales/single/<ファイル名>',
        FORMAT='PARQUET'
    ) AS [result]

ケース2 自動分割ファイル（疑似的に分散をする）

ファイルを単一じゃなく、適当なサイズで分割して配置します。
DWH上では、データがこのように物理分割されて分散されているケースが多いです。(Shared Nothingなど)
なるべく均等に分散されることが重要ですが、分析クエリに頻出する結合処理時に、同じ結合キー項目をもつデータは同じディスク上に存在するように分散してあげると無駄なデータ移動（シャッフル）がおさえられてパフォーマンスがあがります。※少し乱暴な表現かもしれません

分散の機能名：

• Azure Synapse Analytics 専用SQL Poolの分散テーブル
• AWS Redshiftの分散スタイル
• Snowflakeのマイクロパーティション

※Azure以外の製品は私見なので参考まで。厳密には違うという見方もあると思いますが、似た特性を示す機能を記載しています。

準備したファイルは以下のような状態です。
およそ100MBのファイルが49個となっています。

Serverless SQL PoolでのView定義は以下の通り。

sql

CREATE VIEW autodist_sales
as
SELECT
     *
FROM
    OPENROWSET(
        BULK 'https://<ストレージ名>.dfs.core.windows.net/xxx/sales/autodist/*.parquet',
        FORMAT='PARQUET'
    ) AS [result]

ケース3 年で分割したファイル（パーティションを設定する）

DWH上でパーティションを設定する際、一般的によく時間軸でパーティションを設定します。
たとえば、直近の1年しかデータが必要ない場合には、2年以上前のデータを保持したパーティションにはアクセスが不要となりますが、クエリする際に不要なパーティションをスキップする（パーティションプルーニング）ことで最適化をはかる機能があります。

パーティションプルーニングのためのパーティション機能名：

• Azure Synapse Analytics 専用SQL Poolの論理パーティション
• AWS Redshiftのソートキー
• Snowflakeのクラスタリングキー

※Azure以外の製品は私見なので参考まで。厳密には違うという見方もあると思いますが、似た特性を示す機能を記載しています。

準備したファイルは以下のような状態です。
年で21個のフォルダに区切られて、各フォルダにはおよそ234MBのファイルが配置されています。

フォルダの中

Serverless SQL PoolでのView定義は以下の通り。
fillpath()関数を使うことで*部分を項目で持つことができます。
対象の項目はクエリ時に指定することでパーティションプルーニングを実施できます。

Filepath

Delta Lake(Serverlessでの利用はPreview)の場合はfilepath関数は不要です
パーティション分割されたデータに対してクエリを実行する

sql

CREATE VIEW yeardist_sales
as
SELECT 
     [id]
    ,[product_code]
    ,CAST( [result].filepath(1) AS int) as [year]
    ,[sales_quantity]
    ,[sales_class]
FROM
    OPENROWSET(
        BULK 'https://<ストレージ名>.dfs.core.windows.net/xxx/sales/yeardist/*/*.parquet',
        FORMAT='PARQUET'
    ) AS [result]

検証クエリ

1. 全年度の集計

SQLは以下の通り。
計測前に同一のクエリを流して統計情報を作成したうえで、DBCC DROPCLEANBUFFERSしてから計測します。

sql

DBCC DROPCLEANBUFFERS;
SELECT
    [product_code]
    ,[sales_class]
    ,SUM([sales_quantity])sales_quantity
 FROM [dbo].[single_sales]

 GROUP BY 
    [product_code]
    ,[sales_class];

　2. 特定年度の集計

SQLは以下の通り。2000年を対象に集計しています。
全年度集計と同様に計測前に同一のクエリを流して統計情報を作成したうえで、DBCC DROPCLEANBUFFERSしてから計測します。

sql

DBCC DROPCLEANBUFFERS;
SELECT
    [product_code]
    ,[sales_class]
    ,SUM([sales_quantity])sales_quantity
 FROM [dbo].[yeardist_sales]
 WHERE 
    [year] = 2000
 GROUP BY 
    [product_code]
    ,[sales_class];

検証結果

これらの三つのケースと二つの集計パターンについて、結果をみてみます。

検証結果はSynapse Studio上にこのように表示されるものをまとめています。
※画像は今回の検証とは関係ありません。

1. 全年度の集計

小さいほどいい結果となります。
また、製品のベンチマーク材料となってしまうのは本意ではないので、クエリ時間に関しては単一ファイルを1として、比率を記載しています。

ケース	クエリ時間(比率)	データ処理量
単一ファイル	1.000	994.00 MB
自動分割ファイル	0.082	997.00 MB
年で分割したファイル	0.068	1,270.00 MB

分割されたファイルが双方とも。単一ファイルにくらべて10倍以上の性能が出ていることがわかります。
これが分散の効果となります。

分割されたファイルは複数のノードが並列で各ファイルを読み取って処理を進めていくことができますが、単一ファイルでは、単一のノードの読み取り性能に依存するため同じ時間で読み取れる量に差がつきます。
（1GBのデータに対して、200MB/s×5で挑むのと、200MB/s×1で挑むのでは前者の方が5倍速く読み取れますよね）

データ処理量については全体としては5GBのはずですが、Parquetを採用しているので余分な列は読み取らないという列指向フォーマットの特性が出ています。
年分割ファイルが他とくらべてデータ処理量が増えてしまっているのはちょっと謎でした。

列指向ストレージによる集計処理の効率化

2. 特定年度の集計

ケース	クエリ時間	データ処理量
単一ファイル	1.000	1,020.00MB
自動分割ファイル	0.156	1,030.00MB
年で分割したファイル	0.313	62.00 MB

こちらも分割されたファイルが双方ともに単一ファイルにくらべて3倍以上の性能が出ていることがわかります。
想定では、年で分割したファイルが時間も最速になる見込みでしたが、思ったよりオプティマイザが優秀なのか、年分割が逆にオーバヘッドになったのか、自動分割が最速となっています。

ですが、スキャンしたデータ量をみると、圧倒的にパーティション設定が効いた結果となっています。
パーティションを指定していないケースでは、全年度集計のときとスキャン量が余り変わっていません。
Serverless SQL Poolは処理量に課金が発生するサービスなので、コストに直結する部分に効果があることがわかります。