Wann ist es sinnvoll, Google BigQuery zu verwenden?

Im Laufe meiner Karriere musste ich komplexe analytische Abfragen für verschiedene Arten von Berichten und Diagrammen schreiben. Meistens handelte es sich um ein Diagramm, das Daten aggregiert nach Datum, Woche, Quartal usw. anzeigte. Normalerweise werden solche Berichte erstellt, um Kunden dabei zu helfen, Trends zu erkennen und zu veranschaulichen, wie sich ihr Geschäft auf hohem Niveau entwickelt. Aber was passiert, wenn Datenwissenschaftler und Ingenieure einen viel umfangreicheren Bericht auf der Grundlage eines großen Datensatzes erstellen müssen?

Falls der Bericht auf einem kleinen Datensatz basiert, kann die Aufgabe gelöst werden, indem eine SQL-Abfrage unter einer relationalen Datenbank geschrieben wird. In diesem Schritt ist es wichtig, die Grundlagen zum Schreiben von Abfragen zu kennen und sie schneller und effizienter zu gestalten. Manchmal hängt der Bericht jedoch von einem größeren Datensatz ab (z. B. Millionen und mehr Zeilen in einer Tabelle), aber der Bericht ist nicht von Eingabevariablen (Parametern) abhängig, oder Sie stellen möglicherweise fest, dass die Anzahl der Werte recht gering ist. In solchen Szenarien kann eine SQL-Abfrage langsam sein, sodass es für Benutzer nicht optimal wäre, zu warten, bis die Abfrage ausgeführt wird. Die häufigste Vorgehensweise in solchen Fällen besteht darin, eine Abfrage im Voraus auszuführen, bevor der Client einen Bericht anfordert.

Außerdem müssen einige Caching-Funktionen implementiert werden, damit der Client Daten aus dem Cache entnehmen kann, anstatt eine Abfrage in Echtzeit auszuführen. Dieser Ansatz funktioniert perfekt, vorausgesetzt, Sie müssen keine Echtzeitdaten anzeigen. Es kann Daten anzeigen, die eine Stunde oder sogar einen Tag früher berechnet wurden. Der eigentliche Bericht/das Diagramm wird also anhand von zwischengespeicherten Daten angezeigt, nicht basierend auf Echtzeitdaten.

Wenden wir uns Google BigQuery zu

Während ich an einem Analyseprojekt in der Pharmaindustrie arbeitete, brauchte ich Diagramme, die die Postleitzahl und den Medikamentennamen als Eingabeparameter enthielten. Ich musste auch einige Vergleiche zwischen Medikamenten in bestimmten Regionen der Vereinigten Staaten zeigen.

Die analytische Abfrage war sehr komplex und lief am Ende rund 50 Minuten auf unserem Postgres-Server (Quad-Core-CPU mit 16 GB RAM). Ich konnte es nicht im Voraus ausführen und die Ergebnisse zwischenspeichern, da die Abfrage Postleitzahlen und Medikamente als Eingabeparameter verwendete, sodass es Tausende von Kombinationen gab und es unmöglich war, vorherzusagen, welcher Kunde sich entscheiden würde.

Selbst wenn ich versuchen wollte, alle Eingabeparameterkombinationen auszuführen, wäre meine Datenbank höchstwahrscheinlich abgestürzt. Es war also an der Zeit, einen anderen Ansatz zu wählen und eine einfach zu bedienende Lösung auszuwählen. Dieses Diagramm war für den Kunden wichtig, aber der Kunde war nicht bereit, große Änderungen an der Architektur vorzunehmen oder vollständig auf eine andere DB zu migrieren.

Bei diesem speziellen Projekt haben wir ein paar verschiedene Ansätze ausprobiert:

• Vertikale Skalierung des Servers (Hinzufügen von RAM und CPU zum Postgres-Server)
• Verwendung alternativer DBs wie Amazon Redshift und anderer.
• Wir haben auch eine NoSQL-Lösung recherchiert, aber die meisten davon sind ziemlich komplex und erfordern viele Änderungen in der Architektur, von denen viele für den Kunden zu groß gewesen wären.

Schließlich haben wir Google BigQuery ausprobiert. Es erfüllte unsere Erwartungen und ermöglichte es uns, die Arbeit zu erledigen, ohne große Änderungen vorzunehmen, die der Kunde nur ungern genehmigen würde. Aber was ist Google BigQuery und wie funktioniert es?

