Nikkei のデータ分析基盤を支える BigQuery の ワークロード管理のこれまでとこれから

Transcript

1. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Nikkei のデータ分析基盤を支える BigQuery の ワークロード管理のこれまでとこれから 日本経済新聞社
 プラットフォーム推進室 
 データインテリジェンスグループ 
 
 玉越 敬典
 1

2. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 自己紹介 名前：玉越 敬典 (たまこし ひろのり） 所属：日本経済新聞社プラットフォーム推進室 職種：データエンジニア（Atlas チームリーダー） 職歴：Web 系エンジニア -> DB コンサル -> 日本経済新聞社 最近：二児の父として育児（8割) と仕事（2割) に奮闘中 2

3. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Agenda 3
 1 1 日経の データ分析基盤 Atlas 2 3 4 BQ コスト 最適化Tips BQ ワークロード 最適戦略 (これまで) BQ ワークロード 最適化戦略 (これから)
4. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 1. 日経のデータ分析基盤 Atlas 4
5. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Atlas はデジタルマーケティングのためのデータプラットフォーム（データ分析基盤） 日経電子版をはじめとした各種サービス上のお客様の行動ログデータを中心とした様々なデータを 収集、拡張、蓄積し、分析可視化および施策実行するための基盤を利用者に提供する。 5
 Atlas とは何か 拡張 施策実行 分析・可視化 蓄積 計測 連携 計測 拡張 データベース BIツール バッチ デジタルマーケティングのためのデータプラットフォーム Atlas MA ツール 施策発動 ストリーミング
6. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 主に4種類のデータを扱う 6
 Atlas が扱うデータ 行動ログデータ 記事メタデータ 会員属性データ 会員契約データ 日経電子版等の日経が運用するサービス上で お客様が行動したログに付帯情報を加えたもの 例「記事を読んだ」「ページを見た」+ (会員ID、記事ID) 日経電子版で提供される記事のメタデータ 例「タイトル」「見出し」「著者」「ジャンル」…etc デモグラフィック情報 （性別、年齢 ..etc）+ 業種、役職等の「働く人の属性」 会員ごとの日経電子版の料金プランデータ 有料個人プランの申し込み開始、終了日を含めた課金データ
7. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Atlasは大きく３種類のデータソースからデータを取得している 7
 Atlas のデータソース 各種サービスを利用した お客様の端末 各種日経内部システム 外部システム 行動ログの源泉。Atlas の計測の仕組みを 利用して、ブラウザ、iOS アプリ、Android ア プリ等から行動ログが連携される • 日経IDシステム • 課金管理システム • 法人契約管理システム ...etc • Twitter (X) • Google Search Console • Salesforce ...etc
8. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 歴史の長い CRM 基盤である IMS (Intergrated Marketing System) を介して Atlas へ連携される データベース(DWH) は 今後 BigQuery に移行予定 8
 各種日経内部システム連携データを統合するIMS IMS 各種日経内部システム • 日経IDシステム • 課金管理システム • 法人契約管理システム ...etc Atlas には個人情報は マスキングして連携される Redshift
9. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Atlas では利用者の利用用途に応じて3種類のデータストアとそれにアクセスするためのツールを用意 9
 利用用途に応じたデータストア Elasticsearch BigQuery SingleStore Redash Jupyterhub Vertex AI Kibana DataSquad (内製リアルタイム ダッシュボード App) BI ML Tool Datastore データ 反映時間 数秒 ~ 数分 1時間程度 1秒未満 利用用途 行動ログデータを 元にしたニアリアルタイム 簡易分析用 汎用データ分析用. Atlas が扱う全ての データを全期間蓄積する 記事の読者反応等を リアルタイムに ダッシュボードに表示する
10. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 2. BQ コスト最適化 Tips 10
11. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. • Compute の費用は Enterprise Edition で US リージョンの方が約 20 % 安い • Storage の費用は (ストレージの種類によるが) US リージョンの方が 1GB あたり 約 10 - 30% 程度安 い ◦ US リージョンは新機能やサービスが最も早くサポートされるというメリットもある 11
 TIPS1: US リージョンを利用する https://cloud.google.com/bigquery/pricing を参照して算出
12. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. • BigQuery へのストリーミング処理には可能な限り Storage WRITE API を利用する方が安く、 高スループットで高機能(Exactly Once セマンティクスもサポート) ◦ 重複データを気にしなかったり前処理等のデータ加工が不要であれば PubSub BigQuery Subscription のほうがさらに安い ($ 50 per TiB) 12
 TIPS2: ストリーミング処理で tables.insertAll を使わない 2 TB まで無料
13. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Storage Write API ではなく table.insertAll が使用されて高額になるケースがある • 例えば Fastly Compute のリアルタイムログストリーミングで BigQuery を選択する場合、 table.insertAll が使用されるため高額になる ◦ 要件次第ではあるが我々の場合 BigQuery を選択するのをやめて、 GCS 転送 + 外部表参照に変えてコストを抑えた 13
 外部連携サービスから BigQuery へストリーミング処理する時の注意点 Cloud Storage リアルタイムログストリーミング Cloud Storage 外部表 外部表 LOG LOG
14. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 3. BQ ワークロード最適化戦略(これまで) 14
15. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 様々なクライアントから実行されるアドホック/バッチクエリを一つの Reservation Slots (non Autoscaling) で処理する。各ジョブのスロットの配分は BigQuery の Fair Scheduling 頼み。 15
 Atlas BigQuery のワークロード管理 Redash Google Cloud Console Looker Studio Schedule Query Data Transfer Atlas Reservation Batch Adhoc Adhoc Adhoc Batch Adhoc Batch Batch Batch Batch Batch
16. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 個々のクエリパフォーマンスチューニングに多くのリソースは割かない。 代わりにSlot 量やスキャン量を減らすための仕組みを提供してクエリコストが小さく済むようにする。 16
 ワークロード管理ポリシー 行動ログに拡張情報を事前に付与することで JOIN を減らす 行数の多いテーブルはパーティションフィルタ化し、 パーティションフィルタを強制する マテリアライズドビューの活用
17. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 日経電子版をはじめとした各種サービス上で計測する行動ログデータに会員属性情報や日付情報、 記事メタデータ、bot 判定等の拡張情報を BigQuery に連携する前に付与することで JOIN せずに 単一の行動ログデータだけで分析可能にすることを目指す。 17
 行動ログに拡張情報を事前に付与することで JOIN を減らす 行動ログ 記事メタ情報 会員情報 行動ログ 拡張コンポーネント 記事メタ 会員属性 行動ログ ACTION CATEGORY 会員ID 会員性別 会員年齢 記事ID 記事タイトル 記事著者 … page view 1 男 48 1 test 山田 …. 行動ログ テーブル
18. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 行動ログテーブルにはすべてのサービスで計測されたログがすべて集約される。 日経電子版以外のサービスを分析したい場合の行動ログデータに対するクエリスキャン量を減らすためにマ テリアライズドビューを活用する。 18
 マテリアライズドビューの活用 行動ログテーブル （全サービス) 日経電子版以外 行動ログMView Aサービス 行動ログMView Bサービス 行動ログMView 自動 Refresh 90% 削減
19. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 多数のクライアントが一つの Reservation Slot を共有するため少数の高コストなクエリによって多数の クエリが待機させられる可能性がある。そこで、定期的に実行されているジョブをチェックして重いクエリ を排除する仕組みを導入。 • Cloud Composer (Airflow) で条件に合致するクエリをキャンセルし、キャンセルしたクエリの内容について Slack に 投稿する DAG を実行 19
 重いクエリの自動キャンセル ※ DAG (Directed Acyclic Graph) Cloud Composer (Airflow) における一連処理の処理を定義する一つのジョブの単位
20. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 20
 重いクエリの自動キャンセルによるキャンセル通知例 キャンセルされるまでに消費したスロット消費時間(分) キャンセルされた Job の ID Redash を拡張して、実行するクエリの先頭に クエリのメタデータを自動付与するパッチを適用。 これにより Redash でサービスアカウントで BigQuery に対してクエリを実行している場合でも、 キャンセルされたクエリがどのクエリで誰が実行し たかすぐにわかるようになっている。
21. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 突発的なワークロードの増加に対応したり、バッチの突き抜けを回避するためにFlex Slot を活用して 一時的にリソースを増強する仕組みを導入（していた）。 • Cloud Composer で条件に合致する場合に Flex Slot を購入してリソース増強する DAG を実行 • BigQuery Editions 移行後に導入された Slot AutoScaler の超簡易版を運用していた 21
 Flex Slot による一時的なリソース増強
22. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 4. BQ ワークロード最適化戦略(これから) 22
23. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. • 従来の年次コミットメントの料金が変わる前に従来の料金のまま年次コミットメントを購入した • 人的リソースが足りないことを理由に後回しした 23
 実はまだちゃんと BigQuery Editions 移行できていない... 2022年度に購入した 年次コミットメントの 期限 某日 某日 従来の年次 コミットメント購入 可能期限 某日 従来の年次 コミットメント を購入 2023 2024 現在 某日 2023年度に購入した 年次コミットメントの 期限 Legacy Slot Commitments の最大利用期間 BigQuery Editions 完全移行への猶予
24. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 現状のワークロードを分析して BigQuery Editions に移行した時のコストパフォーマンスの最大化を 目指している。 24
 BigQuery Editions 移行に向けて今取り組んでいること 1.現状のワークロードの可視化 ワークロードを最適化して無駄なスロッ トの購入を減らし、利用者のクエリ処理 時間を削減するために、スロット消費の 割合やピーク時間帯をアドホッククエリ ／バッチクエリやサービスごとの区分で 可視化する 2.バッチウィンドウの見直し バッチクエリの実行時間が重なるタイミ ングで Pending Query が増大しているた め、バッチクエリ実行タイミングを 平準化して負荷を分散する。 3.予約スロットパラメータ最適化 1 の可視化と2 のバッチウィンドウの見直 しを踏まえて、最適なベースラインス ロットと最大スロット使用量を決定す る。合わせてバッチとアドホックで予約 スロットを分割することも検討する。
25. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Looker Studio を利用して INFORMATION_SCHEMA.JOBS_BY_TIMELINE + JOBS ビューから ワークロードの状況を可視化 25
 現状のワークロードの可視化 ※ 意図的に縦軸の数値は非公開 (公開して良い数値か確認が必要なため）
26. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. 26
 BigQuery Editions 移行後の予約管理 IMS Atlas PJ Admin PJ Batch PJ Adhoc PJ Commitment Commitment Reservation Atlas Batch PJ Adhoc PJ IMS Reservation (Batch) Reservation (Adhoc) Reservation (Batch) Reservation (Adhoc) Reservation (Batch) Reservation (Adhoc) • 今後 BigQuery に移行予定の IMS と Atlas で使用する Slot Commitments と Reservation を Admin プロジェクトで管理する (Best Practice に則る) • バッチとアドホッククエリを実行する PJ を分けてそれぞれに異なる Reservation を割り当てる。 Redshift
27. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. まとめ 27
28. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. Copyright ⓒ 2023 Nikkei Inc. All rights reserved. • コスト削減は特別なことはやっていない ◦ US リージョン使ってお得に便利に。 ◦ コスト増大しがちなストリーミング処理も Storage Write API でお得に高速に。 • これまでのワークロード管理は単一の Reservation Slot で BigQuery Faire Scheduling 頼み ◦ 個々のクエリのスキャン量を減らすためのいくつかの工夫をしている ◦ バッチ突き抜け防止のための Flex Slot 等簡易版 Auto Scaler を独自に運用していた • これからのワークロード管理はバッチとアドホッククエリを分割した最適な Slot 割り当てを目指す ◦ 本格的な BigQuery Editions 移行に向けて現状の可視化等準備を進めている 28
 まとめ