ネスト・配列・JSON — BigQuery 流のデータモデリング

所要時間の目安: 5 分。サンドボックスで可・無料枠内（作成するテーブルは数百バイト程度）。

1. データセット handson_ch05（ロケーション US）を作成します。コンソールの場合はエクスプローラの「データセットを作成」から、bq の場合は次のコマンドです。

bq mk --dataset --location=US PROJECT_ID:handson_ch05

PROJECT_ID は自分のプロジェクト ID に置き換えます。

2. customer を STRUCT、items を ARRAY<STRUCT> にしたテーブルを CTAS（CREATE TABLE AS SELECT）で作成します。2 件目はあえて明細が 0 件の注文にします。

CREATE TABLE handson_ch05.orders AS
SELECT
  1001 AS order_id,
  STRUCT('田中 太郎' AS name, 'tanaka@example.com' AS email) AS customer,
  [STRUCT('ノート PC' AS item, 2 AS quantity, 128000 AS unit_price),
   STRUCT('マウス' AS item, 1 AS quantity, 3200 AS unit_price)] AS items
UNION ALL
SELECT
  1002,
  STRUCT('鈴木 花子', 'suzuki@example.com'),
  CAST([] AS ARRAY<STRUCT<item STRING, quantity INT64, unit_price INT64>>);

3. UNNEST で明細行に展開します。

SELECT order_id, customer.name AS customer_name, i.item, i.quantity, i.unit_price
FROM handson_ch05.orders, UNNEST(items) AS i
ORDER BY order_id;

期待される出力（order_id 1002 は明細が無いため現れません）:

order_id | customer_name | item      | quantity | unit_price
1001     | 田中 太郎      | ノート PC  | 2        | 128000
1001     | 田中 太郎      | マウス     | 1        | 3200

後片付け: ハンズオン 5-3 まで同じデータセットを使うため、ここでは削除しません。

所要時間の目安: 5 分。サンドボックスで可・無料枠内（bigquery-public-data.github_repos.languages は約 195 MiB。列を絞ってもフルスキャンになりますが無料枠 1 TiB には十分収まります）。

github_repos.languages は repo_name STRING, language ARRAY<STRUCT<name STRING, bytes INT64>> という、1 リポジトリに対して複数の言語構成を持つ典型的なネストテーブルです。まず dry run でスキャン量を確認します。

bq query --use_legacy_sql=false --dry_run '
SELECT repo_name, language
FROM `bigquery-public-data.github_repos.languages`
WHERE repo_name = "torvalds/linux"'

続けて UNNEST で言語ごとの行に展開し、バイト数の多い順に並べます。

-- 処理: 約 195 MiB（列を絞ってもテーブル全体をスキャンする）
SELECT repo_name, lang.name AS language, lang.bytes
FROM `bigquery-public-data.github_repos.languages`,
UNNEST(language) AS lang
WHERE repo_name = 'torvalds/linux'
ORDER BY lang.bytes DESC
LIMIT 5;

実行すると、C や Makefile など、Linux カーネルらしい言語が上位に並びます（構成比はリポジトリの更新により変動するため、具体的な数値はここでは示しません）。全リポジトリを対象に「言語別の総リポジトリ数」を集計することもできます。

SELECT lang.name AS language, COUNT(DISTINCT repo_name) AS repo_count
FROM `bigquery-public-data.github_repos.languages`,
UNNEST(language) AS lang
GROUP BY language
ORDER BY repo_count DESC
LIMIT 10;

後片付け: 公開データセットを読んだだけなので後片付けは不要です。

所要時間の目安: 3 分。サンドボックスで可・無料枠内（数百バイトの自作テーブルへの参照）。

1. ハンズオン 5-1 で作った handson_ch05.orders に対し、まず CROSS JOIN UNNEST（既定の UNNEST の書き方と同じ動作）で注文件数をカウントします。

SELECT COUNT(DISTINCT order_id) AS orders_with_items
FROM handson_ch05.orders, UNNEST(items) AS i;

結果は 1（order_id 1002 の空配列は展開されず消えるため）です。

2. LEFT JOIN UNNEST に書き換えて同じ集計をします。

SELECT COUNT(DISTINCT order_id) AS all_orders
FROM handson_ch05.orders
LEFT JOIN UNNEST(items) AS i;

結果は 2 になり、明細の無い注文も母数に含まれます。「注文全体に対する明細ありの割合」のような分析では、この違いが結果を左右します。

後片付け:

bq rm -r -f -d PROJECT_ID:handson_ch05

この時点で handson_ch05 データセットは不要になるため削除します。次のハンズオンでは新しいデータセットを作り直します。

所要時間の目安: 5 分。サンドボックスで可・無料枠内（数百バイトの自作テーブル）。

1. データセットを作り直し、JSON 型の列を持つテーブルを作成します。

bq mk --dataset --location=US PROJECT_ID:handson_ch05_json

CREATE TABLE handson_ch05_json.events AS
SELECT 1 AS event_id,
  JSON '{"user": {"id": 42, "plan": "pro"}, "tags": ["beta", "mobile"]}' AS payload
UNION ALL
SELECT 2,
  JSON '{"user": {"id": 43, "plan": "free"}, "tags": []}';

2. スカラー値は JSON_VALUE、数値への変換には LAX_INT64、配列は JSON_QUERY_ARRAY で取り出します。

SELECT
  event_id,
  JSON_VALUE(payload, '$.user.plan') AS plan,
  LAX_INT64(JSON_QUERY(payload, '$.user.id')) AS user_id,
  JSON_QUERY_ARRAY(payload, '$.tags') AS tags
FROM handson_ch05_json.events
ORDER BY event_id;

期待される出力:

event_id | plan  | user_id | tags
1        | pro   | 42      | ["beta", "mobile"]
2        | free  | 43      | []

後片付け:

bq rm -r -f -d PROJECT_ID:handson_ch05_json