GSP281

Übersicht

SQL (Structured Query Language) ist eine Standardsprache für Datenoperationen, mit der Sie Fragen stellen und aus strukturierten Datasets Informationen gewinnen können. Diese Sprache wird häufig in der Datenbankverwaltung genutzt. Mit SQL können Sie beispielsweise Transaktionsdaten in relationale Datenbanken schreiben und Daten im Petabytebereich analysieren.

Das Lab besteht aus zwei Blöcken: Im ersten Block lernen Sie grundlegende Schlüsselwörter zur SQL-Abfrage kennen. Diese Schlüsselwörter verwenden Sie anschließend in BigQuery für ein öffentliches Dataset mit Informationen zu Fahrradverleihsystemen in London.

Im zweiten Block lernen Sie, wie Teilmengen des Dataset zu Fahrradverleihsystemen in London in CSV-Dateien exportiert werden, die dann nach Cloud SQL hochgeladen werden. Danach wird gezeigt, wie mit Cloud SQL Datenbanken und Tabellen erstellt und verwaltet werden. Am Ende des Labs erwarten Sie praktische Übungen mit zusätzlichen SQL-Schlüsselwörtern, mit denen Daten verarbeitet und bearbeitet werden.

Lerninhalte

Aufgaben in diesem Lab:

• Daten in BigQuery einlesen
• Einfache Abfragen in BigQuery zur Analyse von Daten ausführen
• Eine Datenteilmenge in eine CSV-Datei exportieren und diese Datei in einem neuen Cloud Storage-Bucket speichern
• Eine neue Cloud SQL-Instanz erstellen und die exportierte CSV-Datei als eine neue Tabelle laden

Vorbereitung

Sehr wichtig: Melden Sie sich von Ihrem privaten oder beruflichen Gmail-Konto ab, bevor Sie mit diesem Lab beginnen.

Dies ist ein Einführungs-Lab. Es werden keine oder nur wenige Vorkenntnisse zu SQL vorausgesetzt. Grundkenntnisse in Cloud Storage und Cloud Shell werden empfohlen, sind aber keine Voraussetzung. In diesem Lab lernen Sie die Grundlagen, um Abfragen in SQL zu lesen und zu schreiben. Diese Kenntnisse wenden Sie dann mit BigQuery und Cloud SQL an.

Bevor Sie dieses Lab absolvieren, sollten Sie Ihre SQL-Kenntnisse überprüfen. Hier sind zwei anspruchsvollere Labs, in denen Sie Ihr Wissen anhand von Anwendungsfällen für Fortgeschrittene testen können:

• Wetterdaten in BigQuery
• Daten zu Geburtsraten mit Vertex AI und BigQuery analysieren

Wenn Sie bereit sind, können Sie im nächsten Schritt Ihre Lab-Umgebung einrichten.

Einrichtung und Anforderungen

Vor dem Klick auf „Start Lab“ (Lab starten)

Lesen Sie diese Anleitung. Labs sind zeitlich begrenzt und können nicht pausiert werden. Der Timer beginnt zu laufen, wenn Sie auf Lab starten klicken, und zeigt Ihnen, wie lange die Ressourcen für das Lab verfügbar sind.

In diesem praxisorientierten Lab können Sie die Lab-Aktivitäten in einer echten Cloud-Umgebung selbst durchführen – nicht in einer Simulations- oder Demo-Umgebung. Dazu erhalten Sie neue, temporäre Anmeldedaten, mit denen Sie für die Dauer des Labs auf Google Cloud zugreifen können.

Für dieses Lab benötigen Sie Folgendes:

• Einen Standardbrowser (empfohlen wird Chrome)

Hinweis: Nutzen Sie den privaten oder Inkognitomodus, um dieses Lab durchzuführen. So wird verhindert, dass es zu Konflikten zwischen Ihrem persönlichen Konto und dem Teilnehmerkonto kommt und zusätzliche Gebühren für Ihr persönliches Konto erhoben werden.

• Zeit für die Durchführung des Labs – denken Sie daran, dass Sie ein begonnenes Lab nicht unterbrechen können.

Hinweis: Wenn Sie über ein persönliches Google Cloud-Konto oder -Projekt verfügen, verwenden Sie es nicht für dieses Lab. So werden zusätzliche Kosten für Ihr Konto vermieden.

Lab starten und bei der Google Cloud Console anmelden

1. Klicken Sie auf Lab starten. Wenn Sie für das Lab bezahlen müssen, wird ein Pop-up-Fenster geöffnet, in dem Sie Ihre Zahlungsmethode auswählen können. Auf der linken Seite befindet sich der Bereich Details zum Lab mit diesen Informationen:

   • Schaltfläche Google Cloud Console öffnen
   • Restzeit
   • Temporäre Anmeldedaten für das Lab
   • Ggf. weitere Informationen für dieses Lab

2. Klicken Sie auf Google Cloud Console öffnen (oder klicken Sie mit der rechten Maustaste und wählen Sie Link in Inkognitofenster öffnen aus, wenn Sie Chrome verwenden).

   Im Lab werden Ressourcen aktiviert. Anschließend wird ein weiterer Tab mit der Seite Anmelden geöffnet.

   Tipp: Ordnen Sie die Tabs nebeneinander in separaten Fenstern an.

   Hinweis: Wird das Dialogfeld Konto auswählen angezeigt, klicken Sie auf Anderes Konto verwenden.

