はじめに

株式会社トラストバンクで地域通貨プラットフォーム chiica(チーカ)の開発責任者をしております湊（みなと）（@karura618）です。

本記事は「Aurora→BigQuery DWH構築」の後編です。

記事構成:

• 前編: AWS Glue Jobによるデータ転送層の設計と実装
• 後編（本記事）: BigQueryでの変換・マスキング層の設計と実装

前編では、Aurora から BigQuery の raw_layer（データレイク層）への転送を解説しました。

後編では、raw_layer から analytics_layer（DWH層）への変換処理を中心に解説します。

技術的成果

• 個人情報マスキングをアーキテクチャレベルで実装
• Single Source of Truthによるクエリ自動生成
• データ分類ポリシーによる統一的なセキュリティ管理

本記事でわかること

• MySQL定義ファイルからBigQueryクエリを自動生成する仕組み
• スナップショット型DWHの設計思想とデータモデリング
• 個人情報を安全にマスキングするアーキテクチャ
• データ分類ポリシー（P0/P1/P2/P3）の設計
• IAM設計とアクセス制御の実装

後編で扱う範囲

後編の範囲:

• raw_layer → analytics_layer の変換処理
• BigQueryクエリの自動生成
• 個人情報マスキングの実装
• IAM設計とアクセス制御

設計判断の背景

1. なぜマスキングをETL後段に置いたか

アーキテクチャの設計思想:

生データ → raw_layer（限定的アクセス）
          ↓ マスキング処理
マスク済データ → analytics_layer（広範なアクセス）

判断理由:

• 役割分離による責任境界の明確化
• 生データは限定的なアクセス権限で保護
• マスキング処理の独立性を保ち、後からマスキングロジックを変更可能

セキュリティ上のメリット:

• データエンジニアのみが生データにアクセス可能
• 分析者は常にマスク済データのみ参照
• 監査ログでアクセス履歴を追跡可能

2. なぜクエリ自動生成にしたか

課題:

• 200テーブルを人力管理はスケールしない
• 人間は必ずミスをする（カラム名の誤記、型の変換ミス）
• テーブル定義変更時の追従漏れが頻発

設計思想:

• Single Source of Truth: MySQL定義ファイルを信頼できる唯一の情報源とする
• Code as Documentation: 生成されたクエリがそのまま仕様書になる
• Fail Fast: パース失敗時に即座にエラーで気づける

実装の決め手:

• ヒューマンエラーゼロ（型変換、カラム漏れ等）
• テーブル定義変更時の追従が容易（再生成するだけ）

3. なぜスナップショット型DWHにしたか（データモデリング思想）

スナップショット型DWHの採用理由:

本基盤では履歴管理型（SCD Type 2等）ではなく、スナップショット型（日次の最新状態のみ保持）を採用しています。

判断理由:

• 分析用途が「現在値ベース」: BIツールでの分析は「現在の会員数」「現在のポイント残高」など、最新状態ベースのレポートが主体
• ストレージコスト最適化: 履歴を全て保持すると200+テーブル × 日次でストレージが急増
• クエリ複雑度の削減: 最新レコードのみ保持することで、分析クエリが単純化（JOINやサブクエリ不要）

トレードオフ:

• 過去の状態は追跡できない → ただし、Auroraのバックアップから遡及可能
• 履歴分析が必要な場合は個別対応 → ログテーブル等は別途履歴保持を検討

実装の決め手:

-- QUALIFY句で各IDの最新レコードのみ抽出
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY id
  ORDER BY modified DESC, _PARTITIONTIME DESC
) = 1;

将来の拡張性:

履歴が必要になった場合は、以下の拡張が可能です：

• SCD Type 2への移行: valid_from/valid_toカラムを追加して履歴管理
• ハイブリッド型: マスタは最新状態、トランザクションは全履歴保持
• Slowly Changing Dimension: 変化頻度の低いディメンションテーブルのみ履歴化

データモデリングの原則:

• YAGNI（You Aren't Gonna Need It）: 現時点で不要な履歴管理は実装しない
• Measure Twice, Cut Once: 将来の拡張性は確保しつつ、シンプルに始める

システムアーキテクチャ

全体フロー（後編の範囲）

データフロー詳細（後編）

処理の要点:

1. マスキング: 個人情報カラムを不可逆ハッシュ化
2. 最新化: 各レコードの最新状態のみ保持（スナップショット型DWH）
3. パーティション分割: 日次パーティションによる効率的なデータ管理

BigQueryクエリ自動生成バッチ

背景

手動でBigQueryクエリを作成すると以下の問題がありました：

• 200+テーブルのクエリ作成に約40時間
• カラム名の誤記、型の変換ミス
• テーブル定義変更時の追従漏れ

解決策: タスクによる自動生成

MySQL のCREATE TABLE定義ファイルを解析して、BigQueryクエリを自動生成するバッチを実装しました。

処理フロー

Single Source of Truth:

• MySQL定義ファイルが唯一の真実の情報源
• テーブル定義変更時は再生成するだけで追従可能

MySQL型 → BigQuery型の変換ロジック

private const TYPE_MAPPING = [
    'tinyint' => 'INT64',
    'smallint' => 'INT64',
    'int' => 'INT64',
    'bigint' => 'INT64',
    'varchar' => 'STRING',
    'text' => 'STRING',
    'longtext' => 'STRING',
    'datetime' => 'TIMESTAMP',
    'date' => 'DATE',
    'decimal' => 'NUMERIC',
    'float' => 'FLOAT64',
    'double' => 'FLOAT64',
];

生成されるクエリの例

-- Step 1: テーブル作成（冪等性を保証）
CREATE TABLE IF NOT EXISTS `your-project-id.analytics_layer.members`
(
  id INT64,
  email STRING,
  name STRING,
  modified TIMESTAMP,
  source_partition_time TIMESTAMP,
  snapshot_date DATE,
  processed_at TIMESTAMP
)
PARTITION BY snapshot_date
OPTIONS(description="会員マスタテーブル（DWH用）");

-- Step 2: データ挿入（最新レコードのみ）
INSERT INTO `your-project-id.analytics_layer.members`
SELECT
  id,
  email,
  name,
  modified,
  _PARTITIONTIME as source_partition_time,
  CURRENT_DATE() as snapshot_date,
  CURRENT_TIMESTAMP() as processed_at
FROM `your-project-id.raw_layer.members`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY id
  ORDER BY modified DESC, _PARTITIONTIME DESC
) = 1;

メタデータカラムの設計

設計のポイント:

• source_partition_time: どのGlue Job実行分のデータか追跡
• snapshot_date: 日次パーティション分割のキー
• processed_at: クエリの実行タイミングを記録

セキュリティ設計

データ分類ポリシー

個人情報を以下の4段階に分類し、それぞれ適切な取り扱いを定義しました。

重要な原則:

• P0データ（パスワード等）はDWHに保存しない
• アプリケーション側で既にハッシュ化されているが、DWH連携から除外することを推奨
• 誤ってDWHに含まれる場合は、ETL時点で除外または再ハッシュ化

個人情報マスキング実装

マスキング方針

個人情報カラム（P1）はSHA256でハッシュ化し、不可逆化します。

セキュリティに関する注記

本記事では説明簡略化のため素のSHA256を例示していますが、
実運用では以下のセキュリティ強化を推奨します：

• ソルト付与: レインボーテーブル攻撃への対策
• HMAC-SHA256の利用: 鍵ベースの認証付きハッシュ
• Cloud KMSで管理する鍵を使用: 鍵のローテーションと監査
• Policy Tagsによる列レベル制御: BigQuery側での追加保護層

マスキングクエリの例

