GSP414

Ü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 ganz auf die Datenanalyse konzentrieren, um wichtige Informationen zu erhalten.

In diesem Lab erfahren Sie, wie Sie partitionierte Tabellen in BigQuery abfragen und erstellen, um die Abfrageleistung zu erhöhen und die Ressourcennutzung zu reduzieren. In diesem Lab arbeiten Sie in BigQuery mit einem ecommerce-Dataset, das Millionen von Google Analytics-Datensätzen des Google Merchandise Store enthält.

Aufgaben

Aufgaben in diesem Lab:

• Partitionierte Tabellen abfragen
• Eigene partitionierte Tabellen erstellen

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 erstellen

1. Zuerst erstellen Sie ein Dataset zum Speichern Ihrer Tabellen.

2. Klicken Sie im Bereich Explorer neben der Projekt‑ID auf Aktionen ansehen und dann auf Dataset erstellen.

3. Legen Sie ecommerce als Dataset-ID fest.

Für die restlichen Optionen können Sie die Standardwerte beibehalten (Speicherort der Daten, Standard-Tabellenablauf).

4. Klicken Sie auf Dataset erstellen.

Klicken Sie auf Fortschritt prüfen.

Dataset mit dem Namen „ecommerce" erstellen

Aufgabe 2: Tabellen mit Datumspartitionen erstellen

Bei der Tabellenpartitionierung werden die Tabellendaten in Segmente unterteilt, die als Partitionen bezeichnet werden. Die Daten einer partitionierten Tabelle lassen sich einfacher verwalten und abfragen. Durch das Gliedern großer Tabellen in kleinere Partitionen werden Abfragen effizienter. Außerdem verringern sich so die Kosten, weil pro Abfrage weniger Byte gelesen werden müssen.

Erstellen Sie nun eine neue Tabelle und legen Sie eine Datums- oder Zeitstempelspalte als Partition fest. Doch zuvor sehen Sie sich die Daten in der unpartitionierten Tabelle einmal genauer an.

Aus Webseitenanalyse Stichprobe der Besucher 2017 ziehen

1. Klicken Sie auf + Neue Abfrage erstellen und fügen Sie die folgende Abfrage ein:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

Vor dem Ausführen können Sie sehen, wie groß die Menge der verarbeiteten Daten ist. Die Angabe finden Sie neben dem Symbol für die Abfragevalidierung: „Diese Abfrage verarbeitet 1,74 GB bei der Ausführung“.

2. Klicken Sie auf Ausführen.

Die Abfrage gibt fünf Ergebnisse zurück.

Aus Webseitenanalyse Stichprobe der Besucher 2018 ziehen

Jetzt wollen wir einige Informationen zu den Besuchern im Jahr 2018 abfragen.

1. Klicken Sie auf Neue Abfrage erstellen, um die Einträge im Abfrageeditor zu löschen, und fügen Sie dann die folgende neue Abfrage hinzu. Der Parameter WHERE date lautet jetzt 20180708:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

Die Abfragevalidierung gibt an, welche Datenmenge bei dieser Abfrage verarbeitet wird.

2. Klicken Sie auf Ausführen.

Sie sehen, dass auch bei dieser Abfrage 1,74 GB verarbeitet wurden, obwohl keine Ergebnisse zurückgegeben wurden. Was ist der Grund dafür? Die Abfrage-Engine muss alle Datensätze im Dataset durchsuchen und prüfen, ob die Datumsbedingung in der WHERE-Anweisung erfüllt wird. Bei jedem einzelnen Datensatz muss das Datum mit der Bedingung "20180708" abgeglichen werden.

Wichtig zu erwähnen ist auch, dass mit dem LIMIT-Wert 5 nicht die Gesamtmenge der verarbeiteten Daten verringert wird. Das wird häufig falsch verstanden.

Gängige Anwendungsfälle für datumspartitionierte Tabellen

Für einen Abgleich der Zeilen mit einer WHERE-Bedingung jedes Mal das gesamte Dataset durchsuchen zu lassen, ist einfach zu aufwendig. Insbesondere dann, wenn Sie sich nur für Daten aus einem bestimmten Zeitraum interessieren, wie etwa:

• Alle Transaktionen im letzten Jahr
• Alle Besucherinteraktionen in den letzten sieben Tagen
• Alle im letzten Monat verkauften Produkte

Anstatt wie in den vorherigen Abfragen das gesamte Dataset zu durchsuchen und nach einem Datumsfeld zu filtern, richten Sie jetzt eine nach Datum partitionierte Tabelle ein. Dadurch können Sie Datensätze in bestimmten Partitionen außer Acht lassen, wenn sie für die jeweilige Abfrage nicht relevant sind.

Neue nach Datum partitionierte Tabelle erstellen

1. Klicken Sie auf NEUE ABFRAGE ERSTELLEN, fügen Sie die folgende Abfrage ein und klicken Sie auf Ausführen:

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

Die Abfrage enthält jetzt die neue Option (PARTITION BY), mit der die Tabelle nach einem bestimmten Feld partitioniert wird, DATE und TIMESTAMP. Die PARSE_DATE-Funktion wird auf das Datumsfeld angewandt, das als Zeichenfolge gespeichert ist, um es in den geeigneten Datumstyp für die Partitionierung umzuwandeln.

2. Klicken Sie auf das Dataset ecommerce und wählen Sie dann die neue Tabelle partiton_by_day aus:

3. Klicken Sie auf den Tab Details.

Es sollte Folgendes angezeigt werden:

• Partitioniert nach: Tag
• Partitioniert nach Feld: date_formatted

