Google BigQuery SQL-Syntax: Ein umfassender Leitfaden

Was ist Google BigQuery? Was kann es für Ihr Unternehmen tun? Ist BigQuery SQL eine wertvolle Fähigkeit? Wo können Sie es lernen und wo können Sie es üben?

Es gibt heute nur noch wenige Unternehmen, die nicht computerisiert sind. Viele Prozesse nutzen heute das IoT (Internet der Dinge), bei dem alle möglichen Geräte vernetzt sind und kontinuierlich Echtzeitdaten in Computersysteme einspeisen. Das Ergebnis ist eine riesige Menge an Daten, die für die Entscheidungsfindung zur Verfügung stehen. Dies wird oft als Datensee bezeichnet.

Die größten Herausforderungen bei diesem Datenstrom sind die Frage, wie und wo sie gespeichert werden sollen und wie sie den Entscheidungsträgern am besten zur Verfügung gestellt werden können. BigQuery von Google ist wohl eine der besten Antworten auf diese Herausforderungen.

BigQuery-SQL-Kenntnisse sind eine wichtige Ergänzung für Ihr Portfolio. Wenn Sie BigQuery lernen möchten, wo sollten Sie anfangen? BigQuery unterstützt ANSI-SQL-Standards, so dass der erste Schritt darin besteht, grundlegende SQL-Kenntnisse zu erwerben.

Wenn Sie noch keine SQL-Kenntnisse haben, werden Sie mit dem Kurs von LearnSQL.de (SQL für Anfänger) sehr schnell auf den neuesten Stand gebracht. Am Ende des Kurses werden Sie in der Lage sein, Daten aus einer oder mehreren Tabellen zu extrahieren, zusammenzufassen und zu analysieren, um aussagekräftige Informationen zu erhalten. Der Kurs dauert etwa 10 Stunden und enthält 129 interaktive Übungen, in denen Sie lernen, reale Probleme mit SQL zu lösen.

Was ist Google BigQuery?

Google BigQuery ist eine serverlose, hoch skalierbare Data-Warehousing-Lösung. Sie kann Terabytes an Daten in Sekunden und Petabytes an Daten in Minuten analysieren. Aber was bedeutet das eigentlich?

Data Warehouse

Daten werden normalerweise für einen von zwei Zwecken gespeichert:

• Zur Unterstützung der täglichen Arbeit eines Unternehmens
• Zur Unterstützung der Entscheidungsfindung.

Der erste Zweck ist als operative Daten bekannt und wird oft von mehreren verschiedenen Computersystemen gespeichert, die verschiedene Aufgaben für das Unternehmen ausführen. Die zweite Gruppe ist als analytische Daten bekannt. Im Idealfall sollten diese Daten an einem zentralen Ort gespeichert werden, an dem sie für jeden, der Informationen benötigt, zugänglich sind.

Dieser zentrale Ort wird als Data Warehouse bezeichnet und sollte so optimiert sein, dass große Datenmengen schnell durchsucht und analysiert werden können. Sie würden einen Prozess verwenden, der als ETL (Extract, Transform, Load) bekannt ist, um operative Daten in das Data Warehouse zu übertragen.

Als Data Warehouse hat BigQuery mehrere Vorteile:

• Es verfügt über hervorragende ETL-Tools.
• Es verwendet das Colossus-Dateisystem, das für "große" Daten ausgelegt ist; bei Bedarf kann problemlos mehr Speicherplatz hinzugefügt werden.
• Es verwendet SQL als Abfragesprache und ist daher mit führenden Business Intelligence (BI)-Tools wie
• Google Data Studio ermöglicht die Darstellung von Daten auf viele verschiedene Arten.

Schnelle Suchvorgänge

BigQuery verwendet die Dremel-Suchmaschine, um große Datenmengen schnell zu verarbeiten. Wie bereits erwähnt, kann BigQuery Petabytes (ein Petabyte entspricht etwa einer Million Gigabytes) in wenigen Minuten durchsuchen. Wie wird dies erreicht?

Jedes Mal, wenn Sie eine Suche in BigQuery durchführen, teilen sich Tausende von Arbeits-Threads die Aufgabe auf; so können enorme Datenmengen gleichzeitig gescannt werden. Die Ergebnisse werden dann zusammengeführt und an den Benutzer zurückgegeben.

