Einführung in partitionierte Tabellen

Diese Seite bietet einen Überblick über die Unterstützung partitionierter 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:

  • Aufnahmezeit: Tabellen werden basierend auf der Aufnahmezeit (Ladezeit) oder Ankunftszeit der Daten partitioniert.

  • Datum/Zeitstempel: Die Tabellen werden nach einer Spalte mit TIMESTAMP- oder DATE-Werten partitioniert.

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

Nach Aufnahmezeit partitionierte Tabellen

Wenn Sie eine Tabelle erstellen, die nach Aufnahmezeit partitioniert ist, lädt BigQuery automatisch Daten in tägliche datumsbasierte Partitionen, die die Aufnahme- oder Eingangszeit der Daten widerspiegeln. Mit Pseudospalten- und Suffix-Bezeichnern können Sie Daten korrigieren (ersetzen) und an Partitionen für einen bestimmten Tag weiterleiten.

Nach Aufnahmezeit partitionierte Tabellen beinhalten eine Pseudospalte mit dem Namen _PARTITIONTIME, die einen datumsbasierten Zeitstempel für die Daten, die in die Tabelle geladen werden, enthält. Abfragen für nach Aufnahmezeit partitionierte Tabellen können die gelesenen Daten durch _PARTITIONTIME-Filter beschränken, die den Speicherort einer Partition abbilden. Alle Daten in der angegebenen Partition werden von der Abfrage gelesen, der Prädikatfilter _PARTITIONTIME beschränkt jedoch die Anzahl der Partitionen, die gescannt werden.

Wenn Sie nach Aufnahmezeit partitionierte Tabellen erstellen, haben die Partitionen die gleiche Schemadefinition wie die Tabelle. Wenn Sie Daten in eine Partition mit einem Schema laden müssen, das nicht mit dem Schema der Tabelle übereinstimmt, müssen Sie das Schema der Tabelle vor dem Laden der Daten aktualisieren. Alternativ können Sie Tabellenschemas ändern, um das Schema der Tabelle im Rahmen eines Lade- oder Abfragejobs zu aktualisieren.

Nach Datum oder Zeitstempel partitionierte Tabellen

BigQuery erlaubt auch partitionierte Tabellen, die auf einer bestimmten DATE- oder TIMESTAMP-Spalte basieren. Anhand des Datumswerts der Partitionierungsspalte (ausgedrückt in UTC) werden Daten automatisch an die entsprechende Partition einer nach Datum/Zeitstempel partitionierten Tabelle gesendet.

Wenn die Tabelle nach einer DATE-Spalte partitioniert ist, enthält jede Partition einen Wertebereich, wobei der Beginn des Bereichs der Beginn eines Tages ist und das Intervall einen Tag beträgt. Wenn die Tabelle nach der Spalte TIMESTAMP partitioniert ist, können Sie Partitionen mit stündlicher oder täglicher Granularität erstellen.

Für nach Datum/Zeitstempel partitionierte Tabellen ist keine Pseudospalte _PARTITIONTIME erforderlich. Abfragen für nach Datum/Zeitstempel partitionierte Tabellen können gemäß der Partitionierungsspalte Prädikatfilter angeben, um die Menge der gescannten Daten zu verringern.

Beim Erstellen von nach Datum/Zeitstempel partitionierten Tabellen werden zwei besondere Partitionen erstellt:

  • Die Partition __NULL__ bildet Zeilen mit NULL-Werten in der Partitionierungsspalte ab.
  • Die Partition __UNPARTITIONED__ bildet Daten ab, die außerhalb des zulässigen Zeitraums existieren.

Mit Ausnahme der Partitionen __NULL__ und __UNPARTITIONED__ stimmen alle Daten in der Partitionierungsspalte mit dem Datum des Partitionsbezeichners überein. Dadurch kann eine Abfrage ermitteln, welche Partitionen keine Daten enthalten, die die Filterbedingungen erfüllen. Abfragen, die Daten nach der Partitionierungsspalte filtern, können Werte beschränken und unnötige Partitionen vollständig außer Acht lassen.

