Aurora→BigQuery DWH構築（後編）

Last updated at 2026-02-19Posted at 2026-02-19

はじめに

株式会社トラストバンクで地域通貨プラットフォーム 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: 将来の拡張性は確保しつつ、シンプルに始める

システムアーキテクチャ

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

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

処理の要点:

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

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`	TIMESTAMP	Glue Jobが書き込んだパーティション時刻
`snapshot_date`	DATE	スナップショット日付（パーティションキー）
`processed_at`	TIMESTAMP	BigQueryクエリ実行日時

設計のポイント:

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

セキュリティ設計

データ分類ポリシー

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

分類	カラム例	DWH保持方針
P0: 機密情報	`password`	DWH非保持（連携しない）
P1: 直接識別子	`email`, `tel`, `name`, `birthday`	ハッシュ化（SHA256）
P2: 間接識別子	`zip`, `address_city`	そのまま保持（組み合わせで識別可能性あり）
P3: 非個人情報	`member_type`, `created`, `modified`	そのまま保持

重要な原則:

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

運用フロー

日次運用（全体像）

処理内容:

時刻	処理	詳細
01:00	AWS Glue Job実行	差分モード: 昨日のmodifiedデータを取得
01:30	データ書き込み	raw_layer に書き込み
02:00	Scheduled Query実行	個人情報マスキング + 最新レコード抽出
02:08	データ書き込み	analytics_layer に書き込み
02:15~	分析可能	BIツールから参照可能

運用戦略

障害検知と復旧

監視項目

監視項目	閾値	アラート条件
BigQuery Scheduled Query失敗	1回失敗	即時通知
データ量の異常値	前日比±50%	データ欠損/重複の可能性

障害発生時の復旧手順

復旧コマンド:

# 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. 定期的な権限レビュー

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

まとめ

実装内容のまとめ

BigQueryクエリ自動生成
- MySQL定義ファイルの自動解析
- 型変換の自動化
- 工数削減（40時間 → 2分）
個人情報マスキング
- SHA256による不可逆ハッシュ化
- データセット分離によるアクセス制御
- データ分類ポリシー（P0/P1/P2/P3）
IAM設計
- Google Groupsによる権限管理
- 最小権限の原則
- 監査ログの活用
スナップショット型DWH
- 最新レコードのみ保持（QUALIFY句）
- 日次パーティション分割
- メタデータカラムによるデータリネージュ

達成した改善点

保守性の向上
- テーブル定義変更時の追従が容易（再生成のみ）
- Single Source of Truthによる一貫性保証
品質の向上
- ヒューマンエラーの構造的な削減（型変換ミス、カラム漏れ等を自動生成により防止）
- コードレビュー不要（自動生成のため）
可観測性の向上
- 監査ログによる追跡可能性
- メタデータカラムによるデータリネージュの確保
セキュリティの向上
- データセット分離による責任境界の明確化
- データ分類ポリシーによる統一的な取り扱い

おわりに

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

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

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

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

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

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

エンジニア募集

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

参考リンク

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up