Google Cloudでシミュレーション機能を構築 - GMOあおぞらネット銀行テック&カルチャーブログ

こんにちは。
GMOあおぞらネット銀行で分析基盤を担当しているTKです。
当社の分析基盤をゼロベースから構築を始めて、早約3年が経過しました。
社内においても利用希望・要望が増え、私が社内営業を行わなくても問い合わせを頂ける状況となり、且つ分析基盤を利用した開発メンバーも少しずつ増えて来ました。
一方で、基盤運用に関わる体制が脆弱な状況がまだ改善されない為、個人的に少しリスクに感じている今日この頃です。

さて、今回は過去の取組みとは少し毛色の違う要件を社内で頂きましたので、この事に関してブログを書いて行きたいと思います。
表題の件となりますが、頂いた要件は当社提供サービスにおけるリスク状況を測る為のシミュレーション処理となります。
具体的なシミュレーション内容には触れませんが、今回の要件を実現する中で便利な機能を幾つか発見したので、それらを紹介できればと考えています。

取組みの背景
準備
- 要件整理
- 検証と仕組み検討
ポイントとなる機能
結果可視化
まとめ

取組みの背景

今回の取組背景は、現在Excel上で行っているシミュレーションの件数が十分では無い為、分析基盤側で十分なボリュームで実行&結果を出力する事が出来ないかと相談を受けた事が発端となります。
利用するデータは直近取り組んでいる要件に関連するものとなる為、データは分析基盤に揃っており、Excel版では対応が難しかった十分なボリュームの実現に加え、データ取得の一手間削減やシミュレーション結果整理などの業務改善も期待できる試みとなりました。
通常の要件はデータを集約させていく傾向がありますが、今回の件は一度ボリュームを膨らませてから集約させる流れであり、これをどのように効率よく構築する事が出来るかと言う事が個人的なモチベーションとなりました。

準備

要件整理

前述の通り要件を実現しているExcelファイルが存在するので、仕様はこのファイルから吸い上げる形で整理を行いました。
Excelファイルでは、最終結果までに集計STEPを区切る形でシートを分割し、それぞれのシートで途中経過を出力する流れで構成されていました。
しかし、今回の要件では途中経過出力は不要である事が確認出来たので、途中経過出力を除外する形で整理を行い、比較的シンプルな計算式にシミュレーション処理を整理する事が出来ました。

シミュレーションのコア部分以外の主な要件・制約は下記となります。

担当者からの要望	・シミュレーション結果は月末断面で確認したい・シミュレーションは10万回実行したい
制約	・実行時間はCloud Run 関数の制限(540秒)内に確実に完了する・Cloud Run関数の過度な呼出は避ける

検証と仕組み検討

今回の仕組みでは、コアとなるシミュレーションは1計算式で実現できる為、ポイントは登録処理をいかに効率よく行うかが仕組みのキモになると考えました。
BigQueryは基本高性能ですが、方針検討の為、BigQueryの登録処理に関して簡単な検証を行いました。
下記は、100件の登録処理を3種の方法で実行した時の処理速度となります。

1回の登録件数	登録回数	処理時間
1件	100回	4分32秒
10件	10回	27秒
100件	1回	2秒

他のDBと同様ですが、BigQueryでも登録処理はそれなりの時間が掛かる事が確認出来ました。
この結果から、登録回数を少なくする方向で仕組みを検討する方針を軸とし、上記以外にも細かな検証&試行錯誤した結果、下記仕様で開発を進める事にしました。
　➀ 1度に登録する件数は1,000件
　② BigQueryで➀の処理を10回繰り返し
　③ Cloud Run関数で②の処理を10回並列実行

検証結果から、➀の件数を更に増やす事でよりパフォーマンス向上を期待できる事は明確でしたが、今回は限界を見極める必要はなく、1,000件で十分なパフォーマンスが期待出来た事から、この構成でFIXとしました。

下記、処理フローのイメージ図となります。

ポイントとなる機能

Cloud Run 関数