3. Kopieren Sie bei Bedarf den folgenden Nutzernamen und fügen Sie ihn in das Dialogfeld Anmelden ein.

   {{{user_0.username | "Username"}}}

   Sie finden den Nutzernamen auch im Bereich Details zum Lab.

4. Klicken Sie auf Weiter.

5. Kopieren Sie das folgende Passwort und fügen Sie es in das Dialogfeld Willkommen ein.

   {{{user_0.password | "Password"}}}

   Sie finden das Passwort auch im Bereich Details zum Lab.

6. Klicken Sie auf Weiter.

   Wichtig: Sie müssen die für das Lab bereitgestellten Anmeldedaten verwenden. Nutzen Sie nicht die Anmeldedaten Ihres Google Cloud-Kontos. Hinweis: Wenn Sie Ihr eigenes Google Cloud-Konto für dieses Lab nutzen, können zusätzliche Kosten anfallen.

7. Klicken Sie sich durch die nachfolgenden Seiten:

   • Akzeptieren Sie die Nutzungsbedingungen.
   • Fügen Sie keine Wiederherstellungsoptionen oder Zwei-Faktor-Authentifizierung hinzu (da dies nur ein temporäres Konto ist).
   • Melden Sie sich nicht für kostenlose Testversionen an.

Nach wenigen Augenblicken wird die Google Cloud Console in diesem Tab geöffnet.

Hinweis: Wenn Sie sich eine Liste der Google Cloud-Produkte und ‑Dienste ansehen möchten, klicken Sie oben links auf das Navigationsmenü.

Aufgabe 1: Die Grundlagen von SQL

Datenbanken und Tabellen

Wie bereits erwähnt, können Sie mit SQL Informationen aus „strukturierten Datasets“ abrufen. Strukturierte Datasets haben klare Regeln und Formatierungen und sind oft in Tabellen organisiert oder bestehen aus Daten, die in Zeilen und Spalten formatiert sind.

Ein Beispiel für unstrukturierte Daten ist eine Bilddatei. SQL kann nicht mit unstrukturierten Daten umgehen. Außerdem können unstrukturierte Daten nicht (oder zumindest nicht nativ) in BigQuery-Datasets oder Tabellen gespeichert werden. Sie müssen einen Dienst wie Cloud Vision (ggf. direkt über seine API) verwenden, um zum Beispiel mit Bilddateien arbeiten zu können.

Im Folgenden sehen Sie ein Beispiel für ein strukturiertes Dataset – eine einfache Tabelle:

Nutzer	Preis	Versendet
Peter	35 $	Ja
Max	50 $	Nein

Wenn Sie Google Tabellen kennen, dann sollte die oben dargestellte Tabelle recht ähnlich aussehen. Die Tabelle enthält die Spalten „Nutzer“, „Preis“ und „Versendet“ sowie zwei Zeilen mit ausgefüllten Spaltenwerten.

Eine Datenbank ist im Wesentlichen eine Sammlung von einer oder mehreren Tabellen. SQL ist ein Managementtool für strukturierte Datenbanken. Sie werden jedoch recht häufig (auch in diesem Lab) Abfragen für eine oder mehrere miteinander verknüpfte Tabellen ausführen und nicht für komplette Datenbanken.

SELECT und FROM

SQL ist an natürlicher Sprache ausgerichtet. Bevor Sie eine Abfrage Ihrer Daten ausführen, ist es daher immer hilfreich, genau zu wissen, was Sie fragen möchten (es sei denn, Sie probieren nur aus Spaß etwas aus).

SQL bietet vordefinierte Schlüsselwörter, mit denen Sie Ihre Frage in das Pseudo-Englisch der SQL-Syntax übersetzen können. Auf diese Weise gibt Ihnen das Datenbankmodul die Antwort zurück, die Sie haben möchten.

Die wichtigsten Schlüsselwörter sind SELECT und FROM:

• SELECT verwenden Sie, um anzugeben, welche Felder des Datasets abgerufen werden sollen.
• Mit FROM legen Sie fest, aus welchen Tabellen Sie Daten abrufen.

Mit einem Beispiel wird es klarer. Nehmen wir an, dass Ihnen die Tabelle example_table vorliegt. Diese Tabelle enthält die Spalten NUTZER, PREIS und VERSENDET:

An dieser Stelle sollen nur Daten aus der Spalte NUTZER abgerufen werden. Dazu führen Sie die folgende Abfrage mit SELECT und FROM aus:

SELECT USER FROM example_table

Mit dem obigen Befehl werden alle Namen der Spalte NUTZER, die in example_table gefunden wurden, ausgewählt.

Mit dem SQL-Schlüsselwort SELECT können Sie mehrere Spalten auswählen. Ein Beispiel: Sie möchten die Daten aus den Spalten NUTZER und VERSENDET abrufen. Dazu verändern Sie die vorherige Abfrage, indem Sie noch einen Spaltenwert zur SELECT-Abfrage hinzufügen. Dabei müssen Sie darauf achten, dass der neue Spaltenwert durch ein Komma getrennt ist:

SELECT USER, SHIPPED FROM example_table

Mit dieser Abfrage werden die Daten für NUTZER und VERSENDET aus dem Speicher abgerufen:

Das wars auch schon. Jetzt kennen Sie zwei grundlegende SQL-Schlüsselwörter. Nun wird es noch ein bisschen interessanter.

