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
Lernziele
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 Google Cloud-Ressourcen für das Lab verfügbar sind.
In diesem praxisorientierten Lab können Sie die Lab-Aktivitäten in einer echten Cloud-Umgebung 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 (empfohlen), 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: Verwenden Sie für dieses Lab nur das Teilnehmerkonto. Wenn Sie ein anderes Google Cloud-Konto verwenden, fallen dafür möglicherweise Kosten an.
Lab starten und bei der Google Cloud Console anmelden
Klicken Sie auf Lab starten. Wenn Sie für das Lab bezahlen müssen, wird ein Dialogfeld 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
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.
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“.
Klicken Sie auf Weiter.
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“.
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.
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 auf Google Cloud-Produkte und ‑Dienste zugreifen möchten, klicken Sie auf das Navigationsmenü oder geben Sie den Namen des Produkts oder Dienstes in das Feld Suchen ein.
Die BigQuery Console öffnen
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.
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.
Klicken Sie im linken Bereich auf den Namen des BigQuery-Projekts (qwiklabs-gcp-xxxx).
Klicken Sie auf das Dreipunkt-Menü neben dem Projektnamen und wählen Sie Dataset erstellen aus.
Das Dialogfeld Dataset erstellen wird geöffnet.
Legen Sie ecommerce als Dataset-ID fest und verwenden Sie für alle anderen Optionen die Standardwerte.
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.
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.
Klicken Sie auf das Dataset ecommerce und rufen Sie die Tabelle products auf.
Daten auf den Tabs „Vorschau“ und „Schema“ untersuchen
Gehen Sie über ecommerce > products zum entsprechenden Dataset und klicken Sie auf den Tab Vorschau.
Klicken Sie auf den Tab Schema.
Abfrage erstellen, die die 5 Produkte mit dem positivsten Sentiment ausgibt
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
Ä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
Erstellen Sie in Ihrem Dataset ecommerce eine Tabelle mit folgenden Eigenschaften:
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
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
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
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
Ä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
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:
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)
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.
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.
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*`
Ä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
Das wars! Sie haben das Lab erfolgreich abgeschlossen.
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 bereits ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Folgen Sie dieser Anleitung zum Exportieren nach BigQuery.
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.
Labs erstellen ein Google Cloud-Projekt und Ressourcen für einen bestimmten Zeitraum
Labs haben ein Zeitlimit und keine Pausenfunktion. Wenn Sie das Lab beenden, müssen Sie von vorne beginnen.
Klicken Sie links oben auf dem Bildschirm auf Lab starten, um zu beginnen
Privates Surfen verwenden
Kopieren Sie den bereitgestellten Nutzernamen und das Passwort für das Lab
Klicken Sie im privaten Modus auf Konsole öffnen
In der Konsole anmelden
Melden Sie sich mit Ihren Lab-Anmeldedaten an. Wenn Sie andere Anmeldedaten verwenden, kann dies zu Fehlern führen oder es fallen Kosten an.
Akzeptieren Sie die Nutzungsbedingungen und überspringen Sie die Seite zur Wiederherstellung der Ressourcen
Klicken Sie erst auf Lab beenden, wenn Sie das Lab abgeschlossen haben oder es neu starten möchten. Andernfalls werden Ihre bisherige Arbeit und das Projekt gelöscht.
Diese Inhalte sind derzeit nicht verfügbar
Bei Verfügbarkeit des Labs benachrichtigen wir Sie per E-Mail
Sehr gut!
Bei Verfügbarkeit kontaktieren wir Sie per E-Mail
Es ist immer nur ein Lab möglich
Bestätigen Sie, dass Sie alle vorhandenen Labs beenden und dieses Lab starten möchten
Privates Surfen für das Lab verwenden
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.
In diesem Lab wird beschrieben, wie Sie Berichtstabellen mithilfe von SQL-Joins und -Unions erstellen.