Serverlose Architektur

Wenn Sie BigQuery verwenden, müssen Sie keinen Server mieten. Das bedeutet, dass Sie sich nicht um die Verwaltung der Infrastruktur, um Backups und Sicherheit kümmern oder herausfinden müssen, ob Sie mehr Festplatten benötigen. Ihre Daten werden Teil des großen Datensees - oder Ozeans!

Dieser digitale Datenozean ist auf unzählige Server weltweit verteilt. Wenn mehr Speicherplatz benötigt wird, weist Google diesen zu und verwaltet ihn. Sie zahlen nur für das, was Sie nutzen.

Wie sieht es mit der Rechenleistung aus, die für die Berechnungen benötigt wird? Google weist die Rechenleistung zu, wenn sie benötigt wird. Die Hochgeschwindigkeits-Netzwerksoftware von Google, Jupiter, wurde speziell für die schnelle Kommunikation zwischen Threads entwickelt; Sie zahlen nur für die Rechenzeit, die Sie nutzen.

Und wie sieht es mit der Sicherheit aus? Sie können Ansichten für Ihre Daten erstellen und genau festlegen, wer was sehen darf. Da die Daten verschlüsselt werden - sowohl bei der Speicherung als auch bei der Übertragung - sind sie vor Eindringlingen sicher.

BigQuery üben

Wenn Sie bereits SQL-Kenntnisse haben, können Sie BigQuery gleich selbst ausprobieren. Wenn nicht, können Sie trotzdem einige der Beispiele im nächsten Abschnitt ausprobieren, um ein gutes Gefühl für die Möglichkeiten zu bekommen. Um die Vorteile von BigQuery voll ausschöpfen zu können, lohnt es sich jedoch, Ihre SQL-Kenntnisse durch die Teilnahme an einem umfassenden SQL-Kurs aufzubessern. LearnSQL.de Der SQL von A bis Z Learning Track umfasst 7 Kurse, die Sie vom Anfänger bis zum Experten führen.

Wo und wie können Sie diese Fähigkeiten in BigQuery üben? Jeder kann kostenlos ein Projekt in BigQuery erstellen. Gebühren fallen nur an, wenn Sie große Datenmengen laden und verarbeiten möchten. In der BigQuery-Sandbox können Sie Ihre eigenen Testdaten einfügen. Sie können auch auf eine Vielzahl öffentlicher Datenbanken zugreifen, um damit zu spielen.

Um loszulegen, besuchen Sie die BigQuery-Seite der Google Cloud Console. Wenn Sie noch nicht in einem Google-Konto angemeldet sind, werden Sie möglicherweise aufgefordert, dies zu tun. Wenn Sie das erste Mal auf diese Konsole zugreifen, sehen Sie einen Hinweis wie diesen:

Klicken Sie auf PROJEKT ERSTELLEN und geben Sie Ihrem Projekt entweder einen Namen oder akzeptieren Sie den Vorschlag von Google.

Jetzt brauchen Sie einige Daten zum Üben. Glücklicherweise sind in BigQuery viele öffentliche Daten verfügbar. Um auf diese zuzugreifen, müssen Sie die öffentlichen Datensätze zu Ihrem SQL-Arbeitsbereich hinzufügen. Oben links auf der Seite sehen Sie dies:

Klicken Sie auf HINZUFÜGEN. Es erscheint ein Pop-up-Fenster, das Ihnen mehrere Möglichkeiten bietet. Klicken Sie auf diese:

Sie werden nach dem Projektnamen gefragt. Geben Sie bigquery-public-data ein und klicken Sie auf 'STAR'. Dieses Projekt sollte nun im Projekt-Explorer auf der linken Seite des Bildschirms aufgelistet sein. Jetzt können Sie mit der Erkundung beginnen. Wenn Sie auf den Pfeil neben bigquery-public-data klicken, sehen Sie eine Liste aller Datenbanken in diesem Projekt. Und wenn Sie auf eine Datenbank klicken, sehen Sie eine Liste der zugehörigen Tabellen. Die Beispiele im nächsten Abschnitt dieses Artikels stammen aus der Datenbank census_bureau_international.

