Partitionierte Tabellen abfragen

In diesem Dokument werden einige spezifische Überlegungen zum Abfragen von partitionierten Tabellen in BigQuery beschrieben.

Allgemeine Informationen zu Abfragen in BigQuery finden Sie unter Interaktive Abfragen und Batchabfragen 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 zum Schreiben von Abfragen beschrieben, die die Partitionsbereinigung nutzen, um die Abfrageleistung zu optimieren und 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.

Die folgende Abfrage bereinigt Partitionen:

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

Im Vergleich dazu bereinigt die folgende Abfrage keine Partitionen, da das Prädikat WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2) kein konstanter Ausdruck ist. Bei dieser Abfrage wird die Partitionierungsspalte mit einem dynamischen Wert verglichen, wodurch die Partitionsbereinigung verhindert wird.

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 bereinigt, da eine Berechnung basierend auf einer zweiten, nicht konstanten Tabellenspalte ts2 oder duration erforderlich ist:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Partitionierungsspalte isolieren oder unterstützte Funktionen verwenden

Damit Partitionen bereinigt werden können, müssen die Filterbedingungen so strukturiert sein, dass BigQuery ermitteln kann, welche Partitionen gescannt werden müssen, 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 die Partitionsbereinigung für Ihre jeweilige Abfrage unterstützt wird.

Die folgenden integrierten Funktionen für die Partitionierungsspalte unterstützen die Partitionsbereinigung, wenn ihre zusätzlichen Argumente konstant sind:

Für andere Funktionen und komplexe mathematische Operationen ist ein vollständiger Tabellenscan erforderlich.

Beispiele

Die folgenden Abfragen zeigen Beispielprädikate, die die Partitionsbereinigung 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';

Die folgende Abfrage überspringt alle Partitionen, da das Prädikat mit keiner Zeile übereinstimmt.

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

Die folgende Abfrage wählt den ersten Tag jedes Monats in der Tabelle aus und unterstützt die Partitionsbereinigung. 

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 keine Partitionen bereinigt, da die Partitionierungsspalte mit nicht unterstützten Funktionen bearbeitet wird:

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

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

Ebenso werden bei einer Abfrage mit dem folgenden Prädikat keine Partitionen bereinigt, da die Partitionierungsspalte mit einer arithmetischen Operation bearbeitet wird: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Wenn Sie die Partitionsbereinigung aktivieren möchten, müssen Sie den Ausdruck neu schreiben, indem Sie die Partitionierungsspalte ts von den nicht unterstützten Funktionen oder arithmetischen Operationen isolieren. Verwenden Sie für Zeitbereiche >= und <, um den genauen Bereich zu erfassen. Verschieben Sie bei arithmetischen Operationen die Operation auf die andere Seite des Vergleichs.

Die folgende Abfrage ermöglicht die Partitionsbereinigung, 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 die Partitionsbereinigung, indem die Partitionierungsspalte von der arithmetischen Operation 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, nach was 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 die Partitionsbereinigung nicht. Auch wenn eine Partition nicht mit dem Prädikat für die Partitionierungsspalte übereinstimmt, kann sie nicht bereinigt werden. 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, bei der die Partitionierung nach der Spalte ts erfolgt, wird die Partitionsbereinigung unabhängig von der Platzierung des Prädikats durchgeführt.

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 für Ansichten und materialisierte Ansichten, die auf die partitionierte Tabelle verweisen.

Hinweis: Es muss mindestens ein Prädikat vorhanden sein, das nur auf eine Partitionierungsspalte verweist, damit der Filter für die Eliminierung der Partition infrage kommt. Für eine nach der Spalte partition_id partitionierte Tabelle mit einer zusätzlichen Spalte f in ihrem Schema erfüllen beide folgenden WHERE-Klauseln die Anforderung:

WHERE partition_id = "20221231"

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

Die folgende Klausel reicht jedoch nicht aus 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