Partitionierung nach Datum/Zeitstempel oder Fragmentierung

Als Alternative zu partitionierten Tabellen können Sie Tabellen auch anhand einer zeitbasierten Benennung wie z. B. [PREFIX]_YYYYMMDD fragmentieren. Dies wird als Erstellen von nach Datum fragmentierten Tabellen bezeichnet. Mit Standard-SQL oder Legacy-SQL können Sie eine Abfrage mit dem UNION-Operator formulieren, um die von der Abfrage gescannten Tabellen einzuschränken.

Nach Datum/Zeitstempel partitionierte Tabellen bieten eine bessere Leistung als Tabellen, die nach Datum fragmentiert sind. Bei datumsbenannten Tabellen muss BigQuery für jede dieser Tabellen eine Kopie des Schemas und der Metadaten speichern. Außerdem muss BigQuery möglicherweise die Berechtigungen für jede abgefragte datumsbenannte Tabelle prüfen. Dieser Mehraufwand bei der Durchführung der Abfrage wirkt sich direkt auf die Abfrageleistung aus. Als Best Practice wird empfohlen, anstelle von nach Datum fragmentierten Tabellen nach Datum/Zeitstempel partitionierte Tabellen zu verwenden.

Partitionierungsoptionen im Vergleich

In der folgenden Tabelle werden fragmentierte Tabellen und nach Datum/Zeitstempel partitionierte Tabellen verglichen.

Voraussetzung Fragmentierte Tabellen Nach Aufnahmezeit partitionierte Tabellen Partitionierte Tabellen
Partitionierungsmethode Keine. Die Partitionierung kann jedoch durch Fragmentieren und Abfragen der Tabellen mit dem UNION-Operator simuliert werden. Partitioniert anhand des Aufnahme- oder Eingangsdatums der Daten. Auf die Partitionsinformationen kann mit einer Pseudospalte verwiesen werden. Partitioniert anhand der Daten in einer angegebenen Spalte TIMESTAMP oder DATE.

Für stündlich partitionierte Tabellen werden nur TIMESTAMP-Spalten für die Partitionierung unterstützt.
Partitionsbezeichner Es kann ein beliebiges gültiges Datum zwischen 0001-01-01 und 9999-12-31 verwendet werden. DML-Anweisungen können jedoch nicht auf Daten vor 1970-01-01 oder nach 2159-12-31 verweisen. Ein gültiger Eintrag aus der verknüpften Spalte DATE oder TIMESTAMP. Derzeit werden Datumswerte vor 1960-01-01 und nach 2159-12-31 in einer gemeinsamen Partition UNPARTITIONED abgelegt. NULL-Werte befinden sich in einer expliziten NULL-Partition.

