BigQuery ist ein vielseitiges Data Warehouse, mit dem Sie aus Big Data wertvolle Erkenntnisse gewinnen – doch die Kosten können schnell aus dem Ruder laufen. Im ersten Teil unserer ausführlichen Leitfaden-Reihe zeigen wir Ihnen, wie Sie BigQuery effizient einsetzen.

Der neue Leitfaden für Kosten- und Performance-Optimierung in BigQuery

Plötzliche Kostensprünge sind nichts Ungewöhnliches, wenn mehrere Analyse-Teams parallel auf mehreren BigQuery-Datasets Abfragen ausführen. Unvermeidlich sind sie deshalb aber noch lange nicht.

In unserem neuen E-Book The BigQuery Optimization Handbook: Preparing to Save liefert Sayle Matthews, Senior Cloud Architect und Datenspezialist bei DoiT, den ersten Teil einer Reihe mit Experten-Insights: Sie erfahren, wie Sie mit BigQuery mehr erreichen und gleichzeitig weniger ausgeben – und Ihre Google-Cloud-Rechnungen planbarer machen.

Das E-Book behandelt:

1. Die Grundlagen sauber aufsetzen
2. Häufige Fehler bei Abfragen vermeiden
3. Ihre teuersten Abfragen aufspüren

Die Grundlagen sauber aufsetzen

Das Data Warehouse BigQuery ist vollständig verwaltet und bringt Funktionen wie Machine Learning, Geodatenanalyse und Business Intelligence von Haus aus mit, damit Sie Big Data verwalten und auswerten können. Ohne ein wenig Vorarbeit entstehen aber schnell unerwartete Kosten. Bevor Sie Ihre BigQuery-Kosten angehen, sollten Sie ein paar Grundlagen kennen. Beginnen wir mit Slots und Preismodellen:

Slots

Die Berechnungen in BigQuery basieren auf einem Konstrukt namens Slot – einer vCPU mit zugehörigem Arbeitsspeicher. Theoretisch gilt: Je mehr Slots zugewiesen und verfügbar sind, desto schneller laufen die Abfragen. Standard-Slots werden immer in Paketen zu 100 Stück innerhalb eines Commitments mit einer Laufzeit von einem Monat oder einem Jahr gebucht.

Slots übernehmen die undokumentierte Aufgabe, Daten zu shufflen. Vereinfacht gesagt heißt Shuffling, verarbeitete Daten an einen neuen Ort zu verteilen, damit der aktuelle oder nächste Schritt im Abfrageplan schneller laufen kann. Bis zu 60 % der Slots, die Ihrem Projekt zugewiesen sind, können zu jedem Zeitpunkt als Shuffle-Slots fungieren. Shuffling steigert die Effizienz von Abfragen, bindet aber wertvolle Slots für Operationen, die die eigentliche Abfrageausführung nicht direkt voranbringen.

Preismodelle

Es gibt zwei Preismodelle: On-Demand und Flat-Rate. Standardmäßig kommt das On-Demand-Modell zum Einsatz, das 5 USD pro TB der von Abfragen gescannten Daten berechnet. Für die meisten Unternehmen ist das der zentrale Kostentreiber in BigQuery.

Das Flat-Rate- bzw. Slot-Pricing legt einen festen Preis für das Scannen in BigQuery fest – möglicherweise zulasten der Performance. Dabei wird die Anzahl der verfügbaren Slots auf das vorab gebuchte Kontingent begrenzt und der Scan-Preis von 5 USD pro TB entfällt.

Beim Flat-Rate-Pricing können Sie Ihre Architektur so anpassen, dass Sie zusätzlich einen Pool sogenannter Flex-Slots einsetzen, um Ihre bestehenden Slots zu erweitern oder zu ersetzen. Flex-Slots bieten kürzere und flexiblere Commitments mit einer Mindestlaufzeit von 60 Sekunden und der Option, die Slots jederzeit per Kündigung wieder aufzulösen.

Um die Performance des On-Demand-Modells zu erreichen, müssen Sie 20 Pakete à 100 Slots erwerben – zu Kosten von 40.000 USD pro Monat oder 34.000 USD pro Jahr. 2.000 Slots zu kaufen, lohnt sich finanziell nur, wenn Sie mindestens diesen Betrag an BigQuery-Scan-Kosten aufwenden.

Kosten ermitteln

Bevor Sie Ihre Kosten optimieren können, müssen Sie die Datennutzung Ihrer Projekte analysieren. Dafür benötigen Sie Zugriff auf die Abfragedaten in Ihren Projekten und Datasets.

Dafür gibt es zwei Wege: Audit Log Sinks und die INFORMATION_SCHEMA-Tabellen. Der Audit Log Sink ist der bevorzugte Weg, weil die Daten deutlich aussagekräftiger sind . Bei DoiT-Kunden, die das BigQuery Lens-Feature in DoiT Cloud Intelligence™ nutzen, ist der Audit Log Sink in der Umgebung bereits eingerichtet. Weitere Informationen finden Sie hier.

Häufige Fehler bei Abfragen vermeiden

