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

Konstanten Filterausdruck verwenden

Verwenden Sie in Ihrem Filter einen konstanten Ausdruck, um die Partitionen zu begrenzen, die in einer Abfrage gescannt werden. Wenn Sie in Ihrem Abfragefilter dynamische Ausdrücke verwenden, muss BigQuery alle Partitionen scannen.

Beispiel: Die folgende Abfrage bereinigt Partitionen, da der Filter einen konstanten Ausdruck enthält:

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

Die folgende Abfrage bereinigt jedoch keine Partitionen, da der Filter WHERE t1.ts = (SELECT timestamp from table where key = 2) kein konstanter Ausdruck ist. Dies hängt von den dynamischen Werten der Felder timestamp und key ab:

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)

Partitionsspalte in Ihrem Filter isolieren

Isolieren Sie die Partitionsspalte, wenn Sie einen Filter angeben. Filter, die Daten aus mehreren Feldern zur Berechnung benötigen, bereinigen Partitionen nicht. Zum Beispiel erfolgt keine Partitionsbereinigung bei Abfragen mit einem Datumsvergleich, die die Partitionierungsspalte und ein zweites Feld verwenden, oder bei Abfragen, die einige Feldverkettungen enthalten.

Beispielsweise bereinigt der folgende Filter keine Partitionen, da er eine Berechnung basierend auf dem Feld ts der Partitionierung und einem zweiten Feld ts2 erfordert:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

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.

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

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

WHERE (partition_id = "20221231" OR f = "20221130") ist jedoch nicht ausreichend.

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