WHERE

WHERE ist ein weiteres SQL-Schlüsselwort, mit dem Tabellen nach bestimmten Spaltenwerten gefiltert werden können. Ein Beispiel: Sie möchten die Namen aus example_table abrufen, für die Pakete versendet wurden. Hierzu können Sie die Abfrage folgendermaßen um das Schlüsselwort WHERE erweitern:

SELECT USER FROM example_table WHERE SHIPPED='YES'

Mit dieser Abfrage werden alle in der Spalte NUTZER aufgeführten Nutzer, deren Pakete versendet wurden (VERSENDET), aus dem Speicher zurückgegeben:

Nachdem Sie nun die wichtigsten SQL-Schlüsselwörter kennen, wenden wir die Abfragetypen in der BigQuery-Konsole an.

Testen Sie Ihr Wissen

Im Folgenden stellen wir Ihnen einige Multiple-Choice-Fragen, um Ihr bisher erworbenes Wissen zu den Konzepten zu testen und zu festigen. Beantworten Sie die Fragen so gut Sie können.

Aufgabe 2: Die BigQuery-Konsole kennenlernen

Das BigQuery-Modell

BigQuery ist ein vollständig verwaltetes Data Warehouse im Petabytebereich, das auf Google Cloud ausgeführt wird. Data Analysts oder Data Scientists können schnell große Datasets abfragen und filtern, Ergebnisse zusammenfassen und komplexe Operationen ausführen, ohne dafür Server einrichten oder verwalten zu müssen. BigQuery liegt entweder in Form eines Befehlszeilentools (in Cloud Shell vorinstalliert) oder einer Webkonsole vor. Mit beiden Varianten können Sie Daten in Google Cloud-Projekten umgehend verwalten und abfragen.

In diesem Lab nutzen Sie die Webkonsole, um SQL-Abfragen auszuführen.

Die BigQuery Console öffnen

1. Klicken Sie in der Google Cloud Console im Navigationsmenü auf BigQuery.

Zuerst wird das Fenster Willkommen bei BigQuery in der Cloud Console geöffnet, das neben allgemeinen Informationen auch einen Link zur Kurzanleitung und zu den Versionshinweisen enthält.

2. Klicken Sie auf Fertig.

Die BigQuery Console wird geöffnet.

Sehen Sie sich nun einige wichtige Elemente der Benutzeroberfläche genauer an. Auf der rechten Seite der Konsole befindet sich unter „Editor“ der Abfrageeditor. Hier schreiben Sie SQL-Befehle, etwa die vorherigen Beispiele, und führen sie aus. Unter dem Abfrageeditor befindet sich der „Abfrageverlauf“, eine Liste der Abfragen, die Sie bisher ausgeführt haben.

Im linken Bereich der Konsole finden Sie das Navigationsmenü. Neben dem selbsterklärenden Abfrageverlauf, den gespeicherten Abfragen und dem Jobverlauf finden Sie hier auch den Tab Explorer.

Die Ressourcen auf der obersten Ebene des Tabs Explorer enthalten Google Cloud-Projekte. Diese entsprechen Ihren temporären Google Cloud-Projekten, bei denen Sie sich anmelden und die Sie in den Google Cloud Skills Boost-Labs verwenden. Wie Sie in der Konsole und im letzten Screenshot sehen können, wird auf dem Tab „Explorer“ nur Ihr Projekt angezeigt. Wenn Sie auf den Pfeil neben dem Projektnamen klicken, werden keine weiteren Elemente eingeblendet.

Der Grund dafür ist, dass Ihr Projekt keine Datasets oder Tabellen enthält – es gibt nichts, was abgefragt werden kann. Zuvor haben Sie gelernt, dass Datasets Tabellen enthalten. Wenn Sie Ihrem Projekt Daten hinzufügen, beachten Sie, dass in BigQuery Projekte Datasets enthalten und Datasets Tabellen enthalten. Da Sie nun ein besseres Verständnis für das Modell „Projekt > Dataset > Tabelle“ und die Funktionsweise der Konsole haben, können Sie abfragbare Daten hochladen.

Abfragbare Daten hochladen

In diesem Abschnitt binden Sie einige öffentliche Daten in Ihr Projekt ein, um die SQL-Befehle in BigQuery in der Praxis auszuprobieren.

1. Klicken Sie auf + HINZUFÜGEN.

2. Wählen Sie Projekt nach Name markieren aus.

3. Geben Sie als Projektnamen bigquery-public-data ein.

4. Klicken Sie auf MARKIEREN.

Beachten Sie, dass Sie in diesem neuen Tab immer noch im Lab-Projekt arbeiten. Sie haben lediglich ein öffentlich zugängliches Projekt mit Datasets und Tabellen zur Analyse in BigQuery eingebunden – Sie sind nicht zu diesem Projekt gewechselt. Alle Ihre Jobs und Dienste sind weiterhin an Ihr Google Cloud Skills Boost-Konto gebunden. Sie können dies selbst überprüfen, indem Sie das Feld „Projekt“ oben in der Konsole ansehen:

5. Sie haben jetzt Zugang zu den folgenden Daten:

• Google Cloud-Projekt → bigquery-public-data
• Dataset → london_bicycles

6. Klicken Sie auf das Dataset london_bicycles, um die zugehörigen Tabellen aufzurufen:

• Tabelle → cycle_hire
• Tabelle → cycle_stations

