DMMにおけるAWSから BigQuery へのデータ基盤移行 

藤井 亮太 合同会社 DMM.com 開発統括本部 データ基盤開発部 

01 DMM について 02 データ基盤移行の背景 03 移行の流れとポイント 04 まとめ 目次 

01 DMM について 

No content

No content

02 データ基盤移行の背景 

5 operation / week ● 稼働中のシステムの保守 以外のデータ系オペレー ション依頼(データ取り込 み、BI ツールユーザー追 加等)が週に 5 件 30,000 query / week ● BI ツール経由で投げられ るクエリは週 30,000 回以 上 ● MAUは 1,500 以上、管理 対象グループは 40 以上 1,500 以上のテーブル ● 取り扱うテーブル数は 1,500 以上 ● ほとんどが日次更新 ● Job のリランなどの保守が 定常的に発生 (移行前)データ基盤利用状況 1 2 3 

オペレーション省力化 ● 前述の通り、データオペ レーションが多く、活用拡 大にリソースを割けない状 況 ● オペレーションの自動化、 省力化を進める ガバナンス ● 誰でもデータを使えるとい う意味では民主化されてい た ● アウトプットの質が利用者 のスキルに強依存 ● 誰が使っても品質の高いア ウトプットとなるようなデー タガバナンスを データ基盤の課題 | 期待 データ活用の拡大 ● データ活用が社内システム に閉じていた ● 広告領域への活用では、 社外システムとの連携が 前提となる ● 柔軟に連携できるシステム へ 1 2 3 

Google Cloud 移行の狙い 1 Serverless, Managed を活用した低保守コストなシステムへ As Is → To Be database S3 + Glue + Athena → BigQuery Workflow digdag → Cloud Composer BI redash → Looker メタデータ管理 内製 → Data Catalog Tracking 内製 → GA4, GTM, BigQuery 

● 移行プロジェクトをリフト → シフトと単純にフェーズ分けせずに、置き換えるプロダクト の特性に沿った形で設計して進める ● データの収集 〜 アウトプット作成までの間に介在する人,組織に注目し、 フィジビリティのある運用設計を行う ● 最終的なアウトプットの質を高めるため、データ信頼度を定義 Google Cloud 移行の狙い 2 データ利用者増加に耐えるガバナンスと運用設計 

Data Activation / ReverseETL ● Retail API, Google Ads, Google Search Console, 各種広告媒体, MA ツールとの連 携 ○ GA, GTM による Realtime 連携 ○ Looker による Batch 連携 コラボレーション ● Service Account, IAM による社外の Google Cloud プロジェクトとの容易な連携 Google Cloud 移行の狙い 3 社外のサービスや組織との柔軟な連携 

03 基盤移行の流れとポイント 

移行の全体像 (新基盤) Analytics Manipulation AWS (既存基盤) Athena S3 Glue Data Catalog ELT Cloud Build Sync Data Transfer Orchestration Workflow Cloud Composer Execution Query BigQuery BI Looker Notebook Vertex AI Store DataLake Cloud Storage DWH/Mart BigQuery Queue Pub/Sub サービス基盤 各種データベース 

AWS 依存解消 Looker 連携 アナリスト向け パイプライン クエリ実行環境 AWS → Google Cloud データ同期 1 2 3 4 5 移行の流れ 

Job Queue Generation AWS → Google Cloud データ同期 1 Data Transfer による同期 S3 Cloud Storage の変更検知と Job の遅延実行 2 変更対象に応じた Merge クエリの生成 3 Sync Data Transfer MergeJob Cloud Workflows DataLake Cloud Storage ChangeQueue Pub/Sub CreateJobQueue Cloud Function JobQueue Firestore DWH BigQuery 

S3から Cloud Storage、Cloud Storage から BigQuery とも差分のみを更 新することで、省コストで高頻度なデータ同期が可能に ● S3からCloud Stoageへの同期はtransfer optionsの適切な設定で差分更新となる。 ● Cloud Storage の変更情報を解析し、変更対象の BigQuery のテーブル、Partition を特定し、対象範囲を絞ってマージを実行 ○ 公式チュートリアル の構成は変更イベントドリブンな Push 型の構成 Storage の変更イベント数によっては Firestore の Rate Limit 制限を受けるため、Scheduler と組 わせ Pull 型の構成にした Point 1 差分更新 

