1. Introduction
Dans cet atelier de programmation, vous allez apprendre à exploiter la puissance d'Apache Spark pour transformer des données dans l'interface familière de BigQuery Studio. Vous lirez les données de BigQuery, les nettoierez et les transformerez à l'aide de PySpark, puis réécrirez les résultats dans une nouvelle table BigQuery, le tout à partir d'un seul notebook.
Dans cet atelier de programmation, vous allez suivre une approche par étapes :
- Préparez votre projet Google Cloud et activez toutes les API requises.
- Créer un bucket GCS pour le dossier temporaire
- Importer les bibliothèques requises pour exécuter Apache Spark
- Initialiser la session Spark avec le connecteur BigQuery
- Lire un exemple de données Google Analytics à partir de l'ensemble de données public BigQuery
- Transformer les données avec des données agrégées par navigateur de l'appareil (métriques de base)
- Transformer les données avec l'analyse des sources de trafic et les calculs des revenus
- Transformer des données avec l'analyse géographique
- Écrire des données après transformation dans une table BigQuery
Présentation de l'architecture
Prérequis
- Un projet Google Cloud Platform (GCP) avec la facturation activée.
- Les API BigQuery et BigQuery Connection sont activées dans votre projet GCP.
- Connaissances de base de SQL et de Python
Points abordés
- Extraire des données à l'aide d'Apache Spark dans un notebook BigQuery Studio
- Transformer ou agréger des données à l'aide d'Apache Spark dans un notebook BigQuery Studio
- Écrire des données après les avoir transformées ou agrégées à l'aide d'Apache Spark dans un notebook BigQuery Studio
Prérequis
- Navigateur Web Chrome
- Un compte Gmail
- Un projet Cloud pour lequel la facturation est activée
2. Configuration de base et conditions requises
Configuration de l'environnement au rythme de chacun
- Connectez-vous à la console Google Cloud, puis créez un projet ou réutilisez un projet existant. Si vous n'avez pas encore de compte Gmail ou Google Workspace, vous devez en créer un.
- Le nom du projet est celui que verront les participants au projet. Il s'agit d'une chaîne de caractères non utilisée par les API Google. Vous pourrez toujours le modifier.
- L'ID du projet est unique parmi tous les projets Google Cloud et non modifiable une fois défini. La console Cloud génère automatiquement une chaîne unique (en général, vous n'y accordez d'importance particulière). Dans la plupart des ateliers de programmation, vous devrez indiquer l'ID de votre projet (généralement identifié par
PROJECT_ID). Si l'ID généré ne vous convient pas, vous pouvez en générer un autre de manière aléatoire. Vous pouvez également en spécifier un et voir s'il est disponible. Après cette étape, l'ID n'est plus modifiable et restera donc le même pour toute la durée du projet. - Pour information, il existe une troisième valeur (le numéro de projet) que certaines API utilisent. Pour en savoir plus sur ces trois valeurs, consultez la documentation.
- Vous devez ensuite activer la facturation dans la console Cloud pour utiliser les ressources/API Cloud. L'exécution de cet atelier de programmation est très peu coûteuse, voire sans frais. Pour désactiver les ressources et éviter ainsi que des frais ne vous soient facturés après ce tutoriel, vous pouvez supprimer le projet ou les ressources que vous avez créées. Les nouveaux utilisateurs de Google Cloud peuvent participer au programme d'essai sans frais pour bénéficier d'un crédit de 300 $.
3. Avant de commencer
Activer l'API
Avant d'utiliser les notebooks BigQuery Studio, vous devez activer les API suivantes :
- L'API Compute Engine
- API Dataform
- API Vertex AI
Pour l'activer manuellement, accédez à BigQuery. Dans la barre d'onglets du volet de l'éditeur, cliquez sur la flèche du menu déroulant à côté du signe +, pointez sur Notebook, puis sélectionnez Modèle BigQuery, Notebook vide ou Modèle Spark.
Dans la fenêtre "Activer l'API des fonctionnalités principales", cliquez sur "Activer" dans l'API BigQuery Unified.
Une fois l'opération terminée, activez-le, puis cliquez sur "Fermer". Pour en savoir plus, consultez Activer BigQuery Studio pour la gestion des éléments.
4. Lire un ensemble de données public
Nous allons commencer par créer un bucket GCS pour une utilisation temporaire afin de pouvoir exécuter Spark dans les notebooks BigQuery Studio.
- Dans la console Google Cloud, accédez à BigQuery.
- Dans la barre d'onglets du volet de l'éditeur, cliquez sur la flèche vers le bas à côté du signe +, pointez sur Notebook, puis sélectionnez Notebook vide.
- Cliquez sur la cellule de code, puis saisissez le script CLI ci-dessous pour créer un bucket GCS. Cliquez ensuite sur le bouton "Exécuter la cellule" ou appuyez sur Maj+Entrée.
!gsutil mb -p <your_project_id> -c STANDARD -l US gs://ioxid2025-<your_project_id>
Mettez à jour les valeurs de <your_project_id> en fonction de ce que vous avez sélectionné lors de la création du projet Google Cloud. Mettez à jour les valeurs de <your_project_id> avec l'ID de votre projet pour créer un nom de bucket GCS unique. Cliquez ensuite sur le bouton "Exécuter la cellule" ou appuyez sur Maj+Entrée pour exécuter la cellule de code.
Ensuite, nous allons lancer une session Spark. Dans cet atelier de programmation, nous allons utiliser la bibliothèque SparkSession, bien que nous puissions utiliser DataprocSession pour exploiter les fonctionnalités Dataproc et exécuter Spark dans un notebook BigQuery Studio.
- Cliquez sur la cellule de code, puis saisissez le script CLI ci-dessous pour initialiser la session Spark. Cliquez sur le bouton "Exécuter la cellule" ou appuyez sur Maj+Entrée.
# 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
Résultat attendu :
SparkSession - in-memory SparkContext Spark UI Version v3.5.4 Master local[*] AppName Google Analytics ETL with Apache Spark
- Cliquez sur la cellule de code, puis saisissez le script CLI ci-dessous pour définir le projet GCP et le bucket GCS temporaire.
# 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)
Exemple de base de données Google Analytics
L'exemple de base de données Google Analytics est fourni sur BigQuery via le programme d'ensembles de données publics de Google Cloud. Cet ensemble de données fournit 12 mois (d'août 2016 à août 2017) de données Google Analytics 360 masquées provenant du Google Merchandise Store , un véritable magasin d'e-commerce qui vend des produits Google dans BigQuery. Il permet d'analyser des données d'entreprise, et de découvrir les avantages de l'utilisation de BigQuery pour analyser les données Analytics 360. En savoir plus sur les données
Ces données sont typiques de ce que voit un site Web d'e-commerce et comprennent les informations suivantes :
- Données sur les sources de trafic : informations sur la provenance des visiteurs du site Web, y compris des données sur le trafic généré par les résultats naturels, par la recherche sponsorisée et par la publicité display
- Données relatives au contenu : informations sur le comportement des utilisateurs sur le site (par exemple, URL des pages consultées par les visiteurs, types d'interactions avec le contenu, etc.)
- Données transactionnelles : informations sur les transactions enregistrées sur le site Web Google Merchandise Store
Exécutez le code ci-dessous pour afficher un échantillon des cinq premières données dans 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)
Résultat attendu :
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. Agréger les données par navigateur de l'appareil (métriques de base)
Ce code agrège les données Google Analytics par navigateur d'appareil, en calculant diverses métriques telles que le nombre total de sessions, de visites, d'accès, de pages vues, de rebonds, le temps passé sur le site, le nombre de visiteurs uniques et le temps moyen par visite. Il renomme ensuite une colonne et trie les résultats.
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)
Résultat attendu :
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. Analyser la source de trafic avec les calculs de revenus
Ce code analyse les données Google Analytics par source et support de trafic. Il calcule des métriques telles que le nombre de sessions, les transactions, le revenu total, le revenu par session et les visiteurs uniques. Il renomme ensuite les colonnes et trie les résultats par revenu total.
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)
Résultat attendu :
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. Analyse géographique
Ce code effectue une analyse géographique des données Google Analytics par pays et région. Il calcule le nombre de sessions, le nombre total de pages vues, le temps total passé sur le site, le temps moyen par session et le nombre de visiteurs uniques. Il renomme ensuite les colonnes et les trie par nombre de sessions.
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)
Résultat attendu :
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. Analyse basée sur le temps
Ce code effectue une analyse temporelle en extrayant l'heure de la colonne visitStartTime, puis regroupe les données par heure pour calculer le nombre de sessions, les transactions, le revenu total et le nombre total de vues de page pour chaque heure. Enfin, il trie les résultats par heure.
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)
Résultat attendu :
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. Écrire le résultat dans une table BigQuery
Ce code exporte quatre DataFrames agrégés (device_agg, traffic_source_agg, geo_agg et hourly_agg) vers des tables distinctes dans Google BigQuery, en écrasant les tables existantes le cas échéant, à l'aide d'une méthode d'écriture directe.
# 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()
Vérifiez la table de sortie dans BigQuery pour vous assurer que les données ont bien été enregistrées après avoir été transformées.
Table device_aggregation
Table geo_aggregation
Table hourly_aggregation
traffic_source_aggregation
10. Orchestrer le code de notebook BigQuery Studio (facultatif)
Vous pouvez orchestrer le code de notebook BigQuery Studio de différentes manières :
- Planifiez le code du notebook depuis la console Google Cloud ( la tarification des notebooks s'applique).
- Exécutez le code du notebook en tant que charge de travail par lot Dataproc Serverless ( les tarifs Dataproc Serverless s'appliquent).
Dans cet atelier de programmation, nous allons planifier le code du notebook à partir de la console Google Cloud.
- Dans la barre d'outils Notebook, cliquez sur Planifier.
- Dans le volet Programmer un notebook, dans le champ Nom de la programmation, saisissez un nom pour la programmation.
- Dans la section Authentification, autorisez le notebook avec les identifiants de votre compte Google ou un compte de service.
- Pour utiliser les identifiants utilisateur de votre compte Google ( Aperçu), sélectionnez Exécuter avec mes identifiants utilisateur.
- Pour utiliser un compte de service, sélectionnez Exécuter avec le compte de service sélectionné, puis sélectionnez un compte de service.
- Dans la section Options du notebook, dans le champ Modèle d'environnement d'exécution, sélectionnez un modèle d'environnement d'exécution de notebook Colab ou les spécifications d'exécution par défaut. Pour savoir comment créer un modèle d'exécution de notebook Colab, consultez Créer un modèle d'exécution.
- Dans le champ Bucket Cloud Storage, cliquez sur Parcourir, puis sélectionnez ou créez un bucket Cloud Storage. Le compte de service sélectionné doit disposer du rôle IAM Administrateur de l'espace de stockage (
roles/storage.admin) sur le bucket sélectionné. Pour en savoir plus, consultez Activer la programmation des notebooks. - Dans la section Fréquence de programmation, procédez comme suit :
- Dans le menu Répétitions, sélectionnez la fréquence d'exécution du notebook programmé.
- Dans le champ À, saisissez l'heure d'exécution du notebook programmé.
- Dans le menu Fuseau horaire, sélectionnez le fuseau horaire de la programmation.
- Cliquez sur Créer la programmation. Si vous avez sélectionné Exécuter avec mes identifiants utilisateur comme méthode d'authentification, vous devez autoriser votre compte Google ( Aperçu).
11. Effectuer un nettoyage
Pour éviter que les ressources utilisées dans cet atelier de programmation soient facturées sur votre compte Google Cloud :
- Dans la console Google Cloud, accédez à la page Gérer les ressources.
- Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
- Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.
12. Félicitations
Vous avez terminé de transformer et d'analyser les données à l'aide d'Apache Spark sans serveur dans les notebooks BigQuery Studio. Vous avez exploré les ensembles de données publics de Google Cloud, effectué un ETL sans serveur avec Apache Spark dans un notebook BigQuery Studio et orchestré un notebook BigQuery Studio. Bravo !
Étapes suivantes :
- Orchestrez le notebook avec un compte de service à des fins d'automatisation.
- Ajoutez un script pour surveiller la durée d'exécution du job ETL.
- Déployez un notebook.
- Utilisez DataprocSparkSession pour exploiter les véritables capacités Apache Spark distribuées avec Dataproc sans serveur dans les notebooks BigQuery Studio.
- Créez une procédure stockée pour Apache Spark dans BigQuery Studio. Vous pouvez ainsi appliquer les principes de la POO pour structurer votre code PySpark afin de mieux l'organiser, le réutiliser et le gérer.
Références :