In diesem Lab verwenden Sie Daten aus cycle_hire. Öffnen Sie die Tabelle cycle_hire und klicken Sie dann auf den Tab Vorschau. Die Seite sollte in etwa so aussehen:

Prüfen Sie die Spalten und die Werte in den Zeilen. Sie können nun einige SQL-Abfragen in der Tabelle cycle_hire ausführen.

SELECT, FROM und WHERE in BigQuery ausführen

Sie haben nun ein grundlegendes Verständnis der SQL-Schlüsselwörter und des BigQuery-Modells sowie einige Daten, mit denen Sie arbeiten können. Mit diesem Dienst können Sie einige SQL-Befehle ausführen.

Unten rechts in der Konsole sehen Sie, dass es 83.434.866 Datenzeilen gibt, d. h., dass in London zwischen 2015 und 2017 tatsächlich so viele Einzelfahrten mit Leihfahrrädern unternommen wurden.

Beachten Sie nun in der siebten Spalte den Schlüssel end_station_name. Dieser steht für den Zielort der Fahrten mit den Leihfahrrädern. Bevor wir tiefer einsteigen, führen wir eine einfache Abfrage aus, um die Spalte end_station_name zu isolieren.

1. Kopieren Sie den folgenden Befehl und fügen Sie ihn in den Abfrageeditor ein:

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

2. Klicken Sie dann auf Ausführen.

Nach ungefähr 20 Sekunden sollten die 83.434.866 Zeilen der abgefragten Spalte end_station_name zurückgegeben werden.

Nun möchten wir herausfinden, welche Fahrten 20 Minuten oder länger gedauert haben.

3. Löschen Sie die Abfrage aus dem Editor und führen Sie dann die folgende Abfrage mit dem Schlüsselwort WHERE aus:

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

Diese Abfrage dauert etwa eine Minute.

Durch SELECT * werden alle Spaltenwerte aus der Tabelle zurückgegeben. Die Dauer wird in Sekunden gemessen. Aus diesem Grund haben Sie den Wert 1.200 gewählt (60 × 20).

Unten rechts sehen Sie nun, dass 26.441.016 Zeilen zurückgegeben wurden. Das bedeutet, dass in London ca. 30 % (26.441.016 ÷ 83.434.866) aller Fahrten mit Leihfahrrädern mindestens 20 Minuten gedauert haben.

Testen Sie Ihr Wissen

Im Folgenden stellen wir Ihnen einige Multiple-Choice-Fragen, um Ihr bisher erworbenes Wissen zu testen und zu festigen. Beantworten Sie die Fragen so gut Sie können.

Aufgabe 3: Weitere SQL-Schlüsselwörter: GROUP BY, COUNT, AS und ORDER BY

GROUP BY

Das Schlüsselwort GROUP BY fasst Zeilen mit Ergebnismengen zusammen, die den gleichen Kriterien (d. h. dem gleichen Spaltenwert) entsprechen, und gibt alle eindeutigen Einträge für ein solches Kriterium zurück.

Es ist ein nützliches Schlüsselwort, um aus Tabellen Informationen nach Kategorien herauszufiltern.

1. Um eine bessere Vorstellung von der Funktionsweise dieses Schlüsselworts zu erhalten, löschen Sie die Abfrage aus dem Editor, kopieren Sie den folgenden Befehl und fügen Sie ihn ein:

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Klicken Sie auf Ausführen.

Als Ergebnis erhalten Sie eine Liste eindeutiger, nur einmal vorkommender Spaltenwerte.

Ohne das Schlüsselwort GROUP BY wären durch die Abfrage alle 83.434.866 Zeilen zurückgegeben worden. Mit GROUP BY werden nur die eindeutigen Spaltenwerte aus der Tabelle ausgegeben. Dies können Sie selbst unten rechts überprüfen. Sie sehen hier 954 Zeilen, was bedeutet, dass es in London 954 unterschiedliche Startpunkte für den Fahrradverleih gibt.

COUNT

Das Schlüsselwort COUNT() gibt die Anzahl der Zeilen zurück, die denselben Kriterien (d. h. dem gleichen Spaltenwert) entsprechen. Diese Funktion kann in Kombination mit GROUP BY sehr hilfreich sein.

Fügen Sie unserer vorherigen Abfrage die Funktion COUNT hinzu, um herauszufinden, wie viele Fahrten an den einzelnen Startpunkten begonnen wurden.

• Löschen Sie die Abfrage aus dem Editor, kopieren Sie den folgenden Befehl, fügen Sie ihn ein und klicken Sie auf Ausführen:

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

Ihre Ausgabe enthält die Anzahl der Fahrten, die an den einzelnen Startpunkten mit Leihfahrrädern begonnen wurden.

AS

AS ist ein SQL-Schlüsselwort, das einen Alias einer Tabelle oder einer Spalte erstellt. Ein Alias ist ein neuer Name, der einer zurückgegebenen Spalte oder Tabelle gegeben wird – je nachdem, was durch AS definiert wird.

1. Fügen Sie der vorherigen Abfrage das Schlüsselwort AS hinzu, um in der Praxis zu sehen, was geschieht. Löschen Sie die Abfrage aus dem Editor, kopieren Sie den folgenden Befehl und fügen Sie ihn ein:

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Klicken Sie auf Ausführen.

