Azure Synapse Analyticsのマテリアライズドビュー使ってみた

マテリアライズドビューとは

RDBMSの中でも多くのデータベースで実装されているマテリアライズドビューですが、これはいわゆる「実体を持つビュー」の事です。通常のビューは実際のデータを持つことはありませんが、マテリアライズドビューでは実体としてデータを保持しています。
通常のビューを利用すると、ビューの定義通りにベースのテーブルにクエリが投げられますが、マテリアライズドビューの場合ベースのテーブルにはクエリは実行されず、実体を保持しているマテリアライズビューからクエリ結果を返します。
JOINや、集計関数を含んだ複雑なクエリはマテリアライズドビューを使って、事前にJOINや集計した結果を実体として格納しておくことによってクエリの高速化が可能です。（ただし、マテリアライズドビューを更新する時間がかかります。）

マテリアライズドビューはクエリの書き換えなしに自動で使用される

Azure Synapse Analyticsのマテリアライズドビューは作成を行うとクエリ内で自動で使用されるようになります。なので、複雑なクエリはマテリアライズドビューを作るだけで、クエリの書き換えを行う必要なく、高速化する事が可能です。

実際にこの動きを試してみます。
まず、以下のようなクエリを実行します。
（クエリはTPCHの3番目のクエリです。TPC-Hを使ってテスト環境を作成する（Synapse SQLプール）)

/* TPC_H Query 3 - Shipping Priority */
SELECT TOP 10
     L_ORDERKEY
    ,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
    ,O_ORDERDATE
    ,O_SHIPPRIORITY
FROM 
     CUSTOMER
    ,ORDERS
    ,LINEITEM
WHERE
    C_MKTSEGMENT = 'BUILDING'
    AND C_CUSTKEY = O_CUSTKEY
    AND L_ORDERKEY = O_ORDERKEY
    AND O_ORDERDATE < '1995-03-15' AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE

このクエリの実行結果は約1分です。

実行計画は以下の通りで、SHUFFLE_MOVEに大きなコストがかかっている事が分かります。

ここで以下のようなマテリアライズドビューを作成します。

CREATE MATERIALIZED VIEW REVENUE_MV WITH(DISTRIBUTION=HASH(L_ORDERKEY)) AS
SELECT
     L_ORDERKEY
    ,O_ORDERDATE
    ,O_SHIPPRIORITY
    ,C_MKTSEGMENT
    ,L_SHIPDATE
    ,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
FROM
     dbo.CUSTOMER
    ,dbo.ORDERS
    ,dbo.LINEITEM
WHERE
        C_CUSTKEY = O_CUSTKEY
    AND L_ORDERKEY = O_ORDERKEY
GROUP BY
     L_ORDERKEY
    ,O_ORDERDATE
    ,O_SHIPPRIORITY
    ,C_MKTSEGMENT
    ,L_SHIPDATE
;

また、作成されているマテリアライズドビューの確認は以下のSQLで可能です。

--マテリアライズドビューの確認
SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

マテリアライズドビュー作成完了後、先ほど実行したクエリを再度実行すると、実行時間は約5秒となりました。

クエリの実行計画もマテリアライズドビューを作成したことにより、参照先がマテリアライズドビューに変更され、よりシンプルになりました。

このように、Azure Synapse Analyticsのマテリアライズドビューはクエリの書き換えなしに自動で使用され、大変便利です。

マテリアライズドビューの更新は自動。ベースのテーブルの更新があった際に同時に実施される

マテリアライズドビューは実体を持つビューなので、マテリアライズドビューが参照しているベースのテーブルに変更があった場合、マテリアライズドビューも更新する必要があります。このマテリアライズドビューの更新処理ですが、Azure Synapse Analyticsでは、マテリアライズドビューが参照しているベースのテーブルに更新があった場合、同時に自動で更新されます。つまり、ベーステーブルとマテリアライズドビューの両方が同じトランザクションで更新されるという仕組みになっています。

この為、マテリアライズドビューを作成すると、マテリアライズドビューの更新の関係でおのずとベーステーブルへの更新処理やデータロード処理が重くなってしまう事に注意が必要です。また、どれくらい重くなってしまうのかに関しては作成するマテリアライズドビューの内容や、ベーステーブルの大きさに依存します。

マテリアライズドビューの使いどころ

一般的には以下のような状況でマテリアライズドビューの使用を検討する事が多いです。

