1. はじめに
この Codelab では、BigQuery Studio の使い慣れたインターフェース内で Apache Spark の機能を活用してデータ変換を行う方法を学びます。このチュートリアルでは、1 つのノートブックから 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 Notebook で 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 を生成できます。または、ご自身で試して、利用可能かどうかを確認することもできます。このステップ以降は変更できず、プロジェクトを通して同じ ID になります。 - なお、3 つ目の値として、一部の API が使用するプロジェクト番号があります。これら 3 つの値について詳しくは、こちらのドキュメントをご覧ください。
- 次に、Cloud のリソースや API を使用するために、Cloud コンソールで課金を有効にする必要があります。この Codelab の操作をすべて行って、費用が生じたとしても、少額です。このチュートリアルの終了後に請求が発生しないようにリソースをシャットダウンするには、作成したリソースを削除するか、プロジェクトを削除します。Google Cloud の新規ユーザーは、300 米ドル分の無料トライアル プログラムをご利用いただけます。
3. はじめに
API を有効にする
BigQuery Studio ノートブックを使用する前に、次の API を有効にする必要があります。
- Compute Engine API
- Dataform API
- Vertex AI API
手動で有効にするには、BigQuery に移動します。エディタペインのタブバーで、+ 記号の横にある矢印プルダウンをクリックし、[ノートブック] にポインタを合わせて、[BigQuery テンプレート]、[空のノートブック]、[Spark テンプレート] のいずれかを選択します。
[Enable Core feature API] ウィンドウで、[Enable in BigQuery Unified API] をクリックします。
完了したら、有効にして [Close] をクリックします。詳細については、アセット管理に関する 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> の値を更新します。<your_project_id> の値をプロジェクト ID に更新して、GCS の一意のバケット名を作成します。その後、[セルを実行] ボタンをクリックするか、Shift + Enter キーを押してコードセルを実行します。
次に、Spark セッションを開始します。この Codelab では SparkSession ライブラリを使用しますが、DataprocSession を使用して dataproc 機能を活用し、BigQuery Studio ノートブックで Spark を実行することもできます。
- コードセルをクリックし、次の 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 で提供されます。このデータセットは、Google ブランドの商品を販売する実在の e コマースストア、Google Merchandise Store の 12 か月間（2016 年 8 月から 2017 年 8 月）の難読化された Google アナリティクス 360 データを BigQuery で提供します。このサンプルを利用して、ビジネスデータを分析し、BigQuery を使用してアナリティクス 360 のデータを分析することのメリットを学ぶことができます。データの詳細
このデータは、e コマースサイトで表示される一般的なデータであり、次のような情報が含まれます。
- トラフィック ソース: ウェブサイトを訪問したユーザーの流入元に関する情報。オーガニック トラフィック、有料検索トラフィック、ディスプレイ トラフィックに関するデータが含まれます。
- コンテンツ データ: サイト内でのユーザーの行動に関する情報（ユーザーが閲覧したページの 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 テーブル
geo_aggregation テーブル
hourly_aggregation テーブル
traffic_source_aggregation
10. BigQuery Studio ノートブック コードをオーケストレートする（省略可）
BigQuery Studio ノートブック コードをオーケストレートするには、次の方法があります。
- Google Cloud コンソールからノートブック コードをスケジュールします（ノートブックの料金が適用されます）。
- ノートブック コードを Dataproc Serverless バッチ ワークロードとして実行します（Dataproc Serverless の料金が適用されます）。
この 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 Notebooks で 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 コードを構造化し、整理、再利用、保守を容易にすることができます。
参照 :