Synapse Dedicated SQL Poolは「分散」と「Index」の組み合わせで最適化する

Posted on:May 14, 2023

Dedicated SQL Poolは Microsoft Azure のサービスの 1 つで、主にデータウェアハウスに使用されます。 要は Snowflake や Redshift の Azure 版です。

本記事では Dedicated SQL Pool を初めて触る方向けに、最低限の tips をまとめます。 理解がより進んでいる方は、Microsoft のベストプラクティスを読むと良いです。

※この記事を書いた日から時間が経ち、古い情報になっている可能性があります。あらかじめご了承ください。

Table of contents

Open Table of contents

テーブル作成時、「分散」と「Index」の組み合わせで最適化する

データを格納するためのテーブルを作成するとき、Dedicated SQL Pool では以下のように記述します。 例えば全国展開しているコンビニエンスストアの購買データが以下のようになっていると仮定しましょう（小売市場での業務経験がないので頓珍漢な構造になっていればすいません）。 テーブルやカラムの名前は適当です。

CREATE TABLE [Shema名].[Table名]
(
      [Datetime]   datetime -- 購買事象発生日時
    , [StoreID]    int      -- お店のID
    , [ProductID]  int      -- 買った商品のID
    , [IsCashless] bit      -- 現金なら0, キャッシュレス支払いなら1
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
    , CLUSTERED COLUMNSTORE INDEX
)
;

他のデータベース、データウェアハウスに関するサービスと同じようにCREATE TABLEでテーブルを宣言しますが、一方でWITHの後で 2 つのオプションを指定していることが分かります。 このデータ量や用途に合わせて指定していくことが、Dedicated SQL Pool を使用する上で重要になります。

1 つ目のDISTRIBUTION = ROUND_ROBINの部分では、データをどのようなルールに従って分散させるか指定しています。 Dedicated SQL Pool は、インターフェイス上では 1 つのテーブルとして扱われていますが、裏側で 60 個のコンピュートノードにデータを分散させています。 いわゆる Massively Parallel Processing Database と呼ばれるものの一種であり、Big なデータを効率よく処理するため、このような設計となっています。 一方で、ノード間での無駄なデータ移動や、ノードごとのデータ量がなるべく均一になるように、ユーザー側の設定が必要になります。

2 つ目のCLUSTERED COLUMNSTORE INDEXと書いてある部分では、テーブルの Index について指定できます。 こちらもデータ量や用途に応じてオプションを変えることも可能です。

繰り返しになりますが、この分散設定と Index 設定を適切に行うことが重要になってきます。

分散方法はHASHを検討し、無理そうなら他の選択肢を検討する

分散方法には 3 つの選択肢があります。 まとめると、以下のような判断基準で分散方法を指定すれば良いと考えます。

このように考える根拠を簡単に書いていきます。

ROUND_ROBIN

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;

1 つ目のROUND_ROBINは単純で、データをランダムに 60 個のコンピュートノードに分散させます。 デフォルトではこちらが指定されるみたいです。 ノードごとにデータ量の偏りが発生することもないため、難しいことを考えずに指定できるという意味では楽チンです。 後述するHASHのようなアルゴリズムを通すこともないため、純粋にテーブルを作成したり、この手法で設計されたテーブルに INSERT するのは高速である可能性も高いです。

一方で、この手法を用いて分散させたテーブルを使用した JOIN や集計が非効率になることを考慮した方が良いです。 ROUND_ROBINで作成されたテーブルを JOIN などに使用する場合は、使用するカラムに従ってデータを並び替えるが発生し、処理効率を低下させる原因になるためです。 作成したテーブルを基に、別のテーブルへの挿入などが場合は、次に紹介するHASHを使用した方が計算効率を向上させる可能性があります。

HASH

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    DISTRIBUTION = HASH([StoreID])
)
;

（管理者権限で設定すれば、複数カラムの指定も可能です。）

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    DISTRIBUTION = HASH([StoreID], [ProductID])
)
;

特定のカラムに従って分散させる選択肢が 2 つ目のHASHです。 指定したカラムの同じ値のデータが、同じノードに配置されるように分散します。 このような設計にすることで、指定したカラムが JOIN の結合キーとなるときや、GROUP BY で集計するときに大きな効果を発揮します。 例えば JOIN する際、結合キーの同じ値が属しているノード同士を突き合わせれば、ノード間をまたいでデータを検索する必要がなくなります。

