Einführung in partitionierte Tabellen

Diese Seite bietet eine Übersicht über partitionierte Tabellen in BigQuery.

Bei einer partitionierten Tabelle handelt es sich um eine spezielle Tabelle, die in Segmente, die als Partitionen bezeichnet werden, unterteilt ist, wodurch sich die Daten einfacher verwalten und abfragen lassen. Durch das Unterteilen einer großen Tabelle in kleinere Partitionen können die Abfrageleistung gesteigert und die Kosten unter Kontrolle gehalten werden, da so die Anzahl der von einer Abfrage gelesenen Bytes verringert wird.

Sie können BigQuery-Tabellen partitionieren nach:

  • Spalte "Zeiteinheit": Tabellen werden nach einer Spalte der Art TIMESTAMP, DATE oder DATETIME in der Tabelle partitioniert.

  • Aufnahmezeit: Tabellen werden nach dem Zeitstempel der Aufnahme der Daten in BigQuery partitioniert.

  • Ganzzahlbereich: Die Tabellen werden nach einer Spalte mit Ganzzahlen partitioniert.

Wenn bei einer Abfrage nach dem Wert der Partitionierungsspalte gefiltert wird, kann BigQuery die Partitionen scannen, die mit dem Filter übereinstimmen, und die übrigen Partitionen überspringen. Dieser Vorgang wird Pruning genannt.

Spaltenpartitionierung nach Zeiteinheit

Sie können Tabellen anhand der Spalten DATE, TIMESTAMP oder DATETIME partitionieren. Wenn Sie Daten in die Tabelle schreiben, überträgt BigQuery die Daten anhand der Werte in der Spalte automatisch in die richtige Partition.

Bei den Spalten TIMESTAMP und DATETIME können die Partitionen entweder einen stündlichen, täglichen, monatlichen oder jährlichen Detaillierungsgrad haben. Bei DATE-Spalten können die Partitionen täglich, monatlich oder jährlich einen Detaillierungsgrad haben. Die Grenzen der Partitionen basieren auf der UTC-Zeit.

Angenommen, Sie partitionieren eine Tabelle nach einer DATETIME-Spalte mit monatlicher Partitionierung. Wenn Sie die folgenden Werte in die Tabelle einfügen, werden die Zeilen in die folgenden Partitionen geschrieben:

Spaltenwert Partition (monatlich)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

Außerdem werden zwei besondere Partitionen erstellt:

  • __NULL__: Enthält Zeilen mit NULL-Werten in der Partitionierungsspalte.
  • __UNPARTITIONED__: Enthält Zeilen, in denen der Wert der Partitionierungsspalte vor 1960-01-01 oder nach 2159-12-31 liegt.

Partitionierung nach Aufnahmezeit

Wenn Sie eine Tabelle erstellen, die nach Aufnahmezeit partitioniert ist, weist BigQuery den Zeilen automatisch Partitionen zu dem Zeitpunkt zu, an dem BigQuery die Daten aufnimmt. Sie können die Granularität pro Stunde, täglich, monatlich oder jährlich für die Partitionen auswählen. Partitionsgrenzen basieren auf der UTC-Zeit.

Eine nach Aufnahmezeit partitionierte Tabelle hat eine Pseudospalte mit dem Namen _PARTITIONTIME. Der Wert dieser Spalte ist die Aufnahmezeit für jede Zeile, gekürzt auf die Partitionsgrenze (z. B. stündlich oder täglich). Angenommen, Sie erstellen eine nach Aufnahmezeit partitionierte Tabelle mit stündlicher Partitionierung und senden Daten zu den folgenden Zeiten:

Aufnahmezeit _PARTITIONTIME Partition (stündlich)
2021-05-07 17:22:00 2021-05-07 17:00:00 2021050717
2021-05-07 17:40:00 2021-05-07 17:00:00 2021050717
2021-05-07 18:31:00 2021-05-07 18:00:00 2021050718

Da die Tabelle in diesem Beispiel die stündliche Partitionierung verwendet, wird der Wert von _PARTITIONTIME auf eine Stundengrenze gekürzt. BigQuery verwendet diesen Wert, um die richtige Partition für die Daten zu ermitteln.

Sie können auch Daten in eine bestimmte Partition schreiben. Sie können beispielsweise Verlaufsdaten laden oder an Zeitzonen anpassen. Es kann ein beliebiges gültiges Datum zwischen 0001-01-01 und 9999-12-31 verwendet werden. Allerdings können DML-Anweisungen nicht auf Datumsangaben vor 1970-01-01 oder nach 2159-12-31 verweisen. Weitere Informationen finden Sie unter Daten in eine bestimmte Partition schreiben.

