20240917_BigQueryを用いたデータ分析基盤の作り方

2024/09/17 Lancers株式会社様主催のウェビナーにて登壇した際の資料です。

Transcript

1. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. ギークス社登壇用 BigQueryを用いたデータ分析基盤の作り方 2024.9.17 株式会社うるる NJSS事業本部 情報収集推進課 栗原 史明
2. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 自己紹介
3. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 自己紹介 株式会社うるる NJSS事業本部 開発部 情報収集推進課 栗原 史明 ▼ プロフィール • 2014年に株式会社パソナテックに新卒入社 ◦ 金融系、小売系など様々な企業のBIツールの開 発 • 2018年に株式会社うるるに転職 ◦ 「入札情報速報サービスNJSS」担当のサーバー サイドエンジニアとして参画 ◦ 保守運用、リプレイスプロジェクト、AI研究開 発等、様々な社内開発Prjを横断 ◦ 現在はクローラー開発・文書データ解析業務を 中心に従事
4. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 株式会社うるるとは / NJSSとは
5. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 株式会社うるるのご紹介
6. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 株式会社うるるのご紹介
7. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 入札情報速報サービス NJSS とは NJSSはSaaSモデル型の入札業務ワンストップサービス 入札市場は年間20兆円を超える安定性を誇る 全国8,400以上の官公庁・自治体などから公示される入札情報 を一括検索・管理・分析ができ、入札参加プロセスの効率化・ 販路拡大を支援 クローラー（自動収集プログラム）で拾いきれない情報は、ク ラウドワーカーが目視で情報収集しており、精度の高い情報を 網羅的に掲載していることが特徴
8. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. うるるの入札事業について（Govtech領域）
9. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. こんなケースで悩んでいませんか
10. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. こんな悩み、ありますよね 社内からデータ抽出の依頼を受けたけど… データ抽出するの めちゃくちゃしんどいんだよなあ
11. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • データベースが気軽に叩くことができる環境下にない ◦ 本番アプリのデータベースを直参照しないといけない、など ◦ 怖すぎて緊急時以外は触りたくない • クエリが全然返ってこない ◦ インデックスが効いていれば平気だけど効いてないカラムに対する絞り込みはしんどい。 ◦ テキストカラムで部分一致検索しないといけないとなったら、もう目も当てられない。 • 複数のデータソースを参照しないといけない ◦ 1つの帳票を作るためにAとBの2つのデータベースを横断しないといけない、など ◦ 各システムで集計したものをExcelで取り込んでVlookupして…うっ頭が…。 なんで「しんどい」のだろう…？
12. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • データ抽出依頼は本番DBを直参照していた ◦ クエリが重たくならないよう、細心の注意を払って書いていた ◦ 激重クエリを叩いて負荷がスパイクする、という状況もたまに発生していた • 複数のサブシステムが動いており、横断的な分析調査が困難だった ◦ NJSSではマイクロサービスアーキテクチャを導入しており複数のデータベースが存在 ◦ システムを跨いでデータ分析を行なう際、いちいち各データベースから情報を抜いてから突 合するという面倒な作業を行なっていた • 競合サービスの nSearch をM&Aした為、比較ニーズが出た ◦ nSearchもNJSSと同じく入札情報を収集し検索するサービス ◦ 両サービスがどれだけ収集や項目解析に差があるのか定量かつ大量に分析したいニーズが出 てきた 当時の我々の背景・課題
13. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 解決するべき課題とは 隔離 分析作業によって 本番アプリケーションに 影響を及ぼさないこと 高速 重いクエリでも 高速に演算が 可能であること 横断 複数のデータソースを 横断的に用いた演算が 可能であること
14. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 「データ分析基盤」とは
15. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. データ分析基盤とは データレイク (Data Lake) データウェアハウス (Data Warehouse) データマート (Data Mart) • 各種データソースをそ のまま保存している場 所 • 構造化データ、非構造 化データ問わず様々な ものが格納される • 構造化データのみを保 存するデータベース • データが整然としてお り、高速かつ効率的な 分析 • 特定のビジネス部門や 機能向けに設計された データストレージ • 特定の目的に特化、利 用が簡単で素早く結果 を得られる 柔軟性 用途特化 巨大な倉庫 図書館 (図書館の中の)専門書コーナー ▼例えるなら…▼
16. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • GoogleCloudが提供するフルマネージドなデータウェアハウス ◦ サーバレスなのでデータベース運用・インフラ管理が一切不要 ◦ 標準SQLを互換性があるため既存のSQL構文でテラバイト単位のデータソースに対する分析を 高速に実現可能 • コストパフォーマンスが非常に良い ◦ コスト体系はストレージ課金＋クエリ利用課金 ◦ 通常利用の範疇であれば非常に低額 BigQueryとは
17. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • 基本的に「ストレージ」と「コンピューティング料金」 ◦ コンピューティング料金とは「クエリ実行時にスキャンしたレコードの容量」 BigQueryのコスト体系 ※いずれもオンデマンドの場合 ※料金は2024/9/11現在 us-east1リージョン ストレージ料金 ActiveStorage 　logical (非圧縮) : $0.023 / GiB 　physical (圧縮) : $0.044 / GiB LongTermStorage (長期保存) 　logical (非圧縮) : $0.016 / GiB 　physical (圧縮) : $0.022 / GiB ※毎月10GiB分は無料 オンデマンド コンピューティング料金 $6.25 / TiB ※毎月1TiB分は無料
18. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. システム構成図
19. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. システム構成図（テーブルベース） Worker • tableA • tableB User • tableA • tableB Crawler • tableA • tableB • tableA • tableB • applog/20240901 • applog/20240902 Dataset • njss_worker_tableA • njss_worker_tableB • njss_user_tableA • njss_user_tableB • njss_crawler_tableA • njss_crawler_tableB • njss_applog (※日付パーティション) • nsearch_tableA • nsearch_tableB
20. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 弊社で扱っているBigQuery連携は3パターン １ : RDB(MySQL)の連携 ２: ログ標準出力の連携 ３:NoSQL(mongodb)連携
21. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • GCPで提供されているDataStreamで 繋げるだけ ◦ 接続情報・取込対象テーブル・更新頻度だ け選択すればテーブル作成まで全自動 ◦ カラム単位で連携する/しないも対応可能 ◦ メジャーどころのRDB（MySQL, Postgres, Oracle）に対応 AWS Aurora(MySQL)の連携
22. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • AWS FireLens と fluentbid で連携 ◦ 標準出力(AWS Logs)をFireLensに向け、 fluentbidコンテナでログを拾ってBigQuery に直接連携する • カラム単位で正規化する場合はログの 出力フォーマットは非常に重要 ◦ fluentbid側でテキスト整形を入れる為 ◦ 正規化を見据えるならログフォーマットは 事前に定義しておく ログ標準出力の連携
23. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • mongodbの連携はCloudStorageと DataFlowの併せ技 ◦ CloudStorageにmonogodumpを出力 ◦ DataFlowを使ってBigQueryに流し込み • 連携都度、全件送信・全件取込になる 為、コストが掛かる ◦ 連携頻度にあわせて通信コストとコン ピューティングコストが比例 ◦ RDB連携と違って慎重に頻度を決定する必 要がある NoSQL(mongodb)の連携
24. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. システム構成図（テーブルベース） Worker • tableA • tableB User • tableA • tableB Crawler • tableA • tableB • tableA • tableB • applog/20240901 • applog/20240902 • njss_worker_tableA • njss_worker_tableB • njss_user_tableA • njss_user_tableB • njss_crawler_tableA • njss_crawler_tableB • njss_applog (※日付パーティション) • nsearch_tableA • nsearch_tableB
25. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 導入後のSQL分析 • 標準SQLで横断的な分析クエリが書けるようになった！ – SQLイメージ SELECT * FROM `njss.入札案件` t1 INNER JOIN `nsearch.入札案件` t2 ON t1.name = t2.name WHERE t1.name LIKE ‘%東京都%工事%’ ;
26. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 導入後、どうなったのか • 精通したメンバーは高度な分析ができるようになった ◦ MySQLでは数時間は帰ってこないようなクエリもBigQueryなら数分で返ってくるように ◦ Excel上でVlookupするしかなかった異種データソース間の突合もBigQuery上で完結 • 分析に精通していないメンバーへの依頼ハードルが下がった ◦ 本番サービスへの影響が無いので依頼ハードルが下がった • エンジニアではないメンバーでも抽出業務が出来るようになった ◦ 事前にクエリを作っておけばエンジニアではない方にも抽出作業ができるように ◦ 依頼側は自分の好きなタイミングで抽出できるし、エンジニアは抽出業務に工数が取られな いので双方に旨味
27. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 運用コストに対するTips
28. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • 基本的に「ストレージ」と「コンピューティング料金」 ◦ コンピューティング料金とは「クエリ実行時にスキャンしたレコードの容量」 BigQueryのコスト体系（おさらい） ※いずれもオンデマンドの場合 ※料金は2024/9/11現在 us-east1リージョン ストレージ料金 ActiveStorage 　logical (非圧縮) : $0.023 / GiB 　physical (圧縮) : $0.044 / GiB LongTermStorage (長期保存) 　logical (非圧縮) : $0.016 / GiB 　physical (圧縮) : $0.022 / GiB ※毎月10GiB分は無料 オンデマンド コンピューティング料金 $6.25 / TiB ※毎月1TiB分は無料
29. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • とにかく「どれだけスキャンする量を減らせるか」が肝 • 「参照列を減らす」ことと「パーティション化」で削減 ◦ SELECTで参照するカラムを明示的に指定する。「*」の利用は原則避ける。 ◦ WHEREやLIMITでレコードを絞ってもフルスキャンされるので効果がないが、パーティショ ンによる絞り込みについては削減効果がある ◦ ログ系のテーブルなど日々大量に積み上がるトランザクション系テーブルにはパーティショ ン化は積極的に推奨。 ◦ パーティション化についてはWeb上に先人の知恵が沢山あるので、そちらを参照 分析コストを削減する
30. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. スキャンの削減：列方向 列全件: 8.99 GB 列指定: 1.52 GB
31. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. スキャンの削減：行方向 行全件: 8.99 GB 行指定: 8.99 GB (減っていない)
32. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. スキャンの削減：行方向 (with パーティション) 行全件: 2060.00 MB 行指定: 89.63 MB
33. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • 常識的な使い方の範囲なら、そこまで行くケースはない ◦ 高額課金のケースは大体はスキャン範囲のミス✖機械的再帰実行の複合ケース ◦ （予算次第だが）人が手でクエリを発行する程度であれば怯える必要は殆どない • BigQueryをスクリプトベースでの参照が視野に入り始めたら ◦ アラート機能やコードレビューで品質担保する体制を作るようにする ◦ クエリの書き方ひとつでオンデマンドコンピューティング課金額は変化する ウン百万請求されたという高額課金の記事があるけど
34. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. • 「予算アラート機能」あります ◦ 予算額を指定して閾値を超えたらメール通知可能 ◦ 予算額に対して割合を段階的に設定することも可能 • お守り的に設定しておくと吉 それでも心配なあなたへ
35. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. より発展的な使い方（データマート構築の未来）
36. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. システム構成図
37. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. BigQueryと連携してデータマートを構築 • Lookerstudioを用いてグラフィカルに表示 ◦ BigQueryに連携したデータを更に正規化（＝データマート化）することで、部門に特化した 情報に昇華。BIツールライクなプラットフォームをGoogleCloud上で実現可能
38. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. まとめ
39. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. まとめ • 分析着手までの課題が「データソースの越境」「本番アプリへの影響」にあ るなら、データウェアハウス構築の戦略は刺さる可能性が高い ◦ 特にRDB系で運用している場合は連携接続が非常に楽なのでお試しでやりやすい • BigQueryは従量課金なので、スモールスタートがやりやすい ◦ 無料枠もあるのでTryしやすい • 導入後、本番アプリに一切の影響を与えない心理的安全性からデータ抽出の 運用に幅を持たせることが出来るようになった。
40. COPYRIGHT © ULURU.CO.,LTD. ALL RIGHTS RESERVED. 採用情報 エンジニア採用サイトはこちら 採用活動中ポジション ・フロントエンドエンジニア ・バックエンドエンジニア（PHP / Ruby） ・SREエンジニア　　　etc. 株式会社うるるでは、エンジニアを積極採用中です ※いずれも2024年9月17日現在の情報です。掲載情報は予告なく変更される場合がございます。