Преобразование данных без сервера с помощью 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 Public Dataset
6. Преобразуйте данные с помощью агрегированных данных по браузеру устройства (основные метрики)
7. Преобразуйте данные с помощью анализа источника трафика и расчета доходов.
8. Преобразуйте данные с помощью географического анализа
9. Запись данных после преобразования в таблицу BigQuery

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

Предпосылки

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

Чему вы научитесь

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

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

• веб-браузер Chrome
• Аккаунт Gmail
• Облачный проект с включенным биллингом

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

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

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

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

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

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

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

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

Включить API

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

• API Compute Engine
• API Dataform
• API Vertex AI

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

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

После завершения включите и нажмите «Закрыть». Подробнее см. в статье «Включение 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 , хотя можно использовать DataprocSession для использования возможностей Dataproc и запуска Spark в BigQuery Studio Notebook.

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

таблица device_aggregation

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

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

агрегация_источника_трафика

10. Организуйте код блокнота BigQuery Studio (необязательно)

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

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

В этой лабораторной работе мы будем использовать код Schedule Notebook из консоли 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 Serverless в блокнотах BigQuery Studio. Попутно вы изучили общедоступные наборы данных Google Cloud, выполнили ETL-загрузку без сервера с Apache Spark в блокнотах BigQuery Studio и Orchestrated BigQuery Studio Notebook. Отличная работа!

Дальнейшие шаги:

• Упорядочьте блокнот с использованием учетной записи службы для целей автоматизации.
• Добавить скрипт для мониторинга длительности выполнения задания 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
• Близнецы