Einführung in partitionierte Tabellen

Eine partitionierte Tabelle ist in Segmente unterteilt, die als Partitionen bezeichnet werden. Die Daten einer partitionierten Tabelle lassen sich einfacher verwalten und abfragen. Durch das Gliedern großer Tabellen in kleinere Partitionen werden Abfragen effizienter. Außerdem verringern sich so die Kosten, weil pro Abfrage weniger Byte gelesen werden müssen. Zum Partitionieren von Tabellen geben Sie eine Partitionsspalte an, die zum Segmentieren der Tabelle verwendet wird.

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 Pruning genannt.

In einer partitionierten Tabelle werden Daten in physischen Blöcken gespeichert, die jeweils eine Datenpartition enthalten. Die einzelnen partitionierten Tabellen verwalten verschiedene Metadaten zu den Sortiereigenschaften über alle Vorgänge hinweg, die sie ändern. Dank der Metadaten kann BigQuery die Abfragekosten genauer schätzen, bevor eine Abfrage ausgeführt wird.

Wann sollte die Partitionierung verwendet werden?

Ziehen Sie die Partitionierung einer Tabelle in den folgenden Szenarien in Betracht:

  • Sie möchten die Abfrageleistung verbessern, indem Sie nur einen Teil einer Tabelle scannen.
  • Der Tabellenvorgang überschreitet ein Standardkontingent für Tabellen und Sie können die Tabellenvorgänge auf bestimmte Partitionsspaltenwerte beschränken, um höhere Kontingente für partitionierte Tabellen zu gewähren.
  • Sie möchten die Abfragekosten ermitteln, bevor die Abfrage ausgeführt wird. BigQuery bietet Kostenschätzungen für Abfragen, bevor die Abfrage für eine partitionierte Tabelle ausgeführt wird. Berechnen Sie eine Schätzung der Abfragekosten, indem Sie eine partitionierte Tabelle bereinigen und dann einen Abfrageprobelauf ausführen, um die Abfragekosten zu schätzen.
  • Sie möchten eine der folgenden Verwaltungsfunktionen auf Partitionsebene:

Unter den folgenden Umständen sollten Sie anstelle des Partitionierens das Clustering einer Tabelle in Betracht ziehen:

  • Sie benötigen mehr Granularität, als die Partitionierung zulässt.
  • Ihre Abfragen verwenden häufig Filter oder eine Zusammenfassung für mehrere Spalten.
  • Die Kardinalität der Anzahl an Werten in einer Spalte oder Gruppe von Spalten ist groß.
  • Für die Abfrageausführung sind keine strengen Kostenschätzungen erforderlich.
  • Die Partitionierung führt zu einer kleinen Datenmenge pro Partition (ungefähr weniger als 10 GB). Durch das Erstellen vieler kleiner Partitionen werden die Metadaten der Tabelle vergrößert, was sich auf die Zugriffszeiten auf die Metadaten bei Abfragen der Tabelle auswirken kann.
  • Die Partitionierung führt zu einer großen Anzahl von Partitionen, die über die Limits für partitionierte Tabellen hinausgehen.
  • Die DML-Vorgänge ändern häufig die meisten Partitionen in der Tabelle (z. B. alle paar Minuten).

In solchen Fällen können Sie mit Tabellen-Clustering Abfragen beschleunigen, indem Sie Daten anhand von benutzerdefinierten Sortierattributen in bestimmten Spalten clustern.

Sie können auch Clustering und Tabellenpartitionierung kombinieren, um eine detailliertere Sortierung zu erreichen. Weitere Informationen zu diesem Ansatz finden Sie unter Geclusterte und partitionierte Tabelle kombinieren.

Partitionierungstypen

In diesem Abschnitt werden die verschiedenen Möglichkeiten zum Partitionieren von Tabellen beschrieben.

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 usw. 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 namens __NULL__ geschrieben.

Weitere Informationen zu nach Ganzzahlenbereich partitionierten Tabellen finden Sie unter Nach Ganzzahlbereich partitionierte Tabelle erstellen.

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.

Weitere Informationen zu nach Spalte partitionierte Tabellen mit Zeiteinheit finden Sie unter Nach Spalte partitionierte Tabelle mit Zeiteinheit erstellen.

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.

