🔀 BigQuery シャーディング

日付ベースのテーブル分割とパーティショニングへの移行

📖 シャーディングとは

🎯 概要

BigQueryのシャーディング（Table Sharding）は、日付や時間ごとに個別のテーブルを作成してデータを分散保存する古い手法です。 例えば、日次でテーブルを分割する場合：

⚠️ 重要: シャーディングは非推奨（Legacy）

シャーディングはレガシーな手法として扱われており、Googleはパーティションテーブル（Partitioned Tables）への移行を強く推奨しています。
新規プロジェクトでは絶対にシャーディングを使用せず、既存のシャーディングテーブルは早急にパーティショニングに移行すべきです。

シャーディングの構造

シャーディング例: 日次イベントログテーブル
📋
events_20241101
100万行
📋
events_20241102
95万行
📋
events_20241103
110万行
📋
events_20241104
105万行
📋
events_20241105
98万行
📋
...
継続
📊 特徴:
  • 1日ごとに独立したテーブルが作成される
  • 各テーブルは完全に独立したメタデータを持つ
  • 命名規則: table_YYYYMMDD形式
  • 1年で365個のテーブルが作成される

🆚 シャーディング vs パーティショニング

❌ シャーディング（非推奨）
日付ごとに別テーブル
構造:
• events_20241120
• events_20241121
• events_20241122
• ...

各テーブルは独立したエンティティ
⚠️ デメリット:
  • テーブル数の爆発（365個/年）
  • クエリが複雑（UNION必須）
  • メタデータ管理のオーバーヘッド
  • テーブル作成・削除の手間
  • スキーマ変更が困難
  • パフォーマンスが劣る
  • 1テーブルに対する操作制限
✅ パーティショニング（推奨）
1つのテーブル、内部で分割
構造:
• events（1つのテーブル）
  ├ partition 2024-11-20
  ├ partition 2024-11-21
  ├ partition 2024-11-22
  └ ...

1つのテーブル、内部でパーティション分割
✅ メリット:
  • テーブルは1つだけ
  • シンプルなクエリ（UNION不要）
  • 自動パーティション管理
  • 高速なパーティションプルーニング
  • スキーマ変更が容易
  • 優れたパフォーマンス
  • クラスタリングとの組み合わせ

❌ シャーディングの問題点

問題 詳細 影響
テーブル数の爆発 1日1テーブルで年間365個、複数年で数千個のテーブル 管理コスト増大、可視性低下
クエリの複雑化 複数日のデータを取得するにはUNION ALLが必須 クエリ作成が面倒、エラーリスク増加
メタデータオーバーヘッド 各テーブルが独立したメタデータを持つ メタデータ取得が遅い、API制限に到達
スキーマ変更の困難さ 全テーブルに対して個別にALTER TABLEが必要 大量のDDL実行、時間とコストがかかる
パフォーマンス ワイルドカードテーブルのスキャンが非効率 パーティションプルーニングより遅い
運用の煩雑さ テーブルの作成・削除を手動または自動化が必要 運用負荷増大、ミスのリスク
コスト管理 古いテーブルの削除を忘れるとストレージコスト増加 予期しないコスト発生

🔍 ワイルドカードテーブルを使ったクエリ

📌 ワイルドカードテーブルとは:

シャーディングされた複数のテーブルを1つのクエリでまとめて検索する機能。
_TABLE_SUFFIX擬似カラムで日付フィルタリングが可能。

基本的なワイルドカードクエリ

-- 全てのevents_*テーブルを検索 SELECT _TABLE_SUFFIX AS table_date, -- テーブル名のサフィックス部分 event_id, user_id, event_type, event_timestamp FROM `project.dataset.events_*` WHERE -- _TABLE_SUFFIXで日付範囲を指定（文字列比較） _TABLE_SUFFIX BETWEEN '20241120' AND '20241125' AND event_type = 'purchase' ORDER BY event_timestamp DESC LIMIT 1000

ワイルドカードパターン

全テーブル

events_*
全てのevents_XXXテーブル

年月指定

events_202411*
2024年11月のテーブルのみ

範囲指定

_TABLE_SUFFIX BETWEEN 'X' AND 'Y'
WHERE句で範囲フィルタ

複数パターン

events_2024*, logs_2024*
複数のテーブルパターン

日付範囲でのクエリ例

-- 過去7日間のデータを集計 SELECT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS event_date, COUNT(*) AS total_events, COUNT(DISTINCT user_id) AS unique_users FROM `project.dataset.events_*` WHERE -- 過去7日分のテーブルのみスキャン _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE()) GROUP BY event_date ORDER BY event_date

集計クエリ例

-- 月次のユーザー行動分析 SELECT SUBSTR(_TABLE_SUFFIX, 1, 6) AS year_month, -- 202411 event_type, COUNT(*) AS event_count, COUNT(DISTINCT user_id) AS unique_users, AVG(session_duration) AS avg_session_duration FROM `project.dataset.events_*` WHERE -- 2024年のデータのみ _TABLE_SUFFIX LIKE '2024%' GROUP BY year_month, event_type ORDER BY year_month, event_count DESC
⚠️ ワイルドカードクエリの注意点:

🔄 パーティショニングへの移行

移行手順

