【BigQuery】Materialized viewのSmart tuningを試す

# はじめに
BigQueryのMaterialized view（実体化されたビュー）の [ドキュメント](https://cloud.google.com/bigquery/docs/materialized-views-intro?hl=ja) には以下のような記載があります。
> #### スマートな調整
ソーステーブルに対するクエリまたはクエリの一部がマテリアライズド ビューへのクエリによって解決できる場合は、BigQuery によってマテリアライズド ビューを使用するようにクエリが書き換えられ（クエリの宛先が変更され）、その結果パフォーマンスと効率が向上します。

なんだかすごそうと思ったので実際に試してみることにしました。


※以下に記載されているクエリを実行するとBigQueryの利用料が発生する可能性がありますのでご注意ください。

# 検証用テーブルを作成する
COVID-19の公開データセット、 `covid19_open_data` から、`2021-07-01` ～ `2021-07-31` までの新規感染者数を抽出したテーブルを作成します。
`my_dataset_001`は適当なdatasetに変更してください。

※以下のクエリでは約600MBのデータがクエリ対象になります。


```SQL
CREATE TABLE
  my_dataset_001.sample_data (
    location_key STRING
    ,date DATE
    ,country_name STRING
    ,subregion1_name STRING
    ,new_confirmed INTEGER
  )
AS SELECT
  location_key
  ,date
  ,country_name
  ,subregion1_name
  ,new_confirmed
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE 
  country_name = 'Japan' -- 日本の都市に限定
  AND subregion1_name IS NOT NULL -- 日本全体の合計のデータを除外
  AND date BETWEEN '2021-07-01' AND '2021-07-31'
;
```

日本の47都道府県の日毎の感染者数のデータ約1,500レコードのテーブルができました。
サイズは55KBくらいありますね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/4f192fbe-4f52-53a0-5ed2-1a173058a4a2.png)

中身はこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/e6faf71e-c68f-c204-79d2-b8acc9730f02.png)


# 普通に集計してみる
都道府県ごとの7月の新規感染者数の合計と、新規感染者数が一番多かった日の新規感染者数を計算してみます。

```SQL
SELECT
  location_key
  ,country_name
  ,subregion1_name
  ,SUM(new_confirmed) AS cumulative_confirmed
  ,MAX(new_confirmed) AS max_confirmed
FROM
  my_dataset_001.sample_data
GROUP BY
  location_key
  ,country_name
  ,subregion1_name
ORDER BY
  location_key
;
```
結果は以下の通りになりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/8659c162-4337-a5ff-fb04-455403c51224.png)

ここで注目するのは `処理されたバイト数: 43.4 KB` というところです。
これはソーステーブル( `sample_data` )のすべての行がスキャンされていることを意味します。
最初に確認したテーブル全体のサイズ（約55KB）より少し少ないのは今回のクエリが `date` カラムを参照していないからです。

# Materialized view を作成する
先ほどの集計のクエリをベースに同じ内容を集計するMaterialized viewを作成します。
`CREATE MATERIALIZED table_name VIEW AS` と書くだけです。
以下のSQLのSELECT以下は集計クエリとほぼ同じですね（ `ORDER BY` だけ外しています）。

```SQL
CREATE MATERIALIZED VIEW `my_dataset_001.summarized_sample_data`
AS SELECT
  location_key
  ,country_name
  ,subregion1_name
  ,SUM(new_confirmed) AS cumulative_confirmed
  ,MAX(new_confirmed) AS max_confirmed
FROM
  my_dataset_001.sample_data
GROUP BY
  location_key
  ,country_name
  ,subregion1_name
;
```

このクエリの結果以下のテーブルが作成されます。
都道府県ごとに集約しているので行数は47行、サイズは `1.77KB` になりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/c59190e9-65f2-cc6e-e80b-42bb29fba042.png)

参照方法は普通のテーブルと同じです。

```SQL
SELECT
  location_key
  ,country_name
  ,subregion1_name
  ,cumulative_confirmed
  ,max_confirmed
FROM
  `my_dataset_001.summarized_sample_data`
ORDER BY
  location_key
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/9c8fc7ad-c317-3306-7e1d-35f057d8c23c.png)

先ほど普通に集計したときと結果は同じですが、`処理されたバイト数: 1.8 KB` となっていますね。これは Materialized view のすべてのデータが参照されたためです。元のテーブルから集計したときよりも参照されるデータの量を小さくすることができていますね。

# Smart tuningを試す
ようやく本題ですが、ここで冒頭の公式ドキュメントをもう一度。

> #### スマートな調整
ソーステーブルに対するクエリまたはクエリの一部がマテリアライズド ビューへのクエリによって解決できる場合は、BigQuery によってマテリアライズド ビューを使用するようにクエリが書き換えられ（クエリの宛先が変更され）、その結果パフォーマンスと効率が向上します。

今回の例ではつまりこういうことです。
`sample_data` テーブルに対する集計クエリの結果が `summarized_sample_data` から得られるなら、クエリの参照先が `summarized_sample_data`に変更されてパフォーマンスが向上する。

試してみましょう。実行するクエリは最初に普通に集計したときと同じものです。

```SQL
SELECT
  location_key
  ,country_name
  ,subregion1_name
  ,SUM(new_confirmed) AS cumulative_confirmed
  ,MAX(new_confirmed) AS max_confirmed
FROM
  my_dataset_001.sample_data
GROUP BY
  location_key
  ,country_name
  ,subregion1_name
ORDER BY
  location_key
;
```

結果はこうなります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/6beb2aed-5bdc-1313-aa7a-a33541a455b6.png)

`処理されたバイト数: 1.8 KB` となおり、これは `summarized_sample_data` を参照したときと同じですね。
念のため実行の詳細を確認してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/220881/cd292807-264a-6d55-503b-ad73d4f4931a.png)

確かに `summarized_sample_data`、つまり Materialized view が参照されていますね、ドキュメントにそう書いてあるので当たり前なんですけど、すごいなぁ。

ちなみに以下の例のように Materialized view の一部のフィールド（カラム）や行をソーステーブルから参照する場合でも Materialized view が参照されます。

```SQL
SELECT
  subregion1_name
  ,SUM(new_confirmed) AS cumulative_confirmed
FROM
  my_dataset_001.sample_data
GROUP BY
  location_key
  ,subregion1_name
ORDER BY
  location_key
;
```

```SQL
SELECT
  subregion1_name
  ,SUM(new_confirmed) AS cumulative_confirmed
FROM
  my_dataset_001.sample_data
WHERE
  subregion1_name = 'Tokyo'
GROUP BY
  subregion1_name
;
```