Open3

BigQueryのマテリアライズドビューはどのように更新されるか

yoppeyoppe

前提として入れておくポイント

とはいえポイントとなる部分をメモっていきます

自動更新がどのように走るか

自動更新ジョブは bigquery-adminbot@system.gserviceaccount.com サービス アカウントによって行われ、マテリアライズド ビュー プロジェクトのジョブ履歴に表示されます。

https://cloud.google.com/bigquery/docs/materialized-views-manage?hl=ja#automatic-refresh

増分アップデート

BigQuery はキャッシュされたビューのデータを新しいデータと組み合わせて、引き続きマテリアライズド ビューを使用しながら一貫したクエリ結果を提供します。これは、単一テーブルのマテリアライズド ビューの場合、最後の更新以降にベーステーブルが変更されていないか、新しいデータのみが追加された場合に可能です。JOIN ビューでは、JOIN の左側にあるテーブルにのみデータを追加できます。JOIN の右側にあるテーブルのいずれかが変更されている場合、ビューを増分更新できません。

https://cloud.google.com/bigquery/docs/materialized-views-use?hl=ja#incremental_updates

増分・非増分の違い(allow_non_incremental_definition)

allow_non_incremental_definition オプションのないマテリアライズド ビューでは、データのサブセットを段階的に更新できます。allow_non_incremental_definition オプションを含むマテリアライズド ビューは、全体を更新する必要があります。
https://cloud.google.com/bigquery/docs/materialized-views-create?hl=ja#non-incremental

Incremental materialized view(増分 マテリアライズドビュー)

例えばapprox_quantilesをmaterialized viewで使おうとするとこう言われます。
Unsupported aggregation function in Incremental materialized view: approx_quantiles.

これは通常のmaterialized viewが増分アップデートできなくなってしまうから、approx_quantilesをサポートしておらず、これは使えないよといっている。

Non-incremental materialized views(非増分 魔テリアライズドビュー)

allow_non_incremental_definitionをtrueとした場合、魔テリアライズドビューが定義できます。この場合はテーブルを全体を更新して増分アップデートをしないから。

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
OPTIONS (
  enable_refresh = true, refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
    allow_non_incremental_definition = true)
AS -- {以下クエリを書く}
yoppeyoppe

自動更新がうまくいったかどうかを知る

https://cloud.google.com/bigquery/docs/materialized-views-monitor?hl=ja#monitor_automatic_refresh

まずはこれを見ましょう。

最後の自動更新が成功したか

単に最後の自動更新がうまくいったかを確認する場合は以下のクエリを使います

SELECT
  table_name, last_refresh_time, refresh_watermark, last_refresh_status
FROM
  `DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

こんな感じ

Jobs explorerで確認する

続いてJobs explorerを見ると、bigquery-adminbot@system.gserviceaccount.comが自動更新のクエリを実行していることがわかる

クエリでより詳しくjobの情報を見る

しかしJobs explorerに記載されているのは一部の情報となっており、どのマテリアライズドビューの更新をCallしたかわからない。詳しく調べたい場合は以下のようにregion-us.INFORMATION_SCHEMA.JOBS_BY_PROJECTからデータを抽出すると良い。

SELECT
  job_id,
  user_email,
  creation_time,
  query,
  total_bytes_processed / POWER(1024, 3) AS total_GB_processed,
  total_bytes_billed / POWER(1024, 3) AS total_GB_billed,
  query_info.query_hashes.normalized_literals,
  mv.table_reference.table_id
FROM
  `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  DATE(creation_time) >= CURRENT_DATE()
AND
  job_id LIKE '%materialized_view_refresh_%'
order by creation_time desc
LIMIT 10;

queryのうち、CALL BQ.REFRESH_MATERIALIZED_VIEW(view_name);でプロシージャを実行できるようになっていて、botがこれを実行していることがわかります