5 BigQuery Tricks, die ich das nächste Mal anwenden würde
By Alexander Junke.
Dieser Blogpost stellt 5 Tricks vor, die ich im Laufe meiner wachsenden SQL-Erfahrung gelernt habe. Der Post richtet sich vor allem an SQL-Anfänger und SQL-Erfahrene, aber auch Personen mit langjähriger Erfahrung in SQL können vielleicht den einen oder anderen Gedanken mitnehmen.
1. Trick: Nutze einen GROUP BY anstand eines DISTINCT Befehls
Nehmen wir Mal an, wir haben folgende Tabellenstruktur und arbeiten das erste Mal mit diesen Daten:
Wenn man sich diese Datenstruktur nun anschaut, bekommt man einen ersten Eindruck von den Daten, und zwar dass die Anzahl an Verkäufen (sales) und der Umsatz (revenue) voraussichtlich nach Datum (date) und nach einem bestimmten Typ aggregiert werden. Auf den ersten Blick wird aber nicht klar, was für Typen es gibt, heißt welche Werte “type_number” und “type_name” annehmen kann. Um das herauszufinden bietet sich natürlich eine SQL Abfrage an.
Früher habe ich dafür folgende SQL-Query geschrieben:
SELECT DISTINCT type_number, type_name
FROM `dataset_example.table_example`
Ergebnis:
Damit erfährt man, dass der Umsatz und die Verkäufe nach z.B. Shop und Online Verkäufen separiert werden und ein Typ immer eine eindeutige Nummer hat. Danach möchte ich ein erstes Gefühl dafür bekommen, wieviel Umsätze/Verkäufe pro Typ erwirtschaftet wurden.
Dafür würde ich folgende Query schreiben:
SELECT type_number, type_name, SUM(revenue)
FROM `dataset_example.table_example`
GROUP BY 1,2
Nach der Untersuchung der Ergebnisse stellt sich die Frage, warum ich eigentlich die erste Query geschrieben habe. Durch den einfachen GROUP BY habe ich viel mehr Einsichten in die Daten bekommen und habe nur eine Zeile mehr geschrieben. Zusätzlich kann ich mit den Daten viel besser arbeiten. Ich kann viel schneller nach Metriken sortieren, einfach weitere Dimensionen hinzufügen (z.B. das Datum) oder die Daten mit einem WHERE schnell eingrenzen.
Mir ist es sehr oft vorgekommen, dass ich meistens zuerst ein DISTINCT angewendet habe und dann bei mindestens 90% der Queries danach ein GROUP BY Befehl benutzt habe, um mehr über die Tabelle zu erfahren. Natürlich möchte ich hiermit dem DISTINCT Befehl nicht seine Wertigkeit absprechen, z.B bei JOINS kann es sehr nützlich sein, um Duplikate zu vermeiden. Aber für den beschriebenen Fall ist ein DISTINCT Befehl meistens eher unnötig.
Merke: Um einen guten ersten Einblick in eine Tabelle zu bekommen und bestimmte Spalten zu untersuchen, starte gleich mit einem GROUP BY und nicht mit einem DISTINCT.
2. Trick: Nutze SAFE_DIVIDE() anstatt /
Wenn man zwei Metriken dividiert, gibt es in BigQuery zwei unterschiedliche Möglichkeiten:
- den einfachen /
- die BigQuery-Funktion SAFE_DIVIDE()
In meinen ersten BigQuery Queries habe ich immer ein / benutzt, weil es funktioniert hat und ich gar nicht wusste, dass es dafür eine separate Funktion gibt. Dann kommt der Tag, bei dem eines der größten mathematischen Probleme der Menschheit bei der Queryausführung auftritt: die Division durch 0. Nutzt man hierfür ein / gibt BigQuery einen Fehler aus und die Query wird nicht erfolgreich ausgeführt.
Nach kurzer Recherche habe ich die Funktion SAFE_DIVIDE() von BigQuery gefunden. Bei der Nutzung dieser Funktion wird bei der Division durch 0 ein NULL in diese Zelle geschrieben und die Query wird erfolgreich ausgeführt. Demzufolge schreibe ich anstatt “column1 / column2” besser “SAFE_DIVIDE(column1, column2)”.
Merke: Verwende von Anfang an immer einen SAFE_DIVIDE() für die Division zweier Zahlen.
3. Trick: LAST_VALUE() funktioniert nicht wie FIRST_VALUE()
Für diesen Trick sollten Kenntnisse zu sogenannten Window Functions in BigQuery vorhanden sein. Sie berechnen einen Wert über eine Gruppe von Zeilen und geben den berechneten Wert für jede Zeile der Gruppe zurück. Eine der ersten Window Functions die ich kennenlernte durfte war FIRST_VALUE(). Hiermit bekommt man den ersten Wert der Spalte von der geordneten Partition.
Als Beispiel soll der Eintrag von der BigQuery Dokumentation (hier) dienen. In der Tabelle sind Läufer, die in unterschiedlichen Divisionen eingeteilt sind und deren Zeiten gespeichert wurden. Möchte man an jede Zeile die schnellste Zeit der jeweiligen Division speichern, schreibt man folgende Query:
FIRST_VALUE(finish_time) OVER (PARTITION BY division ORDER BY finish_time ASC) AS fastest_time
Um die langsamste Zeit pro Division herauszubekommen, muss man nur die Reihenfolge ändern:
FIRST_VALUE(finish_time) OVER (PARTITION BY division ORDER BY finish_time DESC) AS slowest_time
Damit sind die beiden Funktionalitäten von FIRST_VALUE() abgedeckt. LAST_VALUE() kann für diese Fälle ebenfalls genutzt werden, nur das für die definierte Reihenfolge der letzte Wert genommen wird. Probiert man LAST_VALUE( ) mal an diesem Beispiel aus, bekommt man eine unschöne Überraschung:
LAST_VALUE(finish_time) OVER (PARTITION BY division ORDER BY finish_time DESC) AS fastest_time
Da stimmt etwas nicht, bei diesem Beispiel ist die finish time immer gleich der fastest time. Somit liegt es nah, dass die Partition nicht richtig funktioniert. Mit folgendem Zusatz kann man das erwünschte Ergebnis erzielen:
LAST_VALUE(finish_time) OVER (PARTITION BY division ORDER BY finish_time DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS fastest_time
Die Schlussfolgerung ist, dass bei LAST_VALUE() noch die Anweisung “ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING” hinzugefügt werden muss, bei FIRST_VALUE() muss man es nicht. Hierbei muss man wissen, dass durch PARTITION BY die Partition und durch ROWS das Bearbeitungsfenster definiert wird, welches nicht immer gleich ist. Mit dem Zusatz wird angegeben, dass das Bearbeitungsfenster die komplette Partition ist. Die vollständige Erklärung, warum LAST_VALUE() dieses Verhalten aufweist bzw. den Zusatz benötigt würde den Rahmen dieses Blogs sprengen. Interessierte können für die Recherche hier einsteigen.
Merke: Mit dem Einsatz einer FIRST_VALUE() Window Function kann sowohl der erste als auch der letzte Eintrag einer Partition gesetzt werden, abhängig von der gewählten Reihenfolge in der Partition. Somit kann man den Einsatz von LAST_VALUE() mit dem angesprochenen Zusatz vermeiden.
4. Trick: Vergebe ALIAS für (fast) alles
Die Mächtigkeit von ALIAS habe ich am Anfang meiner SQL-”Karriere” unterschätzt. Vermutlich weil man am Anfang froh ist, wenn alles einigermaßen funktioniert. Umso mehr Queries ich geschrieben habe, desto mehr habe ich ein Gefühl für die Vergabe von ALIAS bekommen. Die größten Vorteile bei der Nutzung von ALIAS sind:
- Man kann den Spalten / Tabellen (für den Einsatzzweck) bessere Namen vergeben (z.B. steht in einer Produkttabelle die Spalte “name”, dann vergebe ich in meiner Query dieser Spalte den Namen “product_name”)
- Man kann die Spalten auch schneller austauschen und muss dann nicht alle darauffolgenden Transformationen ebenfalls umstellen, weil diese auf den ALIAS zugreifen.
Besonders, wenn man zwei verschiedene Produkttabellen miteinander verknüpft, ist die Vergabe von ALIAS sinnvoll. Zusätzlich kann mit der Angabe des Tabellennamens vor dem jeweiligen Spaltennamen viel schneller erkannt werden, welche Daten von welcher Tabelle kommen. Einerseits kann man den Tabellen einen ALIAS geben um sie leichter bei dem JOIN zu adressieren und andererseits kann man durch passende Namen schneller zuweisen, von welcher Tabelle der Wert kommt.
Merke: Das vergeben von ALIAS macht die geschriebene Query übersichtlicher und lesbarer.
5. Trick: Eine einfache Query, um die aktuellsten Daten aus einer Tabelle zu bekommen
Manchmal kommt es vor, dass einer Tabelle jeden Tag die aktuellen Daten, inklusive des Datums vom Update, am Ende der Tabelle hinzugefügt werden. Dieses Vorgehen kann z.B. bei Tabellen mit Metainformationen zum Einsatz kommen, die jeden Tag abgefragt werden und die aktuellste Version der Daten der Tabelle hinzugefügt werden. Dies hat den Vorteil, dass man bei auftretenden Problemen den Verlauf der Änderungen der Metainformationen nachvollziehen kann. Der Nachteil ist, dass man sich ein Vorgehen überlegen muss, um die aktuellsten Daten zu bekommen um Duplikate zu vermeiden.
Was ist nun das beste Vorgehen, um in BigQuery immer mit der aktuellsten Version der Daten zu arbeiten?
Eine Beispieltabelle wäre folgende:
In dem Fall möchten wir ab dem 02.01.2020 immer als Typ 1 den Namen shop berlin und Typ 2 online vergeben.
Dafür erstellen wir in BigQuery eine sogenannte View. Eine View ist eine gespeicherte Query, die in einer nicht materialisierten Tabelle resultiert. Der Aufbau der dazugehörigen Query ist immer ähnlich:
SELECT * EXCEPT (rank)
FROM (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY record_timestamp desc) AS row_number
FROM `dataset1.metadata_table1`
)
WHERE row_number = 1
Hierbei werden alle Zeilen mit dem jüngsten Erstellungszeitpunkt in der “row_number” Spalte mit einer 1 und die älteren Einträge mit jeweils aufsteigenden Zahlen versehen. Danach werden nur die Zeilen ausgewählt, die in der “row_number” Spalte eine 1 haben, welche somit die neuesten Informationen enthalten.
Ein wichtiger Punkt hierbei ist, dass für die Nummerierung der Zeilen die Funktion ROW_NUMBER() genutzt wird und nicht RANK(). Sollte der Fall auftreten, dass für eine Partition mehrmals der gleiche record_timestamp vorhanden ist, dann ist der Wert beim RANK() immer 1. Demzufolge sind im Ergebnis der Abfrage wieder Duplikate enthalten. Wird ROW_NUMBER() genutzt bekommen Zeilen mit dem gleichen Wert im ORDER_BY unterschiedliche Zahlen zugewiesen. Damit wird gewährleistet, das keine Duplikate im Resultat enthalten sind.
Auf die erstellte View kann dann mithilfe des Namens von jeder beliebigen Query zugegriffen werden und das Verhalten ist gleich zu einer physisch vorhandenen Tabelle in der Cloud.
Merke: Erstelle eine View in BigQuery um die aktuellsten Informationen einer Tabelle zu bekommen
Further Links
This post is part of the Google Data Analytics series from datadice and explains to you every month the newest features in BigQuery, Data Studio, Google Analytics and Google Tag Manager.
We also started with our own YouTube channel. We talk about important DWH, BigQuery, Data Studio and many more topics. Check out the channel here.
If you want to learn more about how to use Google Data Studio and take it to the next level in combination with BigQuery, check our Udemy course here.
If you are looking for help to set up a modern and cost-efficient data warehouse or analytical dashboards, send us an email to hello@datadice.io and we will schedule a call.
