Serverlose Datentransformation mit Apache Spark in BigQuery Studio-Notebooks

1. Einführung

In diesem Codelab erfahren Sie, wie Sie die Leistungsfähigkeit von Apache Spark für die Datentransformation in der vertrauten Benutzeroberfläche von BigQuery Studio nutzen können. Sie lesen Daten aus BigQuery, bereinigen und transformieren sie mit PySpark und schreiben die Ergebnisse in eine neue BigQuery-Tabelle – alles in einem einzigen Notebook.

In diesem Codelab gehen Sie schrittweise so vor:

1. Google Cloud-Projekt vorbereiten und alle erforderlichen APIs aktivieren
2. GCS-Bucket für temporären Ordner erstellen
3. Erforderliche Bibliotheken für die Ausführung von Apache Spark importieren
4. Spark-Sitzung mit BigQuery-Connector initialisieren
5. Google Analytics-Beispieldaten aus dem öffentlichen BigQuery-Dataset lesen
6. Daten mit aggregierten Daten nach Gerätebrowser transformieren (einfache Messwerte)
7. Daten mit der Analyse der Traffic-Quelle mit Umsatzberechnungen transformieren
8. Daten mit geografischen Analysen transformieren
9. Daten nach der Transformation in eine BigQuery-Tabelle schreiben

Architekturübersicht

Voraussetzungen

• Ein Google Cloud-Projekt mit aktivierter Abrechnung.
• Die BigQuery API und die BigQuery Connection API sind in Ihrem GCP-Projekt aktiviert.
• Grundkenntnisse in SQL und Python

Lerninhalte

• Daten mit Apache Spark in BigQuery Studio-Notebooks extrahieren
• Daten mit Apache Spark in BigQuery Studio-Notebooks transformieren oder aggregieren
• Daten nach der Transformation oder Aggregation von Daten mit Apache Spark in einem BigQuery Studio-Notebook schreiben

Voraussetzungen

• Chrome-Webbrowser
• Ein Gmail-Konto
• Ein Cloud-Projekt mit aktivierter Abrechnung

2. Grundlegende Einrichtung und Anforderungen

Einrichtung der Umgebung im eigenen Tempo

1. Melden Sie sich in der Google Cloud Console an und erstellen Sie ein neues Projekt oder verwenden Sie ein vorhandenes. Wenn Sie noch kein Gmail- oder Google Workspace-Konto haben, müssen Sie eins erstellen.

• Der Projektname ist der Anzeigename für die Teilnehmer dieses Projekts. Es handelt sich um einen String, der nicht von Google APIs verwendet wird. Sie können sie jederzeit aktualisieren.
• Die Projekt-ID ist für alle Google Cloud-Projekte eindeutig und kann nach der Festlegung nicht mehr geändert werden. In der Cloud Console wird automatisch ein eindeutiger String generiert. Normalerweise ist es nicht wichtig, wie dieser String aussieht. In den meisten Codelabs müssen Sie auf Ihre Projekt-ID verweisen (in der Regel als PROJECT_ID angegeben). Wenn Ihnen die generierte ID nicht gefällt, können Sie eine andere zufällige ID generieren. Alternativ können Sie es mit einem eigenen Namen versuchen und sehen, ob er verfügbar ist. Sie kann nach diesem Schritt nicht mehr geändert werden und bleibt für die Dauer des Projekts bestehen.
• Zur Information: Es gibt einen dritten Wert, eine Projektnummer, die von einigen APIs verwendet wird. Weitere Informationen zu diesen drei Werten

Achtung:Eine Projekt-ID ist global eindeutig und kann nach der Auswahl nicht mehr von anderen verwendet werden. Sie sind der einzige Nutzer dieser ID. Auch wenn ein Projekt gelöscht wird, kann die ID nicht wiederverwendet werden.

Hinweis:Wenn Sie ein Gmail-Konto verwenden, können Sie den Standardstandort auf „Keine Organisation“ belassen. Wenn Sie ein Google Workspace-Konto verwenden, wählen Sie einen Standort aus, der für Ihre Organisation sinnvoll ist.

