クエリ最適化とパフォーマンス

公開データセットの Austin Bikeshare の乗車履歴を駅・利用者種別ごとに集計し、実行グラフでステージ構成を確認します。費用目安: サンドボックスで可・無料枠内（スキャン量はごく小さく、目安は数十 MB。必ず dry run で確認してください）。

1. BigQuery Studio のクエリエディタで、まず dry run（右上の見積もり表示、または bq query --dry_run）でスキャン量を確認します。
2. 問題なければ次のクエリを実行します。

SELECT
  start_station_name,
  subscriber_type,
  COUNT(*) AS trip_count,
  ROUND(AVG(duration_minutes), 1) AS avg_duration_minutes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY start_station_name, subscriber_type
ORDER BY trip_count DESC
LIMIT 20;

「実行の詳細」→「実行グラフ」タブを開くと、READ → AGGREGATE（GROUP BY のシャッフル）→ SORT（ORDER BY）→ LIMIT → WRITE の並びのステージが確認できます。各ステージの Wait/Read/Compute/Write 比率と shuffleOutputBytes を見比べ、最も色濃い（スロット時間を多く消費する）ステージを確認してください。

後片付け: このハンズオンは既存テーブルへの参照のみで、作成したリソースはありません。

SELECT * のコストを dry run で比較したあと、自前の小さな駅マスタを作ってブロードキャスト JOIN の実行グラフを観察します。費用目安: サンドボックスで可・無料枠内（数十 MB程度の見込み。必ず dry run で確認）。

1. 列を絞った場合と絞らない場合を dry run だけで比較します（実行はしません）。

-- dry run のみで比較する。実行しない。
SELECT * FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

SELECT start_station_name, duration_minutes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

差を確認したら、自分のプロジェクトへデータセットを作り、駅ごとの乗車回数を集計した小さなマスタテーブルを用意します。

CREATE SCHEMA IF NOT EXISTS `PROJECT_ID.handson_ch08`
OPTIONS (location = 'US');

CREATE OR REPLACE TABLE `PROJECT_ID.handson_ch08.station_lookup` AS
SELECT
  start_station_name AS station_name,
  COUNT(*) AS station_trip_count
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY start_station_name;

PROJECT_ID は自分のプロジェクト ID に置き換えてください。station_lookup は駅の数だけの小さなテーブルです。これを乗車履歴の大テーブルと結合します。

SELECT
  t.subscriber_type,
  s.station_name,
  s.station_trip_count,
  COUNT(*) AS trips_from_here
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS t
JOIN `PROJECT_ID.handson_ch08.station_lookup` AS s
  ON t.start_station_name = s.station_name
WHERE t.duration_minutes < 60
GROUP BY t.subscriber_type, s.station_name, s.station_trip_count
ORDER BY trips_from_here DESC
LIMIT 20;

JOIN ステージのステップ詳細を開くと、station_lookup 側が小さいため JOIN EACH WITH ALL（ブロードキャスト JOIN）が選ばれていることを確認できます。

後片付け: station_lookup は次のハンズオンでも使うため、章末でまとめて削除します。

同じ列への厳密な distinct 集計と近似集計のスロット消費を INFORMATION_SCHEMA.JOBS で確認します。費用目安: サンドボックスで可・無料枠内（数十 MB 程度）。

SELECT COUNT(DISTINCT duration_minutes) AS exact_distinct
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

SELECT APPROX_COUNT_DISTINCT(duration_minutes) AS approx_distinct
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

2 つのクエリを実行したら、直近のジョブ履歴を確認します。INFORMATION_SCHEMA.JOBS（別名 JOBS_BY_PROJECT）はリージョン修飾子が必須です。

SELECT
  creation_time,
  ROUND(total_slot_ms / 1000, 2) AS slot_seconds,
  total_bytes_processed,
  cache_hit,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE)
  AND query LIKE '%duration_minutes%'
ORDER BY creation_time DESC
LIMIT 10;

このテーブルは行数が少なく差は小さく出ますが、total_slot_ms と total_bytes_processed はテーブルが巨大になったときの差を見積もる際にそのまま使えます。creation_time で絞り込むとパーティションプルーニングが効きます。

後片付け: このハンズオンは既存テーブル・システムビューの参照のみで、作成したリソースはありません。

小さなベーステーブルとマテリアライズドビューを作り、集計クエリが自動的にビューへ書き換えられる様子を dry run のバイト数の変化で確認します。費用目安: サンドボックスで可・無料枠内。

CREATE OR REPLACE TABLE `PROJECT_ID.handson_ch08.trips_base` AS
SELECT start_station_name, subscriber_type, duration_minutes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

まずこの集計クエリを dry run し、処理見込みバイト数を控えておきます。

-- dry run で処理バイト数を控える
SELECT
  start_station_name,
  subscriber_type,
  COUNT(*) AS trip_count,
  AVG(duration_minutes) AS avg_duration_minutes
FROM `PROJECT_ID.handson_ch08.trips_base`
GROUP BY start_station_name, subscriber_type;

次にマテリアライズドビューを作成します。COUNT と AVG はどちらもマテリアライズドビューでサポートされる集計関数です。

CREATE MATERIALIZED VIEW `PROJECT_ID.handson_ch08.station_summary_mv`
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30
) AS
SELECT
  start_station_name,
  subscriber_type,
  COUNT(*) AS trip_count,
  AVG(duration_minutes) AS avg_duration_minutes
FROM `PROJECT_ID.handson_ch08.trips_base`
GROUP BY start_station_name, subscriber_type;

初回リフレッシュ完了まで数分待ち、同じ集計クエリをもう一度 dry run してください。スマートチューニングが働けば、処理見込みバイト数がベーステーブル直読みのときより大きく減っているはずです。

後片付け: このハンズオンで作成したデータセットとハンズオン 8-2 のテーブルをまとめて削除します。

bq rm -r -f -d PROJECT_ID:handson_ch08