GSP416

Übersicht

BigQuery ist eine vollständig verwaltete, automatisierte und kostengünstige Analysedatenbank von Google. Mit diesem Tool können Sie mehrere Terabyte an Daten abfragen und müssen dabei weder eine Infrastruktur verwalten, noch benötigen Sie einen Datenbankadministrator. BigQuery basiert auf SQL und die Abrechnung erfolgt nach dem "Pay as you go"-Modell. Mithilfe von BigQuery können Sie sich auf die Datenanalyse konzentrieren und wichtige Informationen erhalten.

In diesem Lab arbeiten Sie mit semistrukturierten Daten (JSON-Daten aufnehmen, Array-Datentypen) in BigQuery. Durch das Denormalisieren eines Schemas in eine einzelne Tabelle mit verschachtelten und wiederkehrenden Feldern lassen sich Leistungsverbesserungen erzielen. Die SQL-Syntax kann jedoch kompliziert sein, wenn mit Array-Daten gearbeitet wird. Sie üben, wie Sie verschiedene semistrukturierte Datasets laden und abfragen, Probleme beheben und Verschachtelungen aufheben.

Aufgaben

Aufgaben in diesem Lab:

• Semistrukturierte Daten laden und abfragen sowie Verschachtelungen aufheben
• Fehler bei Abfragen zu semistrukturierten Daten korrigieren.

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ü.

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.

Aufgabe 1: Ein neues Dataset zum Speichern der Tabellen erstellen

1. Klicken Sie in BigQuery auf die drei Punkte neben der Projekt-ID und wählen Sie Dataset erstellen aus:

2. Nennen Sie das neue Dataset fruit_store. Für die restlichen Optionen können Sie die Standardwerte beibehalten (Speicherort der Daten, Standard-Tabellenablauf).

3. Klicken Sie auf Dataset erstellen.

Aufgabe 2: Mit Arrays in SQL arbeiten

Normalerweise gibt es in SQL für jede Zeile einen einzigen Wert, wie z. B. in folgender Liste von Obstsorten:

Zeile	Obstsorte
1	Himbeere
2	Brombeere
3	Erdbeere
4	Kirsche

Was wäre aber, wenn Sie eine Liste von Obstartikeln für jede Person im Geschäft sehen möchten? Die Ausgabe könnte in diesem Fall ungefähr so aussehen:

Zeile	Obstsorte	Person
1	Himbeere	Sarah
2	Brombeere	Sarah
3	Erdbeere	Sarah
4	Kirsche	Sarah
5	Orange	Frederick
6	Apfel	Frederick

In einer traditionellen relationalen SQL-Datenbank würden Sie nach sich wiederholenden Namen suchen und die obige Tabelle in zwei separate Tabellen aufteilen, nämlich „Obstsorten“ und „Personen“. Dieser Vorgang heißt Normalisierung und beschreibt die Aufteilung von Tabellenspalten in mehrere Tabellen. Für transaktionale Datenbanken wie mySQL ist das eine gängige Methode.

Im Data-Warehouse-Prozess wird der Vorgang oft umgekehrt: Bei der Denormalisierung werden viele Einzeltabellen zu einer großen Berichtstabelle zusammengeführt.

Jetzt lernen Sie eine weitere Methode kennen, bei der Daten in verschiedenen Detaillierungsgraden mithilfe wiederkehrender Felder in einer Tabelle gespeichert werden:

Zeile	Obstsorte (Array)	Person
1	Himbeere	Sarah
	Brombeere
	Erdbeere
	Kirsche
2	Orange	Frederick
	Apfel

Was fällt Ihnen an der Tabelle auf?

• Sie hat nur zwei Zeilen.
• Es gibt mehrere Feldwerte für "Obstsorte" in einer Zeile.
• Die Personen sind mit allen Feldwerten verknüpft.

Das sind die Merkmale des Datentyps Array.

So lässt sich das Obstsorten-Array leichter interpretieren:

Zeile	Obstsorte (Array)	Person
1	[Himbeere, Brombeere, Erdbeere, Kirsche]	Sarah
2	[Orange, Apfel]	Frederick

Beide Tabellen drücken exakt dasselbe aus. Daraus folgen zwei wichtige Erkenntnisse:

• Ein Array ist einfach eine Liste von Elementen in eckigen Klammern [ ].
• In BigQuery werden Arrays vereinfacht dargestellt. Die Werte im Array werden einfach vertikal dargestellt, obwohl sie jeweils zu einer eigenen Zeile gehören.

Sie können es selbst ausprobieren.

1. Geben Sie dazu im BigQuery-Abfrageeditor Folgendes ein:

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array

2. Klicken Sie auf Ausführen.

3. Probieren Sie danach folgenden Befehl aus:

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array

Sie sollten jetzt eine Fehlermeldung erhalten, die so aussieht:

Error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1] (Fehler: Array-Elemente mit den Typen {INT64, STRING} haben keinen gemeinsamen Supertypen [3:1])

In Arrays kann jeweils nur ein Datentyp verwendet werden: nur Strings oder nur Zahlen.

4. Die endgültige Tabelle erhalten Sie mit folgender Abfrage:

#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;

5. Klicken Sie auf Ausführen.

6. Nachdem Sie sich die Ergebnisse angesehen haben, klicken Sie auf den Tab JSON, um die verschachtelte Struktur der Ergebnisse aufzurufen.

Semistrukturierte JSON-Daten in BigQuery laden

Angenommen, Sie haben eine JSON-Datei, die in BigQuery aufgenommen werden soll. Wie gehen Sie vor?

Erstellen Sie im Dataset eine neue Tabelle mit dem Namen fruit_details.

1. Klicken Sie auf das Dataset fruit_store.

Daraufhin wird die Option Tabelle erstellen angezeigt.

Hinweis: Möglicherweise müssen Sie das Browserfenster erweitern, um die Option „Tabelle erstellen“ zu sehen.

2. Nehmen Sie dabei folgende Einstellungen vor:

• Quelle: Wählen Sie im Drop-down Tabelle erstellen aus die Option Google Cloud Storage aus.
• Wählen Sie die Datei aus dem Cloud Storage-Bucket aus: data-insights-course/labs/optimizing-for-performance/shopping_cart.json
• Dateiformat: JSONL (Durch Zeilenumbruch getrenntes JSON)

3. Nennen Sie die neue Tabelle fruit_details.

4. Aktivieren Sie das Kästchen Schema (Automatisch erkennen).

5. Klicken Sie auf Tabelle erstellen.

Im Schema sehen Sie, dass fruit_array als REPEATED (wiederkehrend) markiert ist. Damit wird angezeigt, dass es sich um ein Array handelt.

Zusammenfassung

• In BigQuery werden Arrays nativ unterstützt.
• Die Werte im Array müssen alle denselben Datentyp haben.
• Arrays werden in BigQuery REPEATED-Felder genannt.

Klicken Sie auf Fortschritt prüfen. Erstellen Sie ein neues Dataset und eine neue Tabelle, um Ihre Daten zu speichern

Aufgabe 3: Eigene Arrays mit ARRAY_AGG() erstellen

Wenn Sie noch keine Arrays in Ihren Tabellen haben, können Sie sie erstellen.

1. Untersuchen Sie dieses öffentliche Dataset durch Kopieren und Einfügen der folgenden Abfrage:

SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date

2. Klicken Sie auf Ausführen und sehen Sie sich die Ergebnisse an.

Verwenden Sie jetzt die Funktion ARRAY_AGG(), um unsere Stringwerte in einem Array zu aggregieren.

3. Untersuchen Sie dieses öffentliche Dataset durch Kopieren und Einfügen der folgenden Abfrage:

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

4. Klicken Sie auf Ausführen und sehen Sie sich die Ergebnisse an.

5. Zählen Sie mit der Funktion ARRAY_LENGTH() anschließend die Seiten und Produkte, die wir uns angesehen haben:

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

6. Jetzt deduplizieren Sie die Seiten und Produkte, um zu sehen, wie viele einzelne Produkte aufgerufen wurden. Fügen Sie dafür DISTINCT zu ARRAY_AGG() hinzu:

SELECT fullVisitorId, date, ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed, ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

Klicken Sie auf Fortschritt prüfen. Führen Sie die Abfrage aus, um zu sehen, wie viele einzelne Produkte aufgerufen wurden.

Zusammenfassung

Mit Arrays lassen sich viele nützliche Dinge tun:

• Anzahl der Elemente finden: ARRAY_LENGTH(<Array>)
• Elemente deduplizieren: ARRAY_AGG(DISTINCT <Feld>)
• Elemente sortieren: ARRAY_AGG(<Feld> ORDER BY <Feld>)
• Die Anzahl der Ergebnisse begrenzen: ARRAY_AGG(<Feld> LIMIT 5)

Aufgabe 4: Abfragetabellen mit Arrays

Im öffentlichen BigQuery-Dataset für Google Analytics bigquery-public-data.google_analytics_sample sind viel mehr Felder und Zeilen vorhanden als im Dataset data-to-insights.ecommerce.all_sessions für dieses Lab. Noch interessanter ist jedoch, dass die Feldwerte wie Produkte, Seiten und Transaktionen bereits nativ als Arrays gespeichert sind.

1. Untersuchen Sie die verfügbaren Daten durch Kopieren und Einfügen der folgenden Abfrage. Suchen Sie dabei nach Feldern mit wiederkehrenden Werten (Arrays):

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

2. Klicken Sie auf Ausführen.

3. Scrollen Sie in den Ergebnissen nach rechts, bis Sie zum Feld hits.product.v2ProductName gelangen. Wir behandeln demnächst mehrere Feldaliasse.

Die Anzahl der Felder, die im Google Analytics-Schema zur Verfügung steht, kann die Analyse überlasten.

4. Sie sollten daher nur die Felder „visit“ und „page name“ abfragen:

SELECT visitId, hits.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

Sie erhalten einen Fehler: Fehler:Cannot access field page on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [3:8]

Bevor Sie REPEATED-Felder (Arrays) wie gewohnt abfragen können, müssen die Arrays zuerst in Zeilen aufgeteilt werden.

Das Array für hits.page.pageTitle ist zurzeit als einzelne Zeile gespeichert:

['homepage','product page','checkout']

Es sollte aber so aussehen:

['homepage', 'product page', 'checkout']

Wie können Sie das mit SQL machen?

Antwort: Nutzen Sie dazu die Funktion UNNEST() in Ihrem Array-Feld:

SELECT DISTINCT visitId, h.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h WHERE visitId = 1501570398 LIMIT 10

Mehr zu UNNEST() später. Wichtig für den Moment ist Folgendes:

• Mit UNNEST() werden Arrayelemente wieder in Zeilen angeordnet.
• UNNEST() folgt immer auf den Tabellennamen in der FROM-Klausel. Sie können sich das wie eine vorab verknüpfte Tabelle vorstellen.

Klicken Sie auf Fortschritt prüfen. Führen Sie die Abfrage aus, um UNNEST() auf das Arrayfeld anzuwenden.

Aufgabe 5: Einführung in STRUCTs

Sie fragen sich vielleicht, warum der Feldalias hit.page.pageTitle wie drei Felder aussieht, die jeweils durch Punkte getrennt sind. So wie Sie mit ARRAY-Werten tief in den Detaillierungsgrad der Felder eintauchen, ermöglicht ein weiterer Datentyp einen breiten Blick auf das Schema, weil verwandte Felder gruppiert werden. Dieser SQL-Datentyp heißt STRUCT.

Einen STRUCT kann man sich wie eine separate Tabelle vorstellen, die schon vorab mit Ihrer Haupttabelle verknüpft wurde.

Ein STRUCT zeichnet sich durch Folgendes aus:

• Es kann ein oder mehrere Felder haben.
• Es kann für jedes Feld dieselben oder unterschiedliche Datentypen haben.
• Es kann seinen eigenen Alias haben.

Klingt zunächst einmal wie eine ganz normale Tabelle.

Ein Dataset mit STRUCTs untersuchen

1. Zum Öffnen des Datasets bigquery-public-data klicken Sie auf +Hinzufügen. Wählen Sie dann Projekt nach Name markieren aus und geben Sie den Namen bigquery-public-data aus.

2. Klicken Sie auf Markieren.

Das Projekt bigquery-public-data wird im Abschnitt „Explorer“ aufgelistet.

3. Öffnen Sie bigquery-public-data.

4. Öffnen Sie darin das Dataset google_analytics_sample.

5. Klicken Sie auf die Tabelle ga_sessions(366)_.

6. Scrollen Sie durch das Schema und beantworten Sie die folgende Frage. Nutzen Sie dazu die Suchfunktion des Browsers.