Bei stündlich partitionierten Tabellen müssen Partitionierungs-IDs dem Format yyyyMMddHH folgen.
Beschränkung gescannter Daten Verweisen Sie nur auf die benötigten Fragmente und beschränken Sie die Daten, indem Sie unnötige Spalten von der Abfrage ausschließen. Verwenden Sie die Pseudospalte _PARTITIONTIME, um Partitionen zu löschen. Verwenden Sie Prädikatfilter für die Partitionierungsspalte.
Anzahl der Partitionen Die Anzahl der Tabellen ist unbeschränkt, Abfragen können jedoch nur bis zu 1.000 Tabellen referenzieren. Bis zu 4.000 Partitionen. Bis zu 4.000 Partitionen.
Aktualisierungsvorgänge Es sind maximal 1.000 Aktualisierungen pro Tag zulässig. Mit einem einzelnen Vorgang kann eine einzelne Partition aktualisiert werden. Dies ist entweder die neueste Partition (Standard) oder eine Partition, die mit einem Partitions-Decorator wie z. B. [TABLE]$[DATE] festgelegt wird. Mit einem einzelnen Vorgang können Daten in bis zu 2.000 verschiedenen Partitionen aktualisiert werden.
Streaming-Insert-Anweisungen Ein globaler Puffer für die Tabelle. Basierend auf der aktuellen UTC-Zeit können Sie mit Partitions-Suffixen innerhalb der letzten 31 Tage in der Vergangenheit und 16 Tage in der Zukunft ausgehend vom aktuellen Datum auf Partitionen streamen. Sie können Daten zwischen einem Jahr in der Vergangenheit und sechs Monaten in der Zukunft streamen. Daten außerhalb dieses Bereichs werden abgelehnt. Wenn die Daten gestreamt werden, werden sie zwischen sieben Tagen in der Vergangenheit und drei Tagen in der Zukunft in den Streamingpuffer gestellt und dann in die entsprechenden Partitionen extrahiert. Daten außerhalb dieses Zeitraums (aber innerhalb von einem Jahr/sechs Monaten) werden in der Partition UNPARTITIONED gespeichert. Wenn genügend nicht partitionierte Daten vorhanden sind, werden sie in die entsprechenden Partitionen geladen.

Bei stündlich partitionierten Tabellen werden Daten direkt in die Partition UNPARTITIONED gestreamt, bevor sie extrahiert werden.
Auswertung der Zeitzone Wird durch die Nutzersemantik definiert. UTC UTC

Nach Ganzzahlbereich partitionierte Tabellen

BigQuery unterstützt auch nach einer Spalte vom Typ INTEGER partitionierte Tabellen. Sie legen dabei die Werte für Start, Ende und Intervall fest. Wenn Sie nach Ganzzahlbereich partitionierte Tabellen abfragen, können Sie Prädikatfilter für die Partitionierungsspalte angeben, um den Umfang der gescannten Daten zu reduzieren.

Um eine nach Ganzzahlbereich partitionierte Tabelle zu erstellen, geben Sie Folgendes an:

  • Die Spalte, anhand der die Partitionen nach Ganzzahlbereich erstellt werden
  • Den Beginn der Bereichspartitionierung (einschließlich)
  • Das Ende der Bereichspartitionierung (ausschließlich)
  • Das Intervall jedes Bereichs der Partition

Werte außerhalb des Tabellenbereichs werden in die UNPARTITIONED-Partition geschrieben.

Sie können beispielsweise eine Partition nach Ganzzahlbereich mit folgenden Werten erstellen:

Argument Wert
Spaltenname customer_id
start 0
end 100
interval 10

Die Tabelle wird mithilfe der Spalte "customer_id" in 10er-Intervallen partitioniert. Die Werte 0 bis 9 befinden sich in einer Partition, die Werte 10 bis 19 in der nächsten Partition. Den Abschluss bildet das Intervall mit den Werten 90 bis 99. Werte außerhalb von 0 bis 99 (z. B. -1 oder 100) sind Teil der Partition UNPARTITIONED. Nullwerte befinden sich in der NULL-Partition.

Beim Erstellen von nach Ganzzahlbereich partitionierten Tabellen werden zwei besondere Partitionen erstellt:

  • Die Partition __NULL__ bildet Zeilen mit NULL-Werten in der Partitionierungsspalte ab.
  • Die Partition __UNPARTITIONED__ stellt Daten dar, die außerhalb des zulässigen Ganzzahlbereichs existieren, der sich aus Startwert und Intervall ergibt.

Mit Ausnahme der Partitionen __NULL__ und __UNPARTITIONED__ erfüllen alle Daten in der Partitionierungsspalte die Vorgaben Ganzzahlbereichs aus Anfangswerts und des Intervall. Dadurch kann eine Abfrage ermitteln, welche Partitionen keine Daten enthalten, die die Filterbedingungen erfüllen. Abfragen, die Daten nach der Partitionierungsspalte filtern, können Werte beschränken und unnötige Partitionen vollständig außer Acht lassen.

