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

1. Wprowadzenie

Z tego modułu dowiesz się, jak wykorzystać możliwości Apache Spark do transformacji danych w znanym interfejsie BigQuery Studio. W ramach tego ćwiczenia odczytasz dane z BigQuery, oczyścisz i przekształcisz je za pomocą PySpark, a następnie zapiszesz wyniki w nowej tabeli BigQuery. Wszystko to zrobisz w jednym notatniku.

W ramach ćwiczeń z programowania będziesz wykonywać kolejne czynności:

1. Przygotowywanie projektu Google Cloud i włączanie w nim wszystkich wymaganych interfejsów API
2. Tworzenie zasobnika GCS na potrzeby folderu tymczasowego
3. Zaimportuj wymagane biblioteki do uruchomienia Apache Spark
4. Inicjowanie sesji Spark z oprogramowaniem sprzęgającym BigQuery
5. Odczytywanie przykładowych danych Google Analytics z publicznego zbioru danych BigQuery
6. Przekształcanie danych za pomocą zagregowanych danych według przeglądarki na urządzeniu (podstawowe dane)
7. Przekształcanie danych za pomocą analizy źródeł wizyt z obliczeniami przychodów
8. Przekształcanie danych za pomocą analizy geograficznej
9. Zapisywanie danych po przekształceniu w tabeli BigQuery

Omówienie architektury

Wymagania wstępne

• projekt Google Cloud Platform (GCP) z włączonymi płatnościami;
• Interfejsy BigQuery API i BigQuery Connection API włączone w projekcie GCP.
• 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ądarki Chrome,
• konto Gmail,
• Projekt w chmurze 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 lub Google Workspace, musisz je utworzyć.

• Nazwa projektu to wyświetlana nazwa uczestników tego projektu. Jest to ciąg znaków, który nie jest używany przez interfejsy API Google. Zawsze możesz ją zaktualizować.
• Identyfikator projektu jest unikalny we wszystkich projektach Google Cloud i nie można go zmienić po ustawieniu. Konsola Cloud automatycznie generuje unikalny ciąg znaków. Zwykle nie musisz się tym 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ć własnej nazwy i sprawdzić, czy jest dostępna. Po tym kroku nie można go zmienić i pozostaje on taki przez cały czas trwania projektu.
• Warto wiedzieć, że istnieje jeszcze trzecia wartość, czyli numer projektu, którego używają niektóre interfejsy API. Więcej informacji o tych 3 wartościach znajdziesz w dokumentacji.

Ostrzeżenie: identyfikator projektu jest unikalny na całym świecie i po jego wybraniu nie może być używany przez nikogo innego. Jesteś jedynym użytkownikiem tego identyfikatora. Nawet po usunięciu projektu nie 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ę odpowiednią dla Twojej organizacji.

2. Następnie musisz włączyć rozliczenia w konsoli Cloud, aby korzystać z zasobów i interfejsów API Google Cloud. Wykonanie tego laboratorium 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 programu bezpłatnego okresu próbnego, w którym mają do dyspozycji środki w wysokości 300 USD.

3. Zanim zaczniesz

Włącz API

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

• Interfejs Compute Engine API
• Interfejs Dataform API
• Vertex AI API

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

W oknie Włącz interfejs API funkcji podstawowych kliknij Włącz w BigQuery Unified API.

Po zakończeniu włącz go, a potem 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 można było uruchamiać Sparka w notatnikach BigQuery Studio.

1. W konsoli Google Cloud otwórz BigQuery.
2. Na pasku kart w panelu edytora kliknij strzałkę w dół obok znaku +, najedź kursorem na Notatnik, a potem kliknij 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> o identyfikator 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 rozpoczniemy sesję Spark. W tym samouczku użyjemy biblioteki SparkSession, chociaż możemy też użyć DataprocSession, aby wykorzystać możliwości Dataproc do uruchamiania Sparka w notatniku BigQuery Studio.

1. Kliknij komórkę z kodem, a następnie wpisz poniższy skrypt CLI, aby zainicjować sesję Sparka. 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 , prawdziwego sklepu internetowego, w którym sprzedawane są produkty z logo Google. Można dzięki niemu analizować dane biznesowe i poznać korzyści związane z używaniem BigQuery do analizowania danych Analytics 360. Więcej informacji o danych

