Transformasi Data Tanpa Server dengan Apache Spark di Notebook BigQuery Studio

1. Pengantar

Dalam codelab ini, Anda akan mempelajari cara memanfaatkan kehebatan Apache Spark untuk transformasi data dalam antarmuka BigQuery Studio yang sudah dikenal. Anda akan membaca data dari BigQuery, melakukan pembersihan dan transformasi data menggunakan PySpark, serta menulis hasilnya kembali ke tabel BigQuery baru, semuanya dari satu notebook.

Selama mengikuti codelab, Anda akan menggunakan pendekatan langkah demi langkah sebagai berikut:

1. Siapkan project Google Cloud Anda dan Aktifkan semua API yang diperlukan di project tersebut
2. Buat bucket GCS untuk folder sementara
3. Mengimpor library yang diperlukan untuk menjalankan Apache Spark
4. Menginisialisasi sesi Spark dengan konektor BigQuery
5. Membaca contoh data Google Analytics dari Set Data Publik BigQuery
6. Melakukan transformasi data dengan data gabungan menurut browser perangkat (metrik dasar)
7. Melakukan transformasi data dengan Analisis sumber traffic dengan penghitungan pendapatan
8. Melakukan transformasi data dengan Analisis geografis
9. Menulis data setelah transformasi ke tabel BigQuery

Ringkasan Arsitektur

Prasyarat

• Project Google Cloud Platform (GCP) dengan penagihan diaktifkan.
• BigQuery API dan BigQuery Connection API diaktifkan di project GCP Anda.
• Pengetahuan dasar tentang SQL dan Python.

Yang akan Anda pelajari

• Cara mengekstrak data menggunakan Apache Spark di Notebook BigQuery Studio
• Cara mentransformasi atau menggabungkan data menggunakan Apache Spark di Notebook BigQuery Studio
• Cara menulis data setelah mentransformasi atau menggabungkan data menggunakan Apache Spark di Notebook BigQuery Studio

Yang Anda butuhkan

• Browser web Chrome
• Akun Gmail
• Project Cloud dengan penagihan diaktifkan

2. Penyiapan Dasar dan Persyaratan

Penyiapan lingkungan mandiri

1. Login ke Google Cloud Console dan buat project baru atau gunakan kembali project yang sudah ada. Jika belum memiliki akun Gmail atau Google Workspace, Anda harus membuatnya.

• Nama project adalah nama tampilan untuk peserta project ini. String ini adalah string karakter yang tidak digunakan oleh Google API. Anda dapat memperbaruinya kapan saja.
• Project ID bersifat unik di semua project Google Cloud dan tidak dapat diubah (tidak dapat diubah setelah ditetapkan). Cloud Console otomatis membuat string unik; biasanya Anda tidak mementingkan kata-katanya. Di sebagian besar codelab, Anda harus merujuk Project ID-nya (umumnya diidentifikasi sebagai PROJECT_ID). Jika tidak suka dengan ID yang dibuat, Anda dapat membuat ID acak lainnya. Atau, Anda dapat mencobanya sendiri, dan lihat apakah ID tersebut tersedia. ID tidak dapat diubah setelah langkah ini dan tersedia selama durasi project.
• Sebagai informasi, ada nilai ketiga, Project Number, yang digunakan oleh beberapa API. Pelajari lebih lanjut ketiga nilai ini di dokumentasi.

Perhatian: Project ID bersifat unik secara global dan tidak dapat digunakan orang lain setelah Anda memilihnya. Anda adalah satu-satunya pengguna ID tersebut. Meskipun project sudah dihapus, ID tersebut tidak dapat digunakan lagi

Catatan: Jika menggunakan akun Gmail, Anda dapat menyetel lokasi default ke Tidak ada organisasi. Jika Anda menggunakan akun Google Workspace, pilih lokasi yang sesuai untuk organisasi Anda.

2. Selanjutnya, Anda harus mengaktifkan penagihan di Konsol Cloud untuk menggunakan resource/API Cloud. Menjalankan operasi dalam codelab ini tidak akan memakan banyak biaya, bahkan mungkin tidak sama sekali. Guna mematikan resource agar tidak menimbulkan penagihan di luar tutorial ini, Anda dapat menghapus resource yang dibuat atau menghapus project-nya. Pengguna baru Google Cloud memenuhi syarat untuk mengikuti program Uji Coba Gratis senilai $300 USD.

3. Sebelum memulai

Aktifkan API

Sebelum menggunakan Notebook BigQuery Studio, kita harus mengaktifkan API berikut:

• Compute Engine API
• Dataform API
• Vertex AI API