2. Als Nächstes müssen Sie die Abrechnung in der Cloud Console aktivieren, um Cloud-Ressourcen/-APIs zu verwenden. Die Durchführung dieses Codelabs kostet wenig oder gar nichts. Wenn Sie Ressourcen herunterfahren möchten, um weitere Kosten zu vermeiden, können Sie die erstellten Ressourcen oder das Projekt löschen. Neue Google Cloud-Nutzer können am Programm Kostenlose Testversion mit einem Guthaben von 300$ teilnehmen.

3. Hinweis

API aktivieren

Bevor Sie BigQuery Studio-Notebooks verwenden können, müssen Sie die folgenden APIs aktivieren:

• Die Compute Engine API
• Die Dataform API
• Die Vertex AI API

Wenn Sie die Funktion manuell aktivieren möchten, rufen Sie BigQuery auf. Klicken Sie in der Tableiste des Editorbereichs auf den Drop-down-Pfeil neben dem +-Zeichen, halten Sie den Mauszeiger auf Notebook und wählen Sie dann BigQuery-Vorlage, Leeres Notebook oder Spark-Vorlage aus.

Klicken Sie im Fenster „Core feature API aktivieren“ auf „In BigQuery Unified API aktivieren“.

Aktivieren Sie die Option und klicken Sie auf „Schließen“. Weitere Informationen finden Sie unter BigQuery Studio für die Asset-Verwaltung aktivieren.

4. Öffentliches Dataset lesen

Zuerst erstellen wir einen GCS-Bucket zur temporären Verwendung, damit wir Spark in BigQuery Studio-Notebooks ausführen können.

1. Rufen Sie in der Google Cloud Console BigQuery auf.
2. Klicken Sie in der Tableiste des Editorbereichs auf den Drop-down-Pfeil neben dem +-Zeichen, halten Sie den Mauszeiger auf Notebook und wählen Sie dann Leeres Notebook aus.
3. Klicken Sie auf die Codezelle und geben Sie dann das CLI-Script unten ein, um den GCS-Bucket zu erstellen. Klicken Sie dann auf die Schaltfläche „Zelle ausführen“ oder drücken Sie Umschalt + Eingabetaste.

!gsutil mb -p <your_project_id> -c STANDARD -l US gs://ioxid2025-<your_project_id>

Aktualisieren Sie die Werte für <your_project_id> entsprechend Ihrer Auswahl beim Erstellen des Google Cloud-Projekts. Aktualisieren Sie die Werte für <your_project_id> mit Ihrer Projekt-ID, um einen eindeutigen GCS-Bucket-Namen zu erstellen. Klicken Sie dann auf die Schaltfläche „Zelle ausführen“ oder drücken Sie Umschalt + Eingabetaste, um die Codezelle auszuführen.

Als Nächstes starten wir eine Spark-Sitzung. In diesem Codelab verwenden wir die SparkSession-Bibliothek. Wir könnten aber auch DataprocSession verwenden, um die Dataproc-Funktionen zum Ausführen von Spark in BigQuery Studio-Notebooks zu nutzen.

1. Klicken Sie auf die Codezelle und geben Sie das CLI-Skript unten ein, um die Spark-Sitzung zu initialisieren. Klicken Sie auf die Schaltfläche „Zelle ausführen“ oder drücken Sie Umschalttaste + Eingabetaste.

# Import required libraries 
from pyspark.sql import SparkSession 
from pyspark.sql.functions import col, sum, count, countDistinct, when, expr, date_format 
from pyspark.sql.types import DecimalType 

# Initialize Spark session with BigQuery connector 
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum, count, countDistinct, when, expr, date_format
from pyspark.sql.types import DecimalType

# Initialize Spark session with BigQuery connector
spark = SparkSession.builder \
 .appName("Google Analytics ETL with Apache Spark") \
 .config("spark.jars.packages", "com.google.cloud.spark:spark-bigquery-with-dependencies_2.12:0.32.0") \
 .getOrCreate()

spark

Erwartete Ausgabe:

SparkSession - in-memory
SparkContext
Spark UI
Version
v3.5.4
Master
local[*]
AppName
Google Analytics ETL with Apache Spark

2. Klicken Sie auf die Codezelle und geben Sie das folgende CLI-Script ein, um das GCP-Projekt und den temporären GCS-Bucket festzulegen.

# Set GCP project and temporary bucket 
project_id = "your-gcp-project-id"  # Replace with your GCP project ID 
bucket = "your-gcs-bucket"  # Replace with your GCS bucket for temporary files spark.conf.set("temporaryGcsBucket", bucket)