Wenn Ihre Daten bei Verwendung einer feineren Zeitgranularität die maximale Anzahl von Partitionen pro Tabelle erreichen können, verwenden Sie stattdessen einen gröberen Detaillierungsgrad. Sie können beispielsweise nach Monat statt nach Tag partitionieren, um die Anzahl der Partitionen zu reduzieren. Sie können die Partitionsspalte auch clustern, um die Leistung weiter zu verbessern.

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.

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). Achten Sie bei der stündlichen Partitionierung darauf, dass die Partitionszahl innerhalb der Partitionslimits bleibt.

  • 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 in diesem Dokument unter Geclusterte und partitionierte Tabelle kombinieren.

Geclusterte und partitionierte Tabellen kombinieren

Sie können die Tabellenpartitionierung mit Tabellen-Clustering kombinieren, um eine detaillierte Sortierung für die weitere Abfrageoptimierung zu erreichen.

Eine geclusterte Tabelle enthält geclusterte Spalten, die Daten basierend auf benutzerdefinierten Sortierattributen sortieren. Daten innerhalb dieser geclusterten Spalten werden in Speicherblöcke sortiert, deren Größe an die Größe der Tabelle angepasst ist. Wenn Sie eine Abfrage ausführen, die nach der geclusterten Spalte filtert, scannt BigQuery nur die relevanten Blöcke anhand der geclusterten Spalten und nicht der gesamten Tabelle oder Tabellenpartition. Bei einem kombinierten Ansatz, der die Tabellenpartitionierung und das Clustering verwendet, segmentieren Sie zuerst die Tabellendaten in Partitionen und gruppieren dann die Daten innerhalb jeder Partition nach den Clustering-Spalten.

Wenn Sie eine geclusterte und partitionierte Tabelle erstellen, so ermöglicht das eine genauere Sortierung, wie im folgenden Diagramm dargestellt:

Nicht geclusterte oder partitionierte Tabellen mit Tabellen vergleichen, die geclustert und partitioniert sind.

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.

Partitions-Decorator

Mit Partition-Decorators können Sie auf eine Partition in einer Tabelle verweisen. Sie können damit beispielsweise Daten in eine bestimmte Partition schreiben.

Ein Partitions-Decorator hat die Form table_name$partition_id, wobei das Format des Segments partition_id vom Partitionierungstyp abhängt:

Partitionierungstyp Format Beispiel
Stündlich yyyymmddhh my_table$2021071205
Täglich yyyymmdd my_table$20210712
Monatlich yyyymm my_table$202107
Jährlich yyyy my_table$2021
Ganzzahlbereich range_start my_table$40

In den Daten einer Partition suchen

Verwenden Sie den Befehl bq head mit einem Partitions-Decorator, um in den Daten einer bestimmten Partition zu suchen.

Mit dem folgenden Befehl werden beispielsweise alle Felder in den ersten 10 Zeilen von my_dataset.my_table in der Partition vom 24.02.2018 aufgeführt.

    bq head --max_rows=10 'my_dataset.my_tablee$20180224'

Tabellendaten exportieren

Der Export sämtlicher Daten einer partitionierten Tabelle erfolgt auf die gleiche Weise wie der Datenexport einer nicht partitionierten Tabelle. Weitere Informationen hierzu finden Sie unter Tabellendaten exportieren.

Zum Exportieren von Daten aus einer einzelnen Partition verwenden Sie den bq extract-Befehl und hängen den Partitions-Decorator an den Tabellennamen an. Beispiel: my_table$20160201. Wenn Sie Daten aus den Partitionen __NULL__ und __UNPARTITIONED__ exportieren möchten, hängen Sie die Partitionsnamen an den Tabellennamen an. Beispiel: my_table$__NULL__ oder my_table$__UNPARTITIONED__.

Beschränkungen

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

BigQuery unterstützt die Partitionierung nach mehreren Spalten nicht. Pro Tabelle kann nur eine Spalte partitioniert werden.

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.

Weitere Informationen zu nach Spalte partitionierte Tabellen mit Zeiteinheit finden Sie unter Nach Spalte partitionierte Tabelle mit Zeiteinheit erstellen.

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.

Weitere Informationen zu nach Ganzzahlenbereich partitionierten Tabellen finden Sie unter Nach Ganzzahlbereich partitionierte Tabelle erstellen.

Kontingente und Limits

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 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.

Weitere Informationen zur Kostenkontrolle in BigQuery finden Sie unter Kosten in BigQuery kontrollieren.

Tabellensicherheit

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

Nächste Schritte