プランA: クラスタリング追加手順書

?

何をやるのか・なぜ効くのか

今の問題 BigQueryのテーブルに「整理」がされていない。
1施設のデータをMERGEするとき、全28施設のデータを毎回読み込んでいる。
BigQueryは「読んだ量」に課金されるため、これが1日 $132（月 $4,000）のコスト原因。

やること テーブルに hotel_id の「クラスタリング（索引）」を追加する。
これにより、MERGEで 該当施設のデータだけを読む ようになる。
スキャン量: 22 TB/日 → 1〜2 TB/日（28分の1）。

Cloud Functionsのコード変更は不要 クラスタリングはBigQueryのテーブル構造の設定。
MERGEのSQLにすでに hotel_id が条件に含まれているため、
BigQueryが自動的にクラスタを利用してスキャン量を削減する。
stable社のコードは一切触らなくてよい。

現在の日額

$139/日

22 TB スキャン

➡️

クラスタリング後

$15〜20/日

1〜2 TB スキャン

1

対象テーブル（優先5テーブル）

この5テーブルで全体コストの 76%（$100/日）を占めている。まずこの5つに適用。

#	テーブル名	本番サイズ	行数	MERGE Cost/日	staging サイズ	MERGEキー
1	be_D_御客伝票	188 GB	3.5億	$31.83	2.82 GB	hotel_id, 明細番号, 行, レコード更新日
2	be_D_御客情報	132 GB	9,400万	$22.30	1.96 GB	hotel_id, 御客番号, レコード更新日
3	be_B_御客伝票	113 GB	2.0億	$19.22	1.73 GB	hotel_id, 集計日, 明細番号, 行, 赤黒区分, レコード更新日
4	be_D_御客詳細	82 GB	4.0億	$13.97	1.17 GB	hotel_id, 御客番号, レコード更新日
5	be_D_御客GR	77 GB	6,100万	$12.98	1.17 GB	hotel_id, ｸﾞﾙｰﾌﾟ番号, レコード更新日
	合計	592 GB	11.5億	$100.30	8.85 GB

全テーブルのMERGEキーの先頭が hotel_id 調査の結果、MERGE対象テーブル（約90テーブル）すべてで hotel_id がON条件の先頭に含まれている。
クラスタリングキーに hotel_id を指定すれば、全テーブルで効果がある。

2

ステップ1: staging で検証

staging_frontcrew_data でクラスタリングを追加し、翌日のバッチが正常動作することを確認。
staging は本番の1/60程度のサイズなので、数分で完了する。

SQL - staging 検証用

-- 1. クラスタリングつきの新テーブルを作成
CREATE TABLE `bb-frontcreue.staging_frontcrew_data.be_D_御客伝票_clustered`
CLUSTER BY hotel_id
AS SELECT * FROM `bb-frontcreue.staging_frontcrew_data.be_D_御客伝票`;

-- 2. 旧テーブルをリネーム（バックアップ）
ALTER TABLE `bb-frontcreue.staging_frontcrew_data.be_D_御客伝票`
RENAME TO be_D_御客伝票_old;

-- 3. 新テーブルを正式名にリネーム
ALTER TABLE `bb-frontcreue.staging_frontcrew_data.be_D_御客伝票_clustered`
RENAME TO be_D_御客伝票;

-- 4. 翌日バッチ正常動作を確認後、旧テーブルを削除
DROP TABLE `bb-frontcreue.staging_frontcrew_data.be_D_御客伝票_old`;

上記を5テーブル分実行
翌日の深夜バッチが正常に完了するか確認
INFORMATION_SCHEMA.JOBS でスキャン量が減っているか確認
問題なければステップ2（本番）へ進む

3

ステップ2: 本番テーブル再作成

作業タイミング: 日中（バッチは深夜2時のみ） テーブルのリネーム中にMERGEが走ると競合する。
深夜バッチ完了後〜翌日のバッチ開始前（= 日中）に実施する。

1テーブルあたりの手順（5テーブル分繰り返し）

A

クラスタリングつきの新テーブルを作成

元テーブルの全データをコピーしつつ、hotel_id でクラスタリングを適用。
188GBのテーブルで数分〜15分程度。

B

テーブル名を入れ替え（2つのRENAME）

