1 毎月約500万本のクエリが投げられる BigQueryの運用とデータマネジメント #merpay_techtalk 2021/3/29 Shun Oshidari Data Management Team / Data Manager 動画のアーカイブはこちらです https://www.youtube.com/watch?v=qrxkqlo4m0c 

2 データマネージャー創設の背景 本日お話しすること 全社レベルのデータ活用が進んだ先で直面した新たな課題 マイクロサービス時代に起こるデータの「細分化」と「サイロ化」 02 03 01 

3 データマネージャー創設の背景 

4 ソフトウェア業界における役割細分化の流れ ソフトウェア業界ではここ数年、多くの専門職が新たに生まれてきた。 サーバーサイド
 フロントエンド
 iOS
 Android
 Unity
 React
 Vue.js
 Flutter
 AWS
 GCP
 テスト
 セキュリティ
 LAMPがあれば なんとかなるよ 

5 役割細分化の流れは技術職に限らない 細分化の流れは非技術職でも起こってきた。 ● プロダクトオーナー ● カスタマーサクセス ● プログラムマネージャー ● テクニカルプロジェクトマネージャー ● プロダクトマーケティングマネージャー ● UXデザイナー/UXリサーチャー ...etc. 

6 同じことがデータの現場でも起こり始めている データエンジニアやデータアナリストもこの 10数年で確立した新しい職種。 サーバーサイド
 プロダクト
 マーケ担当
 インフラ
 毎回やるの しんどいな CSV ください 自分で クエリ叩くか 昔は...
 データアナリスト
 データエンジニア
 現在は
 

7 データ関連職種の細分化はこれからもっと起こる データ周辺の専門職はまだまだ不足しており、新たなロールを定義していく必要がある。 データアナリスト
 データエンジニア
 ◯◯◯◯？
 ● より多くのデータを、より速く、よ り確実に届けたい ● 届けたデータをその後どのよう に使うかはあまり詳しくない 複雑なデータを整理し あらゆるデータ利用者が 「信頼できる」データを「最速 で使える」環境を 作ってあげたい ● データを使った分析や意思決定 のイテレーションをなるべく速く 回したい ● そのために使える技術的手段 はあまり詳しくない（SQLやgit はわかる） 

8 データガバナンスは誰の仕事か？ データガバナンスは技術的問題ではないが、技術的理解がなければできない仕事。 ● ガバナンス体制の構築は データエンジニアが解決すべき「技術的課題」か？ ● 一定の技術的な理解がなければ、体制の妥当性は評価できない 法務やコンプライアンスチームだけでは構築できない データの周辺には多くの”隙間”が生まれており、この隙間を埋める新たなロールが求められる。 

9 “隙間”の例 ① 拡大するデータ利用の管理 これまでは「データ規模の拡大」にどう対処するかが課題であったが、 これからは「データ利用の拡大」への対処が必要。 データ 規模 データ 利用 データ 規模 データ 利用 データ 規模 データ 利用 過去 現在 これから 

10 “隙間”の例 ① 拡大するデータ利用の管理 弊社におけるデータ利用規模の例（BigQuery） ● クエリ発行元ユーザーアカウント → 約 700〜800 アカウント ● クエリ発行元システムアカウント → 約 300〜400 アカウント ● クエリ発行元 GCP プロジェクト → 約 200〜300 プロジェクト ● クエリ発行数 → 約 500 万本/月 ● 処理データ量 → 約 500~800 ペタバイト/月 データ基盤に対する負荷は 「掛け算」で増えている 

11 “隙間”の例 ① 拡大するデータ利用の管理 データ基盤に対する負荷は「掛け算」の形で増えているが、問題の本質は技術ではなく、 利用側の急拡大をどうマネジメントしていくかにある。 データ 規模 データ 利用 技術の問題 運用・管理 の問題 

