BigQueryでリスク計算をしている話

eScanの肝である「リスク計算」のロジックを、TypeScript(Node.js)からBigQueryに移行しSQLで実装し直した
その経緯とそれを支えるBigQueryのTipsについて紹介

Transcript

1. BigQuery でリスク計算をしている話 平田 尚史(2024年3月13日)
2. 2 今日のお話 eScanの肝である「リスク計算」のロジックを、 TypeScript(Node.js)からBigQueryに移行しSQLで実装し直した その経緯とそれを支える BigQueryの力💪(Tips)について紹介
3. 3 自己紹介 • 平田尚史（ひらたたかふみ） ◦ github: totem3 ◦ X: takhirata • これまで ◦ ソーシャルゲームの開発や裏側のインフラの運用（オンプレ・ AWS） ◦ データ基盤の構築・分析の支援 ◦ など • 今 ◦ eScanという「日本の電力事業に特化したポートフォリオ最適化システム」を開発 ◦ バックエンド〜基盤よりの設計・開発が多め ◦ 必要に応じてフロントエンドも頑張る
4. 4 > eScanは、日本より15年以上先行して電力自由化した欧米で確立された最先端の金 融工学の理論を基に、発電事業者や電力小売事業者の電力ポートフォリオが抱えるリス クを診断し、定量的に評価するウェブアプリケーションです。 > 日本の電力業界ならではの規制や商慣習も考慮したうえで、市況が高騰して仕入れと 販売が逆ざやになったり、反対に需要 が低下し不良在庫を抱えてしまう、などの 発電事業や電力小売事業に付きまとう様々 なリスク管理をサポートします。 https://enechain.co.jp/services eScanの紹介
5. 5 eScanの紹介 引用: 第3回 電力先物の活性化に向けた検討会 資料2 市場の流動性拡大と透明性向上に向けた事例および取組み より
6. 6 なぜBigQueryなのか もともとeScanのリスク計算はTypeScript(Node.js)で実装されていた ワーカーとして常時起動してイベントを待機し、リスク計算実行のイベントを受けて計 算が起動するスタイル
7. 7 なぜBigQueryなのか もともとeScanのリスク計算はTypeScript(Node.js)で実装されていた。 ワーカーとして常時起動してイベントを待機し、リスク計算実行のイベントを受けて計 算が起動するスタイル → 初期の比較的シンプルな実装から、扱うデータの量や種類、計算の複雑さが増 大するに従って様々な問題が発生 • メモリ消費量が増加: 1回の計算で10GB近くメモリを消費することも • データ量が多い計算の計算時間の増大 • オンラインで参照されることはないが、増え続ける時系列のデータ • 複雑化するコード・データ構造
8. 8 なぜBigQueryなのか そんな中 • 精度向上のため更にデータの粒度を細かくする • 今後ご利用頂きたいより大規模の事業者様の場合、桁違いのデータ量を扱う 必要がある • 調達条件の変更や市場の電力価格の高騰時のシミュレーションのため、扱う データ量も計算の回数も増える というまだまだスケーラビリティが求められる状況であり基盤の移行を決意
9. 9 なぜBigQueryなのか 我々が基盤に求めていた要件 • 扱うデータが増えても勝手にスケールして欲しい ◦ メモリやCPUなどのリソース的にも ◦ 分散処理などで実行時間もデータに比例して増えない • インフラ部分の運用・管理は全てクラウドに任せたい • 計算に利用した入力データや計算の中間結果はしばらく保持しておきたい • 地域・年月・帳簿・・・などなど様々なディメンションとその組み合わせによる計 算をシンプルに記述できる
10. 10 なぜBigQueryなのか BigQueryやSparkが候補に挙がったが、BigQueryに軍配が上がった • インフラ構築の手間がかからない • 既存のシステムとの親和性 ◦ →BigQuery自体は既に社内の各所で使われていて、eScanでも共通デー タ基盤のBigQueryからPostgreSQLにデータをインポートしていたが、 BigQueryにすればそれが不要になる • SQLを使うメリット ◦ SQLはどこでも使っているので、Node.jsに加えてPython(やJava)など他 の言語を追加で使うのに比べて敷居が低い ◦ PdMや数理モデルを構築するクオンツなど、エンジニア以外でもデータを 使うことができる
11. 11 • 最大で計算は25倍高速化 • データ量に応じて線形に計算時間が増えなくなった • 計算はBigQuery上で行われるのでkubernetesのPodのメモリ不足や過剰な確 保の心配から解放された BigQuery化の効果
12. 12 BigQueryでロジックを実装する際の武器たち ユーザー定義関数（UDF） ロジックを移行できるかどうかの肝であった重要な機能 SQLまたはJavaScriptで関数を定義することができ、GCSに置いたJavaScriptのラ イブラリを読み込んで利用できる これにより、さすがにSQLで実装することができない複雑な処理を一部JavaScript に任せられる。 あまりないがSQLだけでは実現できない要件が発生したとしても、これがあれば対 応することができる。
13. 13 BigQueryでロジックを実装する際の武器たち ユーザー定義関数（UDF） eScan内の計算の一部ではユーザーが入力した任意の数式をパースして計算する 箇所があり、そのパースや計算に独自のJavaScriptのライブラリを利用している。詳 しくはブログで https://techblog.enechain.com/entry/build-dsl-with-ohm
14. 14 ユーザー定義関数（UDF） JavaScriptのライブラリをIIFE (即時実行関数式) でビルドすれば使える。 BigQueryでロジックを実装する際の武器たち CREATE TEMP FUNCTION myFunc ( a FLOAT64, b STRING) RETURNS STRING LANGUAGE js OPTIONS (library=['gs://my-bucket/path/to/lib1.js','gs://my-bucket/path/to/lib2.js']) AS r """ // Assumes 'doInterestingStuff' is defined in one of the library files. return doInterestingStuff(a, b); """ ; SELECT myFunc(3.14,'foo'); UDFを作る際に LANGUAGEでjs を指定 OPTIONSでlibrary にGCS上のライブラ リのpathを指定
15. 15 ユーザー定義関数（UDF） 注意点 • BigQueryのドキュメントのベストプラクティスにも記載がある通り、処理中に使 えるメモリには上限があるので、大量のデータを扱う処理は避ける • JavaScriptの実行環境のタイムゾーンはUTCではなく、PDT/PSTなので日付 の操作を行う場合には要注意 • デバッグは例外を投げることによるprintデバッグくらいしかできないため、 BigQueryからは関数呼び出しだけにしてBigQueryの外でデバッグできるよう にするのが無難 BigQueryでロジックを実装する際の武器たち
16. 16 実行グラフ クエリ結果の「実行グラフ」タブで確認できる クエリの各ステージの実行時の情報をグラフィカルに確認できるツール ステージのステップの情報やタイミングの情報を見られる パフォーマンスが悪い場合にはここを見てボトルネックを調査する BigQueryでロジックを実装する際の武器たち
17. 17 実行グラフ クエリが複雑になってくると紐解くのが難しくなるが、実際クエリが終わらなくて詰み そうになったときここを見て紐解いて救われた BigQueryでロジックを実装する際の武器たち
18. 18 複数回参照する場合はCTEよりTEMP TABLE 再帰ではないCTE(WITH句)は実体化されないので、何度も参照するとその都度ク エリが実行されるため高コストになる その場合は、CTEを使うのではなく、TEMP TABLEを使ってテーブルを保存してしま うのが良い リスク計算ではGENERATE_SERIESを使ってリスクを計算する日付の範囲を生成 してマスタとして使ったり、更に日付の差を取ったり相関行列を作るためにそれらを CROSS JOINすることがある。これをWITH句でやっていると、 データが多い場合に処理が終わらず、CPUのQuotaを超えて処理が中断されてしま うことがあった BigQueryでロジックを実装する際の武器たち
19. 19 便利な関数群・増え続ける関数 BigQueryで利用できるGoogleSQLには便利な関数が多く実装されているので、筋 力で実装する前によくよく関数を調べてから実装するのが良い BigQueryへの移行を開始した時点では実装されておらず苦労したMAX_BY関数や ROLLUP系の構文も今では実装されているが、しばらく気付かずに筋力による実装 を続けてしまっていた → 最近では時系列データに関する操作が追加されるなど進化を続けているので、 GCPのリリースノートのRSSを購読してキャッチアップ BigQueryでロジックを実装する際の武器たち
20. 20 課題と今後の展望 基盤の入れ替えで成果が出たものの、課題はある • 計算が複雑になるにつれSQLがテストやモジュール化しにくいことに起因する 開発体験の悪化が露呈 • ワークフローも複雑化し手動での管理が辛くなってきている
21. 21 課題と今後の展望 改善に向けて • BigQuery DataFramesなど新たなAPIの検討 • データからワークフローやデータリネージを生成する手法の検討 → 検討結果は今後もTechBlogなどで