今回の仕組みでCloud Run関数が担う機能は、親プロセスと子プロセスの2種となります。
親と子で2種の関数を用意する事も考えましたが、将来的に1つにまとまっていた方が管理しやすいと考え、1つの関数で両機能に対応する事にしました。
親子プロセスはトリガーとなるPub/Subメッセージで切り分け判断を行い、親プロセスは子プロセスの実行&完了確認を行う仕組みとしました。
以下サンプルコードですが、特に変わった仕組みは無い構成となっています。

▶ Cloud Run(python)

from google.cloud import storage
from google.cloud import bigquery
from google.cloud import pubsub_v1

import base64
import os
import json

def simulation(event, context):
    # メッセージをjsonフォーマットに変換
    pubsub_message_json = json.loads(base64.b64decode(event['data']).decode('utf-8'))

    # クライアント設定
    client = bigquery.Client()
    storage_client = storage.Client()

    # 実行日設定
    target_date = pubsub_message_json["date"]

    # メッセージ判定
    if "SIMULATION" in pubsub_message_json["target"]:
        # 前処理実行
        # 子プロセス呼出
        # 子プロセス完了待ち
        # 後処理
    elif "SIMULATE CALCULATION" in pubsub_message_json["target"]:
        # 子プロセス実行
    else:
        print(logJSON("WORNING", "-  想定されない対象の為、実行をSKIP: " + pubsub_message_json["target"]))

# ログレベル設定
def logJSON(logLevel, message):
    return json.dumps({
      "severity": logLevel,
      "message": message,
    })

BigQuery

今回BigQueryで行う処理は前処理、シミュレーション処理、後処理の3種となりますが、前後処理はあまり重要では無いのでシミュレーション処理にフォーカスの構成を紹介させて頂きます。

シミュレーション処理で実行するBigQueryの構成は下記となります。

1	一時TBL作成
2	シミュレーションを1,000件実行し、一時TBLに登録(10回繰返)
3	一時TBLから集計用TBLへ登録
4	一時TBL削除

※集計用TBL登録前に一時TBLを介する理由は、並列実行を行う子プロセスからの集計用TBL更新回数を最小限にする事で不測のエラーを懸念している為となります。

仕様➀. 1度に登録する件数は1,000件対応にあたり、効率よくシミュレーションを行う為、SELECT句1項目に1件分のシミュレーション計算式を記述する事にしました。

SELECT
    SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_0001,
    SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_0002,
    :
    SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_1000,

この方式採用に辺り、懸念は列項目から行項目への持ち替えをシンプルに行う手段があるかでしたが、BigQueryにはUNPIVOTと言う機能があり、こちらを利用する事で簡単に懸念を解決する事が出来ました。
このBigQueryのUNPIVOT機能が今回紹介したい機能の1つ目となります。
また、今回はUNPIVOTを利用しましたが、PIVOT(行列入替)機能もありました。

▶ BigQuery

こちらは、1子プロセスが実行するシミュレーション結果1,000件登録 × 10回の処理のサンプルとなります。

DECLARE TARGET_DATE STRING DEFAULT '{}';
DECLARE DO_COUNT INT64 DEFAULT 1;
DECLARE END_COUNT INT64 DEFAULT 10;

#集計用TBL作成
CREATE OR REPLACE TABLE `プロジェクトID.データセット.一時登録用テーブル名_TEMP_NUMBER`
(
  SIMULATION_RESULT NUMERIC NOT NULL OPTIONS(description="シミュレーション結果"),
);

#集計処理
LOOP 
  INSERT INTO `プロジェクトID.データセット.一時登録用テーブル名_TEMP_NUMBER`
  (
    SELECT SCORE
    FROM 
    (
      SELECT
         SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_001
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_002
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_003
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_004
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_005
        :
        :
        :
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_997
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_998
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_999
        ,SUM(IF(XXXXX >= RAND(),YYYYY,0))  AS RESULT_000
      FROM `プロジェクトID.データセット.テーブル名` 
      WHERE GCP_PROC_DATE = DATE(TARGET_DATE)  
    ) UNPIVOT(
      SCORE
      FOR SIMULATION_CASE
      IN(
          RESULT_001
          ,RESULT_002
          ,RESULT_003
          ,RESULT_004
          ,RESULT_005
          :
          :
          :
          ,RESULT_997
          ,RESULT_998
          ,RESULT_999
          ,RESULT_000
      )
    )
  );

  SET DO_COUNT = DO_COUNT + 1;
  IF DO_COUNT > END_COUNT THEN
    LEAVE;
  END IF;