Anstelle von _PARTITIONTIME können Sie auch _PARTITIONDATE verwenden. Die Pseudospalte _PARTITIONDATE enthält das UTC-Datum, das dem Wert in der Pseudospalte _PARTITIONTIME entspricht.

Partitionierung nach Ganzzahlbereich

Sie können eine Tabelle basierend auf Wertebereichen in einer bestimmten Spalte vom Typ INTEGER partitionieren. Um eine nach Ganzzahlbereich partitionierte Tabelle zu erstellen, geben Sie Folgendes an:

  • Die Partitionierungsspalte.
  • Der Startwert für die Bereichspartitionierung (einschließlich).
  • Der Endwert für die Bereichspartitionierung (ausschließlich).
  • Das Intervall jedes Bereichs der Partition.

Angenommen, Sie erstellen eine Partition nach Ganzzahlbereich mit der folgenden Spezifikation:

Argument Wert
Spaltenname customer_id
start 0
end 100
interval 10

Die Tabelle ist nach der Spalte customer_id in Intervallen von 10 partitioniert. Die Werte 0 bis 9 gehen in eine Partition, die Werte 10 bis 19 in die nächste Partition und so weiter bis zu 99. Werte außerhalb dieses Bereichs werden in eine Partition mit dem Namen __UNPARTITIONED__ geschrieben. Alle Zeilen, in denen customer_id den Wert NULL hat, werden in eine Partition mit dem Namen __NULL__ geleitet.

Tages-, Stunden-, Monats- oder Jahrespartitionierung wählen.

Wenn Sie eine Tabelle nach einer der Spalten Zeiteinheit oder Aufnahmezeit unterteilen, können Sie auswählen, ob die Partitionen täglich, stündlich, monatlich oder jährlich sind.

  • Die tägliche Partitionierung ist der Standardpartitionierungstyp. Die tägliche Partitionierung ist eine gute Wahl, wenn Ihre Daten über einen großen Zeitraum verteilt sind oder kontinuierlich im Laufe der Zeit hinzugefügt werden.

  • Wählen Sie die stündliche Partitionierung, wenn Ihre Tabellen ein großes Datenvolumen haben, das einen kurzen Zeitraum umfasst (in der Regel Zeitstempelwerte in einem Bereich von weniger als sechs Monaten). Wenn Sie eine stündliche Partitionierung wählen, muss die Anzahl der Partitionen innerhalb der Partitionslimits bleiben.

  • Wählen Sie eine monatliche oder jährliche Partitionierung aus, wenn Ihre Tabellen täglich eine relativ kleine Datenmenge über einen großen Zeitraum enthalten. Diese Partitionierungsoption wird auch empfohlen, wenn Ihr Workflow häufig Aktualisierungen oder das Hinzufügen von Zeilen mit einem großen Zeitraum (z. B. mehr als 500 Datumsangaben) erfordert. Verwenden Sie in diesen Szenarien die monatliche oder jährliche Partitionierung mit Clustering der Partitionierungsspalte, um die beste Leistung zu erzielen. Weitere Informationen finden Sie auf dieser Seite unter Partitionierung und Clustering.

Partitionierung und Clustering

Partitionierung und Clustering können die Leistung verbessern und die Abfragekosten reduzieren.

Verwenden Sie Clustering unter folgenden Umständen:

  • Für die Abfrage ist keine strikte Kostengarantie erforderlich.
  • Sie benötigen mehr Granularität, als die Partitionierung allein zulässt. Wenn Sie zusätzlich zu den Partitionierungsvorteilen Clustering-Vorteile erhalten möchten, können Sie für die Partitionierung und das Clustering dieselbe Spalte verwenden.
  • Ihre Abfragen verwenden häufig Filter oder eine Zusammenfassung für mehrere bestimmte Spalten.
  • Die Kardinalität der Anzahl an Werten in einer Spalte oder Gruppe von Spalten ist groß.

Unter den folgenden Umständen sollten Sie die Partitionierung verwenden:

  • Sie möchten die Abfragekosten kennen, bevor die Abfrage ausgeführt wird. Die Partitionen werden vor der Ausführung der Abfrage bereinigt, wodurch Sie in einem Probelauf die nach der Bereinigung anfallenden Kosten ermitteln können. Die Clusterbereinigung erfolgt während der Ausführung der Abfrage, sodass die Kosten erst nach Abschluss der Abfrage bekannt sind.

  • Sie benötigen eine Verwaltung auf Partitionsebene. Sie können beispielsweise eine Ablaufzeit für Partitionen festlegen, Daten in eine bestimmte Partition laden oder Partitionen löschen wollen.

  • Sie möchten angeben, wie die Daten partitioniert werden und welche Daten jede Partition enthält. Sie können beispielsweise eine Granularität definieren oder die Bereiche definieren, die zum Partitionieren der Tabelle für die Ganzzahlbereichs-Partitionierung verwendet werden.

