Bezserwerowa transformacja danych za pomocą Apache Spark w notatnikach BigQuery Studio

1. Wprowadzenie

Z tego ćwiczenia dowiesz się, jak wykorzystać możliwości Apache Spark do transformacji danych w znanym interfejsie BigQuery Studio. Wszystko to zrobisz w jednym notatniku – odczytasz dane z BigQuery, wykonasz oczyszczanie danych i przekształcenie za pomocą PySpark oraz zapiszesz wyniki w nowej tabeli BigQuery.

W tym ćwiczeniu będziesz wykonywać kolejne kroki:

1. Przygotuj projekt w chmurze Google i włącz w nim wszystkie wymagane interfejsy API.
2. Utwórz zasobnik GCS na folder tymczasowy.
3. Zaimportuj wymagane biblioteki do uruchomienia Apache Spark.
4. Zainicjuj sesję Spark za pomocą łącznika BigQuery.
5. Odczytaj przykładowe dane Google Analytics z publicznego zbioru danych BigQuery.
6. Przekształć dane, agregując dane zbiorcze według przeglądarki urządzenia (podstawowe wskaźniki)
7. Przekształć dane, analizując źródła wizyt i obliczając przychody.
8. Przekształć dane, przeprowadzając analizę geograficzną.
9. Zapisz dane po transformacji w tabeli BigQuery.

Omówienie architektury

Wymagania wstępne

• Projekt Google Cloud Platform (GCP) z włączonymi płatnościami.
• Włączone w projekcie GCP interfejsy BigQuery API i BigQuery Connection API.
• Podstawowa znajomość SQL i Pythona.

Czego się nauczysz

• Jak wyodrębniać dane za pomocą Apache Spark w notatniku BigQuery Studio.
• Jak przekształcać lub agregować dane za pomocą Apache Spark w notatniku BigQuery Studio.
• Jak zapisywać dane po przekształceniu lub agregacji za pomocą Apache Spark w notatniku BigQuery Studio.

Czego potrzebujesz

• Przeglądarka Chrome.
• Konto Gmail.
• Projekt Cloud z włączonymi płatnościami.

2. Podstawowa konfiguracja i wymagania

Samodzielne konfigurowanie środowiska

1. Zaloguj się w konsoli Google Cloud i utwórz nowy projekt lub użyj istniejącego. Jeśli nie masz jeszcze konta Gmail ani Google Workspace, musisz je utworzyć.

• Nazwa projektu to wyświetlana nazwa projektu dla uczestników. Jest to ciąg znaków, który nie jest używany przez interfejsy API Google. W każdej chwili możesz ją zaktualizować.
• Identyfikator projektu jest unikalny we wszystkich projektach Google Cloud i jest niezmienny (nie można go zmienić po ustawieniu). Konsola Cloud automatycznie generuje unikalny ciąg znaków. Zwykle nie musisz się nim przejmować. W większości ćwiczeń z programowania musisz odwoływać się do identyfikatora projektu (zwykle oznaczanego jako PROJECT_ID). Jeśli wygenerowany identyfikator Ci się nie podoba, możesz wygenerować inny losowy identyfikator. Możesz też spróbować użyć własnego identyfikatora i sprawdzić, czy jest dostępny. Po wykonaniu tego kroku nie można go zmienić i pozostaje on taki przez cały czas trwania projektu.
• Dla Twojej informacji istnieje trzecia wartość – numer projektu, której używają niektóre interfejsy API. Więcej informacji o tych 3 wartościach znajdziesz w dokumentacji.

Uwaga: identyfikator projektu jest unikalny na całym świecie i po jego wybraniu nie może go używać nikt inny. Jesteś jedynym użytkownikiem tego identyfikatora. Nawet jeśli projekt zostanie usunięty, nie będzie można ponownie użyć jego identyfikatora.

Uwaga: jeśli używasz konta Gmail, możesz pozostawić domyślną lokalizację ustawioną na Brak organizacji. Jeśli używasz konta Google Workspace, wybierz lokalizację, która ma sens dla Twojej organizacji.

2. Następnie musisz włączyć płatności w konsoli Cloud, aby móc korzystać z zasobów i interfejsów API Cloud. Wykonanie tego ćwiczenia nie będzie kosztować dużo, a może nawet nic. Aby wyłączyć zasoby i uniknąć naliczania opłat po zakończeniu tego samouczka, możesz usunąć utworzone zasoby lub projekt. Nowi użytkownicy Google Cloud mogą skorzystać z bezpłatnego okresu próbnego, w którym mają do dyspozycji środki w wysokości 300 USD.

3. Zanim zaczniesz

Włączanie interfejsu API

Zanim zaczniesz korzystać z notatników BigQuery Studio, musisz włączyć te interfejsy API:

• Compute Engine API
• Dataform API
• Vertex AI API