同期されたデータの差異を検知する機構を実装 AWS(Athena+Glue DataCatalog)と BigQuery 間でテーブル / Partition 毎に論理的な 差異が発生していないかチェック 1. 全レコードを md5 で Hash 化 2. 後続処理の Reducer で分散処理できる XOR を使うため、64 bit に分割し数値化 3. 全レコードから生成された 64 bit 数値を全て XOR 4. 各環境で生成された値に差分がないことを確認 Point 2 データの差分検知 Hashed record1(md5, 128) Hashed record2(md5, 128) Hashed record2(md5, 128) … record1 record2 record2 … Hash1-1(64), Hash1-2(64) Hash2-1(64), Hash2-2(64) Hash2-1(64), Hash2-2(64) … Hash1-1(64) XOR Hash1-2(64) XOR Hash2-1(64) XOR Hash2-2(64) XOR Hash2-1(64) XOR Hash2-2(64) … 

AWS 依存解消 Looker 連携 アナリスト向け パイプライン クエリ実行環境 AWS → Google Cloud データ同期 1 2 3 4 5 移行の流れ 

分析ツール データ管理 クエリ実行環境 1 データの保存領域とデータ利用部門毎 のクエリ実行環境を分離 データ利用部門毎に一部管理を譲渡 ● データ利用者の追加,削除 ● Sandbox 環境内での DDL 実 行,ScheduleQuery 実行 2 DWH/Mart BigQuery データ利用 Query(Div.A) BigQuery Query(Div.B) BigQuery Query(Div.C) BigQuery Usage Check Cloud Monitoring Looker studio Spread Sheet Colaboratory BIツール Trusted BI tool Looker 

ユーザー管理の譲渡 各部門にデータ実行環境(プロジェクト)と Google Group への権限付与 データ基盤利用部門は Google Group のメンバーを管理 過剰利用の監視 データ利用部門毎にスキャン量上限を設定(by User, by Project) Cloud Monitoring (MQM)を利用して、上限抵触を監視 & 通知 Point ガバナンスと民主化 

部門毎の利用量に応じたコスト按分 (Editions 導入前)データ利用プロジェクトのコストを紐づく部門に請求 (Editions 導入後)INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER によるSlot 利用量に応じた按分 ● 組織あたりの Reservation 数は上限が 5 つであり、管理対象部門(プロジェクト)毎の発行は難しい ● By Folder でない View を参照すると、Union の上限にかかった。組織構造に沿った folder の作成とプ ロジェクトの再配備により union せずに集計できるように Point ガバナンスと民主化 

