パーティション分割テーブルに対するクエリ

このドキュメントでは、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 疑似列を使用します。

パーティション分割テーブルを作成するときに [パーティション フィルタを要求] オプションを追加する方法については、パーティション分割テーブルの作成をご覧ください。また、既存のテーブルでこの設定を更新することもできます。

次のステップ