Aby włączyć je ręcznie, otwórz BigQuery. Na pasku kart panelu edytora kliknij strzałkę w dół obok znaku +, najedź kursorem na Notatnik, a następnie wybierz Szablon BigQuery, Pusty notatnik lub Szablon Spark.

W oknie Włącz interfejs API głównej funkcji kliknij Włącz w BigQuery Unified API.

Po zakończeniu włączania kliknij Zamknij. Więcej informacji znajdziesz w artykule Włączanie BigQuery Studio do zarządzania zasobami.

4. Odczytywanie publicznego zbioru danych

Najpierw utworzymy zasobnik GCS do tymczasowego użytku, aby móc uruchamiać Spark w notatnikach BigQuery Studio.

1. W konsoli Google Cloud otwórz BigQuery.
2. Na pasku kart panelu edytora kliknij strzałkę w dół obok znaku + , najedź kursorem na Notatnik , a następnie wybierz Pusty notatnik.
3. Kliknij komórkę kodu, a następnie wpisz poniższy skrypt CLI, aby utworzyć zasobnik GCS. Potem kliknij przycisk Uruchom komórkę lub naciśnij Shift + Enter.

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

Zaktualizuj wartości <your_project_id> zgodnie z tym, co zostało wybrane podczas tworzenia projektu Google Cloud. Zaktualizuj wartości <your_project_id>, używając identyfikatora projektu, aby utworzyć unikalną nazwę zasobnika GCS. Następnie kliknij przycisk Uruchom komórkę lub naciśnij Shift + Enter, aby uruchomić komórkę kodu.

Następnie zainicjujemy sesję Spark. W tym ćwiczeniu użyjemy biblioteki SparkSession, chociaż możemy też użyć DataprocSession, aby wykorzystać możliwości Dataproc do uruchamiania Spark w notatniku BigQuery Studio.

1. Kliknij komórkę kodu, a następnie wpisz poniższy skrypt CLI, aby zainicjować sesję Spark. Kliknij przycisk Uruchom komórkę lub naciśnij Shift + Enter.

# 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

Oczekiwane dane wyjściowe :

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

2. Kliknij komórkę kodu, a następnie wpisz poniższy skrypt CLI, aby ustawić projekt GCP i tymczasowy zasobnik GCS.

# 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)

Przykładowa baza danych Google Analytics

Przykładowa baza danych Google Analytics jest udostępniana w BigQuery w ramach programu publicznych zbiorów danych Google Cloud. Zbiór danych zawiera 12 miesięcy (od sierpnia 2016 r. do sierpnia 2017 r.) maskowanych danych Google Analytics 360 ze sklepu Google Merchandise Store , czyli prawdziwego sklepu e-commerce, który sprzedaje produkty z logo Google. To świetny sposób na analizowanie danych biznesowych i poznawanie korzyści związanych z używaniem BigQuery do analizowania danych Analytics 360. Więcej informacji o danych

Dane obejmują informacje, które przypominają dane z typowej witryny e-commerce, i zawierają te informacje:

• Dane na temat źródeł wizyt: informacje o lokalizacji użytkowników, w tym dane o ruchu bezpłatnym, płatnych wynikach wyszukiwania i ruchu z sieci reklamowej.
• Dane na temat treści: informacje o zachowaniu użytkowników w witrynie, np. adresy URL stron, na które wchodzą użytkownicy, sposób interakcji z treściami itp.
• Dane na temat transakcji: informacje o transakcjach w witrynie Google Merchandise Store.

Uruchom poniższy kod, aby wyświetlić 5 przykładowych danych w Apache Spark.

# 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)

Oczekiwane dane wyjściowe :

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. Agregowanie danych według przeglądarki urządzenia (podstawowe dane)

Ten kod agreguje dane Google Analytics według przeglądarki urządzenia, obliczając różne dane, takie jak łączna liczba sesji, wizyt, wyświetleń, odsłon, odrzuceń, czas spędzony w witrynie, unikalni użytkownicy i średni czas wizyty. Następnie zmienia nazwę kolumny i sortuje wyniki.

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)

Oczekiwane dane wyjściowe :

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. Analizowanie źródła wizyt z obliczeniami przychodów

Ten kod analizuje dane Google Analytics według źródła i medium wizyt, obliczając takie dane jak liczba sesji, transakcje, łączny przychód, przychód na sesję i unikalni użytkownicy. Następnie zmienia nazwy kolumn i sortuje wyniki według łącznego przychodu.

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)

Oczekiwane dane wyjściowe :

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. Analiza geograficzna

Ten kod przeprowadza analizę geograficzną danych Google Analytics według kraju i regionu, obliczając liczbę sesji, łączną liczbę odsłon, łączny czas spędzony w witrynie, średni czas trwania sesji i unikalnych użytkowników. Następnie zmienia nazwy kolumn i sortuje według liczby sesji.

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)

Oczekiwane dane wyjściowe :

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. Analiza w czasie

