はじめに
こんにちは！
bq queryコマンドでクエリ実行中に以下のエラーに遭遇したので、エラーの内容と対応についてまとめます。
Quota exceeded: Your table exceeded quota for Number of partition modifications to a column partitioned table
エラーの原因
原因は対象テーブルのパーティション変更数が1日あたりの割り当てに達したためです。
列パーティション分割テーブルのパーティション変更数の詳細
パーティション変更数上限の仕様について説明します。
1. 列パーティション分割テーブルのパーティション変更数は各テーブル1日に30,000回
2. 変更数の割り当てを増やすことはできない
3. DMLステートメントはカウント対象外
4. この制限にカウントされるのは、主に以下のジョブベースの操作
- Load Job
- GCSやローカルファイルからデータをロードする（例：bq loadコマンド）
- Copy Job
- 他のテーブルからデータをコピーしてくる（例：bq cpコマンド）
- Query Job
- クエリ結果をパーティション分割テーブルに書き込む（「宛先テーブル」として保存する場合）
対象処理内では以下のような方法で特定のテーブルに何度もデータを入れ直していた為、本エラーが発生しました。
# 「宛先テーブル」を使用し、クエリ結果でexample_tableを上書きするQuery Job
bq query --destination_table="example_table" --replace=true 'SELECT * FROM ~'
参考：列パーティション分割テーブルとは
参考：列パーティション分割テーブルのパーティション変更数上限
1日のパーティション変更数確認クエリ
以下のクエリを使用して1日のパーティション変更数の実績を確認することができます。
変更数が30,000回に近いテーブルがあると、エラー発生の可能性があります。
SELECT
destination_table.table_id,
SUM(total_modified_partitions) AS total_modified_partitions
FROM
`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
DATE(creation_time, "Asia/Tokyo") = CURRENT_DATE()
GROUP BY
table_id
ORDER BY
total_modified_partitions DESC
クエリ結果はDMLステートメントで変更されたパーティション数も含まれます。
また、コピージョブによって変更されたパーティション数は含まれません。
エラーの原因となるパーティション変更数のカウントと必ずしも一致しないため、参考程度に確認することを推奨します。
参考：テーブルあたりのクエリジョブと読み込みジョブによって変更されるパーティション数
エラー解消方法
公式ドキュメントで記されている解決策は以下の通りです。
・ パーティション単位の変更
例：日単位のパーティショニングから月単位のパーティショニングへの変更 など
・ パーティショニングの代わりにクラスタリングを使用
・ 複数のジョブを統合し、ジョブの数を減らす
・ BigQuery Storage Write API の使用
私はパーティション単位を日単位から月単位へ変更することで対応しました。
パーティション単位を大きくすると、パーティションキーを検索条件にしたクエリのスキャン量が大きくなります。
その対応としてクラスタリングの設定も同時に行いました。
bq mk
--table \
--force=true \
--schema="jsonファイル" \
--time_partitioning_field=パーティションキー \
--time_partitioning_type=MONTH \
--clustering_fields=パーティションキー \
--location=ロケーション \
プロジェクト名:データセット名.テーブル名
既存の列パーティション分割テーブルに対してパーティション単位の変更はできないため、再作成する必要があります。
クラスタリングの設定を行うことでスキャン量の増加を抑えることが期待できますが、代わりにスロットを消費する傾向があるようです。そのため、クエリ遅くなる可能性があります。
参考：列パーティション分割テーブルのパーティション変更数の割り当てエラーの解決策
参考：パーティション分割テーブルの作成方法
参考：テーブルのパーティショニングとテーブルのクラスタリングの組み合わせについて
エラー解消方法(宛先テーブルを使用したクエリが原因の場合）
公式ドキュメントの解決策として明確に示されてはいないですが、DMLステートメントは本エラーに影響しないため、宛先テーブルを使用しないクエリに置き換えた場合も対応策となり得ると考えます。
※こちらは未検証です
# 本エラーの原因となり得る実行方法
bq query --destination_table="example_table" --replace=true 'SELECT * FROM ~'
# 以下に置き換えることでエラーの回避が可能
bq query 'TRANCATE example_table; INSERT example_table SELECT * FROM ~'
おわりに
今回はBigQueryのエラーについて調査したことをまとめました。
株式会社ジールはMicrosoft Azure やAWS（Amazon Web Services）、Google Cloud Platform（GCP）、Oracle Cloud Infrastructure（OCI）など、多彩なクラウドプラットフォーム構築・運用の知見を有しています。そのため、複数のクラウドサービスを組み合わせるマルチクラウド環境においても、ベンダーロックインを回避し、お客様のご要望に応じた最適なクラウドプラットフォームの構築・運用への対応が可能です。
最後までご覧いただき、ありがとうございました！