12 “隙間”の例 ② データの「細分化」と「サイロ化」 データの「細分化」と「サイロ化」は似ているようだが、微妙に異なる別の問題。 データの「細分化」 ● これまで1枚のテーブルに保存されていたような情報が複数のテーブルに分割して保 存され、結果として使用されるテーブル数が増えている変化 データの「サイロ化」 ● 主にマイクロサービスアーキテクチャにおいて、同じ情報を各マイクロサービスで異な る持ち方をすることで、データの仕様がばらついてしまう変化 

13 データの「細分化」 1枚のテーブルに色々な状態を詰め込み書き換えていく設計から、イミュータブルデータモデリングに代表さ れるような、情報を適切に分解し、変更を記録していく設計が増えてきた結果、作成されるテーブル数は大 きく増加した。 FROM ... LEFT OUTER JOIN LEFT OUTER JOIN LEFT OUTER JOIN LEFT OUTER JOIN LEFT OUTER JOIN アナリスト
 どこに何があるか 全くわからん... カンマ区切り もあるよ コードA
 コードB
 コードC
 コードD
 更新
 更新
 更新
 更新
 

14 データの「サイロ化」 マイクロサービスアーキテクチャでは、各マイクロサービス内でのデータの持ち方は自由に決めることがで きる。 各マイクロサービスは 自サービスに閉じた開発 というメリットを享受できるが、 
 データを横断して使う データ利用者はこの差分を作業のどこかで受け止めなければならない。 
 A ユーザーIDは 「user_id」 だね マイクロサービス
 B ユーザーIDは 「UserId」だ ね マイクロサービス
 C ユーザーID じゃなくて メンバーID だよ マイクロサービス
 D その時刻は 2021-03-29 19:00:00 UTC だね マイクロサービス
 E その時刻は 1617044400 だね マイクロサービス
 データ利用者
 どうしよう... 

15 隙間を埋めるデータマネジメントの取り組み 

16 拡大するデータ利用に対する取り組み ● Reservations（割り当て）によるリソース割り当ての 優先度制御
 ● INFORMATION_SCHEMA を活用したリソース消費の モニタリングと分析
 ● 99%のクエリを守る アドホッククエリ専用環境の構築 
 

17 Reservations によるリソース割り当ての優先度制御 BigQuery Reservationsは、定額で購入したスロットを複数の枠に分割し、リソース配分に優先順位をつけ てワークロードを管理するための仕組み。 Reservations 分割検討時の論点の例 ● カンパニーやリージョン ● お客様への影響の近さ ● 後続処理への影響範囲、復旧の容易さ ● 投げられるクエリの品質がどの程度管理されているか ● アイドルスロットシェアリングの機構を前提とした弾力的なリソース配分 

18 INFORMATION_SCHEMAを活用したリソース消費のモニタリングと分析 INFORMATION_SCHEMAに入っているJOBS_BY_系のシステムテーブルからは、実行されているクエリ やリソースの消費具合について多くのことがわかる。 JOBS_BY_ORGANIZATION ● 組織内のすべてのGCPプロジェクトのクエリログを確認するときに使用 ● SQL文は含まれていない JOBS_BY_PROJECT ● SQL文を見る必要があるときに使用 ● プロジェクトごとに権限を取得する必要がある JOBS_TIMELINE_BY_xxx ● ジョブの状態が1秒単位で入っているすごいテーブル ● スロットの消費状況をタイムラインで可視化するときに使用 ● ただしログが数時間遅れることもあるので、 信用しすぎてはいけない 

19 INFORMATION_SCHEMAを活用したリソース消費のモニタリングと分析 INFORMATION_SCHEMAに入っているJOBS_BY_系のシステムテーブルからは、実行されているクエリ やリソースの消費具合について多くのことがわかる。 JOBS_BY_系テーブルからわかるその他の例 ● Lookerからのクエリは、SQL文の中に「誰が、どのダッシュボードを開いたときのクエリなの か」といったメタ情報が埋め込まれているため、これを見ればクエリからLookerの利用まで たどることができる ● JOBS_BY_にはそのクエリで「参照されたテーブル」も入っているため、「このテーブルが参 照されるときはクエリが重くなりがち」というような調査が可能 ● 実行計画をパースすると、そのクエリで「参照されたカラム」まで特定できるため、あるカラム の名前を変えたい、カラムの定義を変更したいといったときに、「そのカラムを誰がどのくら い使っているのか」を調査することもできる 