SELECT
  id,
  -- P1: 直接識別子をSHA256でハッシュ化
  TO_HEX(SHA256(CAST(email AS STRING))) as email,
  TO_HEX(SHA256(CAST(tel AS STRING))) as tel,
  TO_HEX(SHA256(CAST(last_name AS STRING))) as last_name,
  TO_HEX(SHA256(CAST(first_name AS STRING))) as first_name,
  TO_HEX(SHA256(CAST(birthday AS STRING))) as birthday,
  -- P2: 間接識別子はそのまま保持（分析に必要）
  zip,
  address_city,
  -- P3: 非個人情報はそのまま保持
  member_type,
  created,
  modified
FROM `your-project-id.raw_layer.members`

passwordカラムの取り扱い

重要: passwordカラムは原則DWH非保持

パスワードカラムは以下の理由からDWH連携から除外することを強く推奨します：

• アプリケーション側で既にハッシュ化済み（bcrypt, argon2等）
• DWHでの分析用途が存在しない
• セキュリティインシデント時のリスクが高い

実装方針:

# AWS Glue Job側で除外
exclude_columns = ['password']

# または、BigQueryクエリ生成時に除外
if column_name == 'password':
    continue  # スキップ

マスキング対象カラムの例（P1分類）

• email (メールアドレス)
• tel (電話番号)
• last_name, first_name (氏名)
• last_name_kana, first_name_kana (氏名カナ)
• birthday (生年月日)

NULL値の扱い

-- TO_HEX(SHA256())にNULLを渡すとNULLが返る
TO_HEX(SHA256(CAST(NULL AS STRING))) → NULL

ハッシュ化後のデータ形式

元データ: "example@example.com"
↓ SHA256ハッシュ化
↓ HEX変換
結果: "A665A45920422F9D417E4867EFDC4FB8A04A1F3FFF1FA07E998E86F7F7A27AE3"
（64文字のSTRING型）

IAM設計とアクセス制御

アクセス制御の階層

階層構造:

• プロジェクトレベル: クエリ実行権限（全ユーザー）
• データセットレベル: データアクセス権限（役割別）

推奨アプローチ: Google Groupsの活用

個別ユーザーではなく、Google Groupsを活用します。

メリット:

• グループ単位での管理が容易
• メンバーの追加・削除が簡単
• 監査ログでグループ単位の追跡が可能

グループ構成例

グループ名: BigQuery Analytics Viewers
メールアドレス: bq-dwh-analytics@example.com
説明: analytics_layer データセット閲覧用
メンバー:
  - analyst1@example.com
  - analyst2@example.com
権限: READER (読み取り専用)

グループ名: BigQuery Data Engineers
メールアドレス: bq-dwh-engineers@example.com
説明: analytics_layer データセット編集用
メンバー:
  - engineer1@example.com
権限: WRITER (読み書き可能)

IAM設定の実装

Step 1: プロジェクトレベルでクエリ実行権限を付与

# 分析チームにクエリ実行権限を付与
gcloud projects add-iam-policy-binding your-project-id \
  --member="group:bq-dwh-analytics@example.com" \
  --role="roles/bigquery.jobUser"

Step 2: データセットレベルでアクセス権限を付与

# 分析チーム: 読み取り専用
bq update \
  --dataset \
  --add_access_group="group:bq-dwh-analytics@example.com,READER" \
  your-project-id:analytics_layer

# エンジニアチーム: 読み書き可能
bq update \
  --dataset \
  --add_access_group="group:bq-dwh-engineers@example.com,WRITER" \
  your-project-id:analytics_layer

運用フロー

日次運用（全体像）

処理内容:

運用戦略

障害検知と復旧

監視項目

障害発生時の復旧手順

復旧コマンド:

# 1. BigQueryパーティションの削除（重複防止）
bq rm -f -p your-project-id:analytics_layer.table_name\$20260218

# 2. BigQuery Scheduled Queryの手動実行
# BigQueryコンソールから手動トリガー

冪等性の保証

CREATE TABLE IF NOT EXISTSによる保護

-- 既存テーブルが存在する場合はスキップ
CREATE TABLE IF NOT EXISTS `project.dataset.table` (...)