Hinweis: In Ihrem Labs-Konto laufen Tabellenpartitionen nach 60 Tagen automatisch ab. Wenn Sie ein persönliches Google Cloud-Konto mit aktivierter Abrechnung haben, können Sie jedoch partitionierte Tabellen ohne Ablauf der Partitionen erstellen. Alle weiteren Abfragen, die Sie in diesem Lab vornehmen, werden in partitionierten Tabellen ausgeführt, die bereits erstellt wurden.

Klicken Sie auf Fortschritt prüfen.

Neue nach Datum partitionierte Tabelle erstellen

Aufgabe 3: Ergebnisse aus Abfragen für eine partitionierte Tabelle prüfen

1. Führen Sie die folgende Abfrage aus und achten Sie auf die Menge der zu verarbeitenden Byte:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Diesmal werden 25 KB bzw. 0,025 MB verarbeitet. Das entspricht einem Bruchteil der bisherigen Abfragen.

2. Führen Sie jetzt die folgende Abfrage aus und achten Sie auf die Menge der zu verarbeitenden Byte:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Sie sollten die Meldung sehen: Diese Abfrage verarbeitet 0 B bei der Ausführung.

Aufgabe 4: Partitionierte Tabelle mit automatischem Ablauf erstellen

Partitionierte Tabellen, die automatisch ablaufen, dienen der Einhaltung von Datenschutzbestimmungen und können eine unnötige Belegung von Speicherplatz verhindern, die in einer Produktionsumgebung Kosten verursacht. Wenn Sie ein rollierendes Datenfenster erstellen möchten, fügen Sie ein Ablaufdatum hinzu, damit die Partition nach der Verwendung entfernt wird.

Verfügbare NOAA-Wetterdatentabellen abrufen

1. Klicken Sie im linken Menü im Explorer auf + Hinzufügen und wählen Sie Öffentliche Datasets aus.

2. Suchen Sie nach GSOD NOAA und wählen Sie dann das Dataset aus.

3. Klicken Sie auf Dataset aufrufen.

4. Scrollen Sie durch die Tabellen im Dataset noaa_gsod (diese sind manuell fragmentiert und nicht partitioniert):

Ihr Ziel ist es, eine Tabelle zu erstellen, die folgende Funktionen ausführt:

• Abfragen der Wetterdaten ab 2018
• Filtern nach Tagen mit Niederschlag (Regen, Schnee usw.)
• Speichern der Datenpartitionen für 90 Tage ab dem Datum der Partitionierung (rollierendes Fenster)

1. Fügen Sie zuerst die folgende Abfrage ein:

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 Hinweis: Mit dem Tabellenplatzhalter * in der FROM-Anweisung wird die Anzahl der Tabellen begrenzt, auf die im Filter TABLE_SUFFIX verwiesen wird. Hinweis: Obwohl ein LIMIT-Wert von 10 hinzugefügt wurde, ist die Menge der durchsuchten Daten (ca. 1,83 GB) nicht geringer, da noch keine Partitionen vorhanden sind.

2. Klicken Sie auf Ausführen.

3. Prüfen Sie, ob das Datum korrekt formatiert ist und das Niederschlagsfeld Werte ungleich Null enthält.

Aufgabe 5: Jetzt sind Sie an der Reihe: Erstellen Sie eine partitionierte Tabelle

• Ändern Sie die letzte Abfrage, sodass eine Tabelle mit den folgenden Angaben erstellt wird:

  • Tabellenname: ecommerce.days_with_rain
  • PARTITION BY: Datumsfeld
  • OPTIONS: partition_expiration_days=60
  • Tabellenbeschreibung: description="weather stations with precipitation, partitioned by day"

Die Abfrage sollte in etwa so aussehen:

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2018'

Klicken Sie auf Fortschritt prüfen.

Partitionierte Tabelle erstellen

Prüfen, ob der Ablauf der Datenpartition funktioniert

Um sicherzustellen, dass nur Daten der letzten 60 Tage bis heute gespeichert werden, führen Sie die Abfrage DATE_DIFF aus und rufen Sie das Alter der Partitionen ab, deren Ablaufdatum auf 60 Tage festgelegt ist.

Mit der folgenden Abfrage wird die durchschnittliche Regenmenge an der NOAA-Wetterstation in Wakayama, Japan nachverfolgt, einer Region mit erheblichen Niederschlägen.

• Führen Sie die folgende Abfrage aus:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Aufgabe 6: Prüfen, ob das Partitionsalter maximal 60 Tage beträgt

Aktualisieren Sie die ORDER BY-Anweisung, damit die ältesten Partitionen zuerst angezeigt werden.

• Führen Sie die folgende Abfrage aus:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Hinweis: Wenn Sie die Abfrage später wieder ausführen, werden andere Ergebnisse angezeigt, da die Wetterdaten – und somit auch die Partitionen – kontinuierlich aktualisiert werden.

Glückwunsch!

Sie haben in BigQuery erfolgreich partitionierte Tabellen erstellt und abgefragt.

Weitere Informationen

• In der Dokumentation Einführung in partitionierte Tabellen erfahren Sie, wie nach Aufnahmezeit partitionierte Tabellen erstellt werden, die nicht an eine bestimmte Datums- oder Zeitstempelspalte gebunden sind.
• Sie haben bereits ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Folgen Sie dieser Anleitung zum Exportieren nach BigQuery.
• Sehen Sie sich folgende BigQuery-Labs an:
  • Probleme beim Zusammenführen von Daten beheben
  • In BigQuery mit JSON, Arrays und STRUCTs arbeiten
  • Predict Taxi Fare with a BigQuery ML Forecasting Model

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 1. Januar 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.