Bevor wir zu den Abfragen kommen, mit denen Sie die eigentlich relevanten Daten erhalten, werfen wir einen Blick auf typische Fehler beim Schreiben von BigQuery-Abfragen. Sie führen oft dazu, dass Abfragen länger laufen und teurer werden, als sie müssten.

1. SELECT *

Das ist wahrscheinlich die größte Quelle unnötiger Zusatzkosten bei BigQuery-Abfragen. In aller Regel ist es überflüssig, sämtliche Spalten einer Tabelle oder View auszuwählen. Denken Sie daran: BigQuery rechnet auf Basis der gescannten Datenmenge ab – schränken Sie Ihre Auswahl also so weit wie möglich ein, um Kosten zu senken.

2. Unnötige oder zu große Joins

In Data Warehouses mit OLAP-Fokus (wie BigQuery) ist es Best Practice, die Schemata in der Datenbank zu denormalisieren. Damit werden die Datenstrukturen im Wesentlichen flach gezogen und die Anzahl benötigter Joins gegenüber einer klassischen relationalen Datenbank reduziert.

3. Cross Joins

Cross Joins sind in BigQuery für mehrere Anwendungsfälle erforderlich. Probleme entstehen aber, wenn sie als innerste Operation in der Abfrage ausgeführt werden – dann werden weit mehr Daten eingelesen, als am Ende ausgegeben werden.

4. Common Table Expressions (CTEs) falsch einsetzen

Common Table Expressions (CTEs) sind großartige Werkzeuge, die SQL-Code enorm vereinfachen. Wenn Sie eine CTE in einer Abfrage jedoch mehrfach referenzieren und damit auch mehrfach ausführen lassen, wird Ihnen das Lesen der Daten entsprechend mehrfach in Rechnung gestellt.

5. Keine Partitionen in WHERE-Klauseln nutzen

Partitionen gehören zu den wichtigsten Funktionen von BigQuery, wenn es um Kostensenkung und Lese-Performance geht. Trotzdem werden sie häufig weggelassen – mit unnötigen Mehrkosten als Folge.

6. Zu komplexe Views verwenden

Komplexe Views können die Performance verschlechtern. Wenn die Logik in einer View zu kompliziert wird, ist sie unter Umständen besser in einer vorberechneten Tabelle oder einer Materialized View aufgehoben, um die Performance zu steigern.

7. Kleine Inserts

Wenn Sie eine kleine Zahl von Datensätzen in eine Tabelle einfügen müssen, fügen Sie die Daten als Batch ein, statt viele kleine Inserts hintereinander auszuführen.

8. DML-Statements übermäßig nutzen

Häufig werden DML-Statements überstrapaziert, weil BigQuery wie ein klassisches RDBMS behandelt und Daten nach Belieben neu erzeugt werden. Die bessere Alternative ist ein "additives Modell": Neue Zeilen werden mit einem Zeitstempel eingefügt, der den aktuellsten Stand markiert, und ältere Zeilen werden regelmäßig entfernt, sofern keine Historie benötigt wird.

Ihre teuersten Abfragen aufspüren

Das E-Book verlinkt auf ein GitHub-Repository mit den SQL-Dateien, die Sie zum Aufspüren Ihrer teuersten Abfragen brauchen. Das sind die drei zentralen Abfragen, die Sie einsetzen werden:

• Teuerste Abfragen insgesamt
• Teuerste Einzelabfragen
• Teuerste Nutzer

Weitere Abfragen im Repository

Das GitHub-Repo enthält darüber hinaus Abfragen für speziellere Zwecke – etwa um aus Looker stammende Abfragen aufzuspüren, die Anzahl der Ausführungen einer Abfrage zu ermitteln oder die Kosten von Abfragen mit bestimmten Labels zu bestimmen.

Performance-kritische Abfragen finden

Nachdem Sie die teuersten Abfragen identifiziert haben, gilt es, jene Abfragen aufzudecken, die mehr Ressourcen verbrauchen als nötig und nicht so performen wie erwartet. Diese Abfragen überschneiden sich oft mit den teuersten – Doppelnennungen sind also möglich.

Performance-Tuning behandeln wir in einem späteren Teil dieser Reihe ausführlicher – mit Fokus auf einige der weniger bekannten Stolperfallen der BigQuery-Performance und auf Methoden, sie zu umgehen.

Das letzte Thema, das wir in diesem Teil streifen, ist eine Reihe von Abfragen im GitHub-Repository, die allgemeinere Informationen oder Metadaten liefern:

• Abfragen nach Job-Typ
• Gleichzeitige Abfragen
• Abfrageanzahl

Wie geht es weiter?

Laden Sie sich The BigQuery Optimization Handbook: Preparing to Save. herunter. Da dieser und die kommenden Teile der Reihe sehr umfangreiches Detailmaterial enthalten, empfehlen wir, schon jetzt einen Audit Log Sink für Ihre BigQuery-Projekte einzurichten, um über die Zeit Daten zu sammeln. So holen Sie aus diesen Abfragen und der gesamten Reihe den größten Mehrwert heraus. Denken Sie daran: Bei bestehenden DoiT-Kunden, die das BigQuery Lens-Feature in DoiT Cloud Intelligence nutzen, ist der Audit Log Sink in der Umgebung bereits eingerichtet.