メリット:

• クエリの再実行が安全
• スケジュールドクエリの冪等性を保証

データ品質の確保

異常値検知

-- 日次データ量の確認クエリ
SELECT
  snapshot_date,
  COUNT(*) as record_count,
  LAG(COUNT(*), 1) OVER (ORDER BY snapshot_date) as prev_count,
  (COUNT(*) - LAG(COUNT(*), 1) OVER (ORDER BY snapshot_date)) /
    LAG(COUNT(*), 1) OVER (ORDER BY snapshot_date) * 100 as change_rate
FROM `project.analytics_layer.members`
GROUP BY snapshot_date
ORDER BY snapshot_date DESC
LIMIT 7;

アラート基準:

• change_rateが±50%を超える場合は異常値として通知

コスト最適化

BigQueryストレージコストの削減

-- 90日以前のパーティションを削除
DELETE FROM `project.analytics_layer.members`
WHERE snapshot_date < DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

削減効果:

• 90日以降のデータ削除で約70%のストレージコスト削減
• 分析用途では直近3ヶ月のデータで十分な場合が多い

セキュリティベストプラクティス

1. データセット分離

• raw_layer: ETL処理用（限定的なアクセス）
• analytics_layer: 分析用（広範なアクセス）

2. 最小権限の原則

• 分析者にはREADERのみ付与
• エンジニアには必要に応じてWRITER付与

3. 監査ログの有効化

# Cloud Audit Logsでアクセス履歴を記録
gcloud projects get-iam-policy your-project-id \
  --format=json > iam_policy.json

4. 定期的な権限レビュー

• 四半期ごとに不要な権限を削除
• 退職者のアカウント削除確認

まとめ

実装内容のまとめ

1. BigQueryクエリ自動生成

   • MySQL定義ファイルの自動解析
   • 型変換の自動化
   • 工数削減（40時間 → 2分）

2. 個人情報マスキング

   • SHA256による不可逆ハッシュ化
   • データセット分離によるアクセス制御
   • データ分類ポリシー（P0/P1/P2/P3）

3. IAM設計

   • Google Groupsによる権限管理
   • 最小権限の原則
   • 監査ログの活用

4. スナップショット型DWH

   • 最新レコードのみ保持（QUALIFY句）
   • 日次パーティション分割
   • メタデータカラムによるデータリネージュ

達成した改善点

1. 保守性の向上

   • テーブル定義変更時の追従が容易（再生成のみ）
   • Single Source of Truthによる一貫性保証

2. 品質の向上

   • ヒューマンエラーの構造的な削減（型変換ミス、カラム漏れ等を自動生成により防止）
   • コードレビュー不要（自動生成のため）

3. 可観測性の向上

   • 監査ログによる追跡可能性
   • メタデータカラムによるデータリネージュの確保

4. セキュリティの向上

   • データセット分離による責任境界の明確化
   • データ分類ポリシーによる統一的な取り扱い

おわりに

前編・後編を通して、Aurora から BigQuery への DWH 構築の全体像を解説しました。

前編では、AWS Glue Job による「運搬」に徹したデータ転送層を構築し、変化に強い基盤を実現しました。

後編では、BigQuery による「変換・マスキング」層を構築し、セキュアで保守性の高いDWHを実現しました。

本設計のポイントは、レイヤー分離による責任境界の明確化です：

• ETL層（AWS Glue）: 運搬に徹する（変更頻度: 低）
• データレイク層（raw_layer）: 生データ保持（変更頻度: なし）
• DWH層（analytics_layer）: 加工・マスキング（変更頻度: 高）

この設計により、ビジネス要件の変化に柔軟に対応できる基盤を構築できました。

エンジニア募集

弊社では絶賛エンジニア募集中です！
気になった方、是非お気軽に Wantedly からご連絡ください！

参考リンク

• BigQuery アクセス制御
• BigQuery 暗号化関数
• Google Groups 管理
• BigQuery Scheduled Queries