Wenn Sie bereits Erfahrung mit SQL haben, ist das alles, was Sie brauchen, um loszulegen. Wenn Sie jedoch ein SQL-Neuling sind, möchten Sie vielleicht in einer Umgebung üben, in der Hilfe verfügbar ist und Sie bei der Lösung von Problemen angeleitet werden. In diesem Fall empfehle ich Ihnen den Praxistrack von LearnSQL.de, wo Sie die Möglichkeit haben, über 80 reale Aufgaben zu lösen.

BigQuery-Syntax

Da Sie nun wissen, wie Sie sich mit BigQuery einrichten können, lassen Sie uns einen Blick auf die BigQuery-Syntax werfen. Wie Sie vielleicht bereits wissen, gibt es viele verschiedene SQL-Dialekte, die für unterschiedliche Datenbankprodukte typisch sind. Sie können hier mehr über die verschiedenen SQL-Dialekte erfahren .

Das American National Standards Institute (ANSI) legt die empfohlenen Standards für SQL fest; jeder Dialekt kann, muss aber nicht alle diese Standards implementieren. Viele von ihnen haben ihre eigenen Zusätze zur Sprache, um mehr Funktionalität zu ermöglichen.

BigQuery verfügte ursprünglich über eine nicht standardisierte Version von SQL, die speziell auf seine Bedürfnisse zugeschnitten war. Diese wird heute als "Legacy SQL" bezeichnet, und Sie müssen sie nur lernen, wenn Sie für ein Unternehmen arbeiten wollen, das sie noch verwendet. BigQuery verwendet jetzt Google SQL, das sich sehr eng an den ANSI-Standard hält. Wenn Sie bereits mit einem anderen Dialekt (wie MS SQL Server oder MySQL) vertraut sind, werden Sie bei der Verwendung von BigQuery nur wenige Unterschiede feststellen.

Der größte Unterschied besteht darin, dass BigQuery über einige zusätzliche statistische Funktionen verfügt und komplexe Datenstrukturen wie JSON und Arrays unterstützt.

Sehen wir uns ein paar Beispiele für die Ausführung von Abfragen in der Cloud Console an. Wenn Sie noch nie mit SQL gearbeitet haben und den Beispielen nur schwer folgen können, hilft Ihnen vielleicht dieser Artikel über die wichtigsten SQL-Befehle.

Die vollständige Syntax aller Google SQL-Befehle finden Sie hier.

Ausführen von Befehlen in der BigQuery Cloud-Konsole

Auf der rechten Seite des Projekt-Explorers in der BigQuery Cloud Console sehen Sie ein Fenster, in dem Sie Google SQL-Befehle ausführen können. Es sieht wie folgt aus:

Sie geben Ihre Abfrage in das Abfragefenster ein und klicken dann in der Aktionsleiste oben auf "RUN

. Wenn Sie einen gültigen SQL-Befehl eingegeben haben, werden die angeforderten Daten im Ergebnisfenster angezeigt. Wenn nicht, wird stattdessen eine Fehlermeldung angezeigt.

Einfache SQL-Befehle

Zum Extrahieren und Analysieren von Daten wird nur ein SQL-Befehl verwendet: die Anweisung SELECT. In seiner einfachsten Form lautet der Befehl SELECT * FROM tablename, wobei tablename der Name einer der Tabellen in Ihrer Datenbank ist. Das Sternchen (*) bedeutet, dass alle Spalten der Tabelle zurückgegeben werden sollen.

In Google SQL stellen Sie - sofern die Daten nicht aus Ihrem eigenen Projekt stammen - dem Tabellennamen den Projekt- und Datenbanknamen voran. In den Beispielen werde ich auf Daten aus der Datenbank census_bureau_international im Projekt bigquery-public-data zugreifen. Eine der Tabellen, die ich verwenden werde, heißt midyear_population. Auf diese Tabelle muss ich mich beziehen als:

bigquery-public-data.census_bureau_international.midyear_population

Der Einfachheit halber können Sie diesem recht langen Namen einen Alias geben, damit Sie ihn nicht ständig eingeben müssen. Im folgenden Beispiel gebe ich ihr den Alias midyear.