Für die Anzeige der Ergebnisse wird der Name der rechten Spalte von COUNT(*) in num_starts geändert.

Wie Sie sehen, hat die Spalte COUNT(*) in der zurückgegebenen Tabelle nun den Aliasnamen num_starts. Vor allem bei großen Datasets ist dies ein praktisches Schlüsselwort. Schließlich kommen mehrdeutige Bezeichnungen für Tabellen oder Spalten häufiger vor, als man denkt.

ORDER BY

Das Schlüsselwort ORDER BY sortiert die zurückgegebenen Daten einer Abfrage anhand eines bestimmten Kriteriums oder Spaltenwerts in aufsteigender oder absteigender Reihenfolge. Fügen Sie dieses Schlüsselwort in unsere vorherige Abfrage ein, um Folgendes zu erreichen:

• Rückgabe einer Tabelle mit der Anzahl der Fahrten mit Leihfahrrädern, die an den einzelnen Startpunkten begonnen wurden – alphabetisch sortiert nach dem Stationsnamen
• Rückgabe einer Tabelle mit der Anzahl der Fahrten mit Leihfahrrädern, die an den einzelnen Startpunkten begonnen wurden – aufsteigend sortiert nach der Anzahl der Fahrten
• Rückgabe einer Tabelle mit der Anzahl der Fahrten mit Leihfahrrädern, die an den einzelnen Startpunkten begonnen wurden – absteigend sortiert nach der Anzahl der Fahrten

Jeder der folgenden Befehle ist eine separate Abfrage. Führen Sie für jeden Befehl Folgendes aus:

1. Löschen Sie den Inhalt im Abfrageeditor.
2. Kopieren Sie den Befehl in den Abfrageeditor.
3. Klicken Sie auf Ausführen. Sehen Sie sich die Ergebnisse an.

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

In den Ergebnissen der letzten Abfrage sind die Startstandorte nach der Anzahl der Starts vom jeweiligen Standort aus aufgeführt.

Sie sehen, dass die meisten Fahrten in „Belgrove Street, King's Cross“ begonnen wurden. Der Anteil der Fahrten, die an dieser Station begonnen wurden, entspricht jedoch insgesamt weniger als 1 % (234.458 ÷ 83.434.866).

Testen Sie Ihr Wissen

Im Folgenden stellen wir Ihnen einige Multiple-Choice-Fragen, um Ihr bisher erworbenes Wissen zu den Konzepten zu testen und zu festigen. Beantworten Sie die Fragen so gut Sie können.

Aufgabe 4: Mit Cloud SQL arbeiten

Abfragen als CSV-Dateien exportieren

Cloud SQL ist ein vollständig verwalteter Datenbankdienst, mit dem Sie Ihre relationalen PostgreSQL- und MySQL-Datenbanken ganz einfach in der Cloud einrichten und verwalten können. Cloud SQL kann mit zwei Dateitypen arbeiten: Dumpdateien (.sql) oder CSV-Dateien (.csv). Sie lernen, wie Sie Teilmengen aus der Tabelle cycle_hire in CSV-Dateien exportieren und diese in Cloud Storage als Zwischenspeicherort hochladen.

In der BigQuery-Konsole sollten Sie als Letztes den folgenden Befehl ausgeführt haben:

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

1. Klicken Sie im Bereich „Abfrageergebnisse“ auf ERGEBNISSE SPEICHERN > CSV (lokale Datei). Hierdurch wird die Abfrage als CSV-Datei heruntergeladen und gespeichert. Notieren Sie sich den Speicherort und den Namen der heruntergeladenen Datei, denn Sie benötigen diese Informationen bald.

2. Löschen Sie den Inhalt im Abfrageeditor und führen Sie darin den folgenden Befehl aus:

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

Mit diesem Befehl wird eine Tabelle zurückgegeben, die die Anzahl der Fahrten mit Leihfahrrädern enthält, die an den einzelnen Endpunkten beendet wurden, und zwar absteigend sortiert nach Anzahl der Fahrten.

3. Klicken Sie im Bereich „Abfrageergebnisse“ auf ERGEBNISSE SPEICHERN > CSV (lokale Datei). Hierdurch wird die Abfrage als CSV-Datei heruntergeladen und gespeichert. Notieren Sie sich den Speicherort und den Namen der heruntergeladenen Datei, denn Sie benötigen diese Informationen im nächsten Abschnitt.

CSV-Dateien in Cloud Storage hochladen

1. Rufen Sie die Cloud Console auf, um dort einen Storage-Bucket zu erstellen, in den Sie die eben erstellten Dateien hochladen können.

2. Klicken Sie auf das Navigationsmenü > Cloud Storage > Buckets und dann auf BUCKET ERSTELLEN.

Hinweis: Wenn Sie auf nicht gespeicherte Arbeit hingewiesen werden, klicken Sie auf VERLASSEN.

3. Geben Sie einen eindeutigen Namen für Ihren Bucket ein, übernehmen Sie bei allen anderen Einstellungen die Standardeinstellung und klicken Sie auf Erstellen.

4. Wenn Sie dazu aufgefordert werden, klicken Sie im Dialogfeld Der öffentliche Zugriff wird verhindert auf Bestätigen.

Abgeschlossene Aufgabe testen

Klicken Sie unten auf Fortschritt prüfen. Wenn Sie den Bucket erfolgreich erstellt haben, sehen Sie ein Testergebnis.

Cloud Storage-Bucket erstellen

