Business Intelligence/31. Juli 2023 -Aktualisiert am 15. August 2023

Mit Clustering die beste Performance in BigQuery

Eine Illustration einer Tabelle mit verschiedenfarbigen Zeilen die zu einer Tabelle wird mit den Zeilen geclustert nach den Farben

Das Reporting von Kennzahlen wird immer wichtiger und deshalb werden zunehmend Daten gesammelt. Tabellen können dadurch relativ schnell eine Größe erreichen, die für langsame Abfragen sorgt. Und nicht nur das: Je mehr Zeilen eine Tabelle hat, desto mehr Kosten entstehen bei jeder Abfrage. In diesem Artikel zeigen wir, wie mit Clustering die Performance in BigQuery gesteigert wird – und dabei bares Geld gespart werden kann.

Clustering in BigQuery: Ein Beispiel

Wir haben in unserem Beispiel eine Ausgangstabelle mit über 39 Mio. Zeilen und einer Größe von 2,63 GB. Darin enthalten sind die Seitenaufrufe einer Website – getrennt pro Seite, Land, Medium sowieo Device der Besucher.

BigQuery Metainformationen zur Tabellengröße und Anzahl Zeilen.

Wir haben die Tabelle nun einmal mit und ohne Clustering erstellt und die gleiche Abfrage durchgeführt.

Hier das Ergebnis ohne Clustering:

BigQuery-Interface mit Abfrage ohne Clustering

Und hier das Ergebnis mit Clustering:

BigQuery-Interface mit Abfrage mit Clustering

Die Abfragen im Vergleich:

ClusteringDauerGröße
ohne2,9 Sekunden2,6 GB
mit2,5 Sekunden0,4 GB

Die Abfrage dauert in etwa genauso lange und der zeitliche Unterschied ist hier marginal. Anders jedoch die Abfragemenge: Sie hat sich um ca. 85% reduziert!

Was macht Clustering mit den Daten?

Auf den ersten Blick lassen sich zwischen “normalen” und “gruppierten” Tabellen keine Unterschiede ausmachen. Clustering geschieht im Hintergrund und ganz automatisch. Die Daten können in der Cloud Console daher wie gewohnt abgefragt und bearbeitet werden. Clustering funktioniert sowohl mit partitionierten als auch mit nicht-partitionierten Tabellen.

Die Nutzung von partitionierten Tabellen ist auch sinnvoll, soll aber nicht Gegenstand des Artikels sein. Nur so viel: In BigQuery lassen sich Tabellen zum Beispiel tageweise partitionieren – auch das reduziert die Datenmenge und beschleunigt somit die Abfrage. So ähnlich funktioniert auch Clustering.

Google bildet nach den mitgeteilten Vorgaben “Datengruppen”. Mit anderen Worten, eine gruppierte Tabelle ist sich als eine Art “Ordner” mit “Unterordnern” vorzustellen. Dadurch reduzieren sich die Abfragen, da nur Daten aus einer jeweiligen Gruppe abgefragt werden. Werden zum Beispiel Daten aus unterschiedlichen Ländern in einer Tabelle gespeichert, ergibt es Sinn, die Daten nach Ländern zu gruppieren. Wenn später die Daten für ausgewählte Länder abgefragt werden, d.h. in der WHERE-Clause nach Ländern filtern, werden im Hintergrund auch nur die entsprechenden Länderdaten verarbeitet.

Wann lohnt sich Clustering?

Wenn die Abfragen nach Feldern gefiltert werden, die auch dem Clustering entsprechen, werden generell weniger Daten verarbeitet – schon aus diesem Grund lohnt sich der Einsatz. Google selbst schreibt, dass sich ein signifikanter Performance-Gewinn ab etwa einem GigaByte an Daten zeige. Wir konnten allerdings auch schon einen Unterschied ausmachen, wenn eine Tabelle – trotz geringer Größe – viele Zeilen enthält. Wenn also eine Tabelle vorliegt, die irgendwann “groß” und “voll” wird, dann ist die Nutzung von Clustering definitiv von Vorteil.

