Mit Clustering die beste Performance in BigQuery

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 kleinen Artikel zeigen wir dir, wie du mit Clustering die beste Performance in BigQuery erzielst – und dabei Geld sparen kannst.

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.

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

• Hier das Ergebnis ohne Clustering:

BigQuery-Abfrage ohne Clustering: Dauer 2,9 sec und 2,6GB verarbeitet

• Und hier das Ergebnis mit Clustering:

BigQuery-Abfrage ohne Clustering: Dauer 2,5 sec und 0,4GB verarbeitet

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 Deinen Daten?

Auf den ersten Blick lassen sich zwischen “normalen” und “gruppierten” Tabellen keine Unterschiede ausmachen. Clustering geschieht im Hintergrund und ganz automatisch. Deine Daten kannst du in der Cloud Console daher wie gewohnt abfragen und bearbeiten. 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 deinen Vorgaben “Datengruppen”. Mit anderen Worten, eine gruppierte Tabelle kannst du dir als eine Art “Ordner” mit “Unterordnern” vorstellen. Dadurch reduzieren sich deine Abfragen, da du nur Daten aus einer jeweiligen Gruppe abfragst. Werden zum Beispiel Daten aus unterschiedlichen Ländern in einer Tabelle gespeichert, ergibt es Sinn, deine Daten nach Ländern zu gruppieren. Wenn du später die Daten für ausgewählte Länder abfragst, d.h. in deiner WHERE-Clause nach Ländern filterst, werden im Hintergrund auch nur die entsprechenden Länderdaten verarbeitet.

Wann lohnt sich Clustering für Dich?

Wenn du deine Abfragen nach Feldern filterst, die auch deinem 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 du also eine Tabelle hast, die irgendwann “groß” und “voll” wird, dann solltest du definitiv Clustering nutzen.

Was musst Du beim Clustering in BigQuery beachten?

Gruppierte Tabellen kannst du auf unterschiedliche Weise anlegen, wobei du maximal 4 Felder für das Clustering nutzen kannst. Wesentlich ist hierbei, dass Clustering nicht nachträglich einer bereits bestehenden Tabelle hinzugefügt werden kann.

Prinzipiell kannst du folgende Datentypen kannst du für das Clustering nutzen:

• DATE

• BOOL

• GEOGRAPHY

• INT64

• NUMERIC

• STRING

• TIMESTAMP

Die Reihenfolge beim Clustering beachten

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

Hier ein Beispiel: Du besitzt Marketing-Daten aus unterschiedlichen Ländern, für unterschiedliche Medien, die außerdem nach dem Device deiner Besucher sowie nach der besuchten Seite getrennt sind. Zudem speicherst du 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 deiner User liegen, dann wäre “device” wohl das zweite Feld in der Reihenfolge. Du siehst, es kommt stark auf den Use-Case an.

Tabellen mit Clustering anlegen

Clustering kannst du unterschiedlich anwenden. Nachfolgend zeigen wir dir drei Wege auf, wie du deine Tabellen gruppieren kannst:

1. Mit einem DDL-Statement

2. Als Ergebnis einer Query

3. Mit der Befehls-Zeile / dem Terminal

1. Mit einem DDL-Statement

CREATE TABLE IF NOT EXISTS `projectId.datasetId.tableId` (         
    field1 TYPE   
  , field2 TYPE   
  , field3 TYPE   
  , field4 TYPE   
  , field5 TYPE   
  , field6 TYPE   
  , field7 TYPE   
  , dateField TYPE 
)  
CLUSTER BY field1, field2, field3, field4 
PARTITION BY dateField

2. Ergebnis einer Query

CREATE TABLE `projectId.datasetId.clusteredTableId` (
    timestamp TIMESTAMP
  , customer_id STRING
  , gender  STRING
  , country_name STRING
  , transaction_amount NUMERIC
)

PARTITION BY DATE(timestamp)
CLUSTER BY
    country_name
  , gender
  , customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by country_name, gender and customer_id"
)

AS SELECT * FROM `projectId.datasetId.tableId`

3. Mit Hilfe der Befehlszeile

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

Ohne Partitionierung

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

Mit Partitionierung

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

Hier findest du noch die Referenz zum bq Tool.

Wir hoffen, dir hat der Artikel „mit Clustering die beste Performance in BigQuery“ gefallen und dass wir dir weiterhelfen konnten. Falls du noch Fragen oder Anmerkungen hast, dann melde dich gern bei uns.