総合演習 — ミニ分析基盤をゼロから組む

3 つのデータセットを作り、raw 層に 2 経路でデータを供給します。サンドボックスで完走可・無料枠内（スキャン量は数百 KB 程度）。

1. raw / staging / mart の 3 データセットを作成します（ロケーションは US）。

bq mk --location=US --dataset PROJECT_ID:capstone_raw
bq mk --location=US --dataset PROJECT_ID:capstone_staging
bq mk --location=US --dataset PROJECT_ID:capstone_mart

2. 公開 Cloud Storage バケットの CSV をそのままロードします（第 3 章のバッチロード。第 10 章 でも使った検証済みの公開ファイルです）。

bq load --location=US --autodetect --source_format=CSV \
  capstone_raw.dim_states \
  gs://cloud-samples-data/bigquery/us-states/us-states.csv

-- 処理: 数十 KB 程度
SELECT * FROM `PROJECT_ID.capstone_raw.dim_states` LIMIT 5;

期待される出力: name / post_abbr の 2 列で州名の行が返ります。

3. GENERATE_TIMESTAMP_ARRAY で 1 週間・1 時間刻みの疑似イベントログを生成します。実行前に dry run で処理バイト量を確認する習慣を忘れずに（このクエリは定数からの生成なのでスキャン量はごくわずかです）。

-- 処理: 数百 KB 未満（168 行の生成データ、無料枠内）
CREATE OR REPLACE TABLE `PROJECT_ID.capstone_raw.events_raw` AS
WITH seed AS (
  SELECT ts, seq
  FROM UNNEST(
    GENERATE_TIMESTAMP_ARRAY('2026-07-01 00:00:00', '2026-07-07 23:00:00', INTERVAL 1 HOUR)
  ) AS ts WITH OFFSET seq
)
SELECT
  GENERATE_UUID() AS event_id,
  ts AS event_ts,
  ['CA', 'TX', 'NY', 'WA', 'FL'][OFFSET(MOD(seq, 5))] AS state_abbr,
  ['signup', 'view_item', 'add_to_cart', 'purchase'][OFFSET(MOD(seq, 4))] AS event_name,
  STRUCT(
    ['web', 'ios', 'android'][OFFSET(MOD(seq, 3))] AS platform,
    ARRAY(
      SELECT tag FROM UNNEST(['sale', 'new_user', 'repeat']) AS tag WITH OFFSET o
      WHERE MOD(seq + o, 2) = 0
    ) AS tags
  ) AS client,
  ROUND(RAND() * 200, 2) AS amount_usd
FROM seed;

期待される出力: 168 行（7 日 × 24 時間）。client 列が platform（STRING）と tags（ARRAY<STRING>）を持つ STRUCT になっていることを、スキーマタブで確認してください。この入れ子構造が次のハンズオンで展開する対象です。

後片付け: 3 つのデータセットは次のハンズオンでも使うため、ここでは削除しません。

ネストを展開して staging 層を作り、パーティション・クラスタ化した mart テーブルへ集計します。テーブル作成自体はサンドボックスで完走可・無料枠内。自動化（手順 3）だけは課金プロジェクト必須（概算 1 円未満）。

1. staging 層でネストを展開し、州名を結合します（第 4 章・第 5 章）。client.platform は STRUCT フィールドへのドット表記で取り出し、tags 配列はそのまま列として残します（後で IN UNNEST で判定するため、ここで展開して行を増やしません）。

-- 処理: 数百 KB 未満
CREATE OR REPLACE TABLE `PROJECT_ID.capstone_staging.events_flat` AS
SELECT
  e.event_id,
  DATE(e.event_ts) AS event_date,
  e.event_ts,
  e.state_abbr,
  s.name AS state_name,
  e.event_name,
  e.client.platform AS platform,
  e.client.tags AS tags,
  e.amount_usd
FROM `PROJECT_ID.capstone_raw.events_raw` AS e
LEFT JOIN `PROJECT_ID.capstone_raw.dim_states` AS s
  ON e.state_abbr = s.post_abbr;

期待される出力: 168 行（raw と 1 対 1）。platform がフラットな STRING 列になり、state_name が結合できていることを確認してください。

2. mart 層をパーティション + クラスタ化して作ります（第 6 章）。配列の中身は IN UNNEST で判定し、行を増やさずに集計します。

-- 処理: 数百 KB 未満
CREATE OR REPLACE TABLE `PROJECT_ID.capstone_mart.daily_state_events`
PARTITION BY event_date
CLUSTER BY state_abbr, event_name
AS
SELECT
  event_date,
  state_abbr,
  state_name,
  event_name,
  COUNT(*) AS event_count,
  COUNTIF('repeat' IN UNNEST(tags)) AS repeat_tagged_count,
  ROUND(SUM(amount_usd), 2) AS total_amount_usd
