1. 소개
이 Codelab에서는 BigQuery Studio의 친숙한 인터페이스 내에서 Apache Spark의 강력한 기능을 활용하여 데이터를 변환하는 방법을 알아봅니다. 단일 노트북에서 BigQuery의 데이터를 읽고, PySpark를 사용하여 데이터 정리 및 변환을 실행하고, 결과를 새 BigQuery 테이블에 다시 씁니다.
Codelab을 통해 다음과 같이 단계별 접근 방식을 사용합니다.
- Google Cloud 프로젝트를 준비하고 필요한 API를 모두 사용 설정합니다.
- 임시 폴더용 GCS 버킷 만들기
- Apache Spark 실행에 필요한 라이브러리 가져오기
- BigQuery 커넥터로 Spark 세션 초기화
- BigQuery 공개 데이터 세트에서 Google 애널리틱스 데이터 샘플 읽기
- 기기 브라우저별 집계 데이터로 데이터 변환 (기본 측정항목)
- 수익 계산을 포함한 트래픽 소스 분석으로 데이터 변환
- 지리 분석으로 데이터 변환
- 변환 후 데이터를 BigQuery 테이블에 쓰기
아키텍처 개요
기본 요건
- 결제가 사용 설정된 Google Cloud Platform (GCP) 프로젝트
- GCP 프로젝트에서 BigQuery API 및 BigQuery Connection API가 사용 설정되어 있어야 합니다.
- SQL 및 Python에 관한 기본 지식
학습할 내용
- BigQuery Studio Notebook에서 Apache Spark를 사용하여 데이터를 추출하는 방법
- BigQuery Studio 노트북에서 Apache Spark를 사용하여 데이터를 변환하거나 집계하는 방법
- BigQuery Studio Notebook에서 Apache Spark를 사용하여 데이터를 변환하거나 집계한 후 데이터를 쓰는 방법
필요한 항목
- Chrome 웹브라우저
- Gmail 계정
- 결제가 사용 설정된 Cloud 프로젝트
2. 기본 설정 및 요구사항
자습형 환경 설정
- Google Cloud Console에 로그인하여 새 프로젝트를 만들거나 기존 프로젝트를 재사용합니다. 아직 Gmail이나 Google Workspace 계정이 없는 경우 계정을 만들어야 합니다.
- 프로젝트 이름은 이 프로젝트 참가자의 표시 이름입니다. 이는 Google API에서 사용하지 않는 문자열이며 언제든지 업데이트할 수 있습니다.
- 프로젝트 ID는 모든 Google Cloud 프로젝트에서 고유하며, 변경할 수 없습니다 (설정된 후에는 변경할 수 없음). Cloud 콘솔은 고유한 문자열을 자동으로 생성합니다. 일반적으로는 신경 쓰지 않아도 됩니다. 대부분의 Codelab에서는 프로젝트 ID (일반적으로
PROJECT_ID로 식별됨)를 참조해야 합니다. 생성된 ID가 마음에 들지 않으면 다른 임의 ID를 생성할 수 있습니다. 또는 직접 시도해 보고 사용 가능한지 확인할 수도 있습니다. 이 단계 이후에는 변경할 수 없으며 프로젝트 기간 동안 유지됩니다. - 참고로 세 번째 값은 일부 API에서 사용하는 프로젝트 번호입니다. 이 세 가지 값에 대한 자세한 내용은 문서를 참고하세요.
- 다음으로 Cloud 리소스/API를 사용하려면 Cloud 콘솔에서 결제를 사용 설정해야 합니다. 이 Codelab 실행에는 많은 비용이 들지 않습니다. 이 튜토리얼이 끝난 후에 요금이 청구되지 않도록 리소스를 종료하려면 만든 리소스 또는 프로젝트를 삭제하면 됩니다. Google Cloud 신규 사용자는 300달러(USD) 상당의 무료 체험판 프로그램에 참여할 수 있습니다.
3. 시작하기 전에
API 사용 설정
BigQuery Studio 노트북을 사용하기 전에 다음 API를 사용 설정해야 합니다.
- Compute Engine API
- Dataform API
- Vertex AI API
수동으로 사용 설정하려면 BigQuery로 이동하세요. 편집기 창의 탭 표시줄에서 + 기호 옆에 있는 화살표 드롭다운을 클릭하고 노트북 위로 포인터를 가져간 다음 BigQuery 템플릿, 빈 노트북 또는 Spark 템플릿을 선택합니다.
'핵심 기능 API 사용 설정' 창에서 'BigQuery Unified API에서 사용 설정'을 클릭합니다.
완료되면 사용 설정한 다음 닫기를 클릭합니다. 자세한 내용은 애셋 관리에 BigQuery Studio 사용 설정을 참고하세요.
4. 공개 데이터 세트 읽기
먼저 BigQuery Studio 노트북에서 Spark를 실행할 수 있도록 임시로 사용할 GCS 버킷을 만듭니다.
- Google Cloud 콘솔에서 BigQuery로 이동합니다.
- 편집기 창의 탭 표시줄에서 + 기호 옆에 있는 화살표 드롭다운을 클릭하고 노트북 위로 포인터를 가져간 다음 빈 노트북을 선택합니다.
- 코드 셀을 클릭한 다음 아래 CLI 스크립트를 입력하여 GCS 버킷을 만든 다음 셀 실행 버튼을 클릭하거나 Shift + Enter를 누릅니다.
!gsutil mb -p <your_project_id> -c STANDARD -l US gs://ioxid2025-<your_project_id>
Google Cloud 프로젝트를 만들 때 선택한 내용에 따라 <your_project_id> 값을 업데이트합니다. GCS 고유 버킷 이름을 만들기 위해 <your_project_id> 값을 프로젝트 ID로 업데이트합니다. 그런 다음 셀 실행 버튼을 클릭하거나 Shift + Enter를 눌러 코드 셀을 실행합니다.
그런 다음 스파크 세션을 시작합니다. 이 Codelab에서는 DataprocSession을 사용하여 BigQuery Studio Notebook에서 Spark를 실행하는 Dataproc 기능을 활용할 수 있지만 SparkSession 라이브러리를 사용합니다.
- 코드 셀을 클릭한 다음 아래의 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
- 코드 셀을 클릭한 다음 아래 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 애널리틱스 샘플 데이터베이스
Google 애널리틱스 샘플 데이터베이스는 Google Cloud의 공개 데이터 세트 프로그램을 통해 BigQuery에서 제공됩니다. 이 데이터 세트는 BigQuery에서 Google 브랜드 상품을 판매하는 실제 전자상거래 매장인 Google Merchandise Store 의 난독화된 Google 애널리틱스 360 데이터를 12개월 (2016년 8월~2017년 8월) 동안 제공합니다. 이를 활용해 비즈니스 데이터를 분석하고 BigQuery를 사용하여 애널리틱스 360 데이터를 분석하여 얻을 수 있는 이점에 대해 알아볼 수 있습니다. 데이터에 대해 자세히 알아보기
이 데이터는 전자상거래 웹사이트에서 볼 수 있는 일반적인 데이터이며 다음과 같은 정보를 포함합니다.
- 트래픽 소스 데이터: 자연 트래픽, 유료 검색 트래픽, 디스플레이 트래픽에 관한 데이터를 포함하여 웹사이트 방문자가 출발한 지점에 관한 정보
- 콘텐츠 데이터: 사이트에서 일어나는 사용자의 행동 정보(예: 방문자가 보는 페이지 URL, 방문자가 콘텐츠와 상호작용하는 방식 등)입니다.
- 거래 데이터: Google Merchandise Store 웹사이트의 거래 정보입니다.
아래 코드를 실행하여 Apache Spark에서 샘플 상위 5개 데이터를 표시합니다.
# 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 애널리틱스 데이터를 집계하여 총 세션, 방문, 조회수, 페이지 조회수, 이탈수, 사이트 이용 시간, 순 방문자 수, 방문당 평균 시간과 같은 다양한 측정항목을 계산한 다음 열의 이름을 바꾸고 결과를 정렬합니다.
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 애널리틱스 데이터를 분석하여 세션수, 거래수, 총수익, 세션당 수익, 순 방문자 수와 같은 측정항목을 계산한 다음 열의 이름을 바꾸고 총수익별로 결과를 정렬합니다.
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 애널리틱스 데이터에 대한 지리적 분석을 실행하여 세션 수, 총 페이지 조회수, 총 사이트 시간, 세션당 평균 시간, 순 사용자를 계산한 다음 열의 이름을 바꾸고 세션 수별로 정렬합니다.
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 테이블에 쓰기
이 코드는 집계된 데이터프레임 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 table
geo_aggregation table
hourly_aggregation table
traffic_source_aggregation
10. BigQuery Studio 노트북 코드 오케스트레이션 (선택사항)
다음과 같은 방법으로 BigQuery Studio 노트북 코드를 오케스트레이션할 수 있습니다.
- Google Cloud 콘솔에서 노트북 코드 예약 ( 노트북 가격 적용)
- 노트북 코드를 Dataproc 서버리스 일괄 워크로드로 실행합니다 ( Dataproc 서버리스 가격 책정 적용).
이 Codelab에서는 Google Cloud 콘솔에서 노트북 코드 예약을 사용합니다.
- 노트북 툴바에서 일정을 클릭합니다.
- 노트북 예약 창의 일정 이름 필드에 일정의 이름을 입력합니다.
- 인증 섹션에서 Google 계정 사용자 인증 정보 또는 서비스 계정으로 노트북을 승인합니다.
- Google 계정 사용자 인증 정보 ( 미리보기)를 사용하려면 내 사용자 인증 정보로 실행을 선택합니다.
- 서비스 계정을 사용하려면 선택한 서비스 계정으로 실행을 선택한 후 서비스 계정을 선택합니다.
- 노트북 옵션 섹션의 런타임 템플릿 필드에서 Colab 노트북 런타임 템플릿 또는 기본 런타임 사양을 선택합니다. Colab 노트북 런타임 템플릿을 만드는 방법에 대한 자세한 내용은 런타임 템플릿 만들기를 참고하세요.
- Cloud Storage 버킷 필드에서 찾아보기를 클릭하고 Cloud Storage 버킷을 선택하거나 만듭니다. 선택한 서비스 계정에 선택한 버킷의 스토리지 관리자 (
roles/storage.admin) IAM 역할이 부여되어야 합니다. 자세한 내용은 노트북 예약 사용 설정을 참고하세요. - 일정 빈도 섹션에서 다음을 수행합니다.
- 반복 메뉴에서 예약된 노트북 실행 빈도를 선택합니다.
- 시간 필드에 예약된 노트북 실행 시간을 입력합니다.
- 시간대 메뉴에서 일정의 시간대를 선택합니다.
- 일정 만들기를 클릭합니다. 인증 방법으로 내 사용자 인증 정보로 실행을 선택한 경우 Google 계정을 승인해야 합니다 ( 미리보기).
11. 삭제
이 Codelab에서 사용한 리소스의 비용이 Google Cloud 계정에 청구되지 않도록 하려면 다음 단계를 따르세요.
- Google Cloud 콘솔에서 리소스 관리 페이지로 이동합니다.
- 프로젝트 목록에서 삭제할 프로젝트를 선택하고 삭제를 클릭합니다.
- 대화상자에서 프로젝트 ID를 입력하고 종료를 클릭하여 프로젝트를 삭제합니다.
12. 축하합니다
BigQuery Studio 노트북에서 Apache Spark 서버리스를 활용하여 데이터 변환 및 분석을 완료했습니다. 이 과정에서 Google Cloud의 공개 데이터 세트를 살펴보고, BigQuery Studio Notebook에서 Apache Spark를 사용하여 서버리스 ETL을 실행하고, BigQuery Studio Notebook을 오케스트레이션했습니다. 앞으로의 활동이 더욱 기대됩니다.
다음 단계 :
- 자동화 목적으로 서비스 계정을 사용하여 노트북을 오케스트레이션합니다.
- ETL 작업을 실행할 때 기간을 모니터링하는 스크립트 추가
- 노트북을 배포합니다.
- BigQuery Studio 노트북에서 서버리스 Dataproc을 사용하여 실제 분산 Apache Spark 기능을 활용하려면 DataprocSparkSession을 사용하세요.
- BigQuery Studio에서 Apache Spark의 저장 프로시저를 만듭니다. 이 방법을 사용하면 OOP 원칙을 적용하여 PySpark 코드를 구조화하여 조직화, 재사용성, 유지 관리성을 개선할 수 있습니다.
참고 :