BigQuery ist ein REST-basierter Webdienst, mit dem Sie komplexe analytische SQL-basierte Abfragen für große Datensätze ausführen können. Nachdem wir die Daten in BigQuery hochgeladen und dieselbe Abfrage wie bei Postgres ausgeführt hatten (die Syntax ist unheimlich ähnlich), lief unsere Abfrage viel schneller und dauerte etwa eine Minute. Letztendlich haben wir allein durch die Nutzung eines anderen Dienstes eine 50-fache Leistungssteigerung erzielt. Es ist erwähnenswert, dass andere DBs nicht den gleichen Leistungsgewinn lieferten, und seien wir großzügig und sagen wir einfach, dass sie nicht einmal annähernd waren. Um ehrlich zu sein, war ich wirklich beeindruckt von der Leistungssteigerung durch BigQuery, da die Zahlen besser waren, als wir alle erhofft hatten.

Trotzdem würde ich BigQuery nicht als beste Datenbanklösung der Welt anpreisen. Obwohl es für unser Projekt gut funktioniert hat, hat es immer noch viele Einschränkungen, wie z. B. eine begrenzte Anzahl von Aktualisierungen in der Tabelle pro Tag, Einschränkungen der Datengröße pro Anfrage und andere. Sie müssen verstehen, dass BigQuery nicht als Ersatz für eine relationale Datenbank verwendet werden kann und auf die Ausführung analytischer Abfragen ausgerichtet ist, nicht auf einfache CRUD-Operationen und -Abfragen.

In diesem Artikel werde ich versuchen, die Verwendung von Postgres (meiner bevorzugten relationalen Datenbank) und BigQuery für reale Anwendungsszenarien zu vergleichen. Außerdem würde ich ein paar Anregungen mit auf den Weg geben, nämlich meine Meinung dazu, wann es tatsächlich sinnvoll ist, BigQuery zu verwenden.

Beispieldaten

Um Postgres und Google BigQuery zu vergleichen, habe ich öffentliche demografische Informationen für jedes Land genommen, gruppiert nach Land, Alter, Jahr und Geschlecht (Sie können dieselben Daten von diesem Link herunterladen).

Ich habe die Daten zu vier Tabellen hinzugefügt:

1. populations
2. locations
3. age_groups
4. populations_aggregated

Die letzte Tabelle enthält nur aggregierte Daten aus den vorherigen drei Tabellen. Hier ist das DB-Schema:

Die populations , die ich am Ende hatte, enthält mehr als 6,9 Millionen Zeilen. Es ist nicht zu viel, aber für meinen Test hat es gereicht.

Basierend auf Beispieldaten habe ich versucht, Abfragen zu erstellen, die zum Erstellen von Analyseberichten und Diagrammen aus dem wirklichen Leben verwendet werden können. Also habe ich Abfragen für die nächsten Berichte vorbereitet:

1. Bevölkerung in den USA aggregiert nach Jahren.
2. Bevölkerung im Jahr 2019 für alle Länder, beginnend mit den größten Ländern.
3. Die fünf „ältesten“ Nationen pro Jahr. „Älteste“ bezeichnet Länder, in denen der Anteil der über 60-Jährigen an der Gesamtzahl der Menschen am höchsten ist. Die Abfrage sollte fünf Ergebnisse pro Jahr liefern.
4. Top 5 Nationen aggregiert nach Jahr, wo der Unterschied zwischen männlicher und weiblicher Bevölkerung am größten ist.
5. Erhalten Sie das mittlere (Durchschnitts-)Alter pro Land für jedes Jahr, beginnend mit den „ältesten“ bis zu den „jüngsten“ Ländern.
6. Finden Sie jedes Jahr die fünf „sterbendsten“ Länder. „Sterbend“ bedeutet Länder, in denen die Bevölkerung abnimmt (die Entvölkerung ist am höchsten).

