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

by Recruit

Slide 1

Slide 1 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術スタディサプリでの BigQuery 移管と実践的活用術橘高允伸スタディサプリのデータ基盤を支える技術 2022 ーRECRUIT TECH MEET UP #3ー

Slide 2

Slide 2 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術橘高允伸きったかまさのぶデータエンジニア 2019年リクルート新卒入社 ➔ データ基盤の開発・運用 ➔ データ基盤移管プロジェクト ➔ データプロダクトのバックエンド

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の概要と課題 01

Slide 5

Slide 5 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の構成 Master data Server Side Log Client Side Log 小中高 / ENGLISH TD SDK Kinesis Lambda 分析レポーティング (Presto/Hive)

Slide 6

Slide 6 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の課題 1. 集計クエリのリソースの枯渇 2. 一貫性のないデータセット・権限管理 3. データの不整合 4. クエリの中長期のメンテナンスが困難

Slide 7

Slide 7 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 1. 集計クエリのリソースの枯渇 ➔ 利用者・使用用途が拡大したことで，クエリの渋滞が発生 ➔ 基幹集計が慢性的に遅延 ➔ アドホックな分析でもアナリストに待ち時間が増加旧データ基盤の課題

Slide 8

Slide 8 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 2. 一貫性のないデータセット・権限管理データセット管理の問題 ➔ 同データセット内にレイヤーの異なるテーブルが混在 (マスタ/ログ/データマート) ◆ これにより，replace / snapshot / append 形式のテーブルが混在 ➔ クエリを書く際にパーティションの有無・指定方法を気にする必要がある権限管理の問題 ➔ 権限管理体制が手動でアカウントごとの作業（Admin の乱立） ➔ どのチームがどのデータセットにどのようなアクセス権限を持つということを管理・把握できていなかった旧データ基盤の課題

Slide 9

Slide 9 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 3. データの不整合 ➔ レコードの重複の発生 ◆ 誰かが気付くまで発覚しないので放置されてしまう... ➔ 同様のロジックのCASE文などがクエリ間に散在し，改修漏れが発生 ◆ 微妙に異なるCASE文が存在するが，意図的なのか判断が難しい ➔ ソースのDBに最新のデータが入っていないことでの不整合 ◆ 一部のデータが古い状態でも集計が進んでしまう状態旧データ基盤の課題

Slide 10

Slide 10 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 4. クエリの中長期のメンテナンスが困難 ➔ 小中高，English 合わせて300個以上もの基幹集計クエリ ➔ メンテされていないクエリの存在 ◆ スケジュールクエリや基幹集計の中に不要なものがあるが，判断が難しく迂闊に止められない ➔ クエリやテーブルの使用用途が引き継がれにくい ➔ データ不整合が発生した際の責任の所在が不明瞭旧データ基盤の課題

Slide 11

Slide 11 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術新データ基盤のDWHでの取り組み 02

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 BigQueryへ移管し，集計リソースを強化課題1: 集計クエリのリソースの枯渇 BigQuery 導入での効果 ➔ DWH を BigQuery へ移管したことで，集計処理が高速化 ◆ 例）「課金ログを元に学習者の会員ステータスを管理するテーブル」の集計時間が 60 min (hive) → 1 min (BigQuery) に短縮 ➔ BigQuery を従量課金（オンデマンド料金）で利用しているためコストを意識してクエリを書くように

Slide 15

Slide 15 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術リソースのモニタリング整備 ➔ Looker でモニタリング用ダッシュボードを作成 ◆ BigQuery のクエリ料金 (日次・月次) ◆ BigQuery のストレージ料金 (日次・月次) ◆ BigQuery のクエリスキャン量など課題1: 集計クエリのリソースの枯渇

Slide 16

Slide 16 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術リソースの分割 ➔ 「サービスアカウントによる基幹集計」と「一般ユーザーによるアドホック集計」の BigQuery の実行プロジェクトを分離 ◆ 旧データ基盤では同じリソースで基幹集計とアドホック集計が実行されており，互いにリソースを食い合っていた ➔ 1日のクエリスキャン量の制限 (quota) を設定し，利用料金爆発事故を防止 ◆ Project ごとにデータ処理量の制限を設定 ◆ この制限を設定する前に1日で数万円のクエリを実行してしまったことがある... 課題1: 集計クエリのリソースの枯渇

Slide 17

Slide 17 text

Slide 18

Slide 18 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 ➔ プロジェクト・データセットでより細かい単位で分割 ➔ パーティションの指定方法・有無がより直感的にデータセットのレイヤーの再設計課題2: 一貫性のないデータセット・権限管理プロジェクトデータの種類パーティション方式マスタ用 mongoDB / postgreSQL / spreadsheet など日次スナップショットログ用サーバーサイド/ クライアントサイド/ SBPS ログなど差分のみパーティションデータマート用ユースケースごとに扱いやすくしたテーブルを配置日次スナップショット /差分レポート用 Looker など外部に提供するデータの置き場基本パーティションなしサンドボックス用ユーザーが自由に使って良い場所 - ※パーティションがあるテーブルは，パーティションフィルタ必須

Slide 19

Slide 19 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術アカウントの権限管理の見直しアカウントの権限管理 ➔ チーム単位で権限管理 (Terraform 管理) ➔ 管理者以外はサンドボックス環境へのみ書き込み権限を持つ Looker の権限管理 ➔ 本番 Looker 環境からのデータ参照はレポート用プロジェクトのみ可能 ◆ GitHub 管理の基幹集計クエリで作成されたテーブルのみ参照可能 ◆ 基幹集計処理の管理外で新たな指標定義が生まれることを防止課題2: 一貫性のないデータセット・権限管理

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

#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

Slide 23

Slide 23 text

#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

Slide 24

Slide 24 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術 UDFで処理を共通化し，対応漏れを防止課題3: データの不整合 ➔ 同じロジックの CASE 文が散在し，改修漏れが発生することがあった ➔ クエリ間で散らばっていた CASE 文などを共通の UDF で管理 ◆ BigQuery にない MAX_BY なども javascript で UDF を作成 ➔ UDF は GitHub で管理し，PR マージ時に CloudBuild で BigQuery へ反映 create trigger push Cloud Build BigQuery GitHub

Slide 25

Slide 25 text

Slide 26

Slide 26 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術移管タイミングでのクエリの削減 ➔ 新データ基盤に移管するタイミングで移管対象クエリを選定 ➔ 使用されていないクエリの廃止 ◆ Looker API を使用して参照されていないテーブルの確認 ◆ Looker 参照がないテーブルは利用者の存在を確認して廃止 ➔ English の基幹集計だけでも不要なクエリが約40%存在し，削減できた課題4: クエリの中長期のメンテナンスが困難

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術クエリのGit管理とCIでのテスト ➔ PR 時に CI で SQL を dryrun で実行し，クエリテスト ◆ シンタックスエラー ◆ テーブル/カラム名のタイポ ◆ パーティションの指定忘れ (パーティションフィルタ必須テーブル) などをチェック課題4: クエリの中長期のメンテナンスが困難 dryrun trigger PR Cloud Build BigQuery

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

#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)

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術まとめ 03

Slide 33

Slide 33 text

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

#Rtech スタディサプリでの BigQuery 移管と実践的活用術ご清聴ありがとうございました