Für moderne E-Commerce-Websites werden sehr viele Sitzungsdaten gespeichert.

Der eigentliche Vorteil von 32 STRUCTs in einer Tabelle ist, dass Sie Abfragen dieser Art ohne JOINs ausführen können:

SELECT visitId, totals.*, device.* FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398 LIMIT 10 Hinweis:: Mit der Syntax .* erhält BigQuery die Anweisung, alle Felder für diesen STRUCT zurückzugeben. Das wäre auch so, wenn totals.* eine separate Tabelle wäre, die Sie mit dieser zusammenführen.

Wenn Sie große Berichtstabellen als STRUCTs (vorab verknüpfte „Tabellen“) und ARRAYs (mit hohem Detaillierungsgrad) speichern, bringt das folgende Vorteile:

• Eine signifikant höhere Leistung, weil 32 Tabellen-JOINs vermieden werden
• Detaillierte Daten von ARRAYs, wenn Sie sie brauchen. Wenn nicht, entstehen aber keine Nachteile, weil BigQuery jede Spalte einzeln auf der Festplatte speichert.
• Alle geschäftlichen Daten sind in einer Tabelle gespeichert. Sie müssen also nicht auf JOIN-Schlüssel achten oder überlegen, in welchen Tabellen die gewünschten Daten enthalten sind

Aufgabe 6: Übung mit STRUCTs und ARRAYs

Das nächste Dataset enthält Rundenzeiten von Läuferinnen und Läufern. Jede Runde wird als „Split“ bezeichnet.

1. Probieren Sie mit der folgenden Abfrage die STRUCT-Syntax aus. Sehen Sie sich dabei die verschiedenen Feldtypen innerhalb des STRUCT-Containers an:

#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

Zeile	runner.name	runner.split
1	Rudisha	23.4

Was fällt Ihnen an den Feldaliassen auf? Da der STRUCT-Container verschachtelte Felder enthält ("name" und "split" sind Teilmengen von "runner"), wird eine Punktnotation verwendet.

Wie gehen Sie vor, wenn für einen Läufer mehrere Splits in einem Rennen vorhanden sind, wie etwa verschiedene Rundenzeiten?

In diesem Fall eignet sich ein Array.

2. Führen Sie dazu die folgende Abfrage aus:

#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

Zeile	runner.name	runner.splits
1	Rudisha	23.4
		26.3
		26.4
		26.1

Zusammenfassung:

• STRUCTs sind Container, die mehrere verschachtelte Feldnamen und Daten enthalten können.
• Arrays sind die Feldtypen innerhalb eines STRUCT-Containers, wie Sie oben im „splits“-Feld sehen können.

JSON-Daten aufnehmen

1. Erstellen Sie ein neues Dataset mit dem Namen racing.

2. Klicken Sie auf das Dataset racing und dann auf „Tabelle erstellen“.

Hinweis: Möglicherweise müssen Sie das Browserfenster erweitern, um die Option „Tabelle erstellen“ zu sehen.

• Quelle: Wählen Sie im Drop-down Tabelle erstellen aus die Option Google Cloud Storage aus.
• Wählen Sie die Datei aus dem Cloud Storage-Bucket aus: data-insights-course/labs/optimizing-for-performance/race_results.json
• Dateiformat: JSONL (Durch Zeilenumbruch getrenntes JSON)
• Klicken unter Schema auf den Schieberegler Als Text bearbeiten und fügen Sie folgenden Code hinzu:

[ { "name": "race", "type": "STRING", "mode": "NULLABLE" }, { "name": "participants", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "name", "type": "STRING", "mode": "NULLABLE" }, { "name": "splits", "type": "FLOAT", "mode": "REPEATED" } ] } ]

3. Nennen Sie die neue Tabelle race_results.

4. Klicken Sie auf Tabelle erstellen.

5. Lassen Sie sich nach dem erfolgreichen Ladevorgang eine Vorschau des Schemas für die neu erstellte Tabelle anzeigen:

Welches Feld ist der STRUCT? Woran erkennen Sie das?

Das Feld participants ist der STRUCT, da es vom Typ RECORD ist.

Welches Feld ist das ARRAY?

