GSP413

Überblick

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 der Kopie eines E‑Commerce-Datasets, das Millionen von Google Analytics-Einträgen des Google Merchandise Store enthält. Ihre Aufgabe ist es, anhand der verfügbaren Felder und Zeilen bestimmte Informationen abzurufen.

In diesem Lab wird beschrieben, wie Sie Berichtstabellen mithilfe von SQL‑Joins und ‑Unions erstellen.

Szenario: Das Marketingteam hat Ihnen und Ihrem Data-Science-Team alle Rezensionen für Ihre E‑Commerce-Website zur Verfügung gestellt. Gemeinsam erstellen Sie ein Data Warehouse in BigQuery, in dem Daten aus drei Quellen zusammengeführt werden:

• E‑Commerce-Daten der Website
• Lagerbestände und Auftragsbearbeitung der Produkte
• Sentimentanalyse der Rezensionen

Aufgaben

Aufgaben in diesem Lab:

• Mithilfe einer Sentimentanalyse neue E‑Commerce-Daten prüfen
• Datasets verknüpfen und neue Tabellen erstellen
• Mithilfe von Unions und Tabellenplatzhaltern bisherige Daten hinzufügen

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: Neues Dataset zum Speichern der Tabellen erstellen

Erstellen Sie zuerst in BigQuery ein neues Dataset mit dem Namen ecommerce, in dem die Tabellen gespeichert werden können.

1. Klicken Sie im linken Bereich auf den Namen des BigQuery-Projekts (qwiklabs-gcp-xxxx).

2. Klicken Sie auf das Dreipunkt-Menü neben dem Projektnamen und wählen Sie Dataset erstellen aus.

Das Dialogfeld Dataset erstellen wird geöffnet.

3. Legen Sie ecommerce als Dataset-ID fest und verwenden Sie für alle anderen Optionen die Standardwerte.

4. Klicken Sie auf Dataset erstellen.

Klicken Sie auf Fortschritt prüfen. Ein neues Dataset zum Speichern der Tabellen erstellen

Aufgabe 2: Das Sentiment-Dataset ansehen

Ihr Data-Science-Team hat alle Rezensionen mit der API analysiert und Ihnen die durchschnittlichen „Score“- und „Magnitude“-Werte für jedes Produkt bereitgestellt.

Das Projekt, das das Dataset Ihres Marketingteams enthält, heißt data-to-insights. Öffentliche BigQuery-Datasets werden standardmäßig nicht in BigQuery angezeigt. Die Abfragen in diesem Lab greifen auf das Dataset data-to-insights zu, auch wenn Sie das nicht sehen können.

