Partitionierte Tabellen abfragen

Wenn Sie eine partitionierte Tabelle in BigQuery abfragen, können Sie durch Filtern nach der Partitionierungsspalte Partitionen bereinigen. Dadurch werden die gescannten Byte reduziert und die Abfragekosten gesenkt. In diesem Dokument wird beschrieben, wie Sie nach Zeiteinheiten, Aufnahmezeit und Ganzzahlbereichen partitionierte Tabellen abfragen. Außerdem werden Best Practices für das Partitions-Pruning erläutert.

Allgemeine Informationen zum Ausführen von Abfragen finden Sie unter Abfrage ausführen.

Übersicht

Wenn eine Abfrage einen qualifizierenden Filter für den Wert der Partitionierungsspalte verwendet, kann BigQuery die Partitionen scannen, die dem Filter entsprechen, und die verbleibenden Partitionen überspringen. Dieser Vorgang wird als Partitionsbereinigung bezeichnet.

Das Bereinigen von Partitionen ist der Mechanismus, mit dem BigQuery unnötige Partitionen aus dem Eingabescan entfernt. Die bereinigten Partitionen werden bei der Berechnung der von der Abfrage gescannten Byte nicht berücksichtigt. Im Allgemeinen reduziert die Bereinigung von Partitionen die Abfragekosten.

Das Verhalten bei der Bereinigung variiert für die verschiedenen Arten der Partitionierung. Daher kann es bei der Abfrage von Tabellen, die unterschiedlich partitioniert, aber ansonsten identisch sind, zu einem Unterschied bei den verarbeiteten Byte kommen. Führen Sie einen Probelauf aus, um zu schätzen, wie viele Bytes von einer Abfrage verarbeitet werden.

Nach Spalte partitionierte Tabelle mit Zeiteinheit abfragen

Fügen Sie einen Filter für die Partitionierungsspalte ein, um Partitionen zu bereinigen, wenn Sie eine nach Spalte partitionierte Tabelle mit Zeiteinheit abfragen.

Im folgenden Beispiel wird davon ausgegangen, dass dataset.table nach der Spalte transaction_date partitioniert ist. Die Beispielabfrage bereinigt Datumsangaben vor 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Nach Aufnahmezeit partitionierte Tabelle abfragen

Nach Aufnahmezeit partitionierte Tabellen enthalten eine Pseudospalte mit dem Namen _PARTITIONTIME, der Partitionierungsspalte. Der Wert der Spalte ist die UTC-Aufnahmezeit pro Zeile, gekürzt auf die Partitionsgrenze (z. B. stündlich oder täglich), als TIMESTAMP-Wert.

Wenn Sie beispielsweise Daten am 15. April 2021 um 08:15:00 Uhr UTC anhängen, enthält die _PARTITIONTIME-Spalte für diese Zeilen folgende Werte:

  • Stündlich partitionierte Tabelle: TIMESTAMP("2021-04-15 08:00:00")
  • Täglich partitionierte Tabelle: TIMESTAMP("2021-04-15")
  • Monatlich partitionierte Tabelle: TIMESTAMP("2021-04-01")
  • Jährlich partitionierte Tabelle: TIMESTAMP("2021-01-01")

Ist der Detaillierungsgrad der Partition "täglich", enthält die Tabelle auch eine Pseudospalte mit dem Namen _PARTITIONDATE. Der Wert entspricht _PARTITIONTIME, gekürzt auf einen DATE-Wert.

Beide Pseudospaltennamen sind reserviert. Sie können in keiner Ihrer Tabellen eine Spalte mit einem der Namen erstellen.

Wenn Sie Partitionen bereinigen möchten, filtern Sie nach einer dieser Spalten. Beispiel: Die folgende Abfrage scannt nur die Partitionen zwischen dem 1. Januar 2016 und 2. Januar 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Zum Auswählen der Pseudospalte _PARTITIONTIME müssen Sie einen Alias verwenden. Mit der folgenden Abfrage wählen Sie beispielsweise _PARTITIONTIME aus. Dabei wird der Pseudospalte der Alias pt zugewiesen:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Bei nach Tagen partitionierten Tabellen können Sie die Pseudospalte _PARTITIONDATE auf die gleiche Weise auswählen:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Die Pseudospalten _PARTITIONTIME und _PARTITIONDATE werden von einer SELECT *-Anweisung nicht zurückgegeben. Sie müssen sie explizit auswählen:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Zeitzonen in nach Aufnahmezeit partitionierten Tabellen organisieren

Der Wert von _PARTITIONTIME basiert auf dem UTC-Datum, zu dem das Feld ausgefüllt wurde. Wenn Sie Daten basierend auf einer anderen Zeitzone als UTC abfragen möchten, wählen Sie eine der folgenden Optionen:

  • Passen Sie die Zeitzonenunterschiede in den SQL-Abfragen an.
  • Verwenden Sie Partitions-Decorators, um Daten basierend auf einer anderen Zeitzone, als UTC in spezifische Aufnahmezeitpartitionen zu laden.

