2024年版【GCP資格の勉強 PDE編】BigQueryを用いたETLとは？

BigQueryとETL
BigQueryとETL：データのロード
BigQueryとETL：データの変換と集計
BigQueryとETL：算出結果の確認
【練習問題】BigQueryを用いたETL
まとめ

2024年版【GCP資格の勉強 PDE編】BigQueryを用いたETLとは？について解説します。
（★注意：GCP資格 Professional Data Engineer(PDE)試験の公式テキスト・問題集などを元に独自にまとめたものです）

BigQueryを使用したETLプロセスの例として、Firebaseからのサンプルデータ（ユーザー行動ログ）の取り込みと、そのデータをBigQueryで処理する流れを説明します。課金ユーザーと非課金ユーザーを区別して、集計するプロセスが含まれています。

BigQueryとETL

BigQueryは、Google Cloud Platform（GCP）のサービスで、大量のデータを保存し、分析するためのツールです。データを保存してから、それに対して複雑な質問（クエリ）をすることで、さまざまな情報を得ることができます。

ETLは「Extract（抽出）、Transform（変換）、Load（ロード）」の略で、データを取り扱う一連のプロセスを指します。まず「Extract」でデータをさまざまな場所から集めます。次に「Transform」で、そのデータを必要な形式や構造に変換します。最後に「Load」で、変換したデータをBigQueryのようなデータウェアハウスに保存します。

BigQueryを使うと、ETLプロセスを効率的に実行できます。例えば、ウェブサイトの訪問者データや販売データなどをBigQueryに集め、分析してビジネスの意思決定に役立てることができます。

BigQueryとETL：データのロード

作業用テーブルの作成: BigQuery内に作業用テーブル（例：`gcpbook.work_events`）を作成します。
データロード: Cloud Storage上にあるユーザー行動ログのデータを作業用テーブルにロードします。ここで、`–autodetect`オプションを使用してデータ形式を自動的に検出し、`–source_format=NEWLINE_DELIMITED_JSON`オプションでJSON形式のデータを指定します。
コマンド例：`bq –location=[Region] load –autodetect –source_format=NEWLINE_DELIMITED_JSON gcpbook_ch.work_events gs://$(gcloud config get-value project)-gcpbook/data/events/20181001/*.json.gz`

BigQueryとETL：データの変換と集計

テーブルの結合: 作業用テーブル（`gcpbook.work_events`）とユーザー情報テーブル（`gcpbook.users`）を結合します。
集計の実行: 課金ユーザー（`paid_users`）と無課金ユーザー（`unpaid_users`）を算出し、その結果を別のテーブル（例：`gcpbook.dau`）に挿入します。ここで、`countif`関数を使用して条件に合致するユーザーの数をカウントします。
コマンド例：`bq –location=[Region] query –nouse_legacy_sql –parameter=‘dt:data:2028-10-01’ ‘insert gcpbook.data select @dt as dt, countif(u.is_paid_user) as paid_users, countif(not u.is_paid_user) as unpaid_users from …`

BigQueryとETL：算出結果の確認

データの確認: 最終的に、`gcpbook.dau`テーブルにデータが正しく挿入されたかを確認します。
コマンド例：`bq –location=[Region] query –nouse_legacy_sql –parameter=‘dt:data:2028-10-01’ ‘select dt, paid_users, unpaid_users from gcpbook.dau where dt = @dt’`

【練習問題】BigQueryを用いたETL

練習問題1

問題: Firebaseから取得したユーザーデータがJSON形式でCloud Storageに保存されています。このデータをBigQueryの新しいテーブルにどのようにロードしますか？

A. BigQuery Web UIを使用して手動でデータをロードする
B. bq load コマンドを使用し、--autodetect オプションと --source_format=NEWLINE_DELIMITED_JSON オプションを指定してロードする
C. Dataflowを使用してデータをBigQueryにストリーミングする

解答: B. bq load コマンドを使用し、--autodetect オプションと --source_format=NEWLINE_DELIMITED_JSON オプションを指定してロードする

解説: bq load コマンドはBigQueryにデータをロードするためのコマンドラインツールです。--autodetect オプションはデータのスキーマを自動検出し、--source_format=NEWLINE_DELIMITED_JSON はJSON形式のデータを指定します。これはCloud StorageからBigQueryへの効率的なデータロードに適しています。

練習問題2

問題: 課金ユーザーと非課金ユーザーの日次アクティブ数（DAU）を計算するには、どのようなBigQuery SQLクエリを使用するべきですか？

A. SELECT COUNT(*) FROM user_table WHERE is_paid_user = true
B. SELECT date, COUNTIF(is_paid_user) AS paid_users, COUNTIF(NOT is_paid_user) AS unpaid_users FROM user_table GROUP BY date
C. SELECT COUNT(DISTINCT user_id) FROM user_table GROUP BY is_paid_user

解答: B. SELECT date, COUNTIF(is_paid_user) AS paid_users, COUNTIF(NOT is_paid_user) AS unpaid_users FROM user_table GROUP BY date

解説: COUNTIF 関数は条件に基づいて行をカウントするために使用されます。このクエリでは、各日付ごとに課金ユーザーと非課金ユーザーの数を計算しています。これにより、日次アクティブ数（DAU）の集計が可能です。