パーティション分割テーブルに対するクエリ
このドキュメントでは、BigQuery でパーティション分割テーブルに対してクエリを実行するための具体的な考慮事項について説明します。
BigQuery でのクエリの実行に関する一般的なことについては、インタラクティブ クエリとバッチクエリの実行をご覧ください。
概要
クエリで、パーティショニング列の値に対する限定フィルタを使用すると、BigQuery では、フィルタに一致するパーティションがスキャンされ、残りのパーティションはスキップされます。この処理は「パーティションのプルーニング」と呼ばれます。
パーティションのプルーニングは、BigQuery で入力スキャンから不要なパーティションを削除するために使用する仕組みです。除かれたパーティションは、クエリでスキャンしたバイト数の計算には含まれません。一般に、パーティションのプルーニングは、クエリのコスト削減に役立ちます。
プルーニングの動作はパーティショニングのタイプによって異なるため、パーティション分割が異なるが同一であるテーブルに対してクエリを実行すると、処理されたバイト数に違いが表示される可能性があります。クエリで処理されるバイト数を見積もるには、ドライランを実行します。
時間単位列パーティション分割テーブルに対するクエリ
時間単位の列パーティション分割テーブルに対するクエリでパーティションをプルーニングするには、パーティショニング列にフィルタを追加します。
次の例では、dataset.table
が transaction_date
列で分割されているとします。このサンプルのクエリは、2016-01-01
より前の日付を除外します。
SELECT * FROM dataset.table WHERE transaction_date >= '2016-01-01'
取り込み時間パーティション分割テーブルに対するクエリ
取り込み時間パーティション分割テーブルには、_PARTITIONTIME
という疑似列(パーティショニング列)があります。この列の値は、各行の取り込み時間(UTC)であり、TIMESTAMP
値としてパーティションの範囲(1 時間ごとや 1 日ごとなど)に切り詰められます。
たとえば、UTC 時間の 2021 年 4 月 15 日 8 時 15 分にデータを追加すると、その行の _PARTITIONTIME
列には次の値が含まれます。
- 時間単位のパーティション分割テーブル:
TIMESTAMP("2021-04-15 08:00:00")
- 日単位のパーティション分割テーブル:
TIMESTAMP("2021-04-15")
- 月単位のパーティション分割テーブル:
TIMESTAMP("2021-04-01")
- 年単位のパーティション分割テーブル:
TIMESTAMP("2021-01-01")
パーティションの粒度が日単位の場合、テーブルには _PARTITIONDATE
という名前の疑似列も含まれます。値は、DATE
値に切り詰められた _PARTITIONTIME
と同じです。
疑似列名はどちらも予約されています。その名前でテーブルに列を作成することはできません。
パーティションをプルーニングするには、これらのいずれかの列でフィルタします。たとえば、次のクエリでは、2016 年 1 月 1 日から 2016 年 1 月 2 日までのパーティションのみをスキャンします。
SELECT column FROM dataset.table WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')
_PARTITIONTIME
擬似列を選択するには、エイリアスを使用する必要があります。たとえば、次のクエリでは、エイリアス pt
を疑似列に割り当てて、_PARTITIONTIME
を選択しています。
SELECT _PARTITIONTIME AS pt, column FROM dataset.table
日単位のパーティション分割テーブルの場合も、同じ方法で _PARTITIONDATE
疑似列を選択できます。
SELECT _PARTITIONDATE AS pd, column FROM dataset.table
_PARTITIONTIME
疑似列と _PARTITIONDATE
擬似列は、SELECT *
ステートメントで返されません。それらは、明示的に選択する必要があります。
SELECT _PARTITIONTIME AS pt, * FROM dataset.table
取り込み時間パーティション分割テーブルのタイムゾーンの処理
_PARTITIONTIME
の値は、フィールドが入力されたときの UTC 日付が基準になります。UTC 以外のタイムゾーンに基づいてデータをクエリする場合は、次のいずれかの方法を選択します。
- SQL クエリのタイムゾーンの違いを調整する。
- パーティション デコレータを使用して、UTC とは異なるタイムゾーンに基づき、特定の取り込み時間パーティションにデータを読み込む。
疑似列によるパフォーマンスの向上
クエリのパフォーマンスを向上させるには、_PARTITIONTIME
疑似列を比較の左辺に単独で配置します。
たとえば、次の 2 つのクエリは同等です。テーブルのサイズによっては、2 番目のクエリの方がパフォーマンスが良くなります。_PARTITIONTIME
が >
演算子の左側に単独で配置されるためです。どちらのクエリも、処理するデータの量に違いはありません。
-- Might be slower. SELECT field1 FROM dataset.table1 WHERE TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15"); -- Often performs better. SELECT field1 FROM dataset.table1 WHERE _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);
クエリでスキャンされるパーティションを制限するには、フィルタで定数式を使用します。次のクエリでは、WHERE
句の最初のフィルタ条件に基づいてプルーニングするパーティションが制限されます。しかし、2 番目のフィルタ条件では、動的なテーブル値が使用されているため、スキャンされるパーティションは制限されません。
SELECT column FROM dataset.table2 WHERE -- This filter condition limits the scanned partitions: _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01') -- This one doesn't, because it uses dynamic table values: AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)
スキャンされるパーティションを制限するには、_PARTITIONTIME
フィルタに他の列を含めないでください。たとえば、次のクエリでは、field1
がテーブル内の列であるため、スキャンされるパーティションは制限されません。
-- Scans all partitions of table2. No pruning. SELECT field1 FROM dataset.table2 WHERE _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');
特定の時間範囲を頻繁にクエリする場合は、_PARTITIONTIME
疑似列をフィルタするビューの作成を検討してください。たとえば、次のステートメントでは、dataset.partitioned_table
という名前のテーブルから直近の 7 日間のデータのみを含むビューが作成されます。
-- This view provides pruning. CREATE VIEW dataset.past_week AS SELECT * FROM dataset.partitioned_table WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY) AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);
ビューの作成の詳細については、ビューの作成をご覧ください。
整数範囲パーティション分割テーブルに対するクエリ
整数範囲パーティション分割テーブルに対してクエリを実行するときにパーティションをプルーニングするには、整数パーティショニング列にフィルタを追加します。
次の例では、dataset.table
が customer_id:0:100:10
のパーティショニング仕様を持つ整数範囲パーティション分割テーブルであるとします。このサンプルのクエリは、30、40、50 で始まる 3 つのパーティションをスキャンします。
SELECT * FROM dataset.table WHERE customer_id BETWEEN 30 AND 50 +-------------+-------+ | customer_id | value | +-------------+-------+ | 40 | 41 | | 45 | 46 | | 30 | 31 | | 35 | 36 | | 50 | 51 | +-------------+-------+
整数範囲パーティション分割列の関数に対するパーティションのプルーニングはサポートされていません。たとえば、次のクエリはテーブル全体をスキャンします。
SELECT * FROM dataset.table WHERE customer_id + 1 BETWEEN 30 AND 50
書き込みが最適化されたストレージのデータに対してクエリを実行する
パーティション分割テーブルにストリーミングされるデータが書き込みが最適化されたストレージにある間、このデータは __UNPARTITIONED__
パーティションに一時的に保存されます。パーティション分割テーブルの特定のパーティションに直接ストリーミングされるデータでは、__UNPARTITIONED__
パーティションが使用されません。こうしたデータは、パーティションに直接ストリーミングされます。
書き込みが最適化されたストレージのデータの _PARTITIONTIME
列と _PARTITIONDATE
列に NULL
値があります。
__UNPARTITIONED__
パーティションのデータに対してクエリを実行するには、NULL
値を含む _PARTITIONTIME
疑似列を使用します。例:
SELECT column FROM dataset.table WHERE _PARTITIONTIME IS NULL
詳細については、パーティション分割テーブルへのストリーミングをご覧ください。
パーティションのプルーニングに関するベスト プラクティス
定数フィルタ式を使用する
クエリでスキャンされるパーティションを制限するには、フィルタで定数式を使用します。クエリフィルタで動的式を使用すると、BigQuery はすべてのパーティションをスキャンする必要があります。
たとえば、次のクエリでは、フィルタが定数式を含むため、パーティションがプルーニングされます。
SELECT t1.name, t2.category FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id_field = t2.field2 WHERE t1.ts = CURRENT_TIMESTAMP()
ただし、次のクエリは、フィルタ WHERE t1.ts = (SELECT timestamp from table where key = 2)
が定数式ではないため、パーティションがプルーニングされません。これは、timestamp
フィールドと key
フィールドの動的な値に依存します。
SELECT t1.name, t2.category FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id_field = t2.field2 WHERE t1.ts = (SELECT timestamp from table3 where key = 2)
フィルタでパーティション列を分離する
フィルタを表現するときに、パーティション列を分離します。複数のフィールドのデータを計算することが必要なフィルタでは、パーティションがプルーニングされません。たとえば、パーティショニング列と 2 番目のフィールドを使用して日付を比較するクエリや、いくつかのフィールドの連結が含まれるクエリでは、パーティションがプルーニングされません。
たとえば、次のフィルタでは、パーティショニングする ts
フィールドと 2 番目のフィールド ts2
に基づく計算が必要なため、パーティションはプルーニングされません。
WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2
クエリでパーティション フィルタを要求する
パーティション分割テーブルを作成するときに、[パーティション フィルタを要求] オプションを有効にすると、述語フィルタの使用を要求できます。このオプションを適用した場合、WHERE
句を指定しないでパーティション分割テーブルにクエリを実行すると、次のエラーが発生します。
Cannot query over table 'project_id.dataset.table' without a filter that can be
used for partition elimination
。
パーティションの除外の対象とみなされるフィルタのパーティション列のみを参照する述語が少なくとも 1 つ必要です。たとえば、スキーマ内の別の列 f
を含む partition_id
列にパーティション分割されたテーブルの場合、次の両方の WHERE
句はその要件を満たします。
WHERE partition_id = "20221231"
WHERE partition_id = "20221231" AND f = "20221130"
ただし、WHERE (partition_id = "20221231" OR f = "20221130")
では不十分です。
取り込み時間パーティション分割テーブルでは、_PARTITIONTIME
疑似列か _PARTITIONDATE
疑似列を使用します。
パーティション分割テーブルを作成するときに [パーティション フィルタを要求] オプションを追加する方法については、パーティション分割テーブルの作成をご覧ください。また、既存のテーブルでこの設定を更新することもできます。
次のステップ
- パーティション分割テーブルの概要については、パーティション分割テーブルの概要をご覧ください。
- パーティション分割テーブルの作成の詳細については、パーティション分割テーブルの作成をご覧ください。