1. Erstellen Sie zuerst eine Kopie der Tabelle vom Data-Science-Team, damit Sie sie lesen können:

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` Hinweis: Diese Tabelle dient nur zur Veranschaulichung. Die Abfragen in diesem Lab greifen auf das Projekt data-to-insights zu.

2. Klicken Sie auf das Dataset ecommerce und rufen Sie die Tabelle products auf.

Daten auf den Tabs „Vorschau“ und „Schema“ untersuchen

1. Gehen Sie über ecommerce > products zum entsprechenden Dataset und klicken Sie auf den Tab Vorschau.

2. Klicken Sie auf den Tab Schema.

Abfrage erstellen, die die 5 Produkte mit dem positivsten Sentiment ausgibt

1. Schreiben Sie dazu im Abfrageeditor die entsprechende SQL-Abfrage.

Mögliche Lösung:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. Ändern Sie nun Ihre Abfrage so, dass die 5 Produkte mit dem negativsten Sentiment ausgegeben und NULL-Werte herausgefiltert werden.

Mögliche Lösung:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

Bei welchem Produkt ist das Sentiment am negativsten?

Klicken Sie auf Fortschritt prüfen. Das Sentiment-Dataset ansehen

Aufgabe 3: Datasets zusammenführen, um neue Erkenntnisse zu gewinnen

Szenario: Es ist der erste Tag des Monats und das für das Inventar zuständige Team hat Sie darüber informiert, dass die Werte im Feld orderedQuantity des Datasets für das Produktinventar nicht aktuell sind. Sie haben die Aufgabe, die Gesamtzahl der Verkäufe pro Produkt für den 01.08.2017 abzufragen und diese mit den derzeitigen Lagerbeständen abzugleichen, sodass das Team erkennen kann, welche Produkte zuerst aufgestockt werden müssen.

Tägliche Verkäufe pro Artikelnummer berechnen

1. Erstellen Sie in Ihrem Dataset ecommerce eine Tabelle mit folgenden Eigenschaften:

• Titel: sales_by_sku_20170801
• Datenquelle: data-to-insights.ecommerce.all_sessions_raw
• Berücksichtigt werden nur Ergebnisse, die sich voneinander unterscheiden
• Gibt die Artikelnummer aus: productSKU
• Gibt die Gesamtbestellmenge aus: productQuantity. Tipp: Verwenden Sie SUM() mit einer IFNULL-Bedingung.
• Nach Verkäufen am 20170801 filtern
• Nach den Artikelnummern mit den meisten Bestellungen sortieren: ORDER BY

Mögliche Lösung:

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. Klicken Sie auf die Tabelle sales_by_sku und dann auf den Tab Vorschau.

Wie viele verschiedene SKUs wurden verkauft?

Antwort: 462

Reichern Sie die Verkaufsdaten als Nächstes mit den Daten zum Produktinventar an, indem Sie die beiden Datasets zusammenführen.

Verkaufs- und Inventardaten zusammenführen

1. Mit einem JOIN können Sie die E‑Commerce-Daten der Website mit den folgenden Feldern aus dem Produktinventar anreichern:

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. Vervollständigen Sie die Abfrage:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

Mögliche Lösung:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. Ändern Sie Ihre Abfrage nun so, dass sie Folgendes enthält:

• Ein berechnetes Feld aus (total_ordered/stockLevel) mit dem Namen ratio. Tipp: Verwenden Sie SAFE_DIVIDE(field1,field2), um Fehler aufgrund einer Division durch 0 zu vermeiden, wenn der Lagerbestand 0 ist.
• Einen Ergebnisfilter, mit dem nur Produkte angezeigt werden, von denen schon zu Beginn des Monats mindestens 50 % des Inventars verkauft wurden.

Mögliche Lösung:

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

Klicken Sie auf Fortschritt prüfen. Datasets zusammenführen, um neue Erkenntnisse zu gewinnen

Aufgabe 4: Weitere Dateneinträge hinzufügen

Das Auslandsteam verzeichnet am 02.08.2017 bereits Ladenverkäufe, die Sie in Ihre Tabelle zu täglichen Verkäufen aufnehmen möchten.

Leere Tabelle erstellen, in der für den 02.08.2017 Verkäufe nach „productSKU“ (Artikelnummer) gespeichert werden

1. Geben Sie für das Schema die folgenden Felder an:

• Tabellenname: ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered als INT64-Feld

Mögliche Lösung:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. Prüfen Sie, ob Sie jetzt zwei Verkaufstabellen mit gemeinsamem Datum sehen. Klicken Sie entweder auf das Drop-down-Menü neben dem Tabellennamen Sales_by_sku in den Ergebnissen oder aktualisieren Sie den Browser, damit sie im linken Menü angezeigt werden:

3. Fügen Sie den von Ihrem Vertriebsteam zur Verfügung gestellten Verkaufsdatensatz ein:

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. Prüfen Sie, ob der Datensatz zu sehen ist. Klicken Sie auf den Tabellennamen, damit die Ergebnisse angezeigt werden.

Bisherige Daten hinzufügen

Es gibt mehrere Möglichkeiten, Daten zusammenzufügen, die dasselbe Schema haben. Zwei gängige Methoden sind Unions und Tabellenplatzhalter.

• Union ist ein SQL-Operator, der Zeilen aus verschiedenen Ergebnissätzen zusammenfügt.
• Tabellenplatzhalter sind nur in Standard-SQL verfügbar und ermöglichen die Abfrage mehrerer Tabellen mit kurzen SQL-Anweisungen.

1. Schreiben Sie eine Union-Abfrage, mit der alle Einträge aus den folgenden beiden Tabellen angezeigt werden:

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 Hinweis: Der Unterschied zwischen UNION und UNION ALL besteht darin, dass bei UNION keine Doppeleinträge enthalten sind.

Welches Problem besteht bei mehreren Tabellen zu täglichen Verkäufen? Sie müssen mehrere verkettete UNION-Anweisungen schreiben.

Eine bessere Lösung besteht darin, den Filter für den Tabellenplatzhalter und den Filter _TABLE_SUFFIX zu verwenden.

2. Schreiben Sie eine Abfrage mit dem Tabellenplatzhalter (*), um alle Einträge aus ecommerce.sales_by_sku_ für das Jahr 2017 auszuwählen.

Mögliche Lösung:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. Ändern Sie die vorherige Abfrage so, dass sie einen Filter enthält, der die Ergebnisse auf den 02.08.2017 begrenzt.

Mögliche Lösung:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' Hinweis: Eine andere Möglichkeit besteht darin, eine partitionierte Tabelle zu erstellen, die die täglichen Verkaufsdaten automatisch in die richtige Partition aufnimmt.

Klicken Sie auf Fortschritt prüfen. Weitere Dateneinträge hinzufügen

Glückwunsch!

Sie haben E‑Commerce-Daten analysiert, indem Sie Berichtstabellen erstellt und anschließend mithilfe von SQL-Joins und ‑Unions die Ansichten verändert haben.

Weitere Informationen

• Sie haben ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Folgen Sie dieser Anleitung zum Exportieren nach BigQuery.
• Weitere Informationen zur SQL-Syntax für JOINS finden Sie in der BigQuery-Dokumentation zu JOIN.
• Probieren Sie die Google Datensatzsuche aus.
• Sehen Sie sich folgende Labs an:
  • NCAA-Daten mit BigQuery untersuchen
  • Cloud Composer: BigQuery-Tabellen an verschiedene Orte kopieren

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 31. Oktober 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.