Dane te są podobne do danych z typowej witryny e-commerce i obejmują następujące 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 reklam displayowych.
• Dane na temat treści: informacje o zachowaniu użytkowników w witrynie, takie jak adresy URL stron, które odwiedzają użytkownicy, i informacje o tym, jak z nich korzystają.
• Dane na temat transakcji: informacje o transakcjach w witrynie Google Merchandise Store.

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

# 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 na urządzeniu (podstawowe dane)

Ten kod agreguje dane Google Analytics według przeglądarki na urządzeniu, 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 na podstawie obliczeń przychodów

Ten kod analizuje dane Google Analytics według źródła i medium ruchu, obliczając takie dane jak liczba sesji, transakcje, łączne przychody, przychody na sesję i unikalni użytkownicy. Następnie zmienia nazwy kolumn i sortuje wyniki według łącznych przychodów.

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ę wyświetleń stron, łączny czas spędzony w witrynie, średni czas trwania sesji i liczbę unikalnych użytkowników, a następnie zmienia nazwy kolumn i sortuje dane 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ę czasową, wyodrębniając godzinę z kolumny visitStartTime, a następnie grupuje dane według godziny, aby obliczyć liczbę sesji, transakcji, łącznych przychodów i łącznej liczby wyświetleń strony w każdej godzinie. Na koniec porządkuje 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 przekształceniu.

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 koordynować w ten sposób:

• Planowanie kodu notatnika w konsoli Google Cloud ( obowiązują ceny notatników).
• Uruchamiaj kod notatnika jako zadanie wsadowe Dataproc Serverless ( obowiązuje cennik Dataproc Serverless).

W tym module użyjemy funkcji Schedule notebook code (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 ( Podgląd), 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ę uprawnień Administrator miejsca na dane (roles/storage.admin) w wybranym zasobniku. Więcej informacji znajdziesz w artykule Włączanie harmonogramu notatnika.
8. W sekcji Częstotliwość harmonogramu:
9. W menu Powtarza się wybierz częstotliwość zaplanowanych uruchomień notatnika.
10. W polu O godzinie wpisz godzinę zaplanowanego uruchomienia notatnika.
11. W menu Strefa czasowa wybierz strefę czasową dla harmonogramu.
12. Kliknij Utwórz harmonogram. Jeśli jako metodę uwierzytelniania wybierzesz Execute with my user credentials (Uruchom przy użyciu moich danych logowania), musisz autoryzować swoje konto Google (podgląd).

11. Czyszczenie

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

1. W konsoli Google Cloud otwórz stronę Zarządzanie zasobami.
2. Na liście projektów wybierz projekt, który chcesz usunąć, a potem kliknij Usuń.
3. W oknie wpisz identyfikator projektu i kliknij Wyłącz, aby usunąć projekt.

12. Gratulacje

Ukończono przekształcanie i analizowanie danych za pomocą bezserwerowego Apache Spark w notatnikach BigQuery Studio. W trakcie tego procesu zapoznasz się z publicznymi zbiorami danych Google Cloud, przeprowadzisz bezserwerową ekstrakcję, przekształcanie i wczytywanie danych za pomocą Apache Spark w notatniku BigQuery Studio i zorganizujesz notatnik BigQuery Studio. Świetna robota!

Dalsze kroki :

• Orkiestruj notebooka za pomocą konta usługi w celu automatyzacji.
• Dodaj skrypt do monitorowania czasu trwania podczas uruchamiania zadania ETL.
• Wdróż notatniki.
• Używaj DataprocSparkSession, aby korzystać z prawdziwych możliwości rozproszonego Apache Spark w bezserwerowej usłudze Dataproc w notatnikach BigQuery Studio.
• Utwórz procedurę składowaną dla Apache Spark w BigQuery Studio. W ten sposób możesz zastosować zasady programowania obiektowego, aby lepiej uporządkować, ponownie wykorzystać i łatwiej utrzymywać kod PySpark.

Ź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