2022/01/27_スタディサプリのデータ基盤を支える技術 2022 －RECRUIT TECH MEET UP #3－での、橘高の講演資料になります
#Rtech スタディサプリでの BigQuery 移管と実践的活用術スタディサプリでの BigQuery 移管と実践的活用術橘高 允伸スタディサプリのデータ基盤を支える技術 2022 ーRECRUIT TECH MEET UP #3ー
View Slide
#Rtech スタディサプリでの BigQuery 移管と実践的活用術橘高 允伸きったか まさのぶデータエンジニア2019年リクルート新卒入社➔ データ基盤の開発・運用➔ データ基盤移管プロジェクト➔ データプロダクトのバックエンド
#Rtech スタディサプリでの BigQuery 移管と実践的活用術Agenda | 010203旧データ基盤の概要と課題新データ基盤のDWHでの取り組みまとめ
#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の概要と課題01
#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の構成Master dataServer Side LogClient Side Log小中高 / ENGLISHTD SDKKinesis Lambda分析レポーティング(Presto/Hive)
#Rtech スタディサプリでの BigQuery 移管と実践的活用術旧データ基盤の課題1. 集計クエリのリソースの枯渇2. 一貫性のないデータセット・権限管理3. データの不整合4. クエリの中長期のメンテナンスが困難
#Rtech スタディサプリでの BigQuery 移管と実践的活用術1. 集計クエリのリソースの枯渇➔ 利用者・使用用途が拡大したことで，クエリの渋滞が発生➔ 基幹集計が慢性的に遅延➔ アドホックな分析でもアナリストに待ち時間が増加旧データ基盤の課題
#Rtech スタディサプリでの BigQuery 移管と実践的活用術2. 一貫性のないデータセット・権限管理データセット管理の問題➔ 同データセット内にレイヤーの異なるテーブルが混在 (マスタ/ログ/データマート)◆ これにより，replace / snapshot / append 形式のテーブルが混在➔ クエリを書く際にパーティションの有無・指定方法を気にする必要がある権限管理の問題➔ 権限管理体制が手動でアカウントごとの作業（Admin の乱立）➔ どのチームがどのデータセットにどのようなアクセス権限を持つということを管理・把握できていなかった旧データ基盤の課題
#Rtech スタディサプリでの BigQuery 移管と実践的活用術3. データの不整合➔ レコードの重複の発生◆ 誰かが気付くまで発覚しないので放置されてしまう...➔ 同様のロジックのCASE文などがクエリ間に散在し，改修漏れが発生◆ 微妙に異なるCASE文が存在するが，意図的なのか判断が難しい➔ ソースのDBに最新のデータが入っていないことでの不整合◆ 一部のデータが古い状態でも集計が進んでしまう状態旧データ基盤の課題
#Rtech スタディサプリでの BigQuery 移管と実践的活用術4. クエリの中長期のメンテナンスが困難➔ 小中高，English 合わせて300個以上もの基幹集計クエリ➔ メンテされていないクエリの存在◆ スケジュールクエリや基幹集計の中に不要なものがあるが，判断が難しく迂闊に止められない➔ クエリやテーブルの使用用途が引き継がれにくい➔ データ不整合が発生した際の責任の所在が不明瞭旧データ基盤の課題
#Rtech スタディサプリでの BigQuery 移管と実践的活用術新データ基盤のDWHでの取り組み02
#Rtech スタディサプリでの BigQuery 移管と実践的活用術課題と取り組みのまとめ集計クエリのリソースの枯渇一貫性のないデータセット・権限管理データの不整合クエリの中長期のメンテナンスが困難● BigQueryへ移管し，集計リソースを強化● リソースのモニタリング整備● リソースの分割● データセットのレイヤーの再設計● アカウントの権限管理の見直し● データ基盤移管時のデータ検証作業● 日次集計のデータバリデーションを整備● UDFで処理を共通化し，対応漏れを防止● クエリのGit管理とCIでのテスト● Data Catalogを導入しデータ・マネジメント強化旧データ基盤の課題 新データ基盤での取り組み
#Rtech スタディサプリでの BigQuery 移管と実践的活用術BigQueryへ移管し，集計リソースを強化課題1: 集計クエリのリソースの枯渇BigQuery 導入での効果➔ DWH を BigQuery へ移管したことで，集計処理が高速化◆ 例）「課金ログを元に学習者の会員ステータスを管理するテーブル」の集計時間が 60 min (hive) → 1 min (BigQuery) に短縮➔ BigQuery を従量課金（オンデマンド料金）で利用しているためコストを意識してクエリを書くように
#Rtech スタディサプリでの BigQuery 移管と実践的活用術リソースのモニタリング整備➔ Looker でモニタリング用ダッシュボードを作成◆ BigQuery のクエリ料金 (日次・月次)◆ BigQuery のストレージ料金 (日次・月次)◆ BigQuery のクエリスキャン量 など課題1: 集計クエリのリソースの枯渇
#Rtech スタディサプリでの BigQuery 移管と実践的活用術リソースの分割➔ 「サービスアカウントによる基幹集計」と「一般ユーザーによるアドホック集計」の BigQuery の実行プロジェクトを分離◆ 旧データ基盤では同じリソースで基幹集計とアドホック集計が実行されており，互いにリソースを食い合っていた➔ 1日のクエリスキャン量の制限 (quota) を設定し，利用料金爆発事故を防止◆ Project ごとにデータ処理量の制限を設定◆ この制限を設定する前に1日で数万円のクエリを実行してしまったことがある...課題1: 集計クエリのリソースの枯渇
#Rtech スタディサプリでの BigQuery 移管と実践的活用術➔ プロジェクト・データセットでより細かい単位で分割➔ パーティションの指定方法・有無がより直感的にデータセットのレイヤーの再設計課題2: 一貫性のないデータセット・権限管理プロジェクト データの種類 パーティション方式マスタ用 mongoDB / postgreSQL / spreadsheet など 日次スナップショットログ用 サーバーサイド/ クライアントサイド/ SBPS ログなど 差分のみパーティションデータマート用 ユースケースごとに扱いやすくしたテーブルを配置 日次スナップショット /差分レポート用 Looker など外部に提供するデータの置き場 基本パーティションなしサンドボックス用 ユーザーが自由に使って良い場所 -※パーティションがあるテーブルは，パーティションフィルタ必須
#Rtech スタディサプリでの BigQuery 移管と実践的活用術アカウントの権限管理の見直しアカウントの権限管理➔ チーム単位で権限管理 (Terraform 管理)➔ 管理者以外はサンドボックス環境へのみ書き込み権限を持つLooker の権限管理➔ 本番 Looker 環境からのデータ参照はレポート用プロジェクトのみ可能◆ GitHub 管理の基幹集計クエリで作成されたテーブルのみ参照可能◆ 基幹集計処理の管理外で新たな指標定義が生まれることを防止課題2: 一貫性のないデータセット・権限管理
#Rtech スタディサプリでの BigQuery 移管と実践的活用術データ基盤移管時のデータ検証作業➔ 旧データ基盤の集計テーブルを BigQuery にもコピーし，差分検証実施◆ 旧データ基盤も並行運用し，検証で問題がないテーブルから移管➔ 差分の原因を精査することで旧データ基盤のバグも洗い出し◆ レコードの重複や不正な MAX_BY の使用で結果がランダムになってしまっている，などのバグが発覚◆ 移管のタイミングで修正課題3: データの不整合
#Rtech スタディサプリでの BigQuery 移管と実践的活用術データ基盤移管時のデータ検証作業のTips➔ 細かい差分を確認するのに，`except distinct` が便利➔ このクエリを使用することでテーブル td には存在するが bq には存在しないレコード，とその逆のレコードがわかる課題3: データの不整合WITHin_td AS (SELECT * FROM tdEXCEPT DISTINCTSELECT * FROM bq),in_bq AS (SELECT * FROM bqEXCEPT DISTINCTSELECT * from td)SELECT *, 'td' AS exist_in FROM in_tdUNION ALLSELECT *, 'bq' AS exist_in FROM in_bq
#Rtech スタディサプリでの BigQuery 移管と実践的活用術日次集計のデータバリデーションを整備➔ table_name.yaml ファイルでテーブルのバリデーションタスクを管理◆ この yaml を元に Composer でタスクを自動生成◆ ASSERT クエリでバリデーション➔ データバリデーションの種類◆ データが存在するか◆ レコード数が単調増加しているか◆ ユニーク性課題3: データの不整合- name: validate_records_existerror_message: 前日のパーティションがありません！query: |ASSERT(SELECT COUNT(1)FROM `project.dataset.table`WHERE _PARTITIONDATE = DATE("{{execution_date | localtz_to_date_str }}")) > 0
#Rtech スタディサプリでの BigQuery 移管と実践的活用術UDFで処理を共通化し，対応漏れを防止課題3: データの不整合➔ 同じロジックの CASE 文が散在し，改修漏れが発生することがあった➔ クエリ間で散らばっていた CASE 文などを共通の UDF で管理◆ BigQuery にない MAX_BY なども javascript で UDF を作成➔ UDF は GitHub で管理し，PR マージ時に CloudBuild でBigQuery へ反映createtriggerpushCloud Build BigQueryGitHub
#Rtech スタディサプリでの BigQuery 移管と実践的活用術移管タイミングでのクエリの削減➔ 新データ基盤に移管するタイミングで移管対象クエリを選定➔ 使用されていないクエリの廃止◆ Looker API を使用して参照されていないテーブルの確認◆ Looker 参照がないテーブルは利用者の存在を確認して廃止➔ English の基幹集計だけでも不要なクエリが約40%存在し，削減できた課題4: クエリの中長期のメンテナンスが困難
#Rtech スタディサプリでの BigQuery 移管と実践的活用術クエリのGit管理とCIでのテスト➔ 定常集計クエリは GitHub で一元管理 (旧データ基盤でも主要な集計クエリはGitHub 管理)◆ BigQuery のスケジューリング機能は使わない◆ 定期実行される SQL の GitHub 管理と PR でのレビュー◆ クエリ結果の任意の Google Sheets への保存も可能課題4: クエリの中長期のメンテナンスが困難SQL, yamlレビューSpreadsheetエンジニアアナリスト BigQueryCloud ComposerPRGitHubexportquerydeploy
#Rtech スタディサプリでの BigQuery 移管と実践的活用術クエリのGit管理とCIでのテスト➔ PR 時に CI で SQL を dryrun で実行し，クエリテスト◆ シンタックスエラー◆ テーブル/カラム名のタイポ◆ パーティションの指定忘れ (パーティションフィルタ必須テーブル)などをチェック課題4: クエリの中長期のメンテナンスが困難dryruntriggerPRCloud Build BigQuery
#Rtech スタディサプリでの BigQuery 移管と実践的活用術Data Catalogを導入しデータ・マネジメント強化➔ DMBOK2 に準拠した2種類のメタデータを Data Catalog に連携しデータ・マネジメントを強化課題4: クエリの中長期のメンテナンスが困難メタデータ種類 データの内容と利用の目的 データの例チーム(ビジネス)メタデータ● データの内容・状態やドメイン情報を登録● テーブルのドメイン情報を管理し、暗黙知を形式知化する● テーブル / カラムの説明● 利用ドメインの情報and more…オペレーショナルメタデータ● データの処理・アクセスに関する情報を登録● データの利用状況などを管理し、クエリの棚卸しを継続しやすい状態にする● ユーザー / Lookerの参照回数● 高頻度利用ユーザーand more…
#Rtech スタディサプリでの BigQuery 移管と実践的活用術チームメタデータ➔ yaml / json 形式で管理➔ PR マージ時に CloudBuild でData Catalog を更新課題4: クエリの中長期のメンテナンスが困難Data CatalogCloud Build[{"name": "id","type": "STRING","description": "ユーザーID""tag": {}},...]tags:- key1: value1- key2: value2description: |-### 概要xxxx### 注意点xxxx### ステークホルダー- @aaaaaaaGitHub編集 trigger 更新タグ情報とテーブル説明 (yaml)スキーマ・カラム説明 (json)
#Rtech スタディサプリでの BigQuery 移管と実践的活用術オペレーショナルメタデータ➔ Cloud Logging の BigQuery 監査ログを BigQuery へ export して使用➔ Composer で日次で集計し, Data Catalog のタグ情報を更新課題4: クエリの中長期のメンテナンスが困難BigQueryCloud ComposerCloud Logging Data Catalogexportテーブル作成・参照タグ情報更新
#Rtech スタディサプリでの BigQuery 移管と実践的活用術まとめ03
#Rtech スタディサプリでの BigQuery 移管と実践的活用術今後の展望組織体制面での挑戦➔ 新たに発足した Data Management Group との協力により，中長期的なデータマネジメント・ガバナンスの体制を強化技術的な挑戦➔ データリネージを構築し, クエリ改修時や障害時に影響範囲がわかりやすい世界に➔ 開発環境でのデータバリデーション機構を整備し, データ不整合を未然に防ぐ仕組みを整え, 更なるデータの信頼性向上 (dbt / Dataform も検討)
#Rtech スタディサプリでの BigQuery 移管と実践的活用術ご清聴ありがとうございました
recruitengineers
sbtechnight
foursue
tsubakimoto_s
miyakemito
sparty
oracle4engineer
1ftseabass
uzulla
you5805
teckl
stefafafan
bluesmoon
chrislema
edds
samlambert
quramy
deanohume
bkeepers
eitanlees
jacobian
malarkey
lara
jlugia