20 余談：非効率な書き方をしたクエリは悪いのか？ ● 最近の BigQuery は数百テラバイトの処理も1分かからず終えてしまうことがある
 （ログ調査により判明）
 ● 「数百テラバイト」というサイズ自体は、BigQuery にとって大したことなくなってきた？
 ● BigQuery が「大したことない」と思っているなら、我々がへんに気を使わずとも、BigQuery さ んに働いてもらえばよいのではないか
 （特に定額利用の場合、休ませたところでお金は返ってこないので）
 ● 「富豪的」な考え方への転換は多くの分野でこれまでも何度も起こってきた
 ● BigQuery以降というのは、我々も富豪になれるタイミングなのでは！？
 

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

22 99%のクエリを守るアドホッククエリ専用環境の構築 調査の結果、「ユーザーが実行するアドホッククエリの 99%は5分以内に完了している」ということが判明。 5 分以内に終わるクエリ専用の環境があれば、 99%のクエリを守ることができる。 アドホッククエリ専用環境の特徴 
 ● 5分以上 実行されるクエリは即座にキャンセルされる 
 ● 1つのアカウントで 同時に3本以上 実行されたクエリは全てキャンセルされる 
 ● 上記ルールは バッチクエリ には適用されない
 ● Reservations により、この環境へは 比較的潤沢なリソース が割り当てられている 
 

23 データの「細分化」と「サイロ化」に対する取り組み 複雑なデータを中間テーブル化していった結果できあがったのは、複雑化した中間テーブル群。ただ中間 テーブルを増やすのではなく、もっと統制の取れたアプローチで複雑性と向き合う必要がある。 中間テーブルに埋め込まれていたロジックの例
 ● 分析観点で 本質的に必要 なデータの加工や変換 ● コード値を ラベルへ変換 するもの ● カラム名を そろえるだけ のもの ● データの型を そろえるだけ のもの ● 間違った元データを 強引に修正 するもの 

24 データの「細分化」と「サイロ化」に対する取り組み あらゆるデータ加工処理を 1つの中間テーブルに押し込めてしまうのではなく、処理の性質を種類に分け、 「層」を作って管理することで、見通しを良くすることができる。 インターフェース層
 ● カラム名やタイムゾーンの統一 や、コード値のラベル変換など、 元テーブルの形（レコードの単 位）はそのままに、元テーブルを 統一的な仕様で使いやすくした 薄いビュー ● GROUP BYやJOINはしない コンポーネント層
 ● 「ユーザー」や「決済」などの要素 ごとに必要な加工ロジックを詰め 込んだ比較的小さなテーブル群 ● GROUP BYやJOINも可能 ● 複雑なコンポーネントは分割し、 1つ1つのサイズは小さく保つ データモデル層
 ● 主要なコンポーネント同士を結 合したテーブル（リレーションのよ うな関係） ● STRUCT型かREPEATED型に 変換して1枚のテーブルとして実 体化する まだ名前のない層
 ● ダッシュボード等で必要な表面 的な加工処理を受け持つ ● データの本質とは関係ないた め、データモデル層からは切り離 す ● LookerなどのBIツール内に実 装してもよい 元テーブル
 

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

26 まとめ 我々の周辺では日々新たな職種が生まれています。 それはデータ関連の職種も例外ではなく、データ活用にはもっとたくさんの役割が必要です。 データ規模の拡大のあとには、データ利用の拡大がやってきます。 そしてソフトウェア開発手法の進化にともない、データはますます複雑化していますが、 我々はそれに十分に対応できていません。 データ構築プロセスは宣言的に記述し、データは「ビルド」する時代になりつつあります。 データ活用の発展を止めないためにも、 これからも日々新しいやり方を模索し続ける必要があります。 ぜひ一度議論したいという方がいましたら、我々一同、ご連絡をお待ちしております。