Bei den Best Practices zur BigQuery-Kostenoptimierung den Überblick zu behalten ist anspruchsvoll – doch was, wenn ein Tool Ihnen einfach sagen würde, was zu tun ist?
Ihre BigQuery-Kosten steigen – und jetzt?
Klar, Sie könnten Audit-Logs nach Hinweisen durchforsten oder die BigQuery Best Practices von Google Cloud durcharbeiten, um besser zu verstehen, was sich möglicherweise optimieren lässt.
Aber bei einem Backlog voller anderer Projekte und ständig neuer Baustellen: Können Sie oder Ihr Team sich die nötige Zeit dafür wirklich nehmen? Aus unserer Erfahrung lautet die Antwort meistens ein klares "Nein".
Die meisten Unternehmen, mit denen wir arbeiten, haben schlicht zu viele höher priorisierte produktbezogene Aufgaben und zu wenig Engineering-Ressourcen, um sich der BigQuery-Kostenoptimierung zu widmen. Die Folge: Viele bauen ihre Dateninfrastruktur in BigQuery quasi nebenher auf – und bekommen die Konsequenzen dieser Entscheidungen erst später zu spüren.
Aber was, wenn ein Tool Ihnen einfach sagen würde, was zu tun ist?
Genau dafür haben wir die BigQuery Lens entwickelt. Sie liefert personalisierte Empfehlungen auf Basis von BigQuery Best Practices und unserer eigenen Erfahrung aus der Optimierung der BigQuery-Nutzung von Tausenden Kunden.
Schauen wir uns einige BigQuery Best Practices an – und wie Sie diese mit BigQuery Lens umsetzen.
"Dank der Empfehlungen der BigQuery Lens haben wir Zeit in die Partitionierung unserer wichtigsten Tabellen investiert und konnten unsere BigQuery-Ausgaben um 25 % pro Monat senken."
- Daniel Rimon, Head of Data Engineering, Resident
So hilft Ihnen BigQuery Lens, Ihre Kosten zu optimieren
BigQuery Lens analysiert Ihre BigQuery-Jobs und Tabellen-Metadaten und liefert konkrete, umsetzbare Optimierungsempfehlungen – inklusive detaillierter Einblicke in die Nutzungsstatistiken Ihres Teams.
Sie könnten zwar stundenlang in Audit-Logs und Queries wühlen und versuchen, über Cloud Monitoring oder Abfragen auf INFORMATION_SCHEMA.JOBS eigene Dashboards zu bauen – aber das liefert Ihnen nur Kennzahlen auf hoher Aggregationsebene. Welche Maßnahmen daraus folgen sollen, müssen Sie immer noch selbst herleiten.
BigQuery Lens macht die Umsetzung von Best Practices einfach.
Konkret helfen die Empfehlungen Ihnen dabei,
- die gescannte Datenmenge durch Clustering oder Partitionierung von Tabellen zu reduzieren,
- die Kosten geplanter Jobs zu senken,
- Flat-Rate-Pricing dort zu nutzen, wo es sinnvoll ist,
- Speicherkosten zu senken, indem ungenutzte Tabellen entfernt werden,
- partitionierte Felder in Queries konsequent zu verwenden,
- wiederkehrende Jobs zeitlich umzuverteilen, um den Slot-Bedarf zu senken.
Verarbeitete Bytes durch Clustering und Partitionierung reduzieren
Egal, ob Sie ein Projekt mit On-Demand- oder Flat-Rate-Pricing abfragen – Sie profitieren bei Kosten und Performance, wenn Sie die Anzahl der von Ihren Queries verarbeiteten Bytes reduzieren. Bei On-Demand-Pricing senkt das die Analysekosten unmittelbar. Beim Flat-Rate-Pricing sind die Kosten zwar fix an die Anzahl gekaufter Slots gekoppelt, doch weniger verarbeitete Bytes senken Ihre durchschnittliche Slot-Auslastung – und damit indirekt die Anzahl der Slots, die Sie kaufen müssen (mehr dazu weiter unten).
Ein guter Ausgangspunkt: Tabellen clustern und/oder partitionieren.
Tabellen clustern (und das Maximum herausholen)
Clustering verbessert die Query-Performance, indem Ihre Tabelle anhand der Spalte(n), nach denen Sie clustern, in Datenblöcke organisiert wird. So kann BigQuery gezielt nur die relevanten Datenblöcke scannen – vorausgesetzt, Sie strukturieren Ihre Queries entsprechend.
Wie groß der Nutzen des Clusterings tatsächlich ausfällt, hängt davon ab, nach welcher/welchen Spalte(n) – und in welcher Reihenfolge – Sie clustern und wie Sie Ihre Queries auf geclusterten Tabellen strukturieren.
Sie sollten Tabellen nach häufig abgefragten Spalten clustern, insbesondere wenn diese viele unterschiedliche Werte enthalten. In diesem Fall kann BigQuery Ihre Tabellen effektiver abfragen, weil es die durch Ihre Clustering-Strategie festgelegte Spaltenreihenfolge nutzt.
Wie Sie im Diagramm unten sehen, ist es zudem wichtig, Ihre Queries an der Reihenfolge der gewählten Cluster-Felder auszurichten – also Filter in Cluster-Reihenfolge anzuwenden. So holen Sie das Maximum aus der Clustering-Strategie Ihrer Tabellen heraus.
BigQuery Lens identifiziert Tabellen, die geclustert werden sollten, und zeigt dabei, nach welcher/welchen Spalte(n) und in welcher Reihenfolge. Sie müssen lediglich auf den Tabellennamen im Empfehlungsfenster klicken, um die Tabelle in Ihrer Google Cloud Console zu öffnen und sie zu clustern.
Es lohnt sich außerdem, Ihre bisherigen Queries auf der jeweiligen Tabelle mit den Jobs nach dem Clustering zu vergleichen. Mit hoher Wahrscheinlichkeit werden Sie sowohl bei den Kosten als auch bei der Ausführungszeit spürbare Verbesserungen sehen.
Tabellen partitionieren (und warum sich das lohnt)
Partitionierung hilft, Kosten zu kontrollieren, indem eine große Tabelle in kleinere Abschnitte unterteilt wird. Insbesondere mit Partition Pruning – also dem gezielten Filtern auf bestimmte Partitionen, sodass nicht die gesamte Tabelle gescannt wird – entfaltet die Partitionierung ihre Wirkung.
Außerdem erhalten Sie damit eine feingranulare Kontrolle über Ihre Daten. Sie können einzelne Tabellenpartitionen ablaufen lassen oder neu eingehende Daten effizienter über Partitionen in Ihren BigQuery-Tabellen verwalten.
Ähnlich wie bei geclusterten Tabellen muss Ihre Query auch hier einen geeigneten Filter auf den Wert der Partitionierungsspalte verwenden, damit der Vorteil der Partitionierung greift.
Hinweis: BigQuery Lens identifiziert auch Queries auf partitionierten Tabellen, die zwar einen passenden Filter setzen könnten, dies aber nicht tun (siehe "Partitionsfelder in Queries erzwingen" weiter unten).
Genau wie bei der Clustering-Empfehlung zeigt BigQuery Lens auch hier, welche Tabellen partitioniert werden sollten und nach welchem Feld. Das ist generell hilfreich – besonders aber, wenn Sie BI-gestützte Dashboards auf BigQuery betreiben: Tabellenpartitionen machen Ihre Dashboards schneller und Queries günstiger.
Wann Sie eine Tabelle clustern UND partitionieren sollten
In bestimmten Fällen kann es sinnvoll sein, Partitionierung und Clustering bei BigQuery-Tabellen zu kombinieren.
Diese Strategie ist besonders nützlich, wenn Sie eine feingranulare Sortierung innerhalb Ihrer Tabellen wünschen (siehe Diagramm unten) und gleichzeitig dank der Partition-Pruning-Fähigkeiten von BigQuery vorab mehr Transparenz über die voraussichtlichen Kosten Ihrer Queries haben möchten.
Bedenken Sie jedoch: Die Kombination beider Strategien führt zu mehr Metadaten, die für Ihre Tabellen vorgehalten werden müssen. Zudem kommen die Vorteile des Clusterings weniger zum Tragen, wenn Ihre Tabellen nicht regelmäßig nach denselben Feldern abgefragt werden. Das würde die Vorteile dieser kombinierten Strategie zunichtemachen.
Auf Flat-Rate-Pricing umstellen
Mit wachsender BigQuery-Nutzung und Datenmenge erreichen Sie irgendwann den Punkt, an dem sich der Wechsel vom On-Demand-Modell zu Flat-Rate finanziell lohnt.
Sobald BigQuery Lens erkennt, dass Sie damit Kosten sparen können, zeigt das Tool eine Slot-Commitment-Empfehlung an. Im Beispiel unten zeigt BigQuery Lens, dass unsere maximale durchschnittliche tägliche Slot-Nutzung bei 169 Slots liegt – empfohlen wird daher die Reservierung von 200 Slots, da der Kauf in 100er-Schritten erfolgt. Gleichzeitig sehen wir eine deutlich höhere Spitzenauslastung von 2.238 Slots.
Hinweis: BigQuery-Reservierungen und das On-Demand-Modell schließen sich nicht gegenseitig aus. Sie können über BigQuery Reservations zwischen ihnen wechseln und sogar Flex Slots einbinden.
Da Slots eine Mindestlaufzeit von einem Monat haben, sollten Sie beim Kauf besonders umsichtig vorgehen, wenn Sie sich bei der künftigen Query-Aktivität, extremen Lastspitzen oder saisonalen Schwankungen Ihrer BigQuery-Nutzung unsicher sind. Hier kommen Flex Slots ins Spiel. Mit Flex Slots können Sie Slots für kurze Zeiträume kaufen – ab 60 Sekunden.
Wenn Sie zum Beispiel alle Lade- und Reporting-Jobs um 3 Uhr morgens ausführen, können Sie das Provisioning und Deprovisioning von Flex Slots mit Cloud Composer automatisieren und so flexibel hoch- und runterskalieren. Um zu vermeiden, dass die Nachfrage die verfügbaren Slots übersteigt, prüft einer unserer Kunden mit Cloud Functions sogar alle fünf Minuten, ob die Slot-Nutzung über 90 % des Commitments liegt, und stellt bei Bedarf automatisch Flex Slots bereit.
Frequenz geplanter und wiederkehrender Jobs reduzieren
Möglicherweise laufen bei Ihnen geplante Jobs häufiger als nötig – oder müssten gar nicht laufen. BigQuery Lens identifiziert besonders häufig ausgeführte Jobs und zeigt das Einsparpotenzial, das eine Reduzierung der Ausführungsfrequenz um 10–50 % bringen würde.
Diese Empfehlung schafft eine bessere Grundlage für Kostengespräche im Team und stellt sicher, dass besonders teure Jobs nur dann laufen, wenn sie wirklich gebraucht werden.
Ein Kunde stellte zum Beispiel fest, dass mehrere Jobs deutlich häufiger liefen als nötig. Er sprach das im erweiterten Team an, um die Frequenz zu reduzieren – und kalkulierte, dass sich mit einer leichten Reduzierung der Ausführungen 10–15 % der Query-Kosten einsparen lassen.
Ein anderer Kunde entdeckte geplante Queries, die längst obsolet waren. Vor dem Einsatz von BigQuery Lens fehlte ihm dafür schlicht die Sichtbarkeit. Im Monat nach dem Stoppen dieser unnötigen geplanten Queries sanken die Kosten für BigQuery – Analysis um über 50 %.
Stammen diese wiederkehrenden Queries aus einem BI-gestützten Dashboard (z. B. Looker), lassen sich die Kosten zusätzlich senken, indem Sie sie über BI Engine ausführen. BI Engine ist ideal für Dashboard-Queries, weil es BigQuery-Daten intelligent im Speicher cacht und so für schnellere Queries sorgt. Da BI Engine Daten in-memory hält, ist die Query-Phase, die Tabellendaten liest, kostenlos. Stattdessen zahlen Sie nur für die reservierte Speicherkapazität. Nutzt Ihr Projekt jedoch Flat-Rate-Pricing, beziehen nachgelagerte Phasen Slots aus Ihrer BigQuery-Reservierung.
Speicherkosten senken: ungenutzte Tabellen sichern und entfernen
Nicht nur Queries kosten Geld – Sie zahlen auch für Speicher in BigQuery! Ungenutzte Tabellen sind nach unserer Erfahrung ein häufig übersehener Hebel, um BigQuery-Ausgaben zu senken.
BigQuery Lens hebt Tabellen (und ggf. Partitionen) hervor, die in den letzten 30 Tagen nicht genutzt wurden. Sie können Speicherkosten senken, indem Sie diese Tabellen in Cloud Storage sichern und anschließend aus BigQuery entfernen (oder die Ablaufeinstellungen der Tabelle anpassen).
Ein Kunde nutzte diese Empfehlung kürzlich, um diverse Tabellen mit über 3 Jahre alten, nicht abgefragten Daten aufzuspüren. Nach Sicherung und Entfernung dieser Tabellen sanken seine BigQuery-Kosten um rund 30 %.
Partitionsfelder in Queries erzwingen
Tabellen zu partitionieren ist nur die halbe Miete. Nach der Partitionierung müssen Sie sicherstellen, dass diese Tabellen auch effektiv abgefragt werden – und zwar über das Feld, nach dem partitioniert wurde. So scannen Sie nur kleinere Teile Ihrer Tabelle statt der gesamten Daten und behalten die Kosten besser im Griff.
In einem Team aus Datenanalysten ist es jedoch schwer nachzuvollziehen, ob wirklich alle das Partitionsfeld in ihren Queries einsetzen. BigQuery Lens identifiziert Jobs auf partitionierten Tabellen, in denen das Partitionsfeld nicht genutzt wird.
Flat-Rate-Kosten senken durch zeitliche Umverteilung wiederkehrender Jobs
Stellen Sie sich die Optimierung Ihrer Flat-Rate-Kosten wie eine Partie Tetris vor. Ihr Ziel: die Auslastung stabilisieren und einen möglichst gleichmäßigen Slot-Verbrauch pro Stunde erreichen.
Typischerweise haben Sie aber Spitzen und Täler in Ihrer durchschnittlichen stündlichen Slot-Nutzung (siehe Grafik unten). Wenn Sie Ihre Slot-Käufe an dieser unregelmäßigen Auslastung ausrichten, geben Sie unter Umständen mehr aus als nötig.
Um Ihre Flat-Rate-Kosten zu reduzieren, identifizieren Sie wiederkehrende Jobs in diesen Spitzenzeiten und verschieben Sie sie (sofern möglich) in Stunden mit niedriger Durchschnittsauslastung – oder stellen Sie Flex Slots bereit, um die Mehrlast abzufangen.
Damit glätten Sie Spitzen und erhalten ein niedrigeres, konstantes Slot-Nutzungsprofil. Die Folge: Sie müssen weniger Slots kaufen und senken Ihre Flat-Rate-Kosten langfristig.
BigQuery Lens zeigt Queries an, die auf Projekten mit Flat-Rate-Pricing mehr als 30-mal pro Monat ausgeführt werden – inklusive Ausführungsstunde und Anzahl der konsumierten Slots.
Anschließend können Sie gezielt nach Queries in Spitzenstunden suchen und prüfen, ob sich diese in nutzungsarme Zeiten verschieben lassen. Mit dem Diagramm oben als Beispiel: Suchen Sie nach Queries, die um 4 Uhr morgens oder 16 Uhr ausgeführt werden, und verlegen Sie sie nach Möglichkeit auf 7 Uhr morgens oder 20 Uhr.
So erhalten Sie BigQuery Lens
Wie bei allem in der Public Cloud können auch bei BigQuery die Kosten schnell aus dem Ruder laufen, wenn Sie die Nutzung nicht im Blick haben. Da BigQuery für viele Unternehmen das Herzstück ihrer Datenstrategie ist, überrascht es uns nicht, dass es konstant zu unseren am häufigsten nachgefragten Themen in Kunden-Tickets gehört.
Umso wichtiger ist es, kontinuierlich auf Kosten und Performance hin zu optimieren. Genauso entscheidend ist, dass Sie verstehen, wie Ihr Team BigQuery tatsächlich nutzt – denn was Sie nicht kennen, können Sie nicht optimieren. Genau hier setzt BigQuery Lens an.
Wenn Sie noch kein DoiT-Kunde sind und Zugriff auf BigQuery Lens und unsere BigQuery-Experten wünschen, sprechen Sie uns an.
Sind Sie bereits DoiT-Kunde, sollten Sie BigQuery Lens aktivieren, falls noch nicht geschehen.