Преобразование данных без сервера с помощью Apache Spark в блокнотах BigQuery Studio

1. Введение

В этом практическом занятии вы узнаете, как использовать возможности Apache Spark для преобразования данных в привычном интерфейсе BigQuery Studio. Вы будете считывать данные из BigQuery, выполнять очистку и преобразование данных с помощью PySpark и записывать результаты обратно в новую таблицу BigQuery — всё это из одного блокнота.

В ходе выполнения практического задания вы будете использовать следующий пошаговый подход:

1. Подготовьте свой проект в Google Cloud и включите в него все необходимые API.
2. Создайте корзину GCS для временной папки.
3. Для запуска Apache Spark необходимо импортировать необходимые библиотеки.
4. Инициализация сессии Spark с помощью коннектора BigQuery.
5. Просмотрите пример данных Google Analytics из общедоступного набора данных BigQuery.
6. Преобразовать данные с использованием агрегированных данных по браузеру устройства (базовые метрики)
7. Преобразуйте данные с помощью анализа источников трафика и расчета доходов.
8. Преобразование данных с помощью географического анализа.
9. Запись данных после преобразования в таблицу BigQuery.

Обзор архитектуры

Предварительные требования

• Проект на платформе Google Cloud Platform (GCP) с включенной функцией выставления счетов.
• API BigQuery и API подключения BigQuery должны быть включены в вашем проекте GCP.
• Базовые знания SQL и Python.

Что вы узнаете

• Как извлечь данные с помощью Apache Spark в блокноте BigQuery Studio
• Как преобразовать или агрегировать данные с помощью Apache Spark в блокноте BigQuery Studio
• Как записывать данные после преобразования или агрегирования данных с помощью Apache Spark в блокноте BigQuery Studio

Что вам понадобится

• Веб-браузер Chrome
• Аккаунт Gmail
• Облачный проект с включенной функцией выставления счетов.

2. Базовая настройка и требования

Настройка среды для самостоятельного обучения

1. Войдите в консоль Google Cloud и создайте новый проект или используйте существующий. Если у вас еще нет учетной записи Gmail или Google Workspace, вам необходимо ее создать .

• Название проекта — это отображаемое имя участников данного проекта. Это строка символов, не используемая API Google. Вы всегда можете его изменить.
• Идентификатор проекта уникален для всех проектов Google Cloud и является неизменяемым (его нельзя изменить после установки). Консоль Cloud автоматически генерирует уникальную строку; обычно вам неважно, какая она. В большинстве практических заданий вам потребуется указать идентификатор вашего проекта (обычно обозначается как PROJECT_ID ). Если сгенерированный идентификатор вас не устраивает, вы можете сгенерировать другой случайный идентификатор. В качестве альтернативы вы можете попробовать свой собственный и посмотреть, доступен ли он. После этого шага его нельзя изменить, и он сохраняется на протяжении всего проекта.
• К вашему сведению, существует третье значение — номер проекта, которое используется некоторыми API. Подробнее обо всех трех значениях можно узнать в документации .

Внимание: Идентификатор проекта является уникальным во всем мире и не может быть использован кем-либо еще после того, как вы его выбрали. Вы являетесь единственным пользователем этого идентификатора. Даже если проект будет удален, идентификатор нельзя будет использовать снова.

Примечание: Если вы используете учетную запись Gmail, вы можете оставить значение по умолчанию «Без организации». Если вы используете учетную запись Google Workspace, выберите местоположение, которое подходит для вашей организации.

2. Далее вам потребуется включить оплату в консоли Cloud для использования ресурсов/API Cloud. Выполнение этого практического задания не потребует больших затрат, если вообще потребует. Чтобы отключить ресурсы и избежать дополнительных расходов после завершения этого урока, вы можете удалить созданные ресурсы или удалить проект. Новые пользователи Google Cloud имеют право на бесплатную пробную версию стоимостью 300 долларов США .

3. Прежде чем начать

Включить API

Перед использованием блокнотов BigQuery Studio необходимо включить следующие API:

• API Compute Engine
• API форм данных
• API искусственного интеллекта Vertex

Для включения вручную перейдите в BigQuery. На панели вкладок редактора щелкните стрелку раскрывающегося списка рядом со знаком «+» , наведите указатель мыши на «Блокнот» , а затем выберите «Шаблон BigQuery» , « Пустой блокнот» или «Шаблон Spark».