Die Abfragen Nr. 1, Nr. 2 und Nr. 6 sind recht einfach und unkompliziert, aber die Abfragen Nr. 3, Nr. 4 und Nr. 5 waren nicht so einfach zu schreiben – zumindest für mich. Bitte beachten Sie, dass ich ein Back-End-Ingenieur bin und das Schreiben komplexer SQL-Abfragen nicht gerade mein Spezialgebiet ist, sodass jemand mit mehr SQL-Erfahrung wahrscheinlich intelligentere Abfragen erstellen könnte. Im Moment müssen wir jedoch überprüfen, wie Postgres und BigQuery dieselben Abfragen mit denselben Daten verarbeiten.

Ich habe insgesamt 24 Abfragen erstellt:

• 6 für Postgres DB, die nicht aggregierte Tabellen verwenden ( populations , locations , age_groups )
• 6 für Postgres DB, die die populations_aggregated -Tabelle verwenden
• 6+6 Abfragen für BigQuery, die aggregierte und nicht aggregierte Tabellen verwenden.

Lassen Sie mich BigQuery-Abfragen Nr. 1 und Nr. 5 für aggregierte Daten teilen, damit Sie die Komplexität einfacher (Nr. 1) und komplexer Nr. 5-Abfragen verstehen können.

Bevölkerung in den USA aggregiert nach Jahren Abfrage:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Abfrage des Durchschnittsalters pro Land und Jahr, sortiert vom ältesten zum jüngsten:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Hinweis: Sie finden alle Abfragen in meinem Bitbucket-Repository (Link befindet sich am Ende des Artikels).

Testergebnisse

Zum Ausführen von Abfragen habe ich zwei verschiedene Postgres-Server verwendet. Der erste hat 1 CPU-Kern und 4 GB RAM, unterstützt durch ein SSD-Laufwerk. Der zweite hat 16 CPU-Kerne, 64 GB RAM und verwendete auch ein SSD-Laufwerk (der zweite Server hat das 16-fache des CPU- und RAM-Potenzials).

Beachten Sie auch, dass die Datenbanken während der Testausführung nicht belastet wurden. Ich habe sie nur zum Ausführen von Abfragen erstellt. In realen Situationen dauern Abfragen länger, da möglicherweise andere Abfragen gleichzeitig ausgeführt werden und außerdem parallel ausgeführte Abfragen Tabellen sperren können. Zur Überprüfung der Abfragegeschwindigkeit habe ich pgAdmin3 und die BigQuery-Weboberfläche verwendet.

In meinem Test habe ich diese Ergebnisse erhalten:

Lassen Sie mich diese Ergebnisse in einem Balkendiagramm für Abfrage Nr. 1 und Abfrage Nr. 5 darstellen.

Hinweis: Die Postgres-Datenbank befand sich auf einem US-basierten Server, und ich lebe in Europa, daher gab es eine zusätzliche Verzögerung bei der Datenübertragung von Postgres.

BigQuery-Leistung und Schlussfolgerungen

Basierend auf den Ergebnissen, die ich erhalten habe, habe ich die folgenden Schlussfolgerungen gezogen:

• Wenn Postgres vertikal skaliert wird, sogar um das 16-fache, liefert es nur 10-25 % der Leistung bei der Ausführung einer einzelnen Abfrage. Mit anderen Worten, ein Postgres-Server mit nur einem CPU-Kern und 4 GB RAM führte Abfragen mit einer Zeit aus, die der Zeit sehr ähnlich war, die für einen Server mit 16 CPU-Kernen und 64 GB RAM benötigt wurde. Größere Server können natürlich viel größere Datensätze verarbeiten, dies führt jedoch nicht zu einer wesentlichen Verbesserung der Abfrageausführungszeit.
• Für Postgres-Joins mit kleinen Tabellen ( locations -Tabelle hat etwa 400 Zeilen und age_groups hat 100 Zeilen) ergeben keinen großen Unterschied im Vergleich zu ausgeführten Abfragen unter aggregierten Daten, die sich in einer Tabelle befinden. Außerdem habe ich festgestellt, dass bei Abfragen, die ein bis zwei Sekunden dauern, Abfragen mit inneren Verknüpfungen schneller sind, aber bei Abfragen mit langer Laufzeit ist die Situation anders.
• In BigQuery ist die Situation mit Joins völlig anders. BigQuery mag keine Verknüpfungen. Der Zeitunterschied zwischen Abfragen, die aggregierte und nicht aggregierte Daten verwenden, ist ziemlich groß (für Abfragen #3 und $5 waren es etwa zwei Sekunden). Das bedeutet, dass Sie für BigQuery beliebig viele Unterabfragen durchführen können, aber für eine gute Leistung sollte die Abfrage nur eine Tabelle verwenden.
• Postgres ist schneller für Abfragen, die eine einfache Aggregation oder Filterung verwenden oder einen kleinen Datensatz verwenden. Ich habe festgestellt, dass Abfragen, die in Postgres weniger als fünf Sekunden dauern, in BigQuery langsamer arbeiten.
• BigQuery schneidet bei lang andauernden Abfragen viel besser ab. Je größer der Unterschied in der Datensatzgröße wird, desto unterschiedlicher wird die Zeit, die diese Abfragen zum Abschließen benötigen.

Wann es sinnvoll ist, BigQuery zu verwenden

Kommen wir nun zurück zum Kernthema dieses Artikels: Wann sollten Sie Google BigQuery tatsächlich verwenden? Basierend auf meinen Schlussfolgerungen würde ich vorschlagen, BigQuery zu verwenden, wenn die folgenden Bedingungen erfüllt sind:

• Verwenden Sie es, wenn Sie Abfragen haben, die länger als fünf Sekunden in einer relationalen Datenbank ausgeführt werden. Die Idee von BigQuery besteht darin, komplexe analytische Abfragen auszuführen, was bedeutet, dass es keinen Sinn macht, Abfragen auszuführen, die eine einfache Aggregation oder Filterung durchführen. BigQuery eignet sich für „schwere“ Abfragen, also solche, die mit einer großen Menge an Daten arbeiten. Je größer das Dataset ist, desto mehr Leistung erzielen Sie wahrscheinlich durch die Verwendung von BigQuery. Der von mir verwendete Datensatz war nur 330 MB groß (Megabyte, nicht einmal Gigabyte).
• BigQuery mag keine Verknüpfungen, daher sollten Sie Ihre Daten in einer Tabelle zusammenführen, um eine bessere Ausführungszeit zu erzielen. BigQuery ermöglicht das Speichern von Abfrageergebnissen in einer neuen Tabelle. Um also eine neue aggregierte Tabelle zu erstellen, laden Sie einfach alle Ihre Daten in BigQuery hoch, führen eine Abfrage aus, die alle Daten konsolidiert, und speichern sie einfach in einer neuen Tabelle.
• BigQuery eignet sich gut für Szenarien, in denen sich Daten nicht oft ändern und Sie den Cache verwenden möchten, da es über einen integrierten Cache verfügt. Was bedeutet das? Wenn Sie dieselbe Abfrage ausführen und die Daten in Tabellen nicht geändert (aktualisiert) werden, verwendet BigQuery nur zwischengespeicherte Ergebnisse und versucht nicht, die Abfrage erneut auszuführen. Außerdem berechnet BigQuery kein Geld für zwischengespeicherte Abfragen. Hinweis: Selbst zwischengespeicherte Abfragen benötigen 1–1,2 Sekunden, um Ergebnisse zurückzugeben.
• Sie können BigQuery auch verwenden, wenn Sie die Belastung Ihrer relationalen Datenbank reduzieren möchten. Analytische Abfragen sind „schwer“ und eine übermäßige Verwendung unter einer relationalen Datenbank kann zu Leistungsproblemen führen. Sie könnten also gezwungen sein, über die Skalierung Ihres Servers nachzudenken. Mit BigQuery können Sie diese laufenden Abfragen jedoch zu einem Drittanbieterdienst verschieben, sodass sie sich nicht auf Ihre relationale Hauptdatenbank auswirken.

Abschließend noch ein paar Worte zur Verwendung von BigQuery im wirklichen Leben. Bei unserem realen Projekt änderten sich die Daten für die Berichte wöchentlich oder monatlich, sodass wir Daten manuell in BigQuery hochladen konnten. Wenn sich Ihre Daten jedoch häufig ändern, ist die Synchronisierung von Daten zwischen Ihrer relationalen Datenbank und BigQuery möglicherweise nicht so einfach, und dies ist eine Einschränkung, die Sie beachten sollten.

Verknüpfungen

Die in diesem Artikel verwendeten Beispieldaten finden Sie hier, während die Abfragen und Daten im CSV-Format hier zugänglich sind.