Google Analytics-Beispieldatenbank

Die Google Analytics-Beispieldatenbank wird in BigQuery über das Programm für öffentliche Datasets in Google Cloud bereitgestellt. Das Dataset enthält verschleierte Google Analytics 360-Daten aus einem Zeitraum von zwölf Monaten (August 2016 bis August 2017). Diese Daten stammen aus dem Google Merchandise Store , einem echten E‑Commerce-Shop, in dem Merchandise-Artikel von Google verkauft werden. So können Sie Geschäftsdaten analysieren und die Vorteile der Analyse von Analytics 360-Daten in BigQuery kennenlernen. Weitere Informationen zu den Daten

Die Daten sind typisch für eine E-Commerce-Website und umfassen die folgenden Informationen:

• Daten zu Besucherquellen: Informationen über die Herkunft der Websitebesucher, einschließlich Daten zu organischen Zugriffen, bezahlten Suchzugriffen und Zugriffen über das Displaynetzwerk
• Contentdaten: Informationen zum Verhalten der Nutzer auf der Website, z. B. URLs von Seiten, die sich Besucher ansehen, wie Besucher mit dem Content interagieren usw.
• Transaktionsdaten: Informationen zu Transaktionen auf der Website des Google Merchandise Store.

Führen Sie den folgenden Code aus, um die fünf wichtigsten Beispieldaten in Apache Spark anzuzeigen.

# EXTRACT: Read data from BigQuery
print("Extracting data from BigQuery...")
ga_df = spark.read.format("bigquery") \
   .option("table", "bigquery-public-data.google_analytics_sample.ga_sessions_20170801") \
   .load()

# Show schema sample data
print("Sample data:")
ga_df.show(5, truncate=False)

Erwartete Ausgabe:

Extracting data from BigQuery...
Sample data:
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
|visitorId|visitNumber|visitId   |visitStartTime|date    |totals                                                         |trafficSource                                                                                                                                                                          |device                                                                                                                                                                                                                                                                                                                                                                                                                      |geoNetwork                                                                                                                                                                                                                                                                     |customDimensions    |hits                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |fullVisitorId      |userId|clientId|channelGrouping|socialEngagementType|
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
|NULL     |1          |1501591568|1501591568    |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1}   |{NULL, (not set), (direct), (none), NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}                         |{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Europe, Southern Europe, Greece, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, tellas.gr, not available in demo dataset, not available in demo dataset, not available in demo dataset}          |[]                  |[{1, 0, 5, 46, NULL, true, true, true, https://www.google.gr/, {/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /bags/, /google+zipper+front+sports+bag.axd, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, shop.googlemerchandisestore.com/google+redesign/bags/google+zipper+front+sports+bag.axd, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No,  : }, NULL, NULL, {(not set), Bags, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]     |3418334011779872055|NULL  |NULL    |Organic Search |Not Socially Engaged|
|NULL     |2          |1501589647|1501589647    |20170801|{1, 1, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1}|{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Asia, Southern Asia, India, Maharashtra, (not set), Mumbai, not available in demo dataset, unknown.unknown, not available in demo dataset, not available in demo dataset, not available in demo dataset}                                                                      |[{4, APAC}]         |[{1, 0, 5, 14, NULL, true, true, true, https://analytics.google.com/analytics/web/, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No,  : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]                                                                        |2474397855041322408|NULL  |NULL    |Referral       |Not Socially Engaged|
|NULL     |1          |1501616621|1501616621    |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1}   |{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Europe, Northern Europe, United Kingdom, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, as9105.com, not available in demo dataset, not available in demo dataset, not available in demo dataset} |[{4, EMEA}]         |[{1, 0, 12, 43, NULL, true, true, true, https://analytics.google.com/analytics/web/?utm_source=demoaccount&utm_medium=demoaccount&utm_campaign=demoaccount, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No,  : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]|5870462820713110108|NULL  |NULL    |Referral       |Not Socially Engaged|
|NULL     |1          |1501601200|1501601200    |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1}   |{/analytics/web/, (not set), analytics.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}|{Firefox, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop}|{Americas, Northern America, United States, Texas, Dallas-Ft. Worth TX, Dallas, not available in demo dataset, h5colo.com, not available in demo dataset, not available in demo dataset, not available in demo dataset}                                                        |[{4, North America}]|[{1, 0, 8, 26, NULL, true, true, true, https://analytics.google.com/analytics/web/, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No,  : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]                                                                        |9397809171349480379|NULL  |NULL    |Referral       |Not Socially Engaged|
|NULL     |1          |1501615525|1501615525    |20170801|{1, 1, 1, NULL, 1, NULL, NULL, 1, NULL, NULL, NULL, NULL, 1}   |{/analytics/web/, (not set), adwords.google.com, referral, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, not available in demo dataset, NULL, NULL, NULL, NULL, NULL}, NULL, NULL}  |{Chrome, not available in demo dataset, not available in demo dataset, Windows, not available in demo dataset, false, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, NULL, not available in demo dataset, not available in demo dataset, not available in demo dataset, desktop} |{Americas, Northern America, United States, not available in demo dataset, not available in demo dataset, not available in demo dataset, not available in demo dataset, (not set), not available in demo dataset, not available in demo dataset, not available in demo dataset}|[{4, North America}]|[{1, 0, 12, 25, NULL, true, true, true, https://adwords.google.com/analytics/web/?__o=cues&authuser=0, {/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com, Page Unavailable, NULL, NULL, /google+redesign/, /shop+by+brand/, /youtube, }, NULL, NULL, NULL, {NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, shop.googlemerchandisestore.com/google+redesign/shop+by+brand/youtube, 0}, {NULL, true, NULL, NULL}, NULL, [], [], NULL, NULL, {0, 1, NULL}, [], NULL, [], [], [], PAGE, {NULL, NULL, NULL, NULL, (not set), NULL, No,  : }, NULL, NULL, {(not set), Brands, (not set), (not set), (not set), (entrance), (entrance), (entrance), (entrance), (entrance), NULL, 1, NULL, NULL, NULL}, web, []}]                                                     |6089902943184578335|NULL  |NULL    |Referral       |Not Socially Engaged|
+---------+-----------+----------+--------------+--------+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+------+--------+---------------+--------------------+
only showing top 5 rows

5. Daten nach Gerätebrowser zusammenfassen (grundlegende Messwerte)

In diesem Code werden Google Analytics-Daten nach Gerätebrowser zusammengefasst und verschiedene Messwerte wie Gesamtsitzungen, Besuche, Treffer, Seitenaufrufe, Absprünge, Zeit auf der Website, eindeutige Besucher und durchschnittliche Zeit pro Besuch berechnet. Anschließend wird eine Spalte umbenannt und die Ergebnisse werden sortiert.

print("Transformation 1: Aggregating by device browser...")
device_agg = ga_df.groupBy("device.browser") \
   .agg(
       count("*").alias("total_sessions"),
       sum("totals.visits").alias("total_visits"),
       sum("totals.hits").alias("total_hits"),
       sum("totals.pageviews").alias("total_pageviews"),
       sum("totals.bounces").alias("total_bounces"),
       sum("totals.timeOnSite").alias("total_time_on_site"),
       countDistinct("fullVisitorId").alias("unique_visitors"),
       (sum("totals.timeOnSite")/sum("totals.visits")).alias("avg_time_per_visit")
   ) \
   .withColumnRenamed("browser", "device_browser") \
   .orderBy("total_sessions", ascending=False)

# Show sample transformed data
print("\nDevice Aggregation Sample:")
device_agg.show(5)

Erwartete Ausgabe:

Transformation 1: Aggregating by device browser...

Device Aggregation Sample:
+-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+
|   device_browser|total_sessions|total_visits|total_hits|total_pageviews|total_bounces|total_time_on_site|unique_visitors|avg_time_per_visit|
+-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+
|           Chrome|          1900|        1900|     10896|           8956|          870|            354691|           1689|186.67947368421054|
|           Safari|           397|         397|      1260|           1137|          218|             43150|            364|108.69017632241814|
|          Firefox|           101|         101|       390|            343|           59|             10659|             95|105.53465346534654|
|Internet Explorer|            54|          54|       107|            102|           36|              5589|             50|             103.5|
|             Edge|            23|          23|        63|             55|           12|              2623|             23|114.04347826086956|
+-----------------+--------------+------------+----------+---------------+-------------+------------------+---------------+------------------+
only showing top 5 rows

6. Traffic-Quelle mit Umsatzberechnungen analysieren

Mit diesem Code werden Google Analytics-Daten nach Traffic-Quelle und ‑Medium analysiert und Messwerte wie die Anzahl der Sitzungen, Transaktionen, der Gesamtumsatz, der Umsatz pro Sitzung und die Anzahl der einzelnen Besucher berechnet. Anschließend werden die Spalten umbenannt und die Ergebnisse nach Gesamtumsatz sortiert.

print("Transformation 2: Analyzing traffic sources...")
traffic_source_agg = ga_df.groupBy("trafficSource.source", "trafficSource.medium") \
   .agg(
       count("*").alias("session_count"),
       sum(when(col("totals.transactions").isNotNull(), 1).otherwise(0)).alias("transactions"),
       sum("totals.totalTransactionRevenue").cast(DecimalType(20,2)).alias("total_revenue"),
       (sum("totals.totalTransactionRevenue")/count("*")).cast(DecimalType(10,2)).alias("revenue_per_session"),
       countDistinct("fullVisitorId").alias("unique_visitors")
   ) \
   .withColumnRenamed("source", "traffic_source") \
   .withColumnRenamed("medium", "traffic_medium") \
   .orderBy("total_revenue", ascending=False)

print("\nTraffic Source Aggregation Sample:")
traffic_source_agg.show(5)

Erwartete Ausgabe:

Transformation 2: Analyzing traffic sources...

Traffic Source Aggregation Sample:
+--------------------+--------------+-------------+------------+-------------+-------------------+---------------+
|      traffic_source|traffic_medium|session_count|transactions|total_revenue|revenue_per_session|unique_visitors|
+--------------------+--------------+-------------+------------+-------------+-------------------+---------------+
|            (direct)|        (none)|         2166|          42|8872040000.00|         4096048.01|           1943|
|     mail.google.com|      referral|            2|           1|  17960000.00|         8980000.00|              2|
|       google.com.tw|      referral|            1|           0|         NULL|               NULL|              1|
|analytics.google.com|      referral|           57|           0|         NULL|               NULL|             53|
|           quora.com|      referral|            6|           0|         NULL|               NULL|              5|
+--------------------+--------------+-------------+------------+-------------+-------------------+---------------+
only showing top 5 rows

7. Geografische Analyse

Mit diesem Code werden geografische Analysen von Google Analytics-Daten nach Land und Region durchgeführt. Dabei werden die Anzahl der Sitzungen, die Gesamtzahl der Seitenaufrufe, die Gesamtzeit auf der Website, die durchschnittliche Zeit pro Sitzung und die Anzahl der einzelnen Besucher berechnet. Anschließend werden die Spalten umbenannt und nach der Anzahl der Sitzungen sortiert.

print("Transformation 3: Geographic analysis...")
geo_agg = ga_df.groupBy("geoNetwork.country", "geoNetwork.region") \
   .agg(
       count("*").alias("session_count"),
       sum("totals.pageviews").alias("total_pageviews"),
       sum("totals.timeOnSite").alias("total_time_on_site"),
       (sum("totals.timeOnSite")/count("*")).alias("avg_time_per_session"),
       countDistinct("fullVisitorId").alias("unique_visitors")
   ) \
   .withColumnRenamed("country", "country") \
   .withColumnRenamed("region", "region") \
   .orderBy("session_count", ascending=False)

print("\nGeographic Aggregation Sample:")
geo_agg.show(5)

Erwartete Ausgabe:

Transformation 3: Geographic analysis...

Geographic Aggregation Sample:
+--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+
|       country|              region|session_count|total_pageviews|total_time_on_site|avg_time_per_session|unique_visitors|
+--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+
| United States|not available in ...|          564|           2326|             97829|  173.45567375886526|            494|
| United States|          California|          420|           3102|            116563|   277.5309523809524|            347|
| United States|            New York|          109|            845|             39976|   366.7522935779817|             84|
|United Kingdom|not available in ...|           82|            161|              7791|   95.01219512195122|             79|
|         India|not available in ...|           62|            139|              2869|  46.274193548387096|             61|
+--------------+--------------------+-------------+---------------+------------------+--------------------+---------------+
only showing top 5 rows

8. Zeitbasierte Analyse

Mit diesem Code wird eine zeitbasierte Analyse durchgeführt. Dazu wird die Stunde aus der Spalte visitStartTime extrahiert. Anschließend werden die Daten nach Stunde gruppiert, um die Anzahl der Sitzungen, die Anzahl der Transaktionen, den Gesamtumsatz und die Gesamtzahl der Seitenaufrufe für jede Stunde zu berechnen. Die Ergebnisse werden dann nach Stunde sortiert.

print("Transformation 4: Time-based analysis...")
hourly_agg = ga_df.withColumn("hour", date_format(col("visitStartTime").cast("timestamp"), "H")) \
   .groupBy("hour") \
   .agg(
       count("*").alias("session_count"),
       sum("totals.transactions").alias("transactions"),
       sum("totals.totalTransactionRevenue").cast(DecimalType(20,2)).alias("total_revenue"),
       sum("totals.pageviews").alias("total_pageviews")
   ) \
   .orderBy("hour")

print("\nHourly Aggregation Sample:")
hourly_agg.show(5)

Erwartete Ausgabe:

Transformation 4: Time-based analysis...

Hourly Aggregation Sample:
+----+-------------+------------+-------------+---------------+
|hour|session_count|transactions|total_revenue|total_pageviews|
+----+-------------+------------+-------------+---------------+
|   0|           87|        NULL|         NULL|            372|
|   1|          102|        NULL|         NULL|            494|
|  10|           67|        NULL|         NULL|            149|
|  11|           73|        NULL|         NULL|            167|
|  12|           99|        NULL|         NULL|            313|
+----+-------------+------------+-------------+---------------+
only showing top 5 rows

9. Ergebnis in BigQuery-Tabelle schreiben

Mit diesem Code werden vier aggregierte DataFrames (device_agg, traffic_source_agg, geo_agg und hourly_agg) in separate Tabellen in Google BigQuery exportiert. Dabei werden vorhandene Tabellen überschrieben, sofern sie vorhanden sind. Es wird eine direkte Schreibmethode verwendet.

# Write to BigQuery tables
print("\nLoading data to BigQuery...")

# Set output tables
device_output_table = f"{project_id}.analytics_sample.device_aggregation"
traffic_output_table = f"{project_id}.analytics_sample.traffic_source_aggregation"
geo_output_table = f"{project_id}.analytics_sample.geo_aggregation"
hourly_output_table = f"{project_id}.analytics_sample.hourly_aggregation"

dataset_id = "demo"  # Replace with your BigQuery dataset ID 

# Set BigQuery output table
device_output_table = f"{project_id}.{dataset_id}.device_aggregation"
traffic_output_table = f"{project_id}.{dataset_id}.traffic_source_aggregation"
geo_output_table = f"{project_id}.{dataset_id}.geo_aggregation"
hourly_output_table = f"{project_id}.{dataset_id}.hourly_aggregation"

# Write each DataFrame to BigQuery
device_agg.write \
   .format("bigquery") \
   .option("table", device_output_table) \
   .option("writeMethod", "direct") \
   .mode("overwrite") \
   .save()

traffic_source_agg.write \
   .format("bigquery") \
   .option("table", traffic_output_table) \
   .option("writeMethod", "direct") \
   .mode("overwrite") \
   .save()

geo_agg.write \
   .format("bigquery") \
   .option("table", geo_output_table) \
   .option("writeMethod", "direct") \
   .mode("overwrite") \
   .save()

hourly_agg.write \
   .format("bigquery") \
   .option("table", hourly_output_table) \
   .option("writeMethod", "direct") \
   .mode("overwrite") \
   .save()

Ausgabetabelle in BigQuery prüfen, um sicherzustellen, dass Daten nach der Transformation erfolgreich gespeichert wurden

device_aggregation-Tabelle

Tabelle für geografische Aggregation

hourly_aggregation-Tabelle

traffic_source_aggregation

10. BigQuery Studio-Notebook-Code orchestrieren (optional)

Sie haben folgende Möglichkeiten, BigQuery Studio-Notebook-Code zu orchestrieren:

• Notebook-Code über die Google Cloud Console planen ( es gelten die Notebook-Preise).
• Notebook-Code als serverlose Dataproc-Batcharbeitslast ausführen ( es gelten die Dataproc Serverless-Preise).

In diesem Codelab verwenden wir die Google Cloud Console, um Notebook-Code zu planen.

1. Klicken Sie in der Symbolleiste Notebook auf Planen.
2. Geben Sie im Bereich Notebook planen im Feld Name des Zeitplans einen Namen für den Zeitplan ein.
3. Autorisieren Sie das Notebook im Abschnitt Authentifizierung mit den Anmeldedaten Ihres Google-Kontos oder eines Dienstkontos.
4. Wenn Sie die Nutzeranmeldedaten Ihres Google-Kontos verwenden möchten ( Vorschau), wählen Sie Mit meinen Nutzeranmeldedaten ausführen aus.
5. Wenn Sie ein Dienstkonto verwenden möchten, wählen Sie Mit ausgewähltem Dienstkonto ausführen aus und wählen Sie dann ein Dienstkonto aus.
6. Wählen Sie im Abschnitt Notebook-Optionen im Feld Laufzeitvorlage eine Colab-Notebook-Laufzeitvorlage oder die Standardlaufzeitspezifikationen aus. Weitere Informationen zum Erstellen einer Laufzeitvorlage für ein Colab-Notebook finden Sie unter Laufzeitvorlage erstellen.
7. Klicken Sie im Feld Cloud Storage-Bucket auf Durchsuchen und wählen Sie einen Cloud Storage-Bucket aus oder erstellen Sie einen. Dem ausgewählten Dienstkonto muss die IAM-Rolle Storage-Administrator (roles/storage.admin) für den ausgewählten Bucket zugewiesen sein. Weitere Informationen finden Sie unter Notebook-Planung aktivieren.
8. Führen Sie im Abschnitt Planungshäufigkeit folgende Schritte aus:
9. Wählen Sie im Menü Wiederholungen die Häufigkeit der geplanten Notebook-Ausführungen aus.
10. Geben Sie im Feld Um die Uhrzeit für die geplanten Notebook-Ausführungen ein.
11. Wählen Sie im Menü Zeitzone die Zeitzone für den Zeitplan aus.
12. Klicken Sie auf Zeitplan erstellen. Wenn Sie Mit meinen Nutzeranmeldedaten ausführen als Authentifizierungsmethode ausgewählt haben, müssen Sie Ihr Google-Konto autorisieren ( Vorschau).

11. Bereinigen

So vermeiden Sie, dass Ihrem Google Cloud-Konto die in diesem Codelab verwendeten Ressourcen in Rechnung gestellt werden:

1. Wechseln Sie in der Google Cloud Console zur Seite Ressourcen verwalten.
2. Wählen Sie in der Projektliste das Projekt aus, das Sie löschen möchten, und klicken Sie auf Löschen.
3. Geben Sie im Dialogfeld die Projekt-ID ein und klicken Sie auf Beenden, um das Projekt zu löschen.

12. Glückwunsch

Sie haben die Datentransformationen und -analysen mit serverlosem Apache Spark in BigQuery Studio-Notebooks abgeschlossen. Dabei haben Sie die öffentlichen Datasets von Google Cloud kennengelernt, serverloses ETL mit Apache Spark in BigQuery Studio-Notebooks durchgeführt und BigQuery Studio-Notebooks orchestriert. Fantastisch!

Nächste Schritte :

• Orchestrieren Sie das Notebook mit einem Dienstkonto für Automatisierungszwecke.
• Script zum Überwachen der Dauer beim Ausführen des ETL-Jobs hinzufügen.
• Stellen Sie ein Notebook bereit.
• Verwenden Sie DataprocSparkSession, um die verteilten Apache Spark-Funktionen mit Serverless Dataproc in BigQuery Studio-Notebooks zu nutzen.
• Erstellen Sie eine gespeicherte Prozedur für Apache Spark in BigQuery Studio. So können Sie OOP-Prinzipien anwenden, um Ihren PySpark-Code besser zu organisieren, wiederzuverwenden und zu warten.

References :

• https://cloud.google.com/bigquery/docs/use-spark
• https://cloud.google.com/bigquery/docs/orchestrate-notebooks#explorer-pane
• https://cloud.google.com/bigquery/docs/spark-procedures
• https://cloud.google.com/bigquery/docs/service-dependencies#manually-enable-code-assets
• Gemini