В окне «Включить API основных функций» нажмите «Включить» в разделе «Единый API BigQuery».

После завершения включите функцию, а затем нажмите «Закрыть». Дополнительные сведения см. в разделе «Включение BigQuery Studio для управления ресурсами».

4. Прочитайте общедоступный набор данных.

Сначала мы создадим временный сегмент GCS, чтобы можно было запускать Spark в блокнотах BigQuery Studio.

1. В консоли Google Cloud перейдите в раздел BigQuery.
2. В панели вкладок редактора щелкните стрелку раскрывающегося списка рядом со знаком «+» , наведите указатель мыши на «Блокнот» и выберите «Пустой блокнот».
3. Щелкните ячейку с кодом, затем введите приведенный ниже скрипт CLI для создания корзины GCS, после чего нажмите кнопку «Выполнить» или нажмите Shift + Enter.

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

Обновите значения для <your_project_id> в соответствии с тем, что вы выбрали при создании проекта Google Cloud. Обновите значения для <your_project_id>, указав идентификатор вашего проекта, чтобы создать уникальное имя корзины GCS. После этого нажмите кнопку «Выполнить» или нажмите Shift + Enter , чтобы запустить ячейку с кодом.

Далее мы запустим сессию Spark. В этом практическом занятии мы будем использовать библиотеку SparkSession , хотя для запуска Spark в BigQuery Studio Notebook можно использовать DataprocSession , чтобы задействовать возможности Dataproc.

1. Щелкните ячейку с кодом, затем введите приведенный ниже скрипт CLI для инициализации сессии Spark. Нажмите кнопку «Выполнить» или нажмите 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

Ожидаемый результат:

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

2. Щелкните ячейку с кодом, затем введите указанный ниже скрипт командной строки, чтобы задать проект GCP и временное хранилище 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)

Пример базы данных Google Analytics

Образец базы данных Google Analytics предоставляется в BigQuery через программу общедоступных наборов данных Google Cloud . Этот набор данных содержит обфусцированные данные Google Analytics 360 за 12 месяцев (с августа 2016 по август 2017 года) из Google Merchandise Store — реального интернет-магазина, продающего товары под брендом Google, — в формате BigQuery. Это отличный способ анализа бизнес-данных и изучения преимуществ использования BigQuery для анализа данных Analytics 360. Узнайте больше о данных.

Данные типичны для сайтов электронной коммерции и включают следующую информацию:

• Данные об источниках трафика: информация о том, откуда приходят посетители сайта, включая данные об органическом трафике, платном поисковом трафике и контекстной рекламе.
• Данные о контенте: информация о поведении пользователей на сайте, например, URL-адреса страниц, которые просматривают посетители, как они взаимодействуют с контентом и т. д.
• Данные о транзакциях: информация о транзакциях на сайте Google Merchandise Store.

Запустите приведенный ниже код, чтобы отобразить пример данных из топ-5 в 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)

Ожидаемый результат:

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. Агрегирование данных по браузерам устройств (основные метрики)

Этот код агрегирует данные Google Analytics по браузерам устройств, вычисляя различные метрики, такие как общее количество сессий, посещений, просмотров страниц, отказов, времени на сайте, уникальных посетителей и среднего времени посещения, а затем переименовывает столбец и сортирует результаты.

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)

Ожидаемый результат:

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. Анализ источников трафика с расчетом доходов.

Этот код анализирует данные Google Analytics по источникам и каналам трафика, вычисляя такие показатели, как количество сессий, транзакций, общего дохода, дохода за сессию и уникальных посетителей, а затем переименовывает столбцы и сортирует результаты по общему доходу.

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)

Ожидаемый результат:

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. Географический анализ

Этот код выполняет географический анализ данных Google Analytics по странам и регионам, вычисляя количество сессий, общее количество просмотров страниц, общее время на сайте, среднее время за сессию и количество уникальных посетителей, а затем переименовывает столбцы и сортирует данные по количеству сессий.

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)

Ожидаемый результат:

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. Анализ по времени

Этот код выполняет анализ по времени, извлекая час из столбца visitStartTime , затем группирует данные по часам для расчета количества сессий, транзакций, общей выручки и общего количества просмотров страниц за каждый час, и, наконец, упорядочивает результаты по часам.

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)

