GSP408

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

Ein neu verfügbares E‑Commerce-Dataset mit Millionen Google Analytics-Datensätzen für den Google Merchandise Store wurde in BigQuery geladen. Für dieses Lab verwenden Sie eine Kopie dieses Datasets und durchsuchen die verfügbaren Felder und Zeilen nach Informationen.

In diesem Lab wird Schritt für Schritt beschrieben, wie Sie Fehler in Abfragen beheben. In verschiedenen Übungen können Sie das Gelernte gleich in die Praxis umsetzen. Für die Beispiele in diesem Lab nehmen wir an, dass Sie mit einem neuen Datenanalysten in Ihrem Team zusammenarbeiten. Er hat die folgenden Abfragen erstellt, um einige Fragen zum E-Commerce-Dataset zu beantworten. Korrigieren Sie die Abfragen mithilfe der Antworten, um ein aussagekräftiges Ergebnis zu erhalten.

Lerninhalte

Aufgaben in diesem Lab:

• Projekte an den BigQuery-Ressourcenbaum anpinnen
• Mit dem BigQuery-Abfrageeditor und der BigQuery-Abfragevalidierung SQL-Syntaxfehler und logische Fehler beheben

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: Projekt an den BigQuery-Ressourcenbaum anpinnen

1. Klicken Sie im Navigationsmenü auf  > BigQuery.

Das Fenster „Welcome to BigQuery in the Cloud Console“ (Willkommen bei BigQuery in der Cloud Console) wird geöffnet.

Hinweis: Dieses Fenster enthält einen Link zur Kurzanleitung und Informationen zu Aktualisierungen der Benutzeroberfläche.

2. Klicken Sie auf Fertig.

3. Öffentliche BigQuery-Datasets werden nicht standardmäßig angezeigt. So öffnen Sie das öffentliche Datasets-Projekt: Kopieren Sie data-to-insights.

4. Klicken Sie auf + Hinzufügen > Projekt nach Name markieren. Fügen Sie dann den data-to-insights-Namen ein.

5. Klicken Sie auf Markieren.

Das data-to-insights-Projekt ist jetzt im Abschnitt „Explorer“ aufgelistet.

BigQuery-Abfrageeditor und ‑Abfragevalidierung

Für jede Übung in den folgenden Abschnitten haben wir eine Reihe von Abfragen mit häufig auftretenden Fehlern zusammengestellt, die Sie beheben sollen. Im Lab wird darauf hingewiesen, worauf Sie achten sollten, und vorgeschlagen, wie Sie die Syntax korrigieren und sinnvolle Ergebnisse zurückgeben.

Kopieren Sie die Abfrage und fügen Sie sie in den BigQuery-Abfrageeditor ein, um die Fehler zu beheben. Bei Fehlern wird in der jeweiligen Zeile sowie in der Abfragevalidierung (untere Ecke) ein rotes Ausrufezeichen eingeblendet.

Wenn Sie die Abfrage mit den Fehlern ausführen, schlägt sie fehl und die Fehlerbeschreibung wird in den Jobinformationen angezeigt.

Wenn die Abfrage fehlerfrei ist, sehen Sie in der Abfragevalidierung ein grünes Häkchen. Klicken Sie nun auf Ausführen, um die Abfrage auszuführen und sich die Ergebnisse anzeigen zu lassen.

Hinweis: Informationen zur Syntax finden Sie unter Standard-SQL-Abfragesyntax.

Aufgabe 2: Gesamtzahl aller Kunden ermitteln, die etwas gekauft haben

In diesem Abschnitt erstellen Sie eine Abfrage, die Aufschluss darüber gibt, wie viele einzelne Besucher etwas auf Ihrer Website gekauft haben. Die Daten befinden sich in der Tabelle „rev_transactions“, die Ihr Analyseteam bereitgestellt hat. Sie haben außerdem Beispielabfragen erhalten, die Ihnen den Einstieg erleichtern sollen. Sie sind sich jedoch nicht sicher, ob sie richtig geschrieben wurden.

Fehler in Abfragen beheben, die Validierungs-, Alias- und Kommafehler enthalten

• Sehen Sie sich die unten stehende Abfrage an und beantworten Sie die folgende Frage:

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• Was ist mit dieser aktualisierten Abfrage?

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• Wie verhält es sich mit dieser Abfrage, die Standard-SQL verwendet?

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• Und nun? Diese Abfrage enthält eine Spalte:

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• Und nun? Die folgende Abfrage enthält einen Seitentitel:

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• Und nun? Das fehlende Komma wurde korrigiert.

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