Es können maximal 10.000 mögliche Bereiche zwischen dem Start- und Endwert liegen. Die Anzahl der Bereiche mit Daten ist jedoch auf 4.000 Partitionen pro Tabelle begrenzt, da jeder Bereich eine Partition ist.

Partitionierung nach Ganzzahlbereich oder Clustering

Sowohl die Partitionierung nach Ganzzahlbereich als auch das Clustering können die Leistung verbessern und die Abfragekosten reduzieren. Dabei gibt es wesentliche Unterschiede und Anwendungsfälle.

Die Partitionierung nach Ganzzahlbereich eignet sich für folgende Anwendungsfälle:

  • Explizites Definieren der Bereiche, nach denen die Tabelle partitioniert werden soll. Sie legen fest, wie die Daten partitioniert werden und welche Daten jede Partition enthält.

  • Ermitteln der Kosten einer Abfrage vor dem Ausführen. 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.

  • Angeben einer Partition, beispielsweise beim Laden von Daten in eine bestimmte Partition oder beim Löschen von Daten aus einer bestimmten Partition.

Das Clustering eignet sich für folgende Fälle:

  • Es ist unwichtig, wie die Daten geclustert werden, solange sich die Leistung verbessert und sich die Kosten reduzieren. BigQuery ermittelt automatisch das richtige Clustering der Daten zur Leistungs- und Kostenoptimierung.

  • Sie benötigen mehr als 4.000 Partitionen. In BigQuery kann eine partitionierte Tabelle maximal 4.000 Partitionen haben. Die Anzahl der Cluster pro Tabelle ist unbegrenzt.

Sie können dieselbe Spalte mit Ganzzahlen nutzen, um eine Tabelle zu partitionieren und in Cluster zu gruppieren, um von beiden Vorteile zu profitieren. Die Daten werden zuerst nach den angegebenen Ganzzahlbereichen partitioniert. Wenn der Datenumfang innerhalb jedes Bereichs ausreichend groß ist, werden die Daten zusätzlich geclustert. Bei der Abfrage der Tabelle werden die Abfragekosten durch die Partitionierung anhand der Partitionierungsbereinigung begrenzt. Während der tatsächlichen Abfrage können sich die Abfragekosten weiter reduzieren.

require_partitioning_filter verwenden

Mit der Einführung der Partitionierung nach Ganzzahlbereich unterstützt BigQuery jetzt mehrere Partitionierungstypen:

  • Aufnahmezeit
  • Datum/Zeitstempel
  • Ganzzahlbereich

Zur Vereinfachung der BigQuery API haben wir den Parameter require_partitioning_filter aus der Partitionierungsebene in die Tabellenebene verschoben. Für die Abwärtskompatibilität der Partitionierung nach Datum/Zeitstempel wird der Parameter require_partitioning_filter weiterhin auf der Partitionierungsebene unterstützt. Er kann aber auch auf Tabellenebene festgelegt werden. Bei der Partitionierung nach Ganzzahlbereich können Sie require_partitioning_filter nur auf der Tabellenebene festlegen. Das bq-Befehlszeilentool verwendet die Option bereits auf der Tabellenebene, sodass Sie den bq-Befehl wie gewohnt verwenden können. Wenn Sie die BigQuery API verwenden, müssen Sie die Option require_partitioning_filter auf der Tabellenebene verwenden.

Kontingente und Begrenzungen partitionierter Tabellen

Partitionierte Tabellen unterliegen in BigQuery festen Begrenzungen.

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 Begrenzungen finden Sie unter Kontingente und Begrenzungen.

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.

  • Informationen zu den Preisen für Speicher finden Sie unter Speicherpreise
  • Informationen zu den Preisen für Abfragen finden Sie unter Abfragepreise

Viele Vorgänge für partitionierte Tabellen sind kostenlos, einschließlich des Ladens von Daten in Partitionen, des Kopierens von Partitionen sowie des Exportierens von Daten 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