Untuk mengaktifkan secara manual, buka BigQuery. Di panel tab panel editor, klik drop-down panah di samping tanda +, arahkan kursor ke Notebook, lalu pilih Template BigQuery atau Notebook Kosong atau Template Spark

Di jendela Enable Core feature API, klik Enable di BigQuery Unified API

Setelah selesai, aktifkan lalu klik Tutup. Lihat Mengaktifkan BigQuery Studio untuk pengelolaan aset untuk mengetahui detail selengkapnya

4. Membaca set data publik

Pertama, kita akan membuat bucket GCS untuk penggunaan sementara agar dapat menjalankan Spark di Notebook BigQuery Studio.

1. Di Konsol Google Cloud, buka BigQuery
2. Di panel tab panel editor, klik drop-down panah di samping tanda +, arahkan kursor ke Notebook, lalu pilih Empty Notebook.
3. Klik sel kode, lalu ketik skrip CLI di bawah untuk membuat bucket GCS, lalu Klik tombol Run cell atau tekan Shift + Enter

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

Perbarui nilai untuk <your_project_id> sesuai dengan yang telah Anda pilih saat membuat Project Google Cloud. Perbarui nilai untuk <your_project_id> dengan project ID Anda untuk membuat nama bucket GCS yang unik. Setelah itu, klik tombol Jalankan sel atau tekan Shift + Enter untuk menjalankan sel kode

Kemudian, kita akan memulai sesi spark. Dalam codelab ini, kita akan menggunakan library SparkSession meskipun kita dapat menggunakan DataprocSession untuk memanfaatkan kemampuan dataproc dalam menjalankan spark di Notebook BigQuery Studio

1. Klik sel kode, lalu ketik skrip cli di bawah untuk memulai sesi spark. Klik tombol Jalankan sel atau tekan 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

Output yang Diharapkan :

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

2. Klik sel kode, lalu ketik skrip CLI di bawah untuk menyetel project GCP dan bucket sementara 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)

Database Contoh Google Analytics

Contoh Database Google Analytics disediakan di BigQuery melalui program set data publik Google Cloud. Set data ini memberikan data Google Analytics 360 dengan obfuscation dari Google Merchandise Store selama 12 bulan (Agustus 2016 hingga Agustus 2017), yaitu toko e-commerce sungguhan yang menjual merchandise bermerek Google, di BigQuery. Cara ini bagus untuk menganalisis data bisnis dan mempelajari manfaat penggunaan BigQuery untuk menganalisis data Analytics 360. Pelajari lebih lanjut data

Data tersebut sama seperti yang biasa Anda lihat untuk situs e-commerce dan mencakup informasi berikut:

• Data sumber traffic: informasi mengenai asal pengunjung situs web, termasuk data tentang traffic organik, traffic penelusuran berbayar, dan traffic Display
• Data konten: informasi tentang perilaku pengguna di situs, seperti URL halaman yang dilihat pengunjung, cara mereka berinteraksi dengan konten, dll.
• Data transaksi: informasi tentang transaksi di situs Google Merchandise Store.

Jalankan kode di bawah untuk menampilkan 5 data teratas contoh di 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)

Output yang diharapkan :

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. Menggabungkan data menurut browser perangkat (metrik dasar)

Kode ini menggabungkan data Google Analytics menurut browser perangkat, menghitung berbagai metrik seperti total sesi, kunjungan, hit, tayangan halaman, pantulan, waktu di situs, pengunjung unik, dan waktu rata-rata per kunjungan, lalu mengganti nama kolom dan mengurutkan hasilnya.

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)

Output yang diharapkan :

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. Menganalisis sumber traffic dengan penghitungan pendapatan

Kode ini menganalisis data Google Analytics menurut sumber dan media traffic, menghitung metrik seperti jumlah sesi, transaksi, total pendapatan, pendapatan per sesi, dan pengunjung unik, lalu mengganti nama kolom dan mengurutkan hasil menurut total pendapatan.

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)

Output yang diharapkan :

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. Analisis geografis

Kode ini melakukan analisis geografis pada data Google Analytics menurut negara dan wilayah, menghitung jumlah sesi, total tayangan halaman, total waktu di situs, waktu rata-rata per sesi, dan pengunjung unik, lalu mengganti nama kolom dan mengurutkan menurut jumlah sesi.

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)

Output yang diharapkan :

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. Analisis berbasis waktu

Kode ini melakukan analisis berbasis waktu dengan mengekstrak jam dari kolom visitStartTime, lalu mengelompokkan data menurut jam untuk menghitung jumlah sesi, transaksi, total pendapatan, dan total tayangan halaman untuk setiap jam, dan terakhir mengurutkan hasilnya menurut jam.

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)