Antwort: Mit dieser Abfrage werden zwar Ergebnisse zurückgegeben, aber sind Sie sicher, dass Besucher nicht doppelt gezählt werden? Außerdem erhalten Sie durch eine einzige Zeile keine Antwort auf die Frage, wie viele einzelne Besucher etwas gekauft haben. Im nächsten Abschnitt finden Sie eine Möglichkeit, die Ergebnisse zu aggregieren.

Fehler in Abfragen beheben, die Logikfehler, GROUP BY-Anweisungen und Platzhalterfilter enthalten

• Aggregieren Sie die unten stehende Abfrage, um folgende Frage zu beantworten: Wie viele einzelne Besucher haben etwas gekauft?

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• Was ist hiermit? Die Aggregatfunktion COUNT() wurde hinzugefügt.

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• In dieser nächsten Abfrage wurden die Anweisungen GROUP BY und DISTINCT hinzugefügt:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

Ergebnisse

Sehr gut. Die Ergebnisse sind gut, aber sie sehen merkwürdig aus.

• Filtern Sie sie nach „Checkout Confirmation“.

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

Klicken Sie auf Fortschritt prüfen. Gesamtzahl aller Kunden ermitteln, die etwas gekauft haben

Aufgabe 3: Städte mit den meisten Transaktionen für Ihre E‑Commerce-Website auflisten

Fehler bei der Sortierung, in berechneten Feldern und beim Filtern nach der Aggregation beheben

1. Vervollständigen Sie die Abfrage:

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

Mögliche Lösung:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. Aktualisieren Sie die vorherige Abfrage, damit die am häufigsten vorkommenden Städte zuerst aufgeführt werden.

Mögliche Lösung:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. Aktualisieren Sie die Abfrage und erstellen Sie ein neues berechnetes Feld, um sich die durchschnittliche Anzahl der Produkte pro Bestellung nach Stadt anzeigen zu lassen.

Mögliche Lösung:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Ergebnisse

Filtern Sie die aggregierten Ergebnisse, um nur Städte zurückzugeben, die in der Spalte „avg_products_ordered“ einen Wert von über 20 enthalten.

• Was ist das Problem bei der folgenden Abfrage?

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

Mögliche Lösung:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

Klicken Sie auf Fortschritt prüfen. Städte mit den meisten Transaktionen für Ihre E‑Commerce-Website auflisten

Aufgabe 4: Gesamtzahl der Produkte in jeder Produktkategorie ermitteln

Die meistverkauften Produkte durch Filtern mit NULL-Werten ermitteln

1. Was ist das Problem bei der folgenden Abfrage? Wie können Sie es beheben?

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. Wo liegt der Fehler in der folgenden Abfrage?

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. Aktualisieren Sie die vorherige Abfrage, um nur bestimmte Produkte in jeder Produktkategorie zu zählen.

Mögliche Lösung:

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

Hinweis:

(nicht festgelegt) könnte darauf hinweisen, dass das Produkt keine Kategorie hat.

${productitem.product.origCatName} ist ein Frontend-Code zum Rendern der Kategorie. Dies kann bedeuten, dass das Tracking-Skript von Google Analytics ausgelöst wird, bevor die Seite vollständig gerendert wird.

Klicken Sie auf Fortschritt prüfen. Gesamtzahl der Produkte in jeder Produktkategorie ermitteln

Glückwunsch!

Sie haben fehlerhafte Abfragen in BigQuery-Standard-SQL korrigiert. Denken Sie daran, die Abfragevalidierung für falsche Abfragesyntax zu nutzen und die Abfrageergebnisse auch dann kritisch zu betrachten, wenn die Abfrage erfolgreich ausgeführt wird.

Weitere Informationen

• Öffentliche Google BigQuery-Datasets erkunden
• Sie haben bereits ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Folgen Sie dieser Anleitung zum Exportieren nach BigQuery.
• Lesen Sie den Blogpost 15 Awesome things you probably didn’t know about BigQuery
• In folgenden Labs erfahren Sie mehr über BigQuery:
  • E‑Commerce-Dataset mit SQL in BigQuery erkunden
  • Wetterdaten in BigQuery
  • BigQuery: Nach Datum partitionierte Tabellen erstellen

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 19. Januar 2024 aktualisiert

Lab zuletzt am 28. 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.