データ信頼度定義と委譲 GitLab 社の Trusted Data Solution Criteria をベースに、データの信頼度を定 義 (https://about.gitlab.com/handbook/business-technology/data-team/data-development/) Point ガバナンスと民主化 データ信頼度 データ生成/保存 全社 BI ツール(Looker) 利用可否 1. Explorational 任意の方法, データ利用 PJ 不可能 2. Ad-hoc 任意の方法, データ利用 PJ 不可能 3. Business Insights データマート管理ワークフロー データ管理 PJ 可能 4. Trusted Data データマート管理ワークフロー データ管理 PJ 可能 

AWS 依存解消 Looker 連携 アナリスト向け パイプライン クエリ実行環境 AWS → Google Cloud データ同期 1 2 3 4 5 移行の流れ 

データ管理 アナリスト向けパイプライン 2 Schedule Query のリポジトリ管理 ● データ利用プロジェクトと対応した Code Owner 設定 ● Query の Dry Run によるテスト ● 書き込み対象領域の権限チェック DWH/Mart BigQuery データ利用 CICD github Circle CI Service Account (Terraform) IAM auth/imperson ate_service_ac count Dry Run Query 1 SQL と簡単な設定でマートを生成可能 ● プログラミング未経験のメンバーが容易 に実装できる環境とした Scheduled Query (Div.A) BigQuery Service Account (Div.A) IAM 

Code Owner によるレビュー 全社で利用するダッシュボードの元データとなるデータマートの信頼性を担保するため、 各マート毎に Code Owner を設定 ● データ利用プロジェクト(部門) > Team > データセット名 > 生成クエリという階層で管理 ● データ利用プロジェクト(部門) || Team 毎に Code Owner を設定 ● Branch Protection に Code Owner の approve を必須設定 Point レビューとテスト 

CI への BigQuery Dry Run 組み込み クエリの構文だけでなく、組織外のデータマート作成などの権限の確認を行うため、CI にクエリを dry run するテストを組み込み ● CI に使う Service Account を増やしたくなかったため、各データ利用プロジェクトのScheduled Query 実行 Service Account に impersonate ● 変更対象クエリのパスから Service Account を判定できるように、プロジェクトに紐づく IAMの命名整 備と IaC による管理を徹底 Point レビューとテスト 

AWS 依存解消 Looker 連携 アナリスト向け パイプライン クエリ実行環境 AWS → Google Cloud データ同期 1 2 3 4 5 移行の流れ 

Looker のプロジェクトは組織より細かいチーム粒 度で分割 ● BigQuery の組織単位のプロジェクトと紐づけ Looker 連携 データ利用 1 Scheduled Query (Div.A) BigQuery Service Account (Div.A) IAM Scheduled Query (Div.B) BigQuery Service Account (Div.B) IAM BIツール Trusted BI tool Looker Project (Div.A/Team1) Project (Div.A/Team2) Project (Div.B/Team1) 

AWS 依存解消 Looker 連携 アナリスト向け パイプライン クエリ実行環境 AWS → Google Cloud データ同期 1 2 3 4 5 移行の流れ 

Cloud Build で Embulk を実行 Cloud Composer で dag 制御 Google Cloud Managed AWS(既存基盤) AWS VPC ELT Cloud Build Workflow Cloud Composer DWH/Mart BigQuery サービス基盤(オンプレ) MySQL サービス基盤(AWS) RDS MyVPC NAT Forwarder GCE Static IP Cloud NAT 2 1 AWS依存解消 

取り込みフロー ● Cloud Storage → Staging Table → DWH 用 Table へ Merge 差分取り込みのための Staging Table ● Staging Table は都度生成& 削除し、不要なコストを抑止(TTL も設定済み) 一連の処理は取り込み対象テーブルの Schema から自動生成 設定ミスによる不揃いな型での取り込み等が発生しない Point 1 Information Schema の活用 

Cloud Composer のPrivate pool ● Cloud Build(VPC:servicenetworking) → カスタムVPC → Cloud NAT Privateサービス接続とカスタムExport ● Managed Service の VPC と Peering する VPC を作成 ● 優先度の高い Export ルールを生成し、全ての通信を Google Cloud 上に作成した 別 VPC 内の EC2 経由で Cloud NAT に接続 ○ /0 だと優先度が反映されないため、/1 で 2 つの export ルールを作成 Point 2 IP固定 

04 まとめ 

● BigQuery, Looker, Cloud Composer など、Serverless, Managed を活用したシステ ムに構築した事で、インフラの保守運用より機能開発により多くの時間を割けるように なった。 各機能のつなぎ込みにおいても、Pub/Sub, Cloud Functions などを選択することで、 開発がスムーズに進むようになった ● 本編では紹介できなかったが、Tracking システムのリプレイスでの GA4 / GTM活用 や、Cloud Spanner の Dataflow Template による CDC なども実現できた まとめ 1 Serverless, Managed を活用した低保守コストなシステムへ 

まとめ ● Information Schema の活用により、データ取り込みやデータ利用量の監視などをス ケーラブルな方法で実現できた ● データ利用者管理、アナリストによるデータマート作成とレビューテストプロセスの構 築など、組織の拡大に対し再現性のある運用方法を構築できた ● データ信頼度とそれぞれのデータアクセス方法を提供することで、Delivery 重視なア ドホック分析や Quality 重視なダッシュボード作成などデータ活用の選択肢を提供す ることができた 2 データ利用者増加に耐えるガバナンスと運用設計 

まとめ 3 社外のサービスや組織との柔軟な連携 Data Activation / ReverseETL ● Retail API, Google Ads, Google Search Console, 各種広告媒体, MA ツールとの連 携 ○ GA, GTM による Realtime 連携 ○ Looker による Batch 連携 コラボレーション ● Service Account, IAM による社外のGoogle Cloud プロジェクトとの容易な連携 

Thank you