BigQueryのパーティションフィルタを必須にするとどうなるのか

こんにちは。SCSKの磯野です。

BigQueryのパーティションフィルタについて、気になったことをいくつか調べてみました。

パーティションフィルタとは

パーティションフィルタを有効にすると、パーティション列を適切に指定したWHERE句が存在しないときに、エラーとすることができます。これにより、必ずパーティションが効くクエリしか実行できなくなるため、フルスキャンによる高額課金を防止することができます。

パーティション分割テーブルの管理  |  BigQuery  |  Google Cloud

cloud.google.com

パーティションフィルタを有効にする方法

terraformで管理している場合、以下の一文を追加することでパーティションフィルタを有効にすることが可能です。

require_partition_filter = true

Terraform Registry

registry.terraform.io

なお、テーブル作成後でもパーティションフィルタを有効化することは可能です。（参考）

パーティション分割テーブルを作成するときに、パーティション フィルタを要求するオプションを有効にしない場合でも、テーブルを更新してオプションを追加できます。

動作検証

検証方法

BigQueryの公開データセットを使っています。

bigquery-public-data.wikipedia.pageviews_2024を自分のプロジェクトへコピーし、パーティションフィルタの設定を変えて検証してみました。

パーティションフィルタを有効にすると？

有効化前（パーティションフィルタ省略可能）

パーティションフィルタを省略可能な状態にしたままクエリを実行すると、フルスキャンが走ることが確認できました。

有効化後（パーティションフィルタ必須）

パーティションフィルタを必須にした状態でクエリを実行すると、where句がないためエラーとなることが確認できました。

WHERE句を誤って指定していても、パーティションフィルタは効く？

では、パーティションが効かないようなwhere句を指定した場合はどうなるでしょうか？

以下のようなクエリで試してみました。本クエリは、パーティション列（datehour）によるwhere句が記載されていますが、パーティショニングが効かず、フルアクセスとなってしまいます。

SELECT
*
FROM `myproject.mydateset.wikipedia_pageviews_2024` -- 公式データセットをコピーしたテーブル

WHERE
DATETIME_ADD(datehour, INTERVAL 9 HOUR) BETWEEN "2024-7-3 9:00:00" AND "2024-7-3 15:00:00";

有効化前（パーティションフィルタ省略可能）

パーティショニングが効かないクエリのため、フルスキャンが走ることが確認できました。

有効化後（パーティションフィルタ必須）

パーティションフィルタが効いており、エラーとなることが確認できました。

→パーティションフィルタは、where句が適切でないことを認識できています。

Jupyter Notebookからクエリを実行しても、パーティションフィルタは効く？

import pandas as pd
from google.cloud import bigquery

client = bigquery.Client(project=project_id)

# クエリを定義
query = """
SELECT
*
FROM `myproject.mydateset.wikipedia_pageviews_2024`
WHERE
DATETIME_ADD(datehour, INTERVAL 9 HOUR) BETWEEN "2024-7-3 9:00:00" AND "2024-7-3 15:00:00";
"""

# クエリを実行して結果をDataFrameに読み込む
df = client.query(query).to_dataframe()

# 結果を表示
print(df.head())

Cloud Shellエディタより試しましたが、以下のようにエラーとなり、パーティションフィルタが効いていることが確認できました。

BadRequest: 400 Cannot query over table ‘myproject.mydateset.wikipedia_pageviews_2024’ without a filter over column(s) ‘datehour’ that can be used for partition elimination; reason: invalidQuery, location: query, message: Cannot query over table ‘myproject.mydateset.wikipedia_pageviews_2024’ without a filter over column(s) ‘datehour’ that can be used for partition elimination

結論

パーティションフィルタは、単純に「パーティション列を使用したwhere句の有無」をチェックしているのではなく、「パーティションが効くクエリかどうか」をチェックしていることがわかりました。

パーティション列を使用したwhere句を書いていても、不適切な書き方だとパーティションが効かないことがあります。そのような場合でも、パーティションフィルタを有効にしていればエラーとなるため、意図しないフルスキャンを防止することができます。

また、コンソールだけではなくノートブックからの実行でも、パーティションフィルタは効くことがわかりました。

本記事が皆様のお役に立てれば幸いです。