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

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

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 移管と実践的活用術 ご清聴ありがとうございました