END LOOP;

#集計先TBLへデータ登録
INSERT INTO `プロジェクトID.データセット.登録用テーブル名`
(
  SELECT
    DATE(TARGET_DATE)
    ,SIMULATION_RESULT
    ,CAST(CEIL(ROUND(SIMULATION_RESULT / 10000000, 1)) * 10 AS STRING) AS DISTRIBUTION
    ,CURRENT_TIMESTAMP()
  FROM `プロジェクトID.データセット.一時登録用テーブル名_TEMP_NUMBER`
);

#集計用TBL削除
DROP TABLE `プロジェクトID.データセット.一時登録用テーブル名_TEMP_NUMBER`;

パフォーマンス結果

今回の取組みでは、パフォーマンスが常に念頭にありましたが、事前に各種検証を行った事で、ほぼ想定通り全体を通して2分弱と言う結果を出す事が出来ました。
また、将来的なパフォーマンス改善やシミュレーション件数増加要望が発生しても、対応可能な仕組みとなっており、個人的に満足な結果となりました。
加えて、約2分で10万件のシミュレーション処理を実行できる事がわかったので、今後はこの仕組みを流用して、付加価値のあるデータを提示できる仕組みに発展させていく事も可能だと考えています。

結果可視化

シミュレーション結果の可視化に関しては、前回と同様にLookerを利用しました。
結果の魅せ方に関しては、技術的な気付きはほとんどなかったのですが、1点LookerのPIVOT＋グラフ機能の使い勝手が良かったので紹介したいと思います。
今回担当者から結果は月末断面で確認と言う要望があった為、下記のようなグラフを1つ作成しましたが、このグラフ作成に試行錯誤し前述の『PIVOT＋グラフ』を利用する事で実現する事が出来ました。
この機能が今回紹介したい機能2つ目となります。

詳細な手順に関しては、下記の"Looker(PIVOT＋グラフ)"をご確認ください。

▶ Looker(PIVOT＋グラフ)

まとめ

今回の取組みはシミュレーションと言う未体験のモノではありましたが、利用サービスは変わらないので新しい発見には期待していませんでした。
しかし、対応を進めてみると想定していなかったハードルや発見があり、結果として今回もよい経験となる取組みになりました。
偶然にも驚きを感じたBigQueryのPIVOT/UNPIVOTとLookerのPIVOT＋グラフは両方共PIVOT関連でした。
PIVOTは頻繁に利用する機能ではありませんが、必要な時には無いと困る機能なので、しっかり記憶しておきたいと思います。

今年年初にLookerを導入した事で分析基盤側でINからOUTまで完結する事が出来るようになった事で、社内でも分析基盤利用者が増えてきており、3年越しの取組みが軌道に乗ってきたと感じています。
一方で、可視化が出来るようになった事で、画面作成時などに拘りが出てしまい、求められていない機能まで作り込みたくなってしまう事が、最近の個人的な悩みとなっています。
組織的な悩みとしては、冒頭で記載した運用負荷の増加となりますが、こちらは年内に他メンバーにも諸々対応が可能となる体制構築が完了する予定なので、こちらは解決しそうな見通しです。

現在対応中の要件は近々に落ち着きそうなので、bigqueryかLookerにgemini連携の設定を行い、使用感を体験してみたいと考えている今日この頃です。

ーーー

一緒にGMOあおぞらネット銀行で働いてくれる仲間を募集しています。
社内勉強会はもちろん、GMOグループの勉強会にも参加できます。ご興味のあるエンジニアの方は、当社採用ページをぜひ一度ご覧ください。

gmo-aozora.com