FROM `PROJECT_ID.capstone_staging.events_flat`
GROUP BY event_date, state_abbr, state_name, event_name;

3. この再集計を自動化します。テーブル数は 3 つ、依存も raw → staging → mart の一直線なので、図 18-3 の判断基準に従いスケジュールクエリで十分です（第 11 章）。

bq query \
  --use_legacy_sql=false \
  --display_name="capstone_daily_mart_refresh" \
  --schedule="every 24 hours" \
  --destination_table=PROJECT_ID:capstone_mart.daily_state_events \
  --replace=true \
'SELECT
   event_date, state_abbr, state_name, event_name,
   COUNT(*) AS event_count,
   COUNTIF("repeat" IN UNNEST(tags)) AS repeat_tagged_count,
   ROUND(SUM(amount_usd), 2) AS total_amount_usd
 FROM `PROJECT_ID.capstone_staging.events_flat`
 GROUP BY event_date, state_abbr, state_name, event_name'

サンドボックスで試す場合は、手順 3 のスケジュール登録の代わりに、手順 2 の CREATE OR REPLACE TABLE をそのままクエリエディタで手動実行する運用に置き換えてください（スケジュールクエリは BigQuery Data Transfer Service の機能であり、サンドボックスでは利用できません）。

後片付け: 3 つのデータセットは次のハンズオンでも使うため削除しません。スケジュールクエリを登録して確認が済んだら、BigQuery Studio の Scheduled queries 画面から削除してください。

mart をさらに高速化するマテリアライズドビューを作り、クエリ結果を Data Studio に接続します。いずれもサンドボックスで完走可・無料枠内。

1. プラットフォーム別・日別の集計をマテリアライズドビューにします。

-- 処理: 初回のみ数百 KB 未満。以降はベーステーブルの差分のみ
CREATE MATERIALIZED VIEW `PROJECT_ID.capstone_mart.mv_platform_daily`
AS
SELECT
  event_date,
  platform,
  COUNT(*) AS event_count,
  ROUND(SUM(amount_usd), 2) AS total_amount_usd
FROM `PROJECT_ID.capstone_staging.events_flat`
GROUP BY event_date, platform;

SELECT platform, COUNT(*) AS event_count
FROM `PROJECT_ID.capstone_staging.events_flat`
GROUP BY platform;

SELECT event_date, state_abbr, state_name, event_count, total_amount_usd
FROM `PROJECT_ID.capstone_mart.daily_state_events`
ORDER BY event_date;

後片付け: 3 つのデータセットは次のハンズオンでも使うため削除しません。作成した Data Studio のレポートは、他者と共有していなければ残しておいても課金は発生しません（BigQuery 側のクエリ実行分のみが課金対象です）。

INFORMATION_SCHEMA でコストを確認し、承認済みビューで権限を設計してから、作った 3 データセットをすべて削除します。サンドボックスで完走可・無料枠内（メタデータ照会とビュー作成のみ）。

1. 直近 1 日のジョブをコスト順に確認します。

-- 処理: メタデータのみ（課金対象バイト数はごくわずか）
SELECT
  user_email,
  job_type,
  statement_type,
  ROUND(total_bytes_processed / POW(1024, 3), 4) AS gb_processed,
  ROUND(total_slot_ms / 1000, 1) AS slot_seconds,
  query
FROM `PROJECT_ID`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND state = 'DONE'
  AND REGEXP_CONTAINS(query, r'capstone_')
ORDER BY total_bytes_processed DESC
LIMIT 20;

期待される出力: このハンズオンで実行してきたクエリが一覧され、どのクエリが最もバイト数・スロットを消費したかがひと目で分かります。Data Studio 経由のジョブには looker_studio_report_id / looker_studio_datasource_id という labels が付くため、BI 由来のコストだけを切り分けたいときはこの labels で絞り込めます。

CREATE VIEW `PROJECT_ID.capstone_mart.events_flat_view` AS
SELECT event_date, state_abbr, state_name, event_name, platform, amount_usd
FROM `PROJECT_ID.capstone_staging.events_flat`;

後片付け（この章の最終後片付け）:

bq rm -r -f -d PROJECT_ID:capstone_mart
bq rm -r -f -d PROJECT_ID:capstone_staging
bq rm -r -f -d PROJECT_ID:capstone_raw

スケジュールクエリを登録したままの場合は、BigQuery Studio の Scheduled queries 画面からも削除してください。マテリアライズドビューと承認済みビューは、データセットの削除と同時に削除されます。