Sie sollten sich jetzt in der Cloud Console befinden, in der der neue Cloud Storage-Bucket zu sehen ist.

1. Klicken Sie auf DATEIEN HOCHLADEN und wählen Sie die CSV-Datei aus, die die Daten aus start_station_name enthält.

2. Klicken Sie auf Öffnen. Wiederholen Sie diese Schritte für die Daten aus end_station_name.

3. Benennen Sie die Datei start_station_name um, indem Sie auf die drei Punkte neben der Datei und dann auf Umbenennen klicken. Benennen Sie die Datei in start_station_data.csv um.

4. Benennen Sie die Datei end_station_name um, indem Sie auf die drei Punkte neben der Datei und dann auf Umbenennen klicken. Benennen Sie die Datei in end_station_data.csv um.

Es sollten nun start_station_data.csv und end_station_data.csv in der Liste Objekte auf der Seite Bucket-Details angezeigt werden.

Abgeschlossene Aufgabe testen

Klicken Sie auf Fortschritt prüfen. Wenn Sie CSV-Objekte in Ihren Bucket hochgeladen haben, erhalten Sie ein Testergebnis.

CSV-Dateien in Cloud Storage hochladen

Aufgabe 5: Cloud SQL-Instanz erstellen

Klicken Sie in der Konsole im Navigationsmenü auf SQL.

1. Klicken Sie auf INSTANZ ERSTELLEN > MySQL auswählen.

2. Geben Sie für die Instanz‑ID my-demo ein.

3. Geben Sie in das Feld Passwort ein sicheres Passwort ein und merken Sie sich dieses.

4. Wählen Sie für die Datenbankversion MySQL 8 aus.

5. Entscheiden Sie sich bei Cloud SQL-Version auswählen für die Option Enterprise.

6. Wählen Sie für Voreinstellung die Option Entwicklung (4 vCPU, 16 GB RAM, 100 GB Speicher, Einzelne Zone) aus.

Warnung: Wenn Sie eine Voreinstellung auswählen, die größer als „Entwicklung“ ist, wird Ihr Projekt gekennzeichnet und das Lab beendet.

7. Setzen Sie das Feld Mehrere Zonen (Hochverfügbarkeit) auf .

8. Klicken Sie auf INSTANZ ERSTELLEN.

Hinweis: Das Erstellen der Instanz kann einige Minuten dauern. Wenn sie erstellt ist, wird neben dem Instanznamen auf der Seite der SQL-Instanzen ein grünes Häkchen angezeigt.

9. Klicken Sie auf die Cloud SQL-Instanz. Die Seite SQL-Übersicht wird geöffnet.

Abgeschlossene Aufgabe testen

Klicken Sie unten auf Fortschritt prüfen. Wenn Sie eine Cloud SQL-Instanz erfolgreich eingerichtet haben, erhalten Sie ein Testergebnis.

Cloud SQL-Instanz erstellen

Aufgabe 6: Neue Abfragen in Cloud SQL

Schlüsselwort CREATE (Datenbanken und Tabellen)

Nachdem die Cloud SQL-Instanz läuft, fügen Sie ihr mithilfe der Cloud Shell-Befehlszeile eine Datenbank hinzu.

1. Klicken Sie zum Öffnen von Cloud Shell auf das Symbol oben rechts in der Konsole.

2. Führen Sie den folgenden Befehl aus, um Ihre Projekt‑ID als Umgebungsvariable festzulegen.

export PROJECT_ID=$(gcloud config get-value project) gcloud config set project $PROJECT_ID

Datenbank in Cloud Shell erstellen

1. Führen Sie den folgenden Befehl in Cloud Shell aus, um Auth einzurichten, ohne einen Browser zu öffnen.

gcloud auth login --no-launch-browser

Sie erhalten damit einen Link zum Öffnen in Ihrem Browser. Öffnen Sie den Link in dem Browser, mit dem Sie sich beim Qwiklabs-Konto angemeldet haben. Wenn Sie angemeldet sind, erhalten Sie einen Bestätigungscode zum Kopieren. Fügen Sie diesen Code in Cloud Shell ein.

2. Führen Sie den folgenden Befehl aus, um eine Verbindung zu Ihrer SQL-Instanz herzustellen. Ersetzen Sie dabei my-demo, wenn Sie für Ihre Instanz einen anderen Namen verwendet haben:

gcloud sql connect my-demo --user=root --quiet Hinweis: Es kann einige Minuten dauern, bis die Verbindung zu Ihrer Instanz hergestellt ist. Wenn Sie die Nachricht „Vorgang fehlgeschlagen, da ein anderer Vorgang bereits begonnen hatte“ erhalten, müssen Sie warten, bis die SQL-Instanz erstellt ist, und dann erneut versuchen, eine Verbindung herzustellen.

3. Geben Sie bei entsprechender Aufforderung Ihr Root-Passwort für die Instanz ein.