bigquery-public-data.census_bureau_international.midyear_population AS midyear

Wenn Sie mit Tabellen testen, die sehr groß sein können, ist es eine gute Idee, die Anzahl der zurückgegebenen Zeilen zu begrenzen. Andernfalls könnten Sie die Grenzen der kostenlosen Version von BigQuery überschreiten.

Um alles in den ersten 10 Zeilen dieser Tabelle zu sehen, geben Sie diesen Befehl in das Abfragefenster ein und klicken dann auf RUN:

SELECT * FROM 
bigquery-public-data.census_bureau_international.midyear_population 
AS midyear
LIMIT 10

Probieren Sie es selbst aus. Wenn Sie sich nicht vertippt haben, sollte Ihr Bildschirm wie folgt aussehen:

Sie können die Bildlaufleiste verwenden, um durch die Ergebnisse zu blättern.

Sehen wir uns nun einige der optionalen Funktionen der Anweisung SELECT an.

• Mit der WHERE Klausel können Sie nur Zeilen auswählen, die ein bestimmtes Kriterium erfüllen.
• Anstatt alle Spalten anzuzeigen (d. h. das Sternchen zu verwenden), können Sie die Spaltennamen auflisten, die Sie sehen möchten. In der obigen Abbildung werden die Spaltennamen (country_code, country_name, year und midyear_population) oben in jeder Spalte des Ergebnisfensters angezeigt.
• Sie können die Reihenfolge der Zeilen in der Ergebnismenge mit der Option ORDER BY

Versuchen Sie, diese Abfrage auszuführen, um nur die Ländernamen und Bevölkerungszahlen für 1975 zu sehen, sortiert nach Ländernamen.

SELECT
midyear.country_name, midyear_population
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Wenn Sie alles richtig gemacht haben, sollten Sie dies sehen:

SQL hat viele weitere Funktionen. Zwei der nützlichsten sind:

• Die Verwendung von Aggregatfunktionen, wie z. B. SUM() zur Ermittlung von Gesamtwerten, AVG() zur Ermittlung von Durchschnittswerten und MIN() und MAX() zur Ermittlung der kleinsten/größten Werte. Aggregate werden in der Regel in Verbindung mit der Klausel GROUP BY verwendet, die die Gruppen definiert, für die die Aggregatfunktionen gelten. Ohne Gruppierung der Daten werden die Aggregate für den gesamten Datensatz angezeigt.
• Abrufen von Daten aus zwei oder mehr Tabellen, sofern sie eine oder mehrere Spalten gemeinsam haben, die zur Verknüpfung verwendet werden können.

Lassen Sie uns einige Beispielabfragen ausprobieren. Erstens:

SELECT
midyear.country_name, AVG(midyear.midyear_population)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
GROUP BY country_name
ORDER BY country_name

Diese Abfrage zeigt die durchschnittliche Bevölkerung zur Jahresmitte für jedes Land über den gesamten Zeitraum.

Die nächste Abfrage bezieht Daten aus zwei Tabellen: der Tabelle midyear_population und der Tabelle birth_death_growth_rates. Sie können dann die Wachstumsrate zusammen mit der Bevölkerung sehen.

Sie müssen diese über zwei übereinstimmende Spalten verknüpfen: das Land und das Jahr. Hier ist die Abfrage, gefolgt von ihren Ergebnissen:

SELECT
midyear.country_name, midyear.midyear_population, 
growth.growth_rate
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
JOIN
bigquery-public-data.census_bureau_international.birth_death_growth_rates
AS growth
ON growth.country_name = midyear.country_name
AND growth.year = midyear.year
WHERE midyear.year = 1975
ORDER BY country_name

Für alle diese Abfragen verwendet BigQuery die ANSI-Standardsyntax; die Abfragen würden in jedem SQL-Dialekt gleich gut funktionieren.

Einige Fortgeschrittenes SQL Befehle

Auf die Syntax komplexer SQL-Anweisungen einzugehen, würde den Rahmen dieses Artikels sprengen, aber ich möchte einige fortgeschrittene Abfragen nennen, die in Google SQL dieselbe Syntax haben wie in anderen Dialekten (z. B. MS SQL Server).