Bessere Leistung mit Pseudospalten

Verwenden Sie für eine bessere Abfrageleistung nur die Pseudospalte _PARTITIONTIME auf der linken Seite eines Vergleichs.

Die folgenden beiden Abfragen entsprechen beispielsweise einander. Je nach Tabellengröße arbeitet die zweite Abfrage möglicherweise besser, da sie _PARTITIONTIME allein auf der linken Seite des >-Operators platziert. Beide Abfragen verarbeiten die gleiche Datenmenge.

-- 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);

Verwenden Sie in Ihrem Filter einen konstanten Ausdruck, um die Partitionen zu begrenzen, die in einer Abfrage gescannt werden. In folgender Abfrage wird begrenzt, welche Partitionen basierend auf der ersten Filterbedingung in der WHERE-Klausel bereinigt werden. Die zweite Filterbedingung schränkt die gescannten Partitionen jedoch nicht ein, da sie dynamische Tabellenwerte verwendet.

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)

Wenn Sie die gescannten Partitionen begrenzen möchten, fügen Sie einem _PARTITIONTIME-Filter keine weiteren Spalten hinzu. Beispiel: In folgender Abfrage werden die gescannten Partitionen nicht begrenzt, da field1 eine Spalte in der Tabelle ist.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Wenn Sie häufig einen bestimmten Zeitbereich abfragen, sollten Sie eine Ansicht erstellen, die nach der Pseudospalte _PARTITIONTIME filtert. Mit folgender Anweisung wird beispielsweise eine Ansicht erstellt, die nur Daten aus den letzten sieben Tage aus einer Tabelle mit dem Namen dataset.partitioned_table enthält:

-- 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);

Weitere Informationen zum Erstellen von Ansichten finden Sie unter Ansichten erstellen.

Nach Ganzzahlbereich partitionierte Tabelle abfragen

Um Partitionen zu bereinigen, wenn Sie eine nach Ganzzahlbereich partitionierte Tabelle abfragen, fügen Sie einen Filter für die Ganzzahl-Partitionierungsspalte ein.

Gehen Sie im folgenden Beispiel davon aus, dass dataset.table eine nach Ganzzahlbereich partitionierte Tabelle mit der Partitionierungsspezifikation customer_id:0:100:10 ist. Die Beispielabfrage scannt die drei Partitionen, die mit 30, 40 und 50 beginnen.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

Derzeit wird die Partitionsbeschneidung für Funktionen zu einer nach Ganzzahlbereich partitionierten Spalte nicht unterstützt. Beispiel: Die folgende Abfrage scannt die gesamte Tabelle.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Daten im schreiboptimierten Speicher abfragen

Die Partition __UNPARTITIONED__ enthält vorübergehend diejenigen Daten, die über den schreiboptimierten Speicher in eine partitionierte Tabelle gestreamt werden. Daten, die direkt in eine bestimmte Partition einer partitionierten Tabelle gestreamt werden, verwenden nicht die Partition __UNPARTITIONED__. Stattdessen werden die Daten direkt in die Partition gestreamt.

Daten im schreiboptimierten Speicher haben NULL-Werte in den Spalten _PARTITIONTIME und _PARTITIONDATE.

Zum Abfragen von Daten in der Partition __UNPARTITIONED__ verwenden Sie die Pseudospalte _PARTITIONTIME mit dem Wert NULL. Beispiel:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Weitere Informationen finden Sie unter Streaming in partitionierte Tabellen.

Best Practices für die Partitionsbereinigung

In diesem Abschnitt werden Best Practices für das Schreiben von Abfragen beschrieben, bei denen die Partitionsbereinigung verwendet wird, um die Abfrageleistung zu optimieren und die Kosten zu senken.

Konstanten Filterausdruck verwenden

Wenn Sie die Partitionen begrenzen möchten, die in einer Abfrage gescannt werden, filtern Sie die Partitionierungsspalte mit einem konstanten Ausdruck anstelle eines dynamischen Ausdrucks.

Mit der folgenden Abfrage werden Partitionen bereinigt:

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Im Vergleich dazu werden in der folgenden Abfrage keine Partitionen bereinigt, da das Prädikat WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2) kein konstanter Ausdruck ist. In dieser Abfrage wird die Partitionierungsspalte mit einem dynamischen Wert verglichen, was das Pruning von Partitionen verhindert.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

Außerdem werden bei einer Abfrage mit den folgenden Prädikaten keine Partitionen ausgeschlossen, da sie eine Berechnung basierend auf einer zweiten, nicht konstanten Tabellenspalte ts2 oder duration erfordern:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Partitionsspalte isolieren oder unterstützte Funktionen verwenden