1
パーティションテーブルの作成
日付パーティションを持つ新しいテーブルを作成。
パーティション列とスキーマを定義。
CREATE TABLE `project.dataset.events_partitioned` PARTITION BY DATE(event_timestamp) CLUSTER BY user_id, event_type AS SELECT event_id, user_id, event_type, event_timestamp, properties FROM `project.dataset.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
2
データの検証
移行したデータの整合性を確認。
行数、ユニーク値、集計結果を比較。
-- シャーディングテーブルの行数 SELECT COUNT(*) FROM `project.dataset.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231' -- パーティションテーブルの行数 SELECT COUNT(*) FROM `project.dataset.events_partitioned` WHERE event_timestamp BETWEEN '2024-01-01' AND '2024-12-31'
3
クエリの書き換え
既存のクエリをパーティションテーブル用に書き換え。
UNION ALLやワイルドカードを削除。
❌ 移行前（シャーディング）
SELECT * FROM `events_*` WHERE _TABLE_SUFFIX BETWEEN '20241120' AND '20241125'
✅ 移行後（パーティション）
SELECT * FROM `events_partitioned` WHERE event_timestamp BETWEEN '2024-11-20' AND '2024-11-25'
4
アプリケーションの切り替え
アプリケーション、ETL、ダッシュボードを新テーブルに切り替え。
段階的にロールアウトして影響を最小化。
5
古いテーブルの削除
移行完了後、一定期間（例: 30日）経過したらシャーディングテーブルを削除。
バックアップを取ってから削除を推奨。
-- 古いシャーディングテーブルを削除 DROP TABLE `project.dataset.events_20241101`; DROP TABLE `project.dataset.events_20241102`; -- ... -- またはスクリプトで一括削除 bq ls --max_results=1000 project:dataset | grep 'events_' | \ xargs -I {} bq rm -f -t project:dataset.{}

💻 移行スクリプト例

Python スクリプト（全テーブルを統合）

from google.cloud import bigquery import datetime def migrate_sharded_to_partitioned( project_id, dataset_id, source_prefix, # 'events_' target_table_name, # 'events_partitioned' partition_column # 'event_timestamp' ): client = bigquery.Client(project=project_id) # パーティションテーブルの作成 target_table_id = f"{project_id}.{dataset_id}.{target_table_name}" # シャーディングテーブルから全データをコピー query = f""" CREATE OR REPLACE TABLE `{target_table_id}` PARTITION BY DATE({partition_column}) CLUSTER BY user_id, event_type AS SELECT * FROM `{project_id}.{dataset_id}.{source_prefix}*` WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)) """ print(f"Creating partitioned table: {target_table_id}") query_job = client.query(query) query_job.result() # 完了を待機 print(f"Migration completed: {query_job.total_bytes_processed / (1024**3):.2f} GB processed") # 行数を検証 source_count_query = f""" SELECT COUNT(*) as count FROM `{project_id}.{dataset_id}.{source_prefix}*` WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)) """ target_count_query = f"SELECT COUNT(*) as count FROM `{target_table_id}`" source_count = list(client.query(source_count_query).result())[0].count target_count = list(client.query(target_count_query).result())[0].count print(f"Source tables: {source_count:,} rows") print(f"Target table: {target_count:,} rows") if source_count == target_count: print("✅ Row counts match - migration successful!") else: print("⚠️ Row count mismatch - please investigate") # 実行 migrate_sharded_to_partitioned( project_id="my-project", dataset_id="my_dataset", source_prefix="events_", target_table_name="events_partitioned", partition_column="event_timestamp" )

bqコマンドでの移行

# パーティションテーブルを作成 bq mk \ --table \ --time_partitioning_field=event_timestamp \ --clustering_fields=user_id,event_type \ project:dataset.events_partitioned \ schema.json # シャーディングテーブルからデータをコピー bq query --use_legacy_sql=false \ ' INSERT INTO `project.dataset.events_partitioned` SELECT * FROM `project.dataset.events_*` WHERE _TABLE_SUFFIX BETWEEN "20240101" AND "20241231" ' # 行数を確認 bq query --use_legacy_sql=false \ 'SELECT COUNT(*) FROM `project.dataset.events_partitioned`'

✅ ベストプラクティス

🎯 移行時の推奨事項

項目 推奨事項
移行タイミング できるだけ早く実施。新規プロジェクトでは絶対にシャーディングを使わない
データ検証 移行前後で行数、ユニーク値、集計結果を必ず比較
段階的移行 最初は過去1年分のみ移行し、問題なければ全データを移行
並行運用期間 30日程度は両方のテーブルを維持し、問題ないことを確認
バックアップ 削除前にCloud Storageへエクスポートまたはスナップショット取得
クエリ最適化 パーティション列でのフィルタを必須にし、プルーニングを活用
モニタリング 移行後のコスト、パフォーマンス、エラー率を監視
⚠️ 移行時の注意点:

📊 パーティショニングの利点（詳細）

項目 シャーディング パーティショニング 改善
テーブル管理 365個/年 1個 99.7%削減
クエリの複雑さ UNION ALL必須 通常のSELECT シンプル
スキャン効率 テーブルメタデータ読込 パーティションプルーニング 10-100倍高速
スキーマ変更 全テーブルにALTER 1回のALTER 99%削減
データ保持期間 手動削除 自動削除可能 自動化
コスト最適化 難しい クラスタリング併用 さらに削減
APIコール数 テーブル数に比例 1回 99%削減

📚 まとめ

🎓 BigQueryシャーディングの重要ポイント

🚨 絶対に覚えておくこと:
  • 新規プロジェクト: シャーディングは絶対に使わない
  • 既存システム: できるだけ早くパーティショニングに移行
  • レガシー手法: シャーディングは技術的負債
パーティションテーブルを使って、
シンプルで効率的なデータ管理を実現しましょう！