การเปลี่ยนรูปแบบข้อมูลแบบไร้เซิร์ฟเวอร์ด้วย Apache Spark ในสมุดบันทึก BigQuery Studio

1. บทนำ

ในโค้ดแล็บนี้ คุณจะได้เรียนรู้วิธีใช้ประโยชน์จากประสิทธิภาพของ Apache Spark ในการเปลี่ยนรูปแบบข้อมูลภายในอินเทอร์เฟซ BigQuery Studio ที่คุ้นเคย คุณจะอ่านข้อมูลจาก BigQuery ทำความสะอาดและเปลี่ยนรูปแบบข้อมูลโดยใช้ PySpark และเขียนผลลัพธ์กลับไปยังตาราง BigQuery ใหม่ ทั้งหมดนี้ทำได้จาก Notebook เดียว

ใน Codelab นี้ คุณจะได้ใช้แนวทางแบบทีละขั้นตอนดังนี้

1. เตรียมโปรเจ็กต์ Google Cloud และเปิดใช้ API ที่จำเป็นทั้งหมดในโปรเจ็กต์
2. สร้างที่เก็บข้อมูล GCS สำหรับโฟลเดอร์ชั่วคราว
3. นำเข้าไลบรารีที่จำเป็นเพื่อเรียกใช้ Apache Spark
4. เริ่มต้นเซสชัน Spark ด้วยเครื่องมือเชื่อมต่อ BigQuery
5. อ่านข้อมูลตัวอย่าง Google Analytics จากชุดข้อมูลสาธารณะของ BigQuery
6. ทําการเปลี่ยนรูปแบบข้อมูลด้วยข้อมูลรวมตามเบราว์เซอร์ของอุปกรณ์ (เมตริกพื้นฐาน)
7. ทำการแปลงข้อมูลด้วยการวิเคราะห์แหล่งที่มาของการเข้าชมพร้อมการคำนวณรายได้
8. ทําการแปลงข้อมูลด้วยการวิเคราะห์ทางภูมิศาสตร์
9. เขียนข้อมูลหลังจากเปลี่ยนรูปแบบไปยังตาราง BigQuery

ภาพรวมสถาปัตยกรรม

ข้อกำหนดเบื้องต้น

• โปรเจ็กต์ Google Cloud Platform (GCP) ที่เปิดใช้การเรียกเก็บเงิน
• เปิดใช้ BigQuery API และ BigQuery Connection API ในโปรเจ็กต์ GCP
• มีความรู้พื้นฐานเกี่ยวกับ SQL และ Python

สิ่งที่คุณจะได้เรียนรู้

• วิธีดึงข้อมูลโดยใช้ Apache Spark ใน Notebook ของ BigQuery Studio
• วิธีเปลี่ยนรูปแบบหรือรวบรวมข้อมูลโดยใช้ Apache Spark ใน Notebook ของ BigQuery Studio
• วิธีเขียนข้อมูลหลังจากแปลงหรือรวมข้อมูลโดยใช้ Apache Spark ในสมุดบันทึก BigQuery Studio

สิ่งที่ต้องมี

• เว็บเบราว์เซอร์ Chrome
• บัญชี Gmail
• โปรเจ็กต์ Cloud ที่เปิดใช้การเรียกเก็บเงิน

2. การตั้งค่าพื้นฐานและข้อกำหนด

การตั้งค่าสภาพแวดล้อมแบบเรียนรู้ด้วยตนเอง

1. ลงชื่อเข้าใช้ Google Cloud Console แล้วสร้างโปรเจ็กต์ใหม่หรือใช้โปรเจ็กต์ที่มีอยู่ซ้ำ หากยังไม่มีบัญชี Gmail หรือ Google Workspace คุณต้องสร้างบัญชี

• ชื่อโปรเจ็กต์คือชื่อที่แสดงสำหรับผู้เข้าร่วมของโปรเจ็กต์นี้ ซึ่งเป็นสตริงอักขระที่ Google APIs ไม่ได้ใช้ โดยคุณจะอัปเดตได้ทุกเมื่อ
• รหัสโปรเจ็กต์จะไม่ซ้ำกันในโปรเจ็กต์ Google Cloud ทั้งหมดและเปลี่ยนแปลงไม่ได้ (เปลี่ยนไม่ได้หลังจากตั้งค่าแล้ว) Cloud Console จะสร้างสตริงที่ไม่ซ้ำกันโดยอัตโนมัติ ซึ่งโดยปกติแล้วคุณไม่จำเป็นต้องสนใจว่าสตริงนั้นคืออะไร ใน Codelab ส่วนใหญ่ คุณจะต้องอ้างอิงรหัสโปรเจ็กต์ (โดยปกติจะระบุเป็น PROJECT_ID) หากไม่ชอบรหัสที่สร้างขึ้น คุณอาจสร้างรหัสแบบสุ่มอีกรหัสหนึ่งได้ หรือคุณอาจลองใช้ชื่อของคุณเองและดูว่ามีชื่อนั้นหรือไม่ คุณจะเปลี่ยนแปลงรหัสนี้หลังจากขั้นตอนนี้ไม่ได้ และรหัสจะคงอยู่ตลอดระยะเวลาของโปรเจ็กต์
• โปรดทราบว่ามีค่าที่ 3 คือหมายเลขโปรเจ็กต์ ซึ่ง API บางรายการใช้ ดูข้อมูลเพิ่มเติมเกี่ยวกับค่าทั้ง 3 นี้ได้ในเอกสารประกอบ