ただHASHも決して万能ではなく、今回のテーブル例だとHASH([IsCashless])とするのは得策ではありません。 IsCashlessの値の種類は0と1(とNULL)しか存在しないためです。 指定したカラムを基に各コンピュートノードへ分散配置するとき、値の種類が 60 個よりも極端に少ないと、ノードが 60 個あるうちの数個しか利用しないことになり、分散させる意味がなくなってしまいます。 他にも日付列やNULLの多いカラムは分散に使用するカラムとして指定するべきではないみたいです。

また指定するカラムの分布に偏りがあると、分散自体うまくいかない可能性があります。 1 つのノードにデータが集中することで、多数のノードが計算終了しても 1 つのノードのせいでいつまでも計算完了待ち…なんてことが発生してしまいます。 分散したデータの偏りを確認するには、下記の記事が参考になります。

お使いのディストリビューションが適切な選択かどうかを判断する方法 (2023/05/12 Access)

REPLICATE

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    DISTRIBUTION = REPLICATE
)
;

最後に 3 つ目のREPLICATEですが、こちらは少し毛色が違うものです。 60 個のノードに分散させるというよりは、その名の通りテーブルを複製します。 全てのノードに同じデータが配置されるため、ノードを跨いだデータ移動が全く発生しない一方、データ容量が大きいとそれぞれのノードに負担をかけることになります。 データ量が小さい、テーブルのレコード数が少ない（2GB より小さい）場合はREPLICATEを選択すると良さそうです。

テーブル作成時に Index の設定が可能

前述の通り、Dedicated SQL Pool ではテーブル作成時に Index を指定できます。 Index には 4 つの選択肢があります。とりあえず以下のような判断基準で良さそうです。

CLUSTERED COLUMNSTORE INDEX

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    CLUSTERED COLUMNSTORE INDEX
)
;

データを圧縮することで、大きなデータでも効率よく扱えるようになるようです。 ドキュメントには 6,000 万行を超えると期待された Index の効果を発揮できるとあります。 特に何も指定しなければ（前述のコードからCLUSTERED COLUMNSTORE INDEXが取り除かれた場合）、デフォルトとしてこちらの Index が与えられるようです。

この Index を使用しているテーブルでは、使用するカラムのみ抽出するように心掛けましょう。 一部のカラムしか使用しない場合にSELECT * FROM TABLEみたいな書き方をすると、せっかくの Index 効果を発揮できません。 列ごとにグループを作成するようなデータ圧縮の方法をとっているらしく、不要なカラムまで*で取得しようとすると、その読み込みだけでオーバーヘッドが生じます。

CLUSTERED INDEX ([COLUMN名])

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    CLUSTERED INDEX ([StoreID])
)
;

指定したカラムに対する検索を向上させるため、B-tree インデックスのような Index を与えるものです。 ごく少数の行を検索するときに効果的とのことなので、マスタテーブル（例えば、商品 ID と商品名の対応表のようなテーブル）などに適用を考えると良いでしょう。

HEAP

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    HEAP
)
;

HEAPは小さいテーブル使用時に、読み込みを高速化される可能性があります。 ここでいう「小さいテーブル」は、正直定義が曖昧です。 6,000 万行以上はCLUSTERED COLUMNSTORE INDEXを使うと良いですが、かといって 59,999,999 行だと絶対 NG というわけでもないみたいです。 もし際どいデータ量である場合は実験して決めるのが良いでしょう。

非クラスター化 Index

CREATE TABLE [Shema名].[Table名]
(
    ...
)
WITH
(
    ...
)
;

-- 例えば[StoreID]にIndexを与えるとき
CREATE INDEX sampleIndex ON [Shema名].[Table名]([StoreID])
;

指定したカラムに対する検索を向上させるため、B-tree インデックスのような Index を与えるものです。 CLUSTERED INDEX ([COLUMN名])で指定しなかったカラムや、HEAPで作成されたテーブルと組み合わせることが想定されます。

CLUSTERED INDEX ([COLUMN名])との違いは、Index とテーブルが分離しているということらしいです。 非クラスター化 Index では、Index で行番号を検索したあと、関連する行を引っ張ってくる時間が発生します。 これがタイムロスとなるため、可能な限りCLUSTERED INDEXを使用するのが良さそうです。

終わりに

まとめると、テーブル作成時のWITHの設定に気を遣えば、まずはどうにでもなるでしょうということです。 もちろんもっと高度な Tips はたくさんありますので、Microsoft のベストプラクティスも読んでください。