Von Ajay Lakshminarayanarao und Allen Fung.

Google Bigquery ist ein mächtiges Werkzeug. Ein Software-Ingenieur, der eine minimalistische Vorstellung von SQL hat, kann es mit Leichtigkeit verwenden. Sie können in Sekundenschnelle große Datenmengen für Erkenntnisse zerhacken. Bei ShareThis schauen wir uns täglich Terabytes an verwertbaren sozialen Daten an und es ist ein integraler Bestandteil unserer Arbeit für Analysen, Prüfungen und Verarbeitung geworden.

Eines der jüngsten Probleme, mit denen wir konfrontiert waren, war die Trennung von Zeilen in einer Bigquery (BQ)-Tabelle nach Ländern. BQ basiert auf einer säulenförmigen Speicherung. Anstatt den gesamten Datensatz auf einem einzigen Volume zu speichern, teilt BQ die Zeile anhand von Spaltenwerten und speichert jeden Wert in einem eigenen Speichervolumen. Um die Vorteile dieses Schemas zu nutzen, haben wir eine effiziente und kostengünstige Abfragelösung entwickelt, um Werte für jedes Land abzurufen, wie nachfolgend beschrieben.

Problem: So trennen Sie eine BQ-Tabelle nach Ländern
Der unkomplizierte Weg

Um eine BQ-Tabelle nach Ländern zu trennen, müssen wir die Tabelle abfragen, so oft wie die Anzahl der Länder.

Nehmen wir an, dass wir die Spalten A, B, C, D und E abfragen müssen. Die Größe jeder Spalte beträgt 20GB. Die abrechenbare Anzahl von Bytes beträgt 100GB für unsere Tabelle.

Wenn wir unsere Anfrage so oft wie die Anzahl der Länder, d.h. 240, ausführen müssen, beträgt unsere abrechenbare Anzahl an Bytes 24.000 GB oder fast 24 TB. Nehmen wir an, die Kosten für 1 TB betragen 5$, unsere Gesamtkosten betragen 120$ für einen BQ-Tisch.

Der optimierte Weg

Der Trick dabei ist, die erforderliche Anzahl von Feldern als eine einzige Spalte zu gruppieren. Dies allein speichert nicht die Anzahl der abgerechneten Bytes. Der zweite Trick ist, um die Trennsäule zu schwenken; in diesem Fall nach Land.

Nach den oben genannten Schritten wird unsere Ergebnistabelle so viele Spalten enthalten wie Länder. Jede Spalte stellt Daten für ein Land dar und enthält die zuvor gruppierten Daten nur für dieses Land.

In unserer BQ-Tabelle von 100 GB können wir von einem wahrscheinlichen Ergebnis ausgehen, da die Spalte, die die USA darstellt, 20 GB beträgt, die 10 wichtigsten Länder im Durchschnitt rund 3 GB und der Rest der 230 Länder im Durchschnitt 0,5 GB.

Unsere abrechenbaren Bytes basieren nun auf der Größe der Daten für jedes Land. Die abrechenbaren Kosten für die Abfrage der USA werden nur 0,1$ betragen, die Kosten für die großen Länder werden jeweils 0,015$ betragen und der Rest der Länder viel kleiner als das.

Die Summe der fakturierbaren Bytes beträgt 100 GB für die gruppierte Tabelle und 165 GB für Abfragen aller Länderzusammenschlüsse. Die Gesamtkosten betragen ca. 1,5$!

Generierung der gruppierten Tabelle

Mit CONCAT können wir die gewünschten Spalten zu einer einzigen Spalte zusammenfassen. Wir können eine Zeile in einem Format erstellen, das eine Zeile in einer CSV-Datei oder ein gültiges JSON-Objekt darstellt.

Im zweiten Schritt besteht die Abfrage aus 240 CASE-Anweisungen, mit einer CASE-Anweisung für jedes Land, das auf eine bestimmte Spalte ausgerichtet ist. Die Anfrage wird groß sein, aber BQ unterstützt eine Anfragegröße von bis zu 256KB. Wir können beide Schritte in einer einzigen Abfrage kombinieren, wobei wir jeweils Concat-Anweisung verwenden.

Beispielabfrage:

AUSWAHL
( CASE WHEN geo.ISO ='US' THEN CONCAT( A,B,C,D,E)) ) ALS LANDUS,
( CASE WHEN geo.ISO = 'CA' THEN CONCAT( A,B,C,D,E)) ) ALS LANDCA .....
VON
mergelog_20160101;