ข้อควรระวัง: รหัสโปรเจ็กต์จะไม่ซ้ำกันทั่วโลก และบุคคลอื่นจะใช้รหัสโปรเจ็กต์ที่คุณเลือกไม่ได้ คุณเป็นผู้ใช้รหัสดังกล่าวเพียงคนเดียว แม้ว่าจะลบโปรเจ็กต์ไปแล้ว แต่ก็ไม่สามารถใช้รหัสนั้นอีก

หมายเหตุ: หากใช้บัญชี Gmail คุณสามารถปล่อยให้สถานที่ตั้งเริ่มต้นเป็น "ไม่มีองค์กร" ได้ หากใช้บัญชี Google Workspace ให้เลือกสถานที่ตั้งที่เหมาะสมกับองค์กร

2. จากนั้นคุณจะต้องเปิดใช้การเรียกเก็บเงินใน Cloud Console เพื่อใช้ทรัพยากร/API ของ Cloud การทำตาม Codelab นี้จะไม่เสียค่าใช้จ่ายมากนัก หรืออาจไม่เสียเลย หากต้องการปิดทรัพยากรเพื่อหลีกเลี่ยงการเรียกเก็บเงินนอกเหนือจากบทแนะนำนี้ คุณสามารถลบทรัพยากรที่สร้างขึ้นหรือลบโปรเจ็กต์ได้ ผู้ใช้ Google Cloud รายใหม่มีสิทธิ์เข้าร่วมโปรแกรมช่วงทดลองใช้ฟรีมูลค่า$300 USD

3. ก่อนเริ่มต้น

เปิดใช้ API

ก่อนใช้สมุดบันทึก BigQuery Studio เราต้องเปิดใช้ API ต่อไปนี้

• Compute Engine API
• Dataform API
• Vertex AI API

หากต้องการเปิดใช้ด้วยตนเอง ให้ไปที่ BigQuery ในแถบแท็บของแผงเอดิเตอร์ ให้คลิกลูกศรแบบเลื่อนลงข้างเครื่องหมาย + วางเมาส์เหนือ Notebook แล้วเลือกเทมเพลต BigQuery หรือ Notebook เปล่า หรือเทมเพลต Spark

ในหน้าต่าง "เปิดใช้ Core Feature API" ให้คลิก "เปิดใช้" ใน BigQuery Unified API

หลังจากเสร็จแล้ว ให้เปิดใช้ แล้วคลิกปิด โปรดดูรายละเอียดเพิ่มเติมที่เปิดใช้ BigQuery Studio สำหรับการจัดการชิ้นงาน

4. อ่านชุดข้อมูลสาธารณะ

ก่อนอื่นเราจะสร้างที่เก็บข้อมูล GCS เพื่อใช้ชั่วคราวเพื่อให้เรียกใช้ Spark ในสมุดบันทึก BigQuery Studio ได้

1. ไปที่ BigQuery ใน Google Cloud Console
2. ในแถบแท็บของแผงเอดิเตอร์ ให้คลิกเมนูแบบเลื่อนลงลูกศรข้างเครื่องหมาย + วางเมาส์เหนือ Notebook แล้วเลือก Empty Notebook
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 ใน Codelab นี้ เราจะใช้ไลบรารี SparkSession แม้ว่าเราจะใช้ DataprocSession เพื่อใช้ประโยชน์จากความสามารถของ Dataproc ในการเรียกใช้ Spark ใน Notebook ของ BigQuery Studio ได้ก็ตาม

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 ตามเบราว์เซอร์ของอุปกรณ์ โดยคำนวณเมตริกต่างๆ เช่น เซสชันทั้งหมด การเข้าชม Hit การดูหน้าเว็บ การตีกลับ เวลาที่ใช้ในเว็บไซต์ ผู้เข้าชมที่ไม่ซ้ำ และเวลาเฉลี่ยต่อการเข้าชม จากนั้นจะเปลี่ยนชื่อคอลัมน์และจัดเรียงผลลัพธ์

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

