【アップデート・プレビュー】BigQueryのマテリアライズドビューで複数のテーブルを結合できるようになりました

はじめに

データアナリティクス事業部でGoogle Cloudのデータエンジニアをしています、はんざわです。
2023年4月6日にBigQueryのNon-incremental materialized viewsがプレビューになりました。

https://cloud.google.com/bigquery/docs/release-notes#April_05_2023

これによりマテリアライズドビュー作成時にOUTER JOINやUNION、HAVINGなどが使えるようになりました。

英語版ドキュメント

https://cloud.google.com/bigquery/docs/materialized-views-create#non-incremental

ドキュメントを確認する

Non-incremental materialized viewsについて

参考に利用したドキュメント：Non-incremental materialized views

Non-incremental materialized views support most SQL queries, including OUTER JOIN, UNION, and HAVING clauses, and analytic functions. In scenarios where data staleness is acceptable, for example for batch data processing or reporting, non-incremental materialized views can improve query performance and reduce cost.

非インクリメント型マテリアライズド・ビューは、OUTER JOIN、UNION、HAVING句、分析関数など、ほとんどのSQLクエリをサポートします。バッチデータ処理やレポート作成など、データの陳腐化を許容できるシナリオでは、非インクリメント型マテリアライズド・ビューは、クエリのパフォーマンスを向上させ、コストを削減することができます。

（DeepLによる翻訳）

ドキュメントの一部抜粋です。どうやらNon-incremental materialized viewsを有効にすることでJOINやUNIONなどの結合キーが使えるようになるみたいです。

You can create non-incremental materialized views by using the allow_non_incremental_definition option. This option must be accompanied by the max_staleness option.

The materialized view always represents the state of the base tables within the max_staleness interval. If the last refresh is too stale and doesn't represent the base tables within the max_staleness interval, then the query reads the base tables.

allow_non_incremental_definitionオプションを使用すると、非インクリメント型の実体化ビューを作成できます。このオプションは、max_stalenessオプションと一緒に使用する必要があります。

マテリアライズド・ビューは、常に max_staleness 間隔内のベース・テーブルの状態を表します。最後のリフレッシュが古すぎて、max_staleness間隔内のベース・テーブルを表現していない場合、クエリはベース・テーブルを読み取ります。

（DeepLによる翻訳）

同様にドキュメントの一部抜粋です。Non-incremental materialized viewsを利用する際にはmax_stalenessも一緒に指定する必要があるようです。
どうやらこのmax_stalenessを適切に調整する必要があるみたいです。

max_stalenessについて

参考に利用したドキュメント：max_staleness

どうやら、max_stalenessと最終更新のタイミングの次第でテーブルがどこからデータを参照するかが変わってくるようです。

結論から言うと、イメージ的にはmax_stalenessの分だけ遡り、それまでにテーブルの更新がされていたらマテビューからデータを返し、そうでなければ元々のテーブルからデータを取ってくると理解しました。

上の画像は、max_stalenessまでにテーブルが更新されているケースです。
この場合、テーブルはマテリアライズドビューから直接データを返すようです。

次の上の画像は、逆にmax_stalenessまでにテーブルが更新されていないケースです。
この場合は、元々のテーブルから直接返すようです。

また、公式ドキュメントには以下のようなことも書かれていました。

You should set max_staleness based on your requirements. To avoid reading data from base tables, configure the refresh interval so that the refresh takes place within the staleness interval. You can account for the average refresh runtime plus a margin for growth.

For example, if one hour is required to refresh your materialized view and you want a one-hour buffer for growth, then you should set the refresh interval to two hours. This configuration ensures that the refresh occurs within your report's four-hour maximum for staleness.

max_staleness は要件に応じて設定する必要があります。ベース・テーブルからのデータ読み取りを避けるため、リフレッシュ間隔を設定し、リフレッシュがstaleness間隔内に行われるようにします。平均的なリフレッシュの実行時間に加え、成長のためのマージンを考慮することができます。

たとえば、マテリアライズド・ビューのリフレッシュに1時間必要で、成長のために1時間のバッファが必要な場合、リフレッシュ間隔を2時間に設定します。この構成では、リフレッシュがレポートの最大4時間の陳腐化の範囲内で行われることを保証します。

例）OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)

（DeepLで翻訳）

公式ドキュメントでも言及されているように、元々のテーブルから参照されることを避けるためにも更新タイミングを考慮した上で適切なmax_stalenessを設定することが重要になってくるのではないかと思っています。

試してみる

1. 検証用のテーブルを2つ作成する

検証用に2種類のテーブルを作成しました。
それぞれidとfruitのカラムを持っています。

## sample1

CREATE TABLE `test.sample1` AS 
SELECT 
  n AS id,
  'apple' AS fruit
FROM 
  UNNEST(GENERATE_ARRAY(1, 50)) AS n 
ORDER BY n

## sample2

CREATE TABLE `test.sample2` AS 
SELECT 
  n AS id,
  'banana' AS fruit
FROM 
  UNNEST(GENERATE_ARRAY(1, 50)) AS n 
ORDER BY n

2. UNIONを使ったマテビューを作成する

早速以下のクエリでマテビューを作成します。

## view1
CREATE MATERIALIZED VIEW test.view1
OPTIONS (
  allow_non_incremental_definition = true,
  max_staleness = INTERVAL "0:35:0" HOUR TO SECOND
  )
AS
SELECT
　id,
  fruit
FROM
  test.sample1
UNION ALL
SELECT
  id,
  fruit
FROM
  test.sample2

3. データの確認

念の為、中身の確認します。想定通りに作られてます。

SELECT
  fruit,
  COUNT(*) AS cnt
FROM
  test.view1
GROUP BY 1

+---+-----+-----+
|  fruit  | cnt |
+---------+-----+
|  apple  |  50 |
| banana  |  50 |
+---------+-----+

4. データを追加する

片方のベーステーブルに以下のクエリでデータを追加します。

INSERT `test.sample1`
SELECT
  51 AS id,
  'apple' AS fruit

5. マテビューを確認する

追加した直後にマテビューを確認しても前に確認した時と変化はありませんでしたが、テーブルを作成した35分後にもう一度確認した所、マテビューにもデータの更新が反映されていました。

SELECT
  fruit,
  COUNT(*) AS cnt
FROM
  test.view1
GROUP BY 1

+---+-----+-----+
|  fruit  | cnt |
+---------+-----+
|  apple  |  51 |
| banana  |  50 |
+---------+-----+

注意点

1

max_stalenessは最低30分で最大でも72時間までしか設定できないようです。それ以外の時間を入れると以下のようなエラーメッセージが表示されます。

Materialized view max_staleness in milliseconds (0-0 0 0:10:0) must be between 1800000 and 259200000

2

今回は検証では触れていませんが、JOINを使う際にはいくつか留意事項があるようです。
利用する際にはドキュメントをしっかりと確認しましょう。

https://cloud.google.com/bigquery/docs/materialized-views-create#joins

まとめ

マテビューは制約が多い印象がありましたが、今回のアップデートでそのほとんどがなくなったんじゃないかと思っています。
まだプレビューですが、今後マテビューの利用ケースがさらに増えることは間違いないと思います。
（弊チームでも既に一部利用の検討が始まっています。GAになったらの話ですが...）

みなさんも是非活用してみましょう。