Zum Bereinigen von Partitionen müssen Filterbedingungen so strukturiert sein, dass BigQuery bestimmen kann, welche Partitionen gescannt werden sollen, ohne Tabellendaten zu lesen. Isolieren Sie dazu die Partitionierungsspalte auf einer Seite eines Vergleichsoperators oder umschließen Sie die Spalte nur mit einer unterstützten integrierten Funktion. Mit einem Probelauf können Sie prüfen, ob das Partitions-Pruning für Ihre Abfrage unterstützt wird.

Die folgenden integrierten Funktionen in der Partitionierungsspalte unterstützen das Bereinigen von Partitionen, wenn ihre zusätzlichen Argumente konstant sind:

Andere Funktionen und komplexe mathematische Vorgänge erfordern einen vollständigen Tabellenscan.

Beispiele

Die folgenden Abfragen zeigen Beispielprädikate, die das Partitions-Pruning unterstützen.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

In der folgenden Abfrage werden alle Partitionen übersprungen, da das Prädikat mit keiner Zeile übereinstimmt.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

Mit der folgenden Abfrage wird der erste Tag jedes Monats in der Tabelle ausgewählt. Sie unterstützt das Partitions-Pruning. 

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

Bei Abfragen mit den folgenden Prädikaten werden Partitionen nicht bereinigt, da sie die Partitionierungsspalte mit nicht unterstützten Funktionen bearbeiten:

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

Eine Abfrage mit dem folgenden Prädikat bereinigt ebenfalls keine Partitionen, da die Partitionierungsspalte mit einem arithmetischen Vorgang manipuliert wird: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Wenn Sie das Partitions-Pruning aktivieren möchten, müssen Sie den Ausdruck umschreiben, indem Sie die Partitionierungsspalte ts von den nicht unterstützten Funktionen oder arithmetischen Vorgängen isolieren. Verwenden Sie für Zeiträume >= und <, um den genauen Zeitraum zu erfassen. Verschieben Sie bei arithmetischen Operationen die Operation auf die andere Seite des Vergleichs.

Die folgende Abfrage ermöglicht das Partitions-Pruning, indem die Partitionierungsspalte ts für einen Zeitraum isoliert wird:

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Die folgende Abfrage ermöglicht das Partitions-Pruning, indem die Partitionierungsspalte vom arithmetischen Vorgang isoliert wird:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Nach mehreren Spalten filtern

Ein Prädikat für die Partitionierungsspalte in einer Abfrage schränkt nicht ein, wonach Sie sonst noch filtern können. Sie können Prädikate für andere Spalten in dieselbe WHERE-Klausel einfügen. Die Partitionsbereinigung erfolgt weiterhin, solange die Bedingung, die die Partitionierungsspalte auswertet, den Best Practices entspricht. Beachten Sie, dass AND im folgenden Beispiel wichtig ist. Wenn AND in OR geändert wird, funktioniert das Partitions-Pruning nicht, da eine Partition auch dann nicht bereinigt werden kann, wenn sie nicht mit dem Prädikat in der Partitionierungsspalte übereinstimmt. Daten in diesen Partitionen mit meter_id = 1234 sind weiterhin für die Abfrage geeignet.

Die Prädikate müssen nicht in einer bestimmten Reihenfolge geschrieben werden. In der folgenden Beispielabfrage wird davon ausgegangen, dass die Partitionierung nach der Spalte ts erfolgt. Die Partitionsbereinigung erfolgt unabhängig von der Platzierung des Prädikats.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Partitionsfilter in Abfragen anfordern

Wenn Sie eine partitionierte Tabelle erstellen, können Sie die Option Partitionsfilter anfordern aktivieren und damit die Verwendung eines Prädikatfilters voraussetzen. Wenn diese Option aktiviert ist, führen Versuche, die partitionierte Tabelle ohne Angabe einer WHERE-Klausel abzufragen, zu folgendem Fehler:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Diese Anforderung gilt auch für Abfragen von Ansichten und materialisierten Ansichten, die auf die partitionierte Tabelle verweisen.

Es muss mindestens ein Prädikat vorhanden sein, das nur auf eine Partitionierungsspalte verweist, damit der Filter für die Partitionseliminierung infrage kommt. Für eine Tabelle, die nach der Spalte partition_id partitioniert ist und eine zusätzliche Spalte f im Schema hat, erfüllen beide der folgenden WHERE-Klauseln die Anforderung:

WHERE partition_id = "20221231"

WHERE partition_id = "20221231" AND f = "20221130"

Folgendes ist jedoch nicht ausreichend und führt zu einem Fehler:

WHERE partition_id = "20221231" OR f = "20221130"

Verwenden Sie für nach Aufnahmezeit partitionierte Tabellen eine der Pseudospalten _PARTITIONTIME oder _PARTITIONDATE.

Weitere Informationen zum Einbinden der Option Partitionsfilter anfordern beim Erstellen einer partitionierten Tabelle finden Sie unter Partitionierte Tabellen erstellen. Sie können diese Einstellung auch für eine vorhandene Tabelle aktualisieren.

Nächste Schritte