โค้ดนี้จะส่งออก Dataframe ที่รวบรวม 4 รายการ (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

ตารางการรวมข้อมูลทางภูมิศาสตร์

ตารางการรวบรวมข้อมูลรายชั่วโมง

traffic_source_aggregation

10. จัดระเบียบโค้ด Notebook ของ BigQuery Studio (ไม่บังคับ)

คุณสามารถจัดระเบียบโค้ด Notebook ของ BigQuery Studio ได้ด้วยวิธีต่อไปนี้

• ตั้งเวลารหัส Notebook จาก Google Cloud Console ( การกำหนดราคา Notebook มีผล)
• เรียกใช้โค้ด Notebook เป็นภาระงานแบบกลุ่มของ Dataproc Serverless ( การกำหนดราคา Dataproc Serverless มีผล)

ในโค้ดแล็บนี้ เราจะใช้โค้ดสมุดบันทึกกำหนดการจาก Google Cloud Console

1. คลิกกำหนดเวลาในแถบเครื่องมือของ Notebook
2. ในแผงกำหนดเวลา Notebook ให้ป้อนชื่อกำหนดการในช่องชื่อกำหนดการ
3. ในส่วนการตรวจสอบสิทธิ์ ให้ให้สิทธิ์สมุดบันทึกด้วยข้อมูลเข้าสู่ระบบผู้ใช้บัญชี Google หรือบัญชีบริการ
4. หากต้องการใช้ข้อมูลเข้าสู่ระบบของผู้ใช้บัญชี Google ( ตัวอย่าง) ให้เลือกดำเนินการด้วยข้อมูลเข้าสู่ระบบของผู้ใช้
5. หากต้องการใช้บัญชีบริการ ให้เลือกเรียกใช้ด้วยบัญชีบริการที่เลือก แล้วเลือกบัญชีบริการ
6. ในส่วนตัวเลือก Notebook ในช่องเทมเพลตรันไทม์ ให้เลือกเทมเพลตรันไทม์ของ Colab Notebook หรือข้อกำหนดรันไทม์เริ่มต้น ดูรายละเอียดเกี่ยวกับการสร้างเทมเพลตรันไทม์ของสมุดบันทึก Colab ได้ที่สร้างเทมเพลตรันไทม์
7. ในช่องที่เก็บข้อมูล Cloud Storage ให้คลิกเรียกดู แล้วเลือกหรือสร้างที่เก็บข้อมูล Cloud Storage ต้องมอบบทบาท IAM ผู้ดูแลระบบพื้นที่เก็บข้อมูล (roles/storage.admin) ให้กับบัญชีบริการที่เลือกในที่เก็บข้อมูลที่เลือก ดูข้อมูลเพิ่มเติมได้ที่เปิดใช้การตั้งเวลา Notebook
8. ในส่วนความถี่ของกำหนดการ ให้ทำดังนี้
9. ในเมนูทำซ้ำ ให้เลือกความถี่ของการเรียกใช้ Notebook ที่กำหนดเวลาไว้
10. ในช่องเวลา ให้ป้อนเวลาที่จะเรียกใช้ Notebook ที่กำหนดเวลาไว้
11. ในเมนูเขตเวลา ให้เลือกเขตเวลาสำหรับกำหนดการ
12. คลิกสร้างกำหนดการ หากเลือกดำเนินการด้วยข้อมูลเข้าสู่ระบบของผู้ใช้เป็นวิธีการตรวจสอบสิทธิ์ คุณต้องให้สิทธิ์บัญชี Google ( ตัวอย่าง)

11. ล้างข้อมูล

โปรดทำตามขั้นตอนต่อไปนี้เพื่อเลี่ยงไม่ให้เกิดการเรียกเก็บเงินกับบัญชี Google Cloud สำหรับทรัพยากรที่ใช้ในโค้ดแล็บนี้

1. ในคอนโซล Google Cloud ให้ไปที่หน้าจัดการทรัพยากร
2. ในรายการโปรเจ็กต์ ให้เลือกโปรเจ็กต์ที่ต้องการลบ แล้วคลิกลบ
3. ในกล่องโต้ตอบ ให้พิมพ์รหัสโปรเจ็กต์ แล้วคลิกปิดเพื่อลบโปรเจ็กต์

12. ขอแสดงความยินดี

คุณทําการแปลงและวิเคราะห์ข้อมูลเสร็จแล้วโดยใช้ประโยชน์จาก Apache Spark แบบไม่ต้องใช้เซิร์ฟเวอร์ในสมุดบันทึก BigQuery Studio ในระหว่างนี้ คุณได้สำรวจชุดข้อมูลสาธารณะของ Google Cloud, ดำเนินการ ETL แบบไร้เซิร์ฟเวอร์ด้วย Apache Spark ใน Notebook ของ BigQuery Studio และจัดระเบียบ Notebook ของ BigQuery Studio ยอดเยี่ยมมาก

ขั้นตอนถัดไป

• จัดระเบียบ Notebook โดยใช้บัญชีบริการเพื่อวัตถุประสงค์ด้านการทำงานอัตโนมัติ
• เพิ่มสคริปต์เพื่อตรวจสอบระยะเวลาเมื่อเรียกใช้ชิ้นงาน ETL
• ติดตั้งใช้งาน Notebook
• ใช้ DataprocSparkSession เพื่อใช้ประโยชน์จากความสามารถของ Apache Spark แบบกระจายจริงด้วย Dataproc แบบไร้เซิร์ฟเวอร์ในสมุดบันทึก BigQuery Studio
• สร้างกระบวนการที่จัดเก็บไว้สำหรับ Apache Spark ใน BigQuery Studio วิธีนี้จะใช้หลักการ OOP เพื่อจัดโครงสร้างโค้ด 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
• Gemini