Ten kod przeprowadza analizę w czasie, wyodrębniając godzinę z kolumny visitStartTime, a następnie grupuje dane według godziny, aby obliczyć liczbę sesji, transakcje, łączny przychód i łączną liczbę odsłon w każdej godzinie. Na koniec sortuje wyniki według godziny.

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)

Oczekiwane dane wyjściowe :

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. Zapisywanie wyników w tabeli BigQuery

Ten kod eksportuje 4 zagregowane ramki danych (device_agg, traffic_source_agg, geo_agg i hourly_agg) do osobnych tabel w Google BigQuery, zastępując istniejące tabele, jeśli takie istnieją, za pomocą metody bezpośredniego zapisu.

# 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()

Sprawdź tabelę wyjściową w BigQuery, aby upewnić się, że dane zostały zapisane po przeprowadzeniu transformacji.

Tabela device_aggregation

Tabela geo_aggregation

Tabela hourly_aggregation

traffic_source_aggregation

10. Orkiestracja kodu notatnika BigQuery Studio (opcjonalnie)

Kod notatnika BigQuery Studio możesz orkiestrować na te sposoby:

• Zaplanuj kod notatnika w konsoli Google Cloud ( obowiązują ceny notatników).
• Uruchom kod notatnika jako zadanie wsadowe Dataproc Serverless ( obowiązują ceny Dataproc Serverless).

W tym ćwiczeniu użyjemy opcji Zaplanuj kod notatnika w konsoli Google Cloud.

1. Na pasku narzędzi Notatnik kliknij Zaplanuj.
2. W panelu Zaplanuj notatnik w polu Nazwa harmonogramu wpisz nazwę harmonogramu.
3. W sekcji Uwierzytelnianie autoryzuj notatnik za pomocą danych logowania użytkownika konta Google lub konta usługi.
4. Aby użyć danych logowania użytkownika konta Google ( wersja zapoznawcza), kliknij Uruchom z moimi danymi logowania użytkownika.
5. Aby użyć konta usługi, kliknij Uruchom z wybranym kontem usługi, a następnie wybierz konto usługi.
6. W sekcji Opcje notatnika w polu Szablon środowiska wykonawczego wybierz szablon środowiska wykonawczego notatnika Colab lub domyślne specyfikacje środowiska wykonawczego. Szczegółowe informacje o tworzeniu szablonu środowiska wykonawczego notatnika Colab znajdziesz w artykule Tworzenie szablonu środowiska wykonawczego.
7. W polu Zasobnik Cloud Storage kliknij Przeglądaj i wybierz lub utwórz zasobnik Cloud Storage. Wybrane konto usługi musi mieć przypisaną rolę administratora pamięci masowej (roles/storage.admin) IAM w wybranym zasobniku. Więcej informacji znajdziesz w artykule Włączanie planowania notatników.
8. W sekcji Częstotliwość harmonogramu wykonaj te czynności:
9. W menu Powtarza się wybierz częstotliwość zaplanowanych uruchomień notatnika.
10. W polu O godzinie wpisz godzinę zaplanowanych uruchomień notatnika.
11. W menu Strefa czasowa wybierz strefę czasową harmonogramu.
12. Kliknij Utwórz harmonogram. Jeśli jako metodę uwierzytelniania wybierzesz Uruchom z moimi danymi logowania użytkownika, musisz autoryzować swoje konto Google ( wersja zapoznawcza).

11. Czyszczenie

Aby uniknąć obciążenia konta Google Cloud opłatami za zasoby użyte w tym ćwiczeniu, wykonaj te czynności:

1. W konsoli Google Cloud otwórz stronę Zarządzanie zasobami.
2. Z listy projektów wybierz projekt do usunięcia, a potem kliknij Usuń.
3. W oknie wpisz identyfikator projektu i kliknij Wyłącz , aby usunąć projekt.

12. Gratulacje

Udało Ci się przeprowadzić transformacje i analizy danych za pomocą bezserwerowego Apache Spark w notatnikach BigQuery Studio. Po drodze zapoznałeś się z publicznymi zbiorami danych Google Cloud, przeprowadziłeś bezserwerowe ETL za pomocą Apache Spark w notatniku BigQuery Studio i orkiestrowałeś notatnik BigQuery Studio. Świetna robota!

Dalsze kroki :

• Orkiestruj notatnik, używając konta usługi do celów automatyzacji.
• Dodaj skrypt do monitorowania czasu trwania zadania ETL.
• Wdróż notatniki.
• Użyj DataprocSparkSession, aby wykorzystać rzeczywiste możliwości rozproszonego Apache Spark z bezserwerowym Dataproc w notatnikach BigQuery Studio.
• Utwórz procedurę składowaną dla Apache Spark w BigQuery Studio. Dzięki temu możesz stosować zasady OOP do strukturyzowania kodu PySpark, aby zwiększyć jego przejrzystość, możliwość ponownego użycia i łatwość utrzymania.

Źródła:

• 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