Ожидаемый результат:

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. Запишите результат в таблицу BigQuery.

Этот код экспортирует четыре агрегированных датафрейма ( device_agg , traffic_source_agg , geo_agg и hourly_agg ) в отдельные таблицы Google BigQuery, перезаписывая существующие таблицы, если они есть, используя метод прямой записи.

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

Проверьте выходную таблицу в BigQuery, чтобы убедиться, что данные успешно сохранены после выполнения преобразований.

таблица агрегации устройств

таблица геоагрегации

таблица почасовой агрегации

traffic_source_aggregation

10. Настройка кода блокнота BigQuery Studio (необязательно)

Вы можете управлять кодом блокнота BigQuery Studio следующими способами:

• Запланируйте выполнение кода в блокноте из консоли Google Cloud (действуют тарифные планы для блокнотов ).
• Запускайте код ноутбука в качестве пакетной рабочей нагрузки Dataproc Serverless (применяются тарифные планы Dataproc Serverless ).

В этом практическом занятии мы будем использовать код блокнота Schedule из консоли Google Cloud.

1. На панели инструментов блокнота нажмите «Расписание» .
2. В панели «Записная книжка расписания» в поле «Название расписания» введите название для расписания.
3. В разделе «Аутентификация» авторизуйте блокнот, используя учетные данные вашей учетной записи Google или учетную запись службы.
4. Чтобы использовать учетные данные вашей учетной записи Google ( предварительная версия ), выберите «Выполнить с моими учетными данными пользователя» .
5. Чтобы использовать учетную запись службы, выберите «Выполнить с выбранной учетной записью службы» , а затем выберите учетную запись службы.
6. В разделе «Параметры блокнота» в поле «Шаблон среды выполнения» выберите шаблон среды выполнения блокнота Colab или спецификации среды выполнения по умолчанию. Подробную информацию о создании шаблона среды выполнения блокнота Colab см. в разделе «Создание шаблона среды выполнения» .
7. В поле «Корзина облачного хранилища» нажмите «Обзор» и выберите или создайте корзину облачного хранилища. Выбранной учетной записи службы должна быть предоставлена ​​роль IAM « Администратор хранилища» ( roles/storage.admin ) для выбранной корзины. Для получения дополнительной информации см. раздел «Включение планирования блокнотов» .
8. В разделе «Частота составления расписания» выполните следующие действия:
9. В меню «Повторения» выберите частоту запуска запланированных блокнотов.
10. В поле «Время» укажите время запланированных запусков блокнота.
11. В меню «Часовой пояс» выберите часовой пояс для расписания.
12. Нажмите «Создать расписание» . Если в качестве метода аутентификации вы выбрали «Выполнить с использованием моих учетных данных пользователя» , вам необходимо авторизовать свою учетную запись Google ( предварительная версия ).

11. Уборка

Чтобы избежать списания средств с вашего аккаунта Google Cloud за ресурсы, использованные в этом практическом задании, выполните следующие действия:

1. В консоли Google Cloud перейдите на страницу «Управление ресурсами» .
2. В списке проектов выберите проект, который хотите удалить, и нажмите кнопку «Удалить» .
3. В диалоговом окне введите идентификатор проекта, а затем нажмите «Завершить» , чтобы удалить проект.

12. Поздравляем!

Вы завершили преобразование и анализ данных с использованием бессерверной архитектуры Apache Spark в блокнотах BigQuery Studio. В процессе работы вы изучили общедоступные наборы данных Google Cloud, выполнили бессерверную ETL-обработку с помощью Apache Spark в блокноте BigQuery Studio и организовали работу с блокнотом BigQuery Studio. Отличная работа!

Следующие шаги:

• Используйте учетную запись службы для автоматизации при создании оркестрованных блокнотов.
• Добавить скрипт для мониторинга продолжительности выполнения задания ETL.
• Разверните блокноты .
• Используйте DataprocSparkSession для задействования возможностей распределенной среды Apache Spark с помощью Serverless Dataproc в блокнотах BigQuery Studio.
• Создайте хранимую процедуру для Apache Spark в BigQuery Studio. Таким образом, вы сможете применить принципы ООП для структурирования кода PySpark, что улучшит его организацию, повторное использование и удобство сопровождения.

Ссылки :

• 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
• Близнецы