Es sollte in etwa Folgendes angezeigt werden:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 8.0.31-google (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Eine Cloud SQL-Instanz enthält vorkonfigurierte Datenbanken. Sie erstellen jedoch Ihre eigene Datenbank, um die Daten aus dem Londoner Fahrradverleih zu speichern.

4. Führen Sie den folgenden Befehl in der Eingabeaufforderung des MySQL-Servers aus, um eine Datenbank mit dem Namen bike zu erstellen:

CREATE DATABASE bike;

Sie sollten folgende Ausgabe erhalten:

Query OK, 1 row affected (0.05 sec) mysql>

Abgeschlossene Aufgabe testen

Klicken Sie auf Fortschritt prüfen. Wenn Sie eine Datenbank in der Cloud SQL-Instanz erstellt haben, wird ein Testergebnis angezeigt.

Datenbank erstellen

Tabelle in Cloud Shell erstellen

1. Erstellen Sie mit dem folgenden Befehl eine Tabelle in der Datenbank „bike“:

USE bike; CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

In diesem Befehl wird das Schlüsselwort CREATE verwendet, wobei mit der Klausel TABLE angegeben wird, dass anstelle einer Datenbank eine Tabelle erstellt werden soll. Das Schlüsselwort USE gibt die Datenbank an, mit der die Tabelle verknüpft werden soll. Sie haben nun eine Tabelle mit dem Namen „london1“, die zwei Spalten enthält: „start_station_name“ und „num“. VARCHAR(255) gibt für die Spalte einen in der Länge variablen String mit bis zu 255 Zeichen als Typ an, und INT steht für eine Spalte des Typs „Ganzzahl“.

2. Erstellen Sie eine Tabelle mit dem Namen „london2“, indem Sie den folgenden Befehl ausführen:

USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

3. Prüfen Sie jetzt, ob leere Tabellen erstellt wurden. Geben Sie dazu in der Eingabeaufforderung des MySQL-Servers die folgenden Befehle ein:

SELECT * FROM london1; SELECT * FROM london2;

Für beide Befehle sollten Sie die folgende Ausgabe erhalten:

Empty set (0.04 sec)

Es wird „Empty set“ angezeigt, da noch keine Daten geladen wurden.

CSV-Dateien in Tabellen hochladen

Kehren Sie zur Cloud SQL-Konsole zurück. Dort können Sie die beiden CSV-Dateien start_station_name und end_station_name in die neu erstellen Tabellen „london1“ und „london2“ hochladen.

1. Klicken Sie auf der Instanzseite in Cloud SQL auf IMPORTIEREN.
2. Klicken Sie im Feld „Cloud Storage-Datei“ auf Durchsuchen. Klicken Sie dann auf den Pfeil neben dem Bucket-Namen und anschließend auf start_station_data.csv. Klicken Sie auf Auswählen.
3. Wählen Sie CSV als Dateiformat aus.
4. Wählen Sie die Datenbank bike aus und geben Sie london1 als Tabelle ein.
5. Klicken Sie auf Importieren.

Wiederholen Sie diese Schritte für die andere CSV-Datei.

1. Klicken Sie auf der Instanzseite in Cloud SQL auf IMPORTIEREN.
2. Klicken Sie im Feld „Cloud Storage-Datei“ auf Durchsuchen. Klicken Sie dann auf den Pfeil neben dem Bucket-Namen und anschließend auf end_station_data.csv. Klicken Sie auf Auswählen.
3. Wählen Sie CSV als Dateiformat aus.
4. Wählen Sie die Datenbank bike aus und geben Sie london2 als Tabelle ein.
5. Klicken Sie auf Importieren.

Es sollten nun beide CSV-Dateien in die Tabellen der Datenbank bike hochgeladen sein.

1. Kehren Sie zur Cloud Shell-Sitzung zurück und führen Sie den folgenden Befehl in der Eingabeaufforderung des MySQL-Servers aus, um die Inhalte von „london1“ zu prüfen:

SELECT * FROM london1;

Es sollten 955 Zeilen ausgegeben werden, jeweils eine für einen eindeutigen Stationsnamen.

2. Führen Sie den folgenden Befehl aus, um sicherzugehen, dass auch „london2“ mit Daten gefüllt ist:

SELECT * FROM london2;

Es sollten 959 Zeilen ausgegeben werden, jeweils eine für einen eindeutigen Stationsnamen.

Schlüsselwort DELETE

Hier finden Sie weitere Schlüsselwörter, die bei der Datenverarbeitung nützlich sind. Das erste ist das Schlüsselwort DELETE.

• Führen Sie die folgenden Befehle in Ihrer MySQL-Sitzung aus, um jeweils die erste Zeile von „london1“ und „london2“ zu löschen:

DELETE FROM london1 WHERE num=0; DELETE FROM london2 WHERE num=0;

Nach der Ausführung der einzelnen Befehle sollten Sie jeweils folgende Ausgabe erhalten:

Query OK, 1 row affected (0.04 sec)

Die gelöschten Zeilen in den CSV-Dateien waren die Spaltenüberschriften. Mit dem Schlüsselwort DELETE löscht man nicht generell die erste Zeile einer Datei, sondern alle Zeilen der Tabelle, in denen der Spaltenname (in diesem Fall „num“) einen bestimmten Wert enthält (in diesem Fall „0“). Wenn Sie die Abfragen SELECT * FROM london1; und SELECT * FROM london2; ausführen und an den Anfang der Tabelle scrollen, sehen Sie, dass diese Zeilen gelöscht wurden.

Schlüsselwort INSERT INTO

Sie können auch mit dem Schlüsselwort INSERT INTO Werte in Tabellen eingeben.

• Führen Sie folgenden Befehl aus, um eine neue Zeile in „london1“ einzufügen. Damit werden start_station_name auf „test destination“ und num auf „1“ gesetzt:

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

Für das Schlüsselwort INSERT INTO ist eine Tabelle („london1“) erforderlich, in die eine neue Zeile mit Spalten eingefügt wird. Diese Spalten werden von den Begriffen in der ersten Klammer spezifiziert (in diesem Fall „start_station_name“ und „num“). Was nach „VALUES“ angegeben wird, wird als Wert in die neue Zeile eingefügt.

Sie sollten folgende Ausgabe erhalten:

Query OK, 1 row affected (0.05 sec)

Wenn Sie die Abfrage SELECT * FROM london1; ausführen, sehen Sie am Ende der Tabelle „london1“ eine weitere Zeile.

Schlüsselwort UNION

Das letzte SQL-Schlüsselwort, das wir behandeln, ist UNION. Dieses Schlüsselwort kombiniert die Ausgabe aus mindestens zwei SELECT-Abfragen zu einer Ergebnismenge. Sie verwenden UNION, um Teilmengen aus den Tabellen „london1“ und „london2“ zu kombinieren.

Mit der folgenden verketteten Abfrage rufen Sie bestimmte Daten aus beiden Tabellen ab und kombinieren sie mit dem Schlüsselwort UNION.

• Geben Sie dazu in der Eingabeaufforderung des MySQL-Servers den folgenden Befehl ein:

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000 UNION SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;

Mit der ersten SELECT-Abfrage werden die beiden Spalten der Tabelle „london1“ ausgewählt und es wird für „start_station_name“ ein Alias mit dem Namen „top_stations“ erstellt. Das Schlüsselwort WHERE wird verwendet, um nur die Fahrradverleihstationen abzurufen, an denen mehr als 100.000 Fahrten begonnen wurden.

Mit der zweiten SELECT-Abfrage werden die beiden Spalten der Tabelle „london2“ ausgewählt. Das Schlüsselwort WHERE wird verwendet, um nur die Fahrradverleihstationen abzurufen, an denen mehr als 100.000 Fahrten geendet haben.

Das Schlüsselwort UNION zwischen diesen beiden Abfragen kombiniert die Ausgaben der beiden Abfragen und verarbeitet die Daten aus „london2“ zusammen mit denen aus „london1“. Da „london1“ mit „london2“ zusammengeführt wird, haben die Spaltenwerte „top_stations“ und „num“ Vorrang.

Mit ORDER BY wird die finale, zusammengeführte Tabelle anhand der Spalte „top_stations“ in absteigender alphabetischer Reihenfolge sortiert.

Sie sollten folgende Ausgabe erhalten:

Wie Sie sehen, teilen sich beim Fahrradverleih 13 von 14 Stationen die besten Plätze als Start- und Endpunkte. Sie konnten mithilfe von grundlegenden SQL-Schlüsselwörtern ein Dataset von beträchtlicher Größe abfragen und haben Datenpunkte erhalten, um bestimmte Fragen zu beantworten.

Glückwunsch!

In diesem Lab haben Sie sich mit den Grundlagen von SQL vertraut gemacht und gelernt, wie Sie Schlüsselwörter verwenden sowie Abfragen in BigQuery und Cloud SQL ausführen. Sie haben die zentralen Konzepte zu Projekten, Datenbanken und Tabellen kennengelernt. Die Schlüsselwörter haben Sie in der Praxis angewendet und damit Daten verarbeitet und bearbeitet. Sie haben erfahren, wie Daten in BigQuery eingelesen und wie Abfragen für Tabellen ausgeführt werden. Sie wissen nun, wie Sie Instanzen in Cloud SQL erstellen, und haben geübt, Datenteilmengen in Tabellen zu übertragen, die sich in Datenbanken befinden. Abfragen haben Sie in SQL verkettet und ausgeführt, um interessante Schlüsse über die Start- und Endpunkte des Fahrradverleihsystems in London zu ziehen.

Weitere Informationen

Üben Sie die Anwendung von Cloud SQL und BigQuery mit den folgenden Google Cloud Skill Boost-Labs:

• Wetterdaten in BigQuery
• NCAA-Daten mit BigQuery untersuchen
• Daten in Google Cloud SQL laden
• Cloud SQL mit Terraform

Erfahren Sie mehr über Data Science mit Data Science on the Google Cloud Platform, 2nd Edition: O'Reilly Media, Inc..

Google Cloud-Schulungen und -Zertifizierungen

In unseren Schulungen erfahren Sie alles zum optimalen Einsatz unserer Google Cloud-Technologien und können sich entsprechend zertifizieren lassen. Unsere Kurse vermitteln technische Fähigkeiten und Best Practices, damit Sie möglichst schnell mit Google Cloud loslegen und Ihr Wissen fortlaufend erweitern können. Wir bieten On-Demand-, Präsenz- und virtuelle Schulungen für Anfänger wie Fortgeschrittene an, die Sie individuell in Ihrem eigenen Zeitplan absolvieren können. Mit unseren Zertifizierungen weisen Sie nach, dass Sie Experte im Bereich Google Cloud-Technologien sind.

Anleitung zuletzt am 02. Februar 2024 aktualisiert

Lab zuletzt am 02. Februar 2024 getestet

© 2024 Google LLC. Alle Rechte vorbehalten. Google und das Google-Logo sind Marken von Google LLC. Alle anderen Unternehmens- und Produktnamen können Marken der jeweils mit ihnen verbundenen Unternehmen sein.