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

毎月約500万本のクエリが投げられる BigQuery の運用とデータマネジメント / Big...

毎月約500万本のクエリが投げられる BigQuery の運用とデータマネジメント / BigQuery and Data Management

Shun Oshidari

March 29, 2021
Tweet

More Decks by Shun Oshidari

Other Decks in Programming

Transcript

  1. 5 役割細分化の流れは技術職に限らない 細分化の流れは非技術職でも起こってきた。 • プロダクトオーナー • カスタマーサクセス • プログラムマネージャー •

    テクニカルプロジェクトマネージャー • プロダクトマーケティングマネージャー • UXデザイナー/UXリサーチャー ...etc.
  2. 7 データ関連職種の細分化はこれからもっと起こる データ周辺の専門職はまだまだ不足しており、新たなロールを定義していく必要がある。 データアナリスト
 データエンジニア
 ◯◯◯◯?
 • より多くのデータを、より速く、よ り確実に届けたい •

    届けたデータをその後どのよう に使うかはあまり詳しくない 複雑なデータを整理し あらゆるデータ利用者が 「信頼できる」データを「最速 で使える」環境を 作ってあげたい • データを使った分析や意思決定 のイテレーションをなるべく速く 回したい • そのために使える技術的手段 はあまり詳しくない(SQLやgit はわかる)
  3. 10 “隙間”の例 ① 拡大するデータ利用の管理 弊社におけるデータ利用規模の例(BigQuery) • クエリ発行元ユーザーアカウント → 約 700〜800

    アカウント • クエリ発行元システムアカウント → 約 300〜400 アカウント • クエリ発行元 GCP プロジェクト → 約 200〜300 プロジェクト • クエリ発行数 → 約 500 万本/月 • 処理データ量 → 約 500~800 ペタバイト/月 データ基盤に対する負荷は 「掛け算」で増えている
  4. 13 データの「細分化」 1枚のテーブルに色々な状態を詰め込み書き換えていく設計から、イミュータブルデータモデリングに代表さ れるような、情報を適切に分解し、変更を記録していく設計が増えてきた結果、作成されるテーブル数は大 きく増加した。 FROM ... LEFT OUTER JOIN

    LEFT OUTER JOIN LEFT OUTER JOIN LEFT OUTER JOIN LEFT OUTER JOIN アナリスト
 どこに何があるか 全くわからん... カンマ区切り もあるよ コードA
 コードB
 コードC
 コードD
 更新
 更新
 更新
 更新

  5. 14 データの「サイロ化」 マイクロサービスアーキテクチャでは、各マイクロサービス内でのデータの持ち方は自由に決めることがで きる。 各マイクロサービスは 自サービスに閉じた開発 というメリットを享受できるが、 
 データを横断して使う データ利用者はこの差分を作業のどこかで受け止めなければならない。

    
 A ユーザーIDは 「user_id」 だね マイクロサービス
 B ユーザーIDは 「UserId」だ ね マイクロサービス
 C ユーザーID じゃなくて メンバーID だよ マイクロサービス
 D その時刻は 2021-03-29 19:00:00 UTC だね マイクロサービス
 E その時刻は 1617044400 だね マイクロサービス
 データ利用者
 どうしよう...
  6. 17 Reservations によるリソース割り当ての優先度制御 BigQuery Reservationsは、定額で購入したスロットを複数の枠に分割し、リソース配分に優先順位をつけ てワークロードを管理するための仕組み。 Reservations 分割検討時の論点の例 • カンパニーやリージョン

    • お客様への影響の近さ • 後続処理への影響範囲、復旧の容易さ • 投げられるクエリの品質がどの程度管理されているか • アイドルスロットシェアリングの機構を前提とした弾力的なリソース配分
  7. 18 INFORMATION_SCHEMAを活用したリソース消費のモニタリングと分析 INFORMATION_SCHEMAに入っているJOBS_BY_系のシステムテーブルからは、実行されているクエリ やリソースの消費具合について多くのことがわかる。 JOBS_BY_ORGANIZATION • 組織内のすべてのGCPプロジェクトのクエリログを確認するときに使用 • SQL文は含まれていない JOBS_BY_PROJECT

    • SQL文を見る必要があるときに使用 • プロジェクトごとに権限を取得する必要がある JOBS_TIMELINE_BY_xxx • ジョブの状態が1秒単位で入っているすごいテーブル • スロットの消費状況をタイムラインで可視化するときに使用 • ただしログが数時間遅れることもあるので、 信用しすぎてはいけない
  8. 19 INFORMATION_SCHEMAを活用したリソース消費のモニタリングと分析 INFORMATION_SCHEMAに入っているJOBS_BY_系のシステムテーブルからは、実行されているクエリ やリソースの消費具合について多くのことがわかる。 JOBS_BY_系テーブルからわかるその他の例 • Lookerからのクエリは、SQL文の中に「誰が、どのダッシュボードを開いたときのクエリなの か」といったメタ情報が埋め込まれているため、これを見ればクエリからLookerの利用まで たどることができる •

    JOBS_BY_にはそのクエリで「参照されたテーブル」も入っているため、「このテーブルが参 照されるときはクエリが重くなりがち」というような調査が可能 • 実行計画をパースすると、そのクエリで「参照されたカラム」まで特定できるため、あるカラム の名前を変えたい、カラムの定義を変更したいといったときに、「そのカラムを誰がどのくら い使っているのか」を調査することもできる
  9. 20 余談:非効率な書き方をしたクエリは悪いのか? • 最近の BigQuery は数百テラバイトの処理も1分かからず終えてしまうことがある
 (ログ調査により判明)
 • 「数百テラバイト」というサイズ自体は、BigQuery にとって大したことなくなってきた?


    • BigQuery が「大したことない」と思っているなら、我々がへんに気を使わずとも、BigQuery さ んに働いてもらえばよいのではないか
 (特に定額利用の場合、休ませたところでお金は返ってこないので)
 • 「富豪的」な考え方への転換は多くの分野でこれまでも何度も起こってきた
 • BigQuery以降というのは、我々も富豪になれるタイミングなのでは!?

  10. 21 “ダメ”なクエリとは
 非効率なクエリ
 他のユーザーに迷惑をかける クエリ
 他のユーザーに迷惑をかけるクエリとは? 
 大量のスロットを消費するが、短時間で処理が完了し、
 すぐにスロットを解放する クエリ


    大量(or そこまで多くとも)のスロットを 長時間使用し続ける クエリ
 99%のクエリを守るアドホッククエリ専用環境の構築 富豪的な時代が到来しつつあるとはいえ、現時点ではまだ一定の治安維持が必要。しかし非効率であるこ とが必ずしも悪いことではなくなった今、では一体「ダメなクエリ」とはどんなクエリなのか? 一定量のスロットを購入する定額プランでは、使えるスロットはどんどん使ってさっさと処理を終えてくれた方がありがたい 

  11. 22 99%のクエリを守るアドホッククエリ専用環境の構築 調査の結果、「ユーザーが実行するアドホッククエリの 99%は5分以内に完了している」ということが判明。 5 分以内に終わるクエリ専用の環境があれば、 99%のクエリを守ることができる。 アドホッククエリ専用環境の特徴 
 •

    5分以上 実行されるクエリは即座にキャンセルされる 
 • 1つのアカウントで 同時に3本以上 実行されたクエリは全てキャンセルされる 
 • 上記ルールは バッチクエリ には適用されない
 • Reservations により、この環境へは 比較的潤沢なリソース が割り当てられている 

  12. 24 データの「細分化」と「サイロ化」に対する取り組み あらゆるデータ加工処理を 1つの中間テーブルに押し込めてしまうのではなく、処理の性質を種類に分け、 「層」を作って管理することで、見通しを良くすることができる。 インターフェース層
 • カラム名やタイムゾーンの統一 や、コード値のラベル変換など、 元テーブルの形(レコードの単

    位)はそのままに、元テーブルを 統一的な仕様で使いやすくした 薄いビュー • GROUP BYやJOINはしない コンポーネント層
 • 「ユーザー」や「決済」などの要素 ごとに必要な加工ロジックを詰め 込んだ比較的小さなテーブル群 • GROUP BYやJOINも可能 • 複雑なコンポーネントは分割し、 1つ1つのサイズは小さく保つ データモデル層
 • 主要なコンポーネント同士を結 合したテーブル(リレーションのよ うな関係) • STRUCT型かREPEATED型に 変換して1枚のテーブルとして実 体化する まだ名前のない層
 • ダッシュボード等で必要な表面 的な加工処理を受け持つ • データの本質とは関係ないた め、データモデル層からは切り離 す • LookerなどのBIツール内に実 装してもよい 元テーブル

  13. 25 dbt (data build tool) の導入 前頁のコンポーネント層は dbt がなければ実現できなかった。 すでに100を超える中間テーブルが

    dbt 管理化に移管されている。 • SQL を解析し 依存関係(順序)を理解 したうえで、
 コマンド一発 で、すべて自動的に構築してくれる 
 • データウェアハウスのデータ構造を 
 宣言的に扱うことができる ようになる
 • カラム説明などの メタデータ を記述できる
 • データリネージュを自動的に可視化してくれる 
 • テストを書く ことができる
 データウェアハウスは 宣言的に設計 し、コマンド一つで「ビルド」する 時代になりつつある。 
 https://docs.getdbt.com/docs/building-a-dbt-project/documentation