Das Feld participants.splits ist ein Array aus Gleitkommazahlen innerhalb des STRUCTs participants. Es hat den REPEATED-Modus, was auf einen Array hinweist. Werte aus diesem Array werden als verschachtelte Werte bezeichnet, da es sich um mehrere Werte in einem einzelnen Feld handelt.

Klicken Sie auf Fortschritt prüfen. Erstellen Sie ein Dataset und eine Tabelle, um JSON-Daten aufzunehmen.

Verschachtelte und wiederkehrende Felder abfragen

1. Sehen wir uns alle Teilnehmenden des 800-Meter-Rennens an:

#standardSQL SELECT * FROM racing.race_results

Wie viele Zeilen wurden ausgegeben?

Antwort: 1

Wie können Sie den Namen aller Läuferinnen und Läufer und den Typ des Rennens auflisten?

2. Führen Sie das folgende Schema aus und sehen Sie sich an, was passiert:

#standardSQL SELECT race, participants.name FROM racing.race_results

Fehler: Cannot access field name on a value with type ARRAY<STRUCT<name STRING, splits ARRAY<FLOAT64>>>> at [2:27]

Ähnlich wie beim Weglassen von GROUP BY bei Aggregationsfunktionen werden zwei verschiedene Detaillierungsgrade dargestellt: Eine Zeile für das Rennen und drei Zeilen für die Namen der Teilnehmer. Sie müssen also…

Zeile	race	participants.name
1	800M	Rudisha
2	???	Makhloufi
3	???	Murphy

…in Folgendes ändern:

Zeile	race	participants.name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy

Wie würden Sie bei einer traditionellen relationalen SQL-Datenbank vorgehen, wenn Sie eine "races"-Tabelle und eine "participants"-Tabelle haben und Informationen aus beiden Tabellen erhalten möchten? Sie würden sie mit einem JOIN zusammenführen. Das "participant"-STRUCT ähnelt konzeptionell stark einer Tabelle und ist bereits Teil Ihrer "races"-Tabelle, korreliert jedoch nicht korrekt mit dem Nicht-STRUCT-Feld "race".

Fällt Ihnen ein SQL-Befehl aus zwei Wörtern ein, mit dem Sie den Wert "800M" aus der "races"-Tabelle mit jedem Läufer aus der ersten Tabelle korrelieren können?

Antwort: CROSS JOIN

Prima!

3. Führen Sie jetzt Folgendes aus:

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN participants # this is the STRUCT (it is like a table within a table)

Table name "participants" missing dataset while no default dataset is set in the request.

Obwohl der „participants“-STRUCT wie eine Tabelle aussieht, ist er eigentlich immer noch ein Feld in der Tabelle racing.race_results.

4. Fügen Sie der Abfrage den Dataset-Namen hinzu:

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name

5. Klicken Sie auf Ausführen.

Sehr gut. Sie haben erfolgreich alle Läufer für jedes Rennen aufgelistet.

Zeile	race	name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy
4	800M	Bosse
5	800M	Rotich
6	800M	Lewandowski
7	800M	Kipketer
8	800M	Berian

6. Die letzte Abfrage können Sie vereinfachen, indem Sie:

• einen Alias für die Originaltabelle hinzufügen
• die Wörter "CROSS JOIN" durch ein Komma ersetzen (ein Komma impliziert einen Cross Join)

Sie erhalten dann dasselbe Abfrageergebnis:

#standardSQL SELECT race, participants.name FROM racing.race_results AS r, r.participants

Würde ein CROSS JOIN bei mehreren Renntypen (800M, 100M, 200M) nicht einfach jeden Läufernamen als kartesisches Produkt mit jedem möglichen Renntypen verknüpfen?

Antwort: Nein. Es ist ein korrelierter Cross Join, mit dem nur die Elemente entpackt werden, die mit einer einzelnen Zeile verknüpft sind. Weitere Informationen finden Sie in der Dokumentation Mit Arrays in Standard-SQL arbeiten.

Zusammenfassung zu STRUCTs:

• Ein SQL-STRUCT ist einfach ein Container mit anderen Datenfeldern, die verschiedene Datentypen enthalten. Das Wort „STRUCT“ leitet sich von „Datenstruktur“ ab. Rufen Sie sich noch einmal das vorherige Beispiel ins Gedächtnis: STRUCT(``"Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits``)`` AS runner
• STRUCTs erhalten einen Alias, z. B. „runner“, und man kann sie sich als Tabelle in einer Haupttabelle vorstellen.
• STRUCTs und ARRAYs müssen entpackt werden, bevor Sie mit ihren Elementen arbeiten können. Umschließen Sie den Namen des STRUCTs oder das STRUCT-Feld, das ein Array ist, mit einem UNNEST()-Befehl, um den STRUCT zu entpacken und zusammenzufassen.

Aufgabe 7: Lab-Frage: STRUCT()

Beantworten Sie die Fragen unten anhand der Tabelle racing.race_results, die Sie bereits erstellt haben.

Aufgabe: Führen Sie eine COUNT-Abfrage aus, um zu erfahren, wie viele Läufer insgesamt am Rennen teilgenommen haben.

• Als Hilfe können Sie die folgende unvollständige Abfrage verwenden:

#standardSQL SELECT COUNT(participants.name) AS racer_count FROM racing.race_results Hinweis: Denken Sie daran, dass Sie den Struct-Namen hinter dem FROM-Befehl mit einem Cross Join als zusätzliche Datenquelle hinzufügen müssen.

Mögliche Lösung:

#standardSQL SELECT COUNT(p.name) AS racer_count FROM racing.race_results AS r, UNNEST(r.participants) AS p

Zeile	racer_count
1	8

Antwort: Am Rennen haben 8 Läufer teilgenommen.

Klicken Sie auf Fortschritt prüfen. Führen Sie eine COUNT-Abfrage aus, um zu erfahren, wie viele Läuferinnen und Läufer insgesamt am Rennen teilgenommen haben.

Aufgabe 8: Lab-Frage: Arrays mit UNNEST() entpacken

Schreiben Sie eine Abfrage, mit der die Laufzeit der Teilnehmenden aufgelistet wird, deren Name mit R beginnt. Ordnen Sie die Liste nach der Gesamtzeit und beginnen Sie mit der schnellsten Zeit. Verwenden Sie den Operator UNNEST() in der folgenden unvollständigen Abfrage.

• Vervollständigen Sie die Abfrage:

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_times WHERE GROUP BY ORDER BY ; Note:

• Sie müssen sowohl den STRUCT als auch das Array innerhalb des STRUCTs als Datenquellen nach dem FROM entpacken.
• Verwenden Sie ggf. Aliasse.

Mögliche Lösung:

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_times WHERE p.name LIKE 'R%' GROUP BY p.name ORDER BY total_race_time ASC;

Zeile	name	total_race_time
1	Rudisha	102.19999999999999
2	Rotich	103.6

Klicken Sie auf Fortschritt prüfen. Führen Sie die Abfrage aus, mit der die Gesamtlaufzeit der Teilnehmenden aufgelistet wird, deren Name mit R beginnt.

Aufgabe 9: Innerhalb von Array-Werten filtern

Sie haben gesehen, dass die schnellste Rundenzeit, die für das 800-Meter-Rennen aufgezeichnet wurde, 23,2 Sekunden betrug. Sie haben jedoch nicht herausgefunden, welche Läuferin oder welcher Läufer diese Runde gelaufen ist. Erstellen Sie eine Abfrage, mit der das ausgegeben wird.

• Vervollständigen Sie die Abfrage:

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_time WHERE split_time = ;

Mögliche Lösung:

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;

Zeile	name	split_time
1	Kipketer	23.2

Klicken Sie auf Fortschritt prüfen. Führen Sie die Abfrage aus, um herauszufinden, welcher Läufer die kürzeste Rundenzeit hatte.

Das war's!

Sie haben erfolgreich JSON-Datasets aufgenommen, ARRAYs und STRUCTs erstellt und die Verschachtelung semistrukturierter Daten aufgehoben, um neue Informationen abzurufen.

Weitere Informationen

• Weitere Informationen erhalten Sie in der Dokumentation Mit Arrays in Standard-SQL arbeiten.
• Labs zum Vertiefen Ihres Wissens:
  • BigQuery: Nach Datum partitionierte Tabellen erstellen
  • Taxikosten mit einem ML-Prognosemodell in BigQuery vorhersagen
  • Neue Datasets in BigQuery aufnehmen

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 3. Februar 2024 aktualisiert

Lab zuletzt am 25. August 2023 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.