Was muss beim Clustering in BigQuery beachtet werden?

Gruppierte Tabellen sind auf unterschiedliche Weise anzulegen, wobei maximal 4 Felder für das Clustering genutzt werden können. Wesentlich ist hierbei, dass Clustering nicht nachträglich einer bereits bestehenden Tabelle hinzugefügt werden kann.

Prinzipiell können folgende Datentypen für das Clustering genutzt werden:

  • DATE
  • BOOL
  • GEOGRAPHY
  • INT64
  • NUMERIC
  • STRING
  • TIMESTAMP
Die Reihenfolge beim Clustering beachten

Die Reihenfolge der Felder ist wichtig. Wenn man sich, wie bereits erwähnt, gruppierte Tabellen als “Ordner” vorstellt, dann sollte man sich überlegen, welcher Ordner auf welcher Ebene liegt. Letztlich bestimmt diese Reihenfolge, in welcher Sortierung die Daten am Ende in BigQuery gespeichert werden. Das kann von Fall zu Fall unterschiedlich sein.

Hier ein Beispiel: Ein Unternehmen besitzt Marketing-Daten aus unterschiedlichen Ländern, für unterschiedliche Medien, die außerdem nach dem Device der Besucher sowie nach der besuchten Seite getrennt sind. Zudem speichert das Unternehmen die Dauer der Seitenaufrufe ab.

Die Tabelle hätte also folgende Felder:

  • country_name
  • medium
  • device
  • page
  • session_duration
Nach welcher Reihenfolge sollte man hier nun gruppieren?

Wenn man häufig länderspezifische Auswertungen durchführt, würde man vermutlich mit country_name beginnen. Folgen dann hauptsächlich Analysen, die nach dem Medium getrennt sind, dann würde man medium als zweites Feld wählen. Der Analyse-Fokus könnte aber auch auf dem Gerätetyp der User liegen, dann wäre device wohl das zweite Feld in der Reihenfolge. Dies zeigt, es kommt stark auf den Use-Case an.

Tabellen mit Clustering anlegen

Clustering kann man unterschiedlich anwenden. Nachfolgend zeigen wir drei Wege auf, wie man Tabellen gruppieren kann:

  • Mit einem DDL-Statement
  • Als Ergebnis einer Query
  • Mit der Befehls-Zeile / dem Terminal

1. Mit einem DDL-Statement

1CREATE TABLE IF NOT EXISTS `projectId.datasetId.tableId` ( 
2  field1 TYPE, 
3  field2 TYPE,
4  field3 TYPE,
5  field4 TYPE,
6  field5 TYPE,
7  field6 TYPE,
8  field7 TYPE,
9  dateField TYPE
10) CLUSTER BY field1, field2, field3, field4
11  PARTITION BY dateField
mysql

2. Ergebnis einer Query

1CREATE TABLE `projectId.datasetId.clusteredTableId` ( 
2  timestamp TIMESTAMP , 
3  customer_id STRING , 
4  gender STRING , 
5  country_name STRING , 
6  transaction_amount NUMERIC 
7) PARTITION BY DATE(timestamp) 
8CLUSTER BY country_name , gender , customer_id 
9OPTIONS ( 
10partition_expiration_days=3, 
11description="a table clustered by country_name, gender and customer_id" 
12) AS SELECT * FROM `projectId.datasetId.tableId`
mysql

3. Mit Hilfe der Befehlszeile

Ist das BigQuery Command-Line-Tool installiert, kannst man folgende Befehle nutzen, um Tabellen zu erstellen.

Ohne Partitionierung

1bq mk \ --table projectId:datasetId.tableId \ --clustering_fields field1,field2,field3,field4 \ --schema my-table-schema.json
sh

Mit Partitionierung

1bq mk \ --table projectId:datasetId.tableId \ --time_partitioning_field dateField \ --clustering_fields field1,field2,field3,field4 \ --schema my-table-schema.json
sh

Hier ist noch die Referenz zum bq Tool zu finden.