Upgrade to Pro — share decks privately, control downloads, hide ads and more …

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #Google...

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

テクノロジーカンファレンス「Google Cloud Day ’23 Tour in TOKYO」の登壇資料です。詳細は当社ニュースをご参照ください。
https://kazaneya.com/5a50c1c1bb7b42f1bd9eb7b35d813ba1

---
スモールチームで 300 万テーブル規模のデータ基盤を構築・運用し、社内・社外にデータを提供しています。スケーラブルな仕組みやデータ流通を実現するヒントになればと思います。

具体的には
- BigQuery へのマイグレーション
- dbt によるデータモデリング
- IAM や AnalyticsHub によるデータ共有
- BigQueryML による異常検知
- CS 活動におけるデータ活用
といったテーマを扱います。

----------------------------------------------------------------------------------------------------
【補足】
共同登壇者である熱田さんのスライドURLです。内容は同じです。
https://speakerdeck.com/ryo_atsuta/300-mo-teburunodetaliu-tong-wozhi-eruenziniaringu

風音屋 (Kazaneya)

May 23, 2023
Tweet

More Decks by 風音屋 (Kazaneya)

Other Decks in Technology

Transcript

  1. スピーカー自己紹介 リクルートやメルカリにてデータ活用を推進後、外資ITを経 て、風音屋を創業。広告配信最適化や営業インセンティブ 設計など、データを駆使した業務改善を得意とする。コミュ ニティ活動では Developers Summit や Data Engineering

    Study の運営に携わり、データ基盤やダッ シュボード構築について情報発信している。主な著書に 『データマネジメントが30分でわかる本』『実践的データ基 盤への処方箋』がある。 横山翔 株式会社風音屋 代表取締役
  2. 目次 • プロジェクト概要 • データ収集から社外提供まで ◦ データ収集 ◦ データ移行 ◦

    モデリング・加工 ◦ 社内への提供 ◦ 社外への提供 • まとめ
  3. 会社概要 • NE株式会社(エヌイー株式会社)は EC Attractions「NEXT ENGINE」を 中核に、EC SaaS 事業、EC コンサルティング事業、ふるさと納税支援事業を

    提供し、全てのコマースを支えることを目指して活動 • スマホケース「iFace」などを制作販売している Hamee株式会社から 2022 年 8 月に分社して出来た会社
  4. 背景・課題 • データ基盤として Treasure Data を使っていたが 想定以上のコストが掛かっていた • 旧データ基盤のパフォーマンスの問題があった •

    SQL に癖があり、社内で浸透しなかった • 契約が年末に切れるとのことでデータ基盤移行プロジェクト発足
  5. プロジェクト体制 以下5人チーム • 執行役員 • Analytics Engineer(PL) • Analytics Engineer(Unit

    の MGR 兼任) • Data Engineer(当時 SRE) • 外部アドバイザー(風音屋)
  6. シャードからの取得 24 シャード・約 300 万テーブルから日次でデータ取得する連携システム 1. EventBridge で毎日 0 時に実行し、Fargate

    コンテナを 24 並列起動 2. 300万テーブルからデータを取得 3. 取得したデータをBigQuery へ流し込む
  7. ETL SaaS もいくつか検討したが... • 流石に 300 万テーブルに耐えるサービスは存在しなかった • trocco を試してみたが「300

    万テーブルの転送がそれぞれ 1 分かかった場合、転送にかかる時間は 5 万時間となり、 月換算だと 150 万時間となる」といわれ断念 • Datastream を試してみたが連携できるのは 1 万テーブルまでだったため断念 ◦ 1 シャード約 12 万テーブル
  8. ① 全データ取得 • 不正データや Communication Link Failure などエラーが多数発生し連携に失敗 ◦ 一つずつ原因を特定して修正

    • データ取得には負荷と IO 課金が大きな額発生(SRE と協力) ◦ スナップショットから復元したシャードから取得することで負荷を回避 ◦ 社内調整 • DateTime が 9 時間ずれることで集計結果が旧データ基盤と一致しない ◦ 全取得する際に復元したシャードのパラメータグループのタイムゾーンがUTC ◦ 取得しなおし
  9. ③ SQL 移行と整合性確認の課題 • とにかく SQL の量が膨大だった • Treasure Data

    記法から、BigQuery の書き方に全て変更しなければならなかった • 旧データ基盤も値が間違っていることがあった • 関係者全員から理解を得るには「なぜ集計結果がズレているのか」を 全て洗い出さなければならなかった
  10. データ移行のまとめ • シャードから約 15 年分の全データ流し込み • 旧データ基盤(TD)にあるデータを Cloud Run で

    100 並列バックアップ取得 • 旧データ基盤(TD)に存在した合計数千行にもなる SQL の移行 • スプレッドシートで 2 ヶ月間データ整合性確認
  11. Data Lake (DL) • 概要 ◦ 生データを保存 ◦ ソースの種類毎にデータセット作成 ◦

    機密性の観点から DM からの参照は許可しない • ソースの種類 ◦ GA4 ◦ Marketo ◦ プロダクトデータ • 連携方法 ◦ BQ 連携システム(Embulk) ◦ Airbyte ◦ BigQuery Data Transfer Service
  12. Data Ware House (DWH) • cleansing ◦ クレンジング済みデータ ◦ 機密情報は取得する際にマスキング

    • cleansing_secret ◦ マスキング前の機密情報 ◦ IAM 権限で強く縛っている • snapshot ◦ ログや履歴など ◦ 推移などに利用 • fact ◦ イベント毎にレコードが新規作成されるもの
  13. Data Mart (DM) • role_<ロール名> ◦ ロール毎にデータセットを作成 • service_<サービス名> ◦

    外部システムから参照する • monitoring ◦ 監視用テーブル配置 • widetable ◦ 広く共通で使われるデータ ◦ 一番利用率が高い
  14. list には test1, test2, test3 という文字列がリストの要素として入っている {% for data in

    list %} ・{{ data }} {% endfor %} list には test1, test2, test3 という文字列がリストの要素として入っている ・test1 ・test2 ・test3 test.tpl test.txt
  15. SQL Generator 処理の流れ 1. データベースの information_schema からテーブル、カラムのメタデータを 取得し、CSV 形式で保存 2.

    masking, partitioning, casting 対象のテーブル、カラムを それぞれ CSV 形式で保存 3. それらを一つの CSV に結合する 4. その結合した CSV の情報を元にテンプレート エンジンで SQL 生成
  16. 社内勉強会の開催 • テーマ ◦ 移行プロジェクトの説明 ◦ BigQuery の利用案内 ◦ Connected

    Sheet の紹介 • 頻度 ◦ 四半期毎に実施 • 対象者 ◦ エンジニアか否かは問わず ◦ データ基盤に興味のある方
  17. 課題解決:IAM管理 Resource / Role Data Sales Enginneer role_xxxx R -

    R public_xxxx - - - widetables R R R workshop WS WS WS - : 閲覧禁止 R: 閲覧者 WW: 編集者(弱) WS: 編集者(強)
  18. 社内提供 > BigQuery ML による 異常検出 • 収集 • 移行

    • モデリング・加工 • 社内提供 • 社外提供・ビジネス開拓
  19. 社外データ提供のプラットフォーム比較 現在のシステム構成や既存取引先へのデータ連携の観点で比較検討し Google Cloud に完結すると最速でスモールスタートを実現できると判断 NE(データ提供)観点 データ利用者観点 Google Cloud (例:Analytics

    Hub, GCS) ◯ ・データ基盤が BigQuery なのですぐに使える ◯ ・Google アカウント利用中ならすぐに使える Amazon Web Service (例:S3) ◯ ・プロダクトが AWS なのですぐに使える △ ・AWS アカウントを作ってもらう必要がある ・プロダクト間の自動連携には有力な選択肢 Microsoft 365 / Azure (例:SharePoint) △ ・新規にデータ連携の仕組みが必要 ◯ ・Microsoft アカウント利用中ならすぐに使える Snowflake Marketplace △ ・新規に契約が必要 ・新規にデータ連携の仕組みが必要 △ ・Snowflake アカウントを作ってもらう必要がある ・NE ユーザー企業の Snowflake 導入が増えたり、  海外企業とやり取りするときには有力な選択肢 その他、 データ販売会社を経由した提供 △ ・新規に契約が必要 △ ・データ提供先を広げるときには有力な選択肢 ・技術面の選択肢が柔軟(ロックイン回避が可能)
  20. Google Cloud による社外データ提供の選択肢 • 新しい仕組みや機能を導入せずに、1 人の相手に暫定対応するだけなら GCS > データセット分離 &

    IAM > Analytics Hub > BI ツール経由での提供 • スケーラブルな仕組みにする、かつ相手が Google Cloud を利用中の場合 Analytics Hub > データセット分離 & IAM > GCS > BI ツール経由での提供 → 試してみて簡単に使えたので Analytics Hub を採用!
  21. 社外データ提供における技術要求 ① 低コスト、少労力で検証したい → Analytics Hub で実現可能 ② 流出防止・権限管理を徹底したい → Analytics Hub & IAM

    で実現可能 ③ 変更容易性を担保したい (より良いプラクティスが分かったらデータ構成や仕組みを切り替えたい) という 3 つの要求を満たせる仕組みが必要
  22. データ受け渡しのシステム構成 • 提供者はデータマートで渡して、利用者はデータレイクで受け取る • 提供者:ユースケースの 1 つ(CS向けにリストを作るのと同じ) • 利用者:データソースの 1

    つ(気象庁から天候データを取得するのと同じ) • データ管理の責務を分けて、各自で IAM による権限管理(既存運用と同じ) ネクストエンジンの Google Cloud プロジェクト データ マート Hamee の Google Cloud プロジェクト データ レイク Analytics Hub
  23. 社外データ提供における技術要求 ① 低コスト、少労力で検証したい → Analytics Hub で実現可能 ② 流出防止・権限管理を徹底したい → Analytics Hub & IAM

    で実現可能 ③ 変更容易性を担保したい → スケーラブルなスクリプトを作成 (より良いプラクティスが分かったらデータ構成や仕組みを切り替えたい) という 3 つの要求を満たせる仕組みが必要
  24. 変更容易性へのアプローチ 【提供者視点】Analytics Hub やデータマートの自動生成スクリプトで 社外へのデータ提供の仕組みを爆速で実現できる → すぐに作り直せる、置き換えれば OK 【利用者視点】互換性担保については Web

    API のベスト プラクティス (例:v1, v2 といった命名でのバージョニング)を適用できる → 今回は省略、課題が顕在化したら v2 への切り替えを検討する
  25. 社外データ提供における技術要求 ① 低コスト、少労力で検証したい → Analytics Hub で実現可能 ② 流出防止・権限管理を徹底したい → Analytics Hub & IAM

    で実現可能 ③ 変更容易性を担保したい → スケーラブルなスクリプトを作成 (より良いプラクティスが分かったらデータ構成や仕組みを切り替えたい) という 3 つの要求を満たせる仕組みが必要 → OK!
  26. データ取引・流通の市場トレンド データ提供の概況をリサーチして勝ち筋を模索 1. 認知 2. マッチング ✕ アンチパターン ◦ 成功事例

    プラットフォームへの公開だけでは自然に利用されない (仕掛けが必要) 【コンテンツマーケ/分析事例公開】 海外にはデータ販売のために ホワイトペーパー作成を請け負う データ分析会社が存在 【BizDev/業務提携】 国内だとセコム、シャープ、 KDDIの業務提携&データ連携による 見守りサービスが代表的
  27. データ取引・流通へのアプローチ • 認知 ◦ 大学の研究室と共同研究やインターンの座組みを擦り合わせ中 ◦ 最終的には分析事例として公開できるところを目指したい →横山が今春より某国立大の研究員に就任してPJを開始 • マッチング

    ◦ グループ会社(Hamee環境)にAnalyticsHub経由で提供済み ◦ ネクストエンジン利用中のクライアントにも同様にデータ提供可能 →現在NEのコンサルタントチームがPJを推進中
  28. データ提供を支えるテクノロジー • BigQuery, Analytics Hub, Cloud IAM による 社内外へのデータ提供 •

    Connected Sheet, Looker Studio による データ分析の民主化 • Vertex AI / AutoML, BigQuery ML, ChatGPT による 機械学習の スモールスタート • Cloud Run, Amazon Event Bridge, AWS Fargate, dbt による スケーラブルなデータ収集・移行・加工・モデリング