Output yang Diharapkan :

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. Menulis hasil ke tabel BigQuery

Kode ini mengekspor empat dataframe gabungan (device_agg, traffic_source_agg, geo_agg, dan hourly_agg) ke tabel terpisah di Google BigQuery, mengganti tabel yang ada jika ada, menggunakan metode penulisan langsung.

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

Periksa tabel output di BigQuery untuk memastikan data berhasil disimpan setelah melakukan beberapa transformasi

Tabel device_aggregation

Tabel geo_aggregation

tabel hourly_aggregation

traffic_source_aggregation

10. Mengatur kode notebook BigQuery Studio (Opsional)

Anda dapat mengatur kode notebook BigQuery Studio dengan cara berikut:

• Jadwalkan kode notebook dari konsol Google Cloud ( berlaku harga notebook).
• Jalankan kode notebook sebagai workload batch Dataproc Serverless ( berlaku harga Dataproc Serverless).

Dalam codelab ini, kita akan menggunakan kode notebook Jadwalkan dari Konsol Google Cloud.

1. Di toolbar Notebook, klik Jadwalkan.
2. Di panel Schedule Notebook, di kolom Schedule name, masukkan nama untuk jadwal.
3. Di bagian Authentication, beri otorisasi notebook dengan kredensial pengguna Akun Google Anda atau akun layanan.
4. Untuk menggunakan kredensial pengguna Akun Google Anda ( Pratinjau), pilih Jalankan dengan kredensial pengguna saya.
5. Untuk menggunakan akun layanan, pilih Jalankan dengan akun layanan yang dipilih, lalu pilih akun layanan.
6. Di bagian Notebook options, di kolom Runtime template, pilih template runtime notebook Colab atau spesifikasi runtime default. Untuk mengetahui detail tentang cara membuat template runtime notebook Colab, lihat Membuat template runtime.
7. Di kolom Cloud Storage bucket, klik Browse, lalu pilih atau buat bucket Cloud Storage. Akun layanan yang dipilih harus diberi peran IAM Storage Admin (roles/storage.admin) di bucket yang dipilih. Untuk mengetahui informasi selengkapnya, lihat Mengaktifkan penjadwalan notebook.
8. Di bagian Frekuensi jadwal, lakukan tindakan berikut:
9. Di menu Ulangi, pilih frekuensi eksekusi notebook terjadwal.
10. Di kolom At time, masukkan waktu untuk menjalankan notebook terjadwal.
11. Di menu Zona waktu, pilih zona waktu untuk jadwal.
12. Klik Buat jadwal. Jika Anda memilih Jalankan dengan kredensial pengguna saya untuk metode autentikasi, Anda harus memberi otorisasi ke Akun Google Anda ( Pratinjau).

11. Pembersihan

Agar tidak menimbulkan biaya pada akun Google Cloud Anda untuk resource yang digunakan dalam codelab ini, ikuti langkah-langkah berikut:

1. Di konsol Google Cloud, buka halaman Manage resources.
2. Dalam daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

12. Selamat

Anda telah menyelesaikan transformasi dan analisis data dengan memanfaatkan serverless Apache Spark di Notebook BigQuery Studio. Selama ini, Anda telah menjelajahi Set Data Publik Google Cloud, melakukan ETL tanpa server dengan Apache Spark di Notebook BigQuery Studio, dan Mengatur Notebook BigQuery Studio. Hebat!

Langkah berikutnya :

• Mengatur notebook dengan menggunakan akun layanan untuk tujuan otomatisasi.
• Tambahkan skrip untuk memantau durasi saat menjalankan tugas ETL.
• Men-deploy notebook.
• Gunakan DataprocSparkSession untuk memanfaatkan kemampuan Apache Spark terdistribusi yang sebenarnya dengan Dataproc Serverless di Notebook BigQuery Studio.
• Buat prosedur tersimpan untuk Apache Spark di BigQuery Studio. Dengan cara ini, Anda dapat menerapkan prinsip OOP untuk menyusun kode PySpark agar lebih teratur, dapat digunakan kembali, dan mudah dipelihara.

Referensi :

• https://cloud.google.com/bigquery/docs/use-spark
• https://cloud.google.com/bigquery/docs/orchestrate-notebooks#explorer-pane
• https://cloud.google.com/bigquery/docs/spark-procedures
• https://cloud.google.com/bigquery/docs/service-dependencies#manually-enable-code-assets
• Gemini