Fensterfunktionen werden verwendet, um einzelne Zeilen mit Aggregaten aus dem gesamten Datensatz zu vergleichen. In diesem Beispiel werden die Länder nach der Bevölkerungszahl geordnet - das Land mit der höchsten Bevölkerungszahl steht auf Platz 1.

SELECT
midyear.country_name, midyear.midyear_population, RANK() OVER (ORDER BY midyear_population DESC)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Ihre Ergebnisse sollten wie folgt aussehen:

Weitere erweiterte Funktionen sind GROUP BY-Erweiterungen, mit denen Sie Aggregate auf mehr als einer Ebene anzeigen können. Auch hier folgt die Syntax von BigQuery den ANSI-Standards; wenn Sie diese Funktionen in MS SQL gelernt haben, können Sie sie auch in BigQuery verwenden.

Hier ist ein Beispiel. Hier werden Summen auf den Ebenen Alter und Land angezeigt.

SELECT
m.country_name, m.age, SUM(m.population)
FROM
bigquery-public-data.census_bureau_international.midyear_population_agespecific
AS m
WHERE m.year = 1975
GROUP BY ROLLUP (m.country_name, m.age)
ORDER BY m.country_name, m.age

Die Ergebnisse sehen wie folgt aus. Die Nullen zeigen an, dass es sich bei der Ausgabezeile um eine Gesamtsumme handelt.

Zusätzliche Funktionen von BigQuery

BigQuery hat viele Funktionen, die in den meisten Dialekten nicht zu finden sind. Diese machen es besonders nützlich für den Umgang mit großen Daten, die aus vielen verschiedenen Quellen stammen.

Es hat mehrere zusätzliche Datentypen:

• Geography - Speichert geografische Koordinaten und kann verwendet werden, um die Entfernung zwischen zwei Orten zu berechnen.
• Interval - Speichert ein Zeitintervall.
• JSON - Ein von vielen Anwendungen verwendetes Format.
• Struct - Ein Container zur Aufnahme geordneter Felder.

BigQuery ermöglicht auch die Arbeit mit Arrays.

Bei der Arbeit mit sehr großen Datensätzen ist es besonders nützlich, Zufallsstichproben von Daten extrahieren zu können. Hier ist ein Beispiel für die Entnahme einer Zufallsstichprobe aus der Tabelle midyear_population.

SELECT
AVG (m.life_expectancy)
FROM
bigquery-public-data.census_bureau_international.mortality_life_expectancy
AS m
TABLESAMPLE SYSTEM (10 PERCENT)

Anstatt die gesamte Tabelle zu durchsuchen, die sehr groß ist, wird eine statistische Stichprobe von 10 % der Tabelle gezogen. Da es sich um eine Zufallsstichprobe handelt, kann es sein, dass Sie bei jedem Durchlauf etwas andere Ergebnisse erhalten. Die Ausgabe könnte wie folgt aussehen:

Weitere Informationen über die zusätzlichen Funktionen von BigQuery SQL finden Sie in der Produktdokumentation.

Arbeiten mit Google BigQuery: Der nächste Schritt

Der digitale Ozean wird jedes Jahr größer, und immer mehr Arbeitgeber suchen nach BigQuery-Kenntnissen. Es lohnt sich, ein Experte auf diesem Gebiet zu werden. Aber wie?

Verbessern Sie Ihre SQL-Kenntnisse durch die Teilnahme an einigen Kursen. LearnSQL.de bietet eine breite Palette von Kursen, die alles von Anfängern bis hin zu fortgeschrittenen Themen abdecken. Sie können nach bestimmten Kursen suchen oder einfach das Angebot durchstöbern.

LearnSQL.deIm Praxiskurs von SQL lernen Sie, alle Arten von Problemen mit SQL zu lösen. Sie sollten auch viel Zeit damit verbringen, mit den öffentlichen Datensätzen in Google Cloud Console zu experimentieren. Wenn Sie Ihr eigenes Unternehmen haben und ein SQL-Schulungsprogramm für Ihre Mitarbeiter einrichten möchten, können wir Ihnen auch dabei helfen.

BigQuery ist auf dem Vormarsch, also fangen Sie jetzt an, ein Experte zu werden!