旧テーブル → _old にリネーム、新テーブル → 正式名にリネーム。
この2つは即時完了（メタデータ変更のみ）。

C

翌日バッチ確認後、旧テーブル削除

バッチが正常動作したら _old テーブルを削除してストレージ節約。
確認できるまでは削除しない（ロールバック用バックアップ）。

SQL - 本番用（be_D_御客伝票の例）

-- Step A: 新テーブル作成（CLUSTER BY hotel_id）
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_clustered`
CLUSTER BY hotel_id
AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_D_御客伝票`;

-- Step B-1: 旧テーブルをリネーム
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票`
RENAME TO be_D_御客伝票_old;

-- Step B-2: 新テーブルを正式名にリネーム
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_clustered`
RENAME TO be_D_御客伝票;

-- Step C: 翌日バッチ確認後に削除
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_old`;

5テーブル分のSQL（コピペ用）

一括実行用

-- ========== be_D_御客伝票 (188GB) ==========
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_clustered`
CLUSTER BY hotel_id AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_D_御客伝票`;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票` RENAME TO be_D_御客伝票_old;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_clustered` RENAME TO be_D_御客伝票;

-- ========== be_D_御客情報 (132GB) ==========
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客情報_clustered`
CLUSTER BY hotel_id AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_D_御客情報`;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客情報` RENAME TO be_D_御客情報_old;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客情報_clustered` RENAME TO be_D_御客情報;

-- ========== be_B_御客伝票 (113GB) ==========
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_B_御客伝票_clustered`
CLUSTER BY hotel_id AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_B_御客伝票`;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_B_御客伝票` RENAME TO be_B_御客伝票_old;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_B_御客伝票_clustered` RENAME TO be_B_御客伝票;

-- ========== be_D_御客詳細 (82GB) ==========
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客詳細_clustered`
CLUSTER BY hotel_id AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_D_御客詳細`;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客詳細` RENAME TO be_D_御客詳細_old;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客詳細_clustered` RENAME TO be_D_御客詳細;

-- ========== be_D_御客GR (77GB) ==========
CREATE TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客GR_clustered`
CLUSTER BY hotel_id AS SELECT * FROM `bb-frontcreue.production_frontcrew_data.be_D_御客GR`;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客GR` RENAME TO be_D_御客GR_old;
ALTER TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客GR_clustered` RENAME TO be_D_御客GR;

-- ========== 翌日バッチ確認後に旧テーブル削除 ==========
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客伝票_old`;
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客情報_old`;
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_B_御客伝票_old`;
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客詳細_old`;
-- DROP TABLE `bb-frontcreue.production_frontcrew_data.be_D_御客GR_old`;

CREATE TABLE の課金について CREATE TABLE AS SELECT は、元テーブルをフルスキャンするため課金が発生する。
5テーブル合計 592GB = 約 $3.7 の一時コスト。
削減効果（$100+/日）に比べれば問題ない。

4

ステップ3: 効果確認

翌日の深夜バッチ完了後に、以下のSQLで効果を確認する。

確認用SQL

-- 当日のスキャン量・コスト確認
SELECT
  DATE(creation_time) AS date,
  COUNT(*) AS job_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tb_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS cost_usd
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP('今日の日付')
  AND job_type = 'QUERY' AND state = 'DONE'
GROUP BY date;

スキャン量が 22 TB → 1〜2 TB に減っている
コストが $139 → $15〜20 に減っている
バッチの処理時間が大幅に遅くなっていない
dbt集計・Looker Studioのレポートが正常に表示される
確認OKなら _old テーブルを削除（ストレージ節約）

もし問題が発生した場合 旧テーブル（_old）をリネームで元に戻すだけでロールバック可能。
ALTER TABLE ...be_D_御客伝票 RENAME TO be_D_御客伝票_failed;
ALTER TABLE ...be_D_御客伝票_old RENAME TO be_D_御客伝票;
即時復旧できる（データのコピーなし、メタデータ変更のみ）。

5

（任意）残りのテーブルにも拡大

上位5テーブルで効果が確認できたら、残りの約35テーブル（$32/日分）にも同じ手順で適用可能。
小さいテーブルが多いので数分で完了する。

全テーブル適用で $139/日 → $5〜10/日（96%削減）まで下がる見込み。