CrimsonDream

pyspa Advent Calendar 2022 10日目です. 9日目は aodag さんでした.

ここ1年ほど分析基盤のようなものを整備する仕事をしていたのでその備忘録的な話です.

この記事で話さないこと

データを活用するニーズ自体の問題
データをどう事業に活用かの問題
BigQuery 以外との比較
BigQuery の細かい仕様

背景

BtoC のモバイル中心の基本無料のオンラインゲーム運営
ゲームの開発と分析は別の人が行う
システムが出力するログは分析以外にも使用する(カスタマーサポートや不具合調査など)
複数のゲームが同じ分析基盤を利用可能する
ゲームのシステムはクラウド上に構築されている

分析基盤に求める要素

ログの投入はリアルタイムで行いたい (発生から10分以内程度には検索可能にしたい)
スキーマレスにログを投入したい (ゲームシステム側のコードにログ出力を行ないさえすれば自動的に検索可能になってほしい)
GB ~ PB クラスのログをシームレスに取り扱い(投入・保管・加工)可能 (ゲームごとに全く違うユーザー数, 同じゲームでも時によってデータ量が数千倍以上変化する)

分析基盤のために用意するもの

一般に分析基盤といえばデータを集めて加工して保管して利活用するためのデータレイクやDWH, ETLツール, BIツールが挙げられると思います.

しかし誤解を恐れずに言えば

データを素早く集めて保管し
簡単に加工でき
自由に組み合わせられて
好きなように見れて
できるだけ安いシステム

があれば良いわけです.

そんな夢のようなシステムがあるか?

無いならこんな記事は書かないわけです.
そう, みなさんご存知の BigQuery です.

BigQuery とは

BigQuery は一般にはクラウド型 DWH と説明されます.

DWH なのでデータレイクに保存した一次データ(生ログ)をETLツール等で加工した後に入れるところであり, さらに最終的な分析結果は保管せずデータマートに送り出すだけのイメージがあるかもしれません.

しかし, そもそも生ログから最終加工品置き場であるデータマート, さらにそれらの加工まで全てを行えるポテンシャルを BigQuery は持っています

さっそく, それぞれの要素について BigQuery でどうするのかを話していきましょう.

データを素早く集めて保管する

分析対象のデータは一般的な HTTP を使った APIサーバーや Web サーバー, スマホのクライアント等で発生することが多いです.
これらはサーバーの増減やソフトウェアの中断終了など揮発性のデータとなるのでなるべくはやく収集してあげる必要があります.
私は Kinesis(Kafka) が好きですが話し始めるとここだけで時間を使い切ってしまうのでこの記事では割愛します.

結論から言えば必要なのはスキーマレス(半構造化)データの取り扱い, バッチおよびリアルタイムでのデータ投入性能と信頼性, あとは読み出し速度です.

スキーマレスについては当初から STRING 型に json を格納すれば JSON_VALUE 関数等で実行時にパースすることで対応できます. 最近だと JSON 型にも対応しています.

バッチでのデータ投入はどのDWHでも, 当然BQでも効率的なので割愛.
リアルタイムでのデータ投入については BigQuery の Streaming Insert で実現可能です. 10秒あたりのオペレーション数という上限は存在するのである程度バッファしてまとめて投入するなどは必要ですが何を使うでも必要なことですし特に制約が厳しいということはないです.

信頼性については現時点で BigQuery の SLA 99.99% です. データ消失についても2ゾーンに対して書き込みをするので一般的なユースケースでは十分と言えるのではないでしょうか. https://cloud.google.com/bigquery/docs/reliability-disaster
絶対に無くなってはまずいデータというものもあると思うので, そういう場合は Export 機能を使って CloudStorage や S3 に定期的なバックアップを行うなど, 対策を行います.

読み出し性能については PB クラスのクエリだろうが数十秒で処理できるポテンシャルがあるので性能そのものが問題になることはないでしょう.

簡単に加工する

一口に加工と言っても様々なものがありますが, 実際の分析では SQL で表現できることがほとんどではないでしょうか？

こういうものもあるのでかなり柔軟性はあります.
今年発表されてまだ preview ですが Spark が動きます. なんだこれ

原則として BigQuery の SQL の結果は BigQuery のテーブルになります(宛先を指定しなければ一時的なテーブル, 指定すれば指定先に従う)
つまり SQL を実行して結果の格納先テーブルを指定するだけで Extract/Transform/Load (抽出/変換/格納) の全てが完了します.

