以前SnowPro core Certificationsに合格したもののなかなか使う機会がなくて、
資格試験対策レベルの薄い知識の維持すら怪しくなってきた。
Materialized Viewについて良くわからず使っていたので、
「やりなおし」のついでに知識をアップデートしていこうと思う記事第2弾。
個人の学び以上でも以下でもなく、内容に誤りがあるかもしれないので、
ことの真偽は公式ドキュメントを参照のこと。
【目次】
- ∨Materialized Viewとは
- ∨Materialized Viewは透過的にリフレッシュされる
- ∨普通のViewを使うべきか、Materialized Viewを使うべきか
- ∨Materialized Viewのパフォーマンス
- ∨クエリオプティマイザとMaterialized View
- ∨Materialized Viewのメンテナンスコスト
- ∨Materialized Viewの上手い使い方
- ∨DDL,DML
- ∨まとめ
Materialized Viewとは
何らかの集計を行おうとすると、多くの場合、中間の集計を合わせて最終的な集計結果を得る。
中間の集計を行う際にJOINにより結合を行う場合、それが高コストだと最終的にコスト高になる。
途中の集計結果をどこかに保存できれば、毎回高コストな集計を無駄に実行しなくて良くなる。
そんな時に使うのが Materialized View。「マテビュー」とか省略される。
e-Wordsによると以下の通り。
マテリアライズドビューとは、リレーショナルデータベースで作成されたビューにある程度の永続性を持たせ、参照する度に再検索しなくていいようにしたもの。特定のビューを頻繁に参照する場合に性能が向上する。
SnowflakeにおけるMaterialized Viewについては以下。
マテリアライズドビューの使用
Materialized Viewは透過的にリフレッシュされる
重要な点として、SnowflakeにおけるMaterialized Viewは自動的・透過的にリフレッシュされる。
オリジンとなるデータが変わった場合、アプリケーション側はノータッチでSnowflakeが自動更新する。
アプリケーション側でオリジンの鮮度を意識しないで良いというのはかなり楽。
透過的な自動リフレッシュの機構について、より詳細な内部情報として以下の通り。
ギリギリまでDMLを反映しないでくれる機構がついているっぽい。
- クエリの前に実行されたDMLがクエリに影響する場合、クエリ時にDML反映
- クエリの前に実行されたDMLがクエリに影響しない場合、スルーで応答
アプリケーション側がノータッチで自動的・透過的にリフレッシュがかかるが、
そのリフレッシュでクレジットが消費される。
普通のViewを使うべきか、Materialized Viewを使うべきか
どのようなクエリであればその結果をMaterialized Viewに乗せるべきか。
クエリに時間がかかるなら使うべき。ただしオリジンとなるデータが変われば、
Materialized Viewのリフレッシュが必要となるから、オリジンが頻繁に変わるケースは対象外。
時間がかかる処理として公式には以下の例があがっている。
- 半構造化データに対するクエリ
- S3上のファイルなど遅い外部テーブルに対するクエリ
普通のViewにすべきか、Materialized Viewにすべきかの判断基準は以下。
| 普通のView | Materialized View | |
|---|---|---|
| ビューからのクエリ結果(※) | 頻繁に更新される | ほとんど更新されない |
| クエリ結果の使用 | 頻繁に使用される | あまり使用されない |
| リフレッシュにかかるコスト | 処理時間大、ストレージ大 | 処理時間小、ストレージ小 |
※ベースとなるテーブルが「完全に更新されない」まで限定しなくても、「クエリ結果の範囲に限定して更新されない」でOK。
Materialized Viewのパフォーマンス
Snowflakeのパフォーマンスを上げる機構として「クエリ結果キャッシュ」がある。
要は同じ条件のクエリに対して、キャッシュがあればクエリを実行せずにキャッシュを返す、というもの。
実際に運用してみると「クエリ結果キャッシュ」を使わせるためには複数の条件があり、
なかなか仕様通りキャッシュを使い続けるのは難しいが、キャッシュが効けば速くなる。
比較することに意味があるのかちょっと怪しいが、
純粋にパフォーマンスを比較すると以下となるらしい。
普通のTable = 普通のView < Materialized View < クエリ結果キャッシュ
クエリオプティマイザとMaterialized View
アプリケーションがexplicitにMaterialized Viewを指定してやらなくても、
ベーステーブルに対するクエリ結果の行と列がMaterialized Viewに全て含まれている場合、
クエリオプティマイザが自動的にクエリを置換する。
さらに、もしベーステーブルがフィールドによってクラスタ化されていて、
プルーニングが良い結果をもたらすと判断されれば、Materialized Viewではなく
ベーステーブルに対するプルーニングが使用される。
BIのようにアプリケーション全域で検索キーが分散し「どう呼ばれるかわからない」ケースでは、
なかなかカスタムでクラスタキーを設計してより良いプルーニング結果を得ることが難しいが、
分析タスクのように「ある程度呼ばれ方が決まっている」ケースならクラスタキーを偏らせる
メリットはありそうで、そんなときにMaterialized Viewとベーステーブルのプルーニング、
どちらが良いか、なんてことを考えることもあるんだろう(本当か?)
ただ公式には、ベストプラクティスとしてMaterialized Viewを作る場合、
ベーステーブルのプルーニングを解除しMaterialized Viewを優先すべきと記述がある。
マテリアライズドビューとそのベーステーブルをクラスタリングするためのベストプラクティス
サブクエリについて暗黙的にMaterialized Viewが使われる、というケースもある。
この場合、クエリプロファイルにシレッとMaterialized Viewが鎮座する、ということになる。
Materialized Viewのメンテナンスコスト
コンピューティング、ストレージともにクレジットを使用する。
Materialized Viewに対するクエリ結果が保存され、そのストレージに対してコストがかかる。
頻繁に使われるクエリなのであれば、相対的にストレージのコストが低くなると考えられるが、
もしロクに使われないクエリなのであれば、そのストレージコストが本当に低いのか考えるところ。
透過的なリフレッシュのためにコンピューティングのコストがかかる。
Materialized Viewの上手い使い方
ベーステーブルの多くデータを取得してしまうと、無駄にリソースを使ってしまう。
なるべく行・列が少なくなるようなデータセットをMaterialized Viewに格納すべき。
公式には、ベーステーブルにログがあるとして異常値のみをMaterialized Viewに置く、
という例が示されている。
要はアプリケーションの設計段階で、何をMaterialized Viewとすべきかを検討すべき。
ベーステーブルを頻繁に更新してしまうと、都度自動リフレッシュが実行されてしまう。
そのため、ベーステーブルの更新頻度を下げる必要がある。
SnowflakeはDMLをバッチ処理するように推奨している。
バッチ処理できるようなデータの並びとなるようにしておく必要がある。
DDL,DML
実際にMaterialized Viewを作ってみる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--- ベーステーブルの作成 create table mv_base (id integer, value integer) ; --- Materialized Viewの作成 create or replace materialized view mv1 as select id, value from mv_base ; ---ベーステーブルにInsert insert into mv_base (id, value) values (1, 100) ; --- Materialized Viewからデータ取得 select id, value from mv1 ; id value --------------- 1 100 ---ベーステーブルにInsert insert into mv_base (id, value) values ( 2,200) ; --- Materialized Viewからデータ取得 select id, value from mv1 ; id value --------------- 1 100 2. 200 |
Materialized Viewの自動更新を停止することができる。
停止中にデータを取得しようとするとエラーが発生し取得できない。
停止と再開の組みは以下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
alter materialized view mv1 suspend ; select id, value from mv1 ; SQLコンパイルエラー： ビュー「MV1」の展開中の失敗： SQLコンパイルエラー：マテリアライズドビュー MV1 は無効です。 alter materialized view mv1 resume ; select id, value from mv1 ; id value --------------- 1 100 2. 200 |
まとめ
更新頻度が低く利用頻度が高い中間クエリについて Materialized View に格納すると効果的。
Materialized Viewは自動的・透過的にリフレッシュがかかる。
自動リフレッシュに際してコンピューティングコストがかかるため更新頻度は低い方が良い。
ベーステーブルに対するDMLをバッチ処理とすると自動リフレッシュの頻度を下げられる。
クエリ結果キャッシュよりは遅いが普通のテーブルよりは速い。