はじめに
BigQuery では、テーブルへのデータ取り込み日時を基準としてデータを分割する、取り込み時間パーティショニングを利用できます。
取り込み時間パーティションでは、実際のテーブル列とは別に、_PARTITIONTIME や _PARTITIONDATE といった疑似列が自動的に提供されます。これらの疑似列をクエリの絞り込み条件に利用することで、参照するパーティションを限定し、スキャン量を削減できます。
本記事では、BigQuery のネイティブテーブルに対して、日単位および月単位の取り込み時間パーティションを設定し、データ登録後の疑似列の値を確認します。あわせて、パーティションを設定していない通常テーブルとの違いや、Lakehouse for Apache Iceberg テーブルで同様の設定を試した結果についても整理します。
日単位の取り込み時間パーティション
はじめに、データが取り込まれた日付を基準として、1日単位でデータを分割するテーブルを作成します。
テーブルの作成とサンプルデータの登録
PARTITION BY _PARTITIONDATE を指定することで、日単位の取り込み時間パーティションを設定します。
CREATE OR REPLACE TABLE
abc.ingestion_time_partitioned_day (
transaction_id INT64,
ingest_time TIMESTAMP
)
PARTITION BY
_PARTITIONDATE
OPTIONS (
partition_expiration_days = 3,
require_partition_filter = FALSE
);
INSERT INTO abc.ingestion_time_partitioned_day (
transaction_id,
ingest_time
)
VALUES
(1, current_timestamp()),
(2, current_timestamp()),
(3, current_timestamp())
;
OPTIONS について
-
partition_expiration_days：パーティションを保持する日数を指定します。パーティションの有効期限を過ぎると、そのパーティションに格納されているデータが削除対象になります。 -
require_partition_filter：パーティションを参照するクエリに、パーティションを絞り込む条件を必須とするかどうかを指定します。TRUEを指定した場合、パーティション列または疑似列を参照するWHERE句が必要です。
テーブル詳細画面を確認すると、取り込み時間パーティションが設定されていることが確認できます。
疑似列の確認
作成したテーブルを参照し、各レコードが格納されているパーティションの時刻を確認します。
疑似列を SELECT 句に指定する場合は、列名にエイリアスを付けます。
また、今回は require_partition_filter = FALSE を指定しているため、パーティションを絞り込む WHERE 句を指定しなくてもクエリを実行できます。
SELECT
transaction_id,
ingest_time,
_PARTITIONTIME AS pt
FROM abc.ingestion_time_partitioned_day;
月単位の取り込み時間パーティション
次に、データの取り込み時刻を月単位に切り詰め、1か月単位でデータを分割するテーブルを作成します。
テーブルの作成とサンプルデータの登録
月単位の取り込み時間パーティションを設定する場合は、DATE_TRUNC を使用して _PARTITIONTIME を月単位に切り詰めます。
CREATE OR REPLACE TABLE
abc.ingestion_time_partitioned_month (
transaction_id INT64,
ingest_time TIMESTAMP
)
PARTITION BY
DATE_TRUNC(_PARTITIONTIME, MONTH)
OPTIONS (
partition_expiration_days = 3,
require_partition_filter = FALSE
);
INSERT INTO abc.ingestion_time_partitioned_month (
transaction_id,
ingest_time
)
VALUES
(1, current_timestamp()),
(2, current_timestamp()),
(3, current_timestamp())
;
疑似列の確認
登録したデータを参照し、_PARTITIONTIME の値を確認します。
SELECT
transaction_id,
ingest_time,
_PARTITIONTIME AS pt
FROM abc.ingestion_time_partitioned_month;
パーティションを設定していない通常テーブルとの違い
テーブル作成時に取り込み時間パーティションを明示的に設定していない通常テーブルでは、疑似列を参照できません。
また、パーティション分割なしのテーブルをパーティション分割テーブルに変換することはできないため、再作成が必要になります。
Lakehouse for Apache Iceberg テーブルでの検証
Lakehouse for Apache Iceberg テーブルに対して、同様の取り込み時間パーティションを設定できるか確認しました。
取り込み時間パーティションを指定したテーブル作成時に内部エラーが発生し、テーブルを作成できませんでした。
発生したエラーは次のとおりです。
An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support. Error: 80038528