必要なのはいつも書いている SQL だけですから新しいツールを覚える必要がありません. さらに性能面でも BigQuery ですから心配いらないわけです.

定期的に実行するのも Scheduled Query でできます. もう少し複雑な依存関係の管理や SQL の git 管理などがしたいのであれば dataform も良いでしょう.

こうした今まで前段や外部で加工を行っていた処理を DWH に任せることを上記 ETL に対して ELT と言ったりしますが, BigQuery は ELT における最強といっても良いソリューションだと思います.

自由に組み合わせる

個人的なポイントは外部データソースの豊富さ, とくに CloudStorage と Google SpreadSheet との連携です.
CloudStorage 上のデータをテーブルとして透過的にアクセスできるのは基本ですが, 広告効果測定ツールなどの外部ツールのデータを扱う上ではかなり便利な機能です.
また ELT のコンセプトで行う上で, 直接参照できることで BigQuery ネイティブテーブルへの取り込みと一次加工を BigQuery で行えるのも大きいですね.

Google SpreadSheet のシートをテーブルとして扱える機能は, マスターデータなど人間が編集するデータをそのまま BigQuery で扱えるので非常に強力です.
会社でデータを扱う時, だれでも SQL をかけたりするわけではないですが, SpreadSheet に記入をお願いすることはできる場合はあります.
システム的に扱いづらいデータがあってそれを活用したい時や, すでにあるワークフローに Google SpreadSheet が使われているなら考慮するべき機能です.

あと, あまり話題になりませんが BigQuery では Google Cloud のプロジェクトを横断してデータを処理できます.

例えば特定のゲーム用プロジェクトに入っている売上データのテーブルと, 会社全体で共通のデータを入れるプロジェクトにある為替データのテーブルを JOIN したクエリをかけます.
この時必要なのは, クエリ実行者の両方のデータにアクセスする権限のみです.

今はレコード単位のアクセス制御等細かい権限管理が可能な BigQuery ですが, データ管理者が違うような状況であればそもそもプロジェクトごと分けてしまう方が事故が少なく楽だったり, コスト管理もしやすい状況は多いのではないでしょうか.
そう言った場合でも, 利活用上の不便さがないのは非常に重要な要素です.

好きなように見れる

先ほど Google SpreadSheet をデータソースとして扱う機能を紹介しましたが, 逆に SpreadSheet から BigQuery のデータを見ることもできます.
そのデータを VLOOKUP で参照するのも, ピボットテーブルで集計することもできますし, データのビジュアライゼーションツールとして使えるのです.

また SQL はわからないけど SpreadSheet なら使える人たちとデータを共有しないといけないというシチュエーションでも BigQuery が活用できます.

もちろん, 本格的な BI ツールである Looker も今は Google Cloud の製品ですし, 最近名前が変わった Looker Studio のような無料のツールもあります.

できるだけ安くする

まずコンプライアンス上の問題がない場合, 基本的に BigQuery を使う場合は us (マルチリージョン) を使うべきです.
そもそも BigQuery のクエリは最低でも秒単位かかります. データ投入にしてもある程度まとめて送ることを考えると個別の通信のレイテンシーは大きな問題になりません.
それよりも一番安い単価 (クエリ料金 us: $5/TB に対して tokyo $6/TB) で利用できるリージョンに統一した方が上記のプロジェクトを横断するクエリーのことを考えても効率的です.

データの保管料金を考えた場合, BigQuery のストレージ料金は

アクティブ $0.020 per GB
長期保存(Long Term) $0.010 per GB

ですが, この価格は CloudStorage の Standard Storage / Nearline Storage と同等です.
よくデータレイクとして分散オブジェクトストレージが出されると思いますがアクセスのあるデータなのであれば単価は変わらないことになります.

唯一の欠点は圧縮前の容量に対してかかるという点ですが圧縮後のデータ量で計算する料金プランも今後でてくるそうです.

クエリ料金については 1TB をスキャンして $5 というのがそもそも破格の安さです. ( 1TB のデータを処理して数秒で結果を返す DWH のインスタンス料金を考えれば)
単価やコスト固定を求めて定額制を使ってもよいですし, 予期しないコストが不安なら今はカスタムコスト管理でハードリミットを設定することすらできます.

まとめ

分析基盤構築とかでツールいっぱい導入しなくてもそれ全部BQで出来ることも多いので便利に使うと良いんじゃないかなぁと思います.