Unter den folgenden Umständen sollten Sie Clustering gegenüber einer Partitionierung bevorzugen:

  • Die Partitionierung führt zu einer kleinen Datenmenge pro Partition (ungefähr weniger als 1 GB).
  • Die Partitionierung führt zu einer großen Anzahl von Partitionen, die über die Limits für partitionierte Tabellen hinausgehen.
  • Die Partitionierung führt dazu, dass die Mehrheit der Partitionen in der Tabelle häufig geändert wird (z. B. alle paar Minuten).

Sie können auch Partitionierung und Clustering kombinieren. Die Daten werden zuerst partitioniert und dann pro Partition nach den Clustering-Spalten geclustert.

Bei der Abfrage der Tabelle werden die Abfragekosten durch die Partitionierung anhand der Partitionierungsbereinigung begrenzt. Durch das Pruning des Clusters können weitere Senkungen der Abfragekosten möglich werden, wenn die Abfrage tatsächlich ausgeführt wird.

Unterschiede zwischen Partitionierung und Fragmentierung

Bei der Fragmentierung werden Daten in mehreren Tabellen mit einem Namenspräfix wie [PREFIX]_YYYYMMDD gespeichert.

Partitionierung wird gegenüber Tabellenfragmentierung empfohlen, da partitionierte Tabellen eine bessere Leistung erzielen. Bei fragmentierten Tabellen muss BigQuery eine Kopie des Schemas und der Metadaten für jede Tabelle verwalten. BigQuery muss möglicherweise auch die Berechtigungen für jede abgefragte Tabelle prüfen. Dieser Mehraufwand bei der Durchführung der Abfrage wirkt sich direkt auf die Abfrageleistung aus.

Wenn Sie bereits datumsfragmentierte Tabellen erstellt haben, können Sie diese in eine nach Aufnahmezeit partitionierte Tabelle konvertieren. Weitere Informationen finden Sie unter Datumsfragmentierte Tabellen in nach Aufnahmezeit partitionierte Tabellen konvertieren.

Beschränkungen

Sie können Legacy-SQL nicht zum Abfragen partitionierter Tabellen oder zum Schreiben von Abfrageergebnissen in partitionierte Tabellen verwenden.

Nach Zeiteinheitsspalte partitionierte Tabellen unterliegen den folgenden Einschränkungen:

  • Die Partitionierungsspalte muss eine skalare Spalte vom Typ DATE, TIMESTAMP oder DATETIME sein. Der Modus der Spalte kann REQUIRED oder NULLABLE, jedoch nicht REPEATED (arraybasiert) sein.
  • Die Partitionierungsspalte muss ein Feld der obersten Ebene sein. Blattfelder aus einem RECORD (STRUCT) können nicht als Partitionierungsspalte verwendet werden.

Nach Ganzzahlbereich partitionierte Tabellen unterliegen den folgenden Einschränkungen:

  • Die Partitionierungsspalte muss eine INTEGER-Spalte sein. Der Modus der Spalte kann REQUIRED oder NULLABLE sein, nicht jedoch REPEATED (arraybasiert).
  • Die Partitionierungsspalte muss ein Feld der obersten Ebene sein. Blattfelder aus einem RECORD (STRUCT) können nicht als Partitionierungsspalte verwendet werden.

Kontingente und Limits partitionierter Tabellen

Partitionierte Tabellen unterliegen in BigQuery festen Limits.

Kontingente und Beschränkungen gelten auch für die verschiedenen Arten von Jobs, die für partitionierte Tabellen ausgeführt werden können, einschließlich:

Weitere Informationen zu allen Kontingenten und Limits finden Sie unter Kontingente und Limits.

Preise für partitionierte Tabellen

Die Kosten für die Erstellung und Verwendung von partitionierten Tabellen in BigQuery richten sich nach der Menge der in den Partitionen gespeicherten Daten und danach, welche Abfragen für die Daten ausgeführt werden.

Viele Vorgänge für partitionierte Tabellen sind kostenlos, einschließlich Laden von Daten in Partitionen, Kopieren von Partitionen und Datenexport aus Partitionen. Obwohl diese Vorgänge kostenlos sind, unterliegen sie den Kontingenten und Begrenzungen von BigQuery. Weitere Informationen zu allen kostenlosen Vorgängen finden Sie in der Preisübersicht unter Kostenlose Vorgänge.

Sicherheit partitionierter Tabellen

Die Zugriffssteuerung für partitionierte Tabellen entspricht der Zugriffssteuerung für Standardtabellen. Weitere Informationen finden Sie unter Einführung in die Tabellenzugriffssteuerung.

Mehr zur Verwendung der Play Console erfahren