巨大なテーブルに対する複雑な分析クエリのパフォーマンスを高める

複雑な分析用のクエリは、たくさんのテーブルを結合したり、集計関数を多く使用する為、実行計画の中にSHUFFLE_MOVEやBROADCAST_MOVEなど多く発生し、クエリが長時間化します。
このような場合は、クエリでよく使われる箇所（インラインビュー等）にマテリアライズドビューを利用することで、結果をマテリアライズドビューから返すことが出来るようになるため、高速化する事が可能です。

クエリの変更をまったく行わずにパフォーマンスを向上させる

マテリアライズドビューの使用はユーザーから透過的に行われます。マテリアライズドビューを使用することで、クエリを変更することなく、確実に高速化する事が可能になります。（ただし、マテリアライズドビューの物理的なデータ領域が必要になり、またベーステーブルの更新処理は遅くなる点に注意してコストとメリットを天秤にかける必要はあります。）

パフォーマンスを向上させるために別のデータ分散方式を指定する

Azure Synapse Analyticsではデータを分散配置します。配置の仕方はHASH分散、RoundRobin分散、レプリケーティッドテーブル（ALL分散）の３つで、いずれかの分散方式で60のディストリビューションに分散されます。

このデータの分散は基本的にALTER TABLE文などでは変更できません。この為、クエリのチューニングをしていると、クエリによってはHASH分散しているテーブルのHASH KEYを変更したい場合などが発生します。このような場合、マテリアライズドビューでも同様に３つの分散方式のどれでも利用することが出来る為、違う分散方式やHASH分散でもHASH KEYの異なるテーブルをマテリアライズドビューとして作成する事が可能です。これによって、クエリに対する最適なデータ分散が選択可能となります。

推奨のマテリアライズドビューの確認

Azure Synapse Analyticsでは、クエリに対する推奨のマテリアライズドビューを教えてくれる機能があります。
以下のSQLを実行すると、対象のクエリに対する推奨のマテリアライズドビューを教えてくれます。

EXPLAIN WITH_RECOMMENDATIONS <対象のクエリ>;

例はこんな感じです。

EXPLAIN WITH_RECOMMENDATIONS
SELECT TOP 10
     L_ORDERKEY
    ,SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
    ,O_ORDERDATE
    ,O_SHIPPRIORITY
FROM 
     CUSTOMER
    ,ORDERS
    ,LINEITEM
WHERE
    C_MKTSEGMENT = 'BUILDING'
    AND C_CUSTKEY = O_CUSTKEY
    AND L_ORDERKEY = O_ORDERKEY
    AND O_ORDERDATE < '1995-03-15' AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE;

結果はXML形式で出力されます。

結果の中の、<materialized_view_candidates with_constants="False">と、<materialized_view_candidates with_constants="True">に推奨のCREATE MATERIALIZED VIEWが記載されています。
FALSEは定数無し（WHERE句の中のリテラル文字での絞り込み条件などが除外された状態）でのCREATE文となり、Trueはその逆で定数ありでのCREATE文となります。

一方で、これは該当のSQLのみ最適化するのようなマテリアライズドビューとなり、その他のクエリにとっては最適ではない可能性があります。

例えば、以下のようなSQLで例を示します。

-- Query 1
SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2
SELECT C, SUM(D)
FROM T
GROUP BY C

上記それぞれで、EXPLAIN WITH_RECOMMENDATIONSを実行すると、Query1、2それぞれで最適化されるような以下のCREATE MATERIALIZED文が推奨されます。
※少し見やすくするために、実際の出力結果から若干修正を加えています。

-- Query 1のEXPLAIN WITH_RECOMMENDATIONSの推奨事項
CREATE MATERIALIZED VIEW View1 AS
SELECT　A, SUM(B)
FROM T
GROUP BY A;

-- Query 2のEXPLAIN WITH_RECOMMENDATIONSの推奨事項
CREATE MATERIALIZED VIEW View2 AS
SELECT C, SUM(D)
FROM T
GROUP BY C;

このように個別最適化されたマテリアライズドビューを2つ作るよりもQuery1とQuery2が実行される可能性がある場合、1つのマテリアライズドビューで最適化する事が望ましいです。

CREATE MATERIALIZED VIEW View1_2 AS
SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C;

少々難しいかもしれませんが、EXPLAIN WITH_RECOMMENDATIONSの推奨事項だけでなく、他のクエリとの兼ね合いも考慮に入れることが重要です。