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

スタディサプリでのBigQuery移管と実践的活用術 / meetup_kittaka

85da685d91fda190e2e3162d0de248a4?s=47 Recruit
January 27, 2022

スタディサプリでのBigQuery移管と実践的活用術 / meetup_kittaka

2022/01/27_スタディサプリのデータ基盤を支える技術 2022 -RECRUIT TECH MEET UP #3-での、橘高の講演資料になります

85da685d91fda190e2e3162d0de248a4?s=128

Recruit

January 27, 2022
Tweet

More Decks by Recruit

Other Decks in Technology

Transcript

  1. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 スタディサプリでの BigQuery 移管と 実践的活用術 橘高 允伸

    スタディサプリのデータ基盤を支える技術 2022 ーRECRUIT TECH MEET UP #3ー
  2. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 橘高 允伸 きったか まさのぶ データエンジニア 2019年リクルート新卒入社

    ➔ データ基盤の開発・運用 ➔ データ基盤移管プロジェクト ➔ データプロダクトのバックエンド
  3. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 Agenda | 01 02 03 旧データ基盤の概要と課題

    新データ基盤のDWHでの取り組み まとめ
  4. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 旧データ基盤の概要と課題 01

  5. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 旧データ基盤の構成 Master data Server Side Log

    Client Side Log 小中高 / ENGLISH TD SDK Kinesis Lambda 分析 レポーティング (Presto/Hive)
  6. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 旧データ基盤の課題 1. 集計クエリのリソースの枯渇 2. 一貫性のないデータセット・権限管理 3.

    データの不整合 4. クエリの中長期のメンテナンスが困難
  7. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 1. 集計クエリのリソースの枯渇 ➔ 利用者・使用用途が拡大したことで,クエリの渋滞が発生 ➔ 基幹集計が慢性的に遅延

    ➔ アドホックな分析でもアナリストに待ち時間が増加 旧データ基盤の課題
  8. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 2. 一貫性のないデータセット・権限管理 データセット管理の問題 ➔ 同データセット内にレイヤーの異なるテーブルが混在 (マスタ/ログ/データマート)

    ◆ これにより,replace / snapshot / append 形式のテーブルが混在 ➔ クエリを書く際にパーティションの有無・指定方法を気にする必要がある 権限管理の問題 ➔ 権限管理体制が手動でアカウントごとの作業(Admin の乱立) ➔ どのチームがどのデータセットにどのようなアクセス権限を持つということを 管理・把握できていなかった 旧データ基盤の課題
  9. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 3. データの不整合 ➔ レコードの重複の発生 ◆ 誰かが気付くまで発覚しないので放置されてしまう...

    ➔ 同様のロジックのCASE文などがクエリ間に散在し,改修漏れが発生 ◆ 微妙に異なるCASE文が存在するが,意図的なのか判断が難しい ➔ ソースのDBに最新のデータが入っていないことでの不整合 ◆ 一部のデータが古い状態でも集計が進んでしまう状態 旧データ基盤の課題
  10. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 4. クエリの中長期のメンテナンスが困難 ➔ 小中高,English 合わせて300個以上もの基幹集計クエリ ➔

    メンテされていないクエリの存在 ◆ スケジュールクエリや基幹集計の中に不要なものがあるが,判断が難し く迂闊に止められない ➔ クエリやテーブルの使用用途が引き継がれにくい ➔ データ不整合が発生した際の責任の所在が不明瞭 旧データ基盤の課題
  11. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 新データ基盤のDWHでの取り組み 02

  12. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  13. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  14. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 BigQueryへ移管し,集計リソースを強化 課題1: 集計クエリのリソースの枯渇 BigQuery 導入での効果 ➔

    DWH を BigQuery へ移管したことで,集計処理が高速化 ◆ 例)「課金ログを元に学習者の会員ステータスを管理するテーブル」の 集計時間が 60 min (hive) → 1 min (BigQuery) に短縮 ➔ BigQuery を従量課金(オンデマンド料金)で利用しているためコストを意識し てクエリを書くように
  15. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 リソースのモニタリング整備 ➔ Looker でモニタリング用ダッシュボードを作成 ◆ BigQuery

    のクエリ料金 (日次・月次) ◆ BigQuery のストレージ料金 (日次・月次) ◆ BigQuery のクエリスキャン量 など 課題1: 集計クエリのリソースの枯渇
  16. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 リソースの分割 ➔ 「サービスアカウントによる基幹集計」と「一般ユーザーによるアドホック集 計」の BigQuery の実行プロジェクトを分離

    ◆ 旧データ基盤では同じリソースで基幹集計とアドホック集計が実行され ており,互いにリソースを食い合っていた ➔ 1日のクエリスキャン量の制限 (quota) を設定し,利用料金爆発事故を防止 ◆ Project ごとにデータ処理量の制限を設定 ◆ この制限を設定する前に1日で数万円のクエリを実行してしまったことが ある... 課題1: 集計クエリのリソースの枯渇
  17. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  18. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 ➔ プロジェクト・データセットでより細かい単位で分割 ➔ パーティションの指定方法・有無がより直感的に データセットのレイヤーの再設計 課題2:

    一貫性のないデータセット・権限管理 プロジェクト データの種類 パーティション方式 マスタ用 mongoDB / postgreSQL / spreadsheet など 日次スナップショット ログ用 サーバーサイド/ クライアントサイド/ SBPS ログなど 差分のみパーティション データマート用 ユースケースごとに扱いやすくしたテーブルを配置 日次スナップショット /差分 レポート用 Looker など外部に提供するデータの置き場 基本パーティションなし サンドボックス用 ユーザーが自由に使って良い場所 - ※パーティションがあるテーブルは,パーティションフィルタ必須
  19. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 アカウントの権限管理の見直し アカウントの権限管理 ➔ チーム単位で権限管理 (Terraform 管理)

    ➔ 管理者以外はサンドボックス環境へのみ書き込み権限を持つ Looker の権限管理 ➔ 本番 Looker 環境からのデータ参照はレポート用プロジェクトのみ可能 ◆ GitHub 管理の基幹集計クエリで作成されたテーブルのみ参照可能 ◆ 基幹集計処理の管理外で新たな指標定義が生まれることを防止 課題2: 一貫性のないデータセット・権限管理
  20. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  21. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 データ基盤移管時のデータ検証作業 ➔ 旧データ基盤の集計テーブルを BigQuery にもコピーし,差分検証実施 ◆

    旧データ基盤も並行運用し,検証で問題がないテーブルから移管 ➔ 差分の原因を精査することで旧データ基盤のバグも洗い出し ◆ レコードの重複や不正な MAX_BY の使用で結果がランダムになってし まっている,などのバグが発覚 ◆ 移管のタイミングで修正 課題3: データの不整合
  22. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 データ基盤移管時のデータ検証作業のTips ➔ 細かい差分を確認するのに, `except distinct` が便利

    ➔ このクエリを使用することで テーブル td には存在するが bq には 存在しないレコード, とその逆のレコードがわかる 課題3: データの不整合 WITH in_td AS ( SELECT * FROM td EXCEPT DISTINCT SELECT * FROM bq ), in_bq AS ( SELECT * FROM bq EXCEPT DISTINCT SELECT * from td ) SELECT *, 'td' AS exist_in FROM in_td UNION ALL SELECT *, 'bq' AS exist_in FROM in_bq
  23. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 日次集計のデータバリデーションを整備 ➔ table_name.yaml ファイルでテーブルのバリデーションタスクを管理 ◆ この

    yaml を元に Composer でタスクを自動生成 ◆ ASSERT クエリでバリデーション ➔ データバリデーションの種類 ◆ データが存在するか ◆ レコード数が単調増加しているか ◆ ユニーク性 課題3: データの不整合 - name: validate_records_exist error_message: 前日のパーティションがありません! query: | ASSERT ( SELECT COUNT(1) FROM `project.dataset.table` WHERE _PARTITIONDATE = DATE("{{ execution_date | localtz_to_date_str }}") ) > 0
  24. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 UDFで処理を共通化し,対応漏れを防止 課題3: データの不整合 ➔ 同じロジックの CASE

    文が散在し,改修漏れが発生することがあった ➔ クエリ間で散らばっていた CASE 文などを共通の UDF で管理 ◆ BigQuery にない MAX_BY なども javascript で UDF を作成 ➔ UDF は GitHub で管理し,PR マージ時に CloudBuild で BigQuery へ反映 create trigger push Cloud Build BigQuery GitHub
  25. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  26. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 移管タイミングでのクエリの削減 ➔ 新データ基盤に移管するタイミングで移管対象クエリを選定 ➔ 使用されていないクエリの廃止 ◆

    Looker API を使用して参照されていないテーブルの確認 ◆ Looker 参照がないテーブルは利用者の存在を確認して廃止 ➔ English の基幹集計だけでも不要なクエリが約40%存在し,削減できた 課題4: クエリの中長期のメンテナンスが困難
  27. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 クエリのGit管理とCIでのテスト ➔ 定常集計クエリは GitHub で一元管理 (旧データ基盤でも主要な集計クエリ

    はGitHub 管理) ◆ BigQuery のスケジューリング機能は使わない ◆ 定期実行される SQL の GitHub 管理と PR でのレビュー ◆ クエリ結果の任意の Google Sheets への保存も可能 課題4: クエリの中長期のメンテナンスが困難 SQL, yaml レビュー Spreadsheet エンジニア アナリスト BigQuery Cloud Composer PR GitHub export query deploy
  28. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 クエリのGit管理とCIでのテスト ➔ PR 時に CI で

    SQL を dryrun で実行し,クエリテスト ◆ シンタックスエラー ◆ テーブル/カラム名のタイポ ◆ パーティションの指定忘れ (パーティションフィルタ必須テーブル) などをチェック 課題4: クエリの中長期のメンテナンスが困難 dryrun trigger PR Cloud Build BigQuery
  29. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 Data Catalogを導入しデータ・マネジメント強化 ➔ DMBOK2 に準拠した2種類のメタデータを Data

    Catalog に 連携しデータ・マネジメントを強化 課題4: クエリの中長期のメンテナンスが困難 メタデータ種類 データの内容と利用の目的 データの例 チーム(ビジネス) メタデータ • データの内容・状態やドメイン情報を登録 • テーブルのドメイン情報を管理し、暗黙知を形式知 化する • テーブル / カラムの説明 • 利用ドメインの情報 and more… オペレーショナル メタデータ • データの処理・アクセスに関する情報を登録 • データの利用状況などを管理し、クエリの棚卸しを継 続しやすい状態にする • ユーザー / Lookerの参照回数 • 高頻度利用ユーザー and more…
  30. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 チームメタデータ ➔ yaml / json 形式で管理

    ➔ PR マージ時に CloudBuild で Data Catalog を更新 課題4: クエリの中長期のメンテナンスが困難 Data Catalog Cloud Build [ { "name": "id", "type": "STRING", "description": "ユーザーID" "tag": {} }, ... ] tags: - key1: value1 - key2: value2 description: |- ### 概要 xxxx ### 注意点 xxxx ### ステークホルダー - @aaaaaaa GitHub 編集 trigger 更新 タグ情報とテーブル説明 (yaml) スキーマ・カラム説明 (json)
  31. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 オペレーショナルメタデータ ➔ Cloud Logging の BigQuery

    監査ログを BigQuery へ export して使用 ➔ Composer で日次で集計し, Data Catalog のタグ情報を更新 課題4: クエリの中長期のメンテナンスが困難 BigQuery Cloud Composer Cloud Logging Data Catalog export テーブル作成 ・参照 タグ情報更新
  32. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 まとめ 03

  33. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 課題と取り組みのまとめ 集計クエリのリソースの枯渇 一貫性のない データセット・権限管理 データの不整合 クエリの中長期の

    メンテナンスが困難 • BigQueryへ移管し,集計リソースを強化 • リソースのモニタリング整備 • リソースの分割 • データセットのレイヤーの再設計 • アカウントの権限管理の見直し • データ基盤移管時のデータ検証作業 • 日次集計のデータバリデーションを整備 • UDFで処理を共通化し,対応漏れを防止 • クエリのGit管理とCIでのテスト • Data Catalogを導入しデータ・マネジメント強化 旧データ基盤の課題 新データ基盤での取り組み
  34. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 今後の展望 組織体制面での挑戦 ➔ 新たに発足した Data Management

    Group との協力により, 中長期的なデータマネジメント・ガバナンスの体制を強化 技術的な挑戦 ➔ データリネージを構築し, クエリ改修時や障害時に影響範囲がわかりやすい世 界に ➔ 開発環境でのデータバリデーション機構を整備し, データ不整合を未然に防ぐ 仕組みを整え, 更なるデータの信頼性向上 (dbt / Dataform も検討)
  35. #Rtech スタディサプリでの BigQuery 移管と実践的活用術 ご清聴ありがとうございました