こんにちは。
最近は完全に分析基盤の人になっている、GMOあおぞらネット銀行でエンジニアをしているT.Kです。
担当業務は、主に当社分析基盤(GCP)全般(管理・開発・運用等々)と、昨年末に導入したBIツールLookerの全般(管理・開発・運用等々)となっています。
さて、今回のブログも前回と同様にAML関連で進めて行こうと考えています。
今回の内容は、前回のブログで触れた不正口座ネットワーク可視化の前段処理となる、特定条件にHITする取引の抽出処理に関してとなります。
また、前述のBIツールのLookerを導入したので、こちらにも少し触れていこうと思います。
取組みの背景
まずは、今回の取組み背景となります。
対象となる処理は、現在外部にお願いをしているのですが、このコストが比較的大きいという事から、『内製化する事でコスト削減をしたい』と言う事が発端となっています。
取組み依頼は、前回と同じような軽いノリで『出来るっしょ』的な口頭依頼からの出発となりました(笑)
前回構築した”不正口座ネットワーク可視化”機能(イメージ図の緑部)の前処理(イメージ図の赤部)となり、AML担当者の白黒判断材料となる、一定条件に適合する取引をピックアップし提示する機能となります。
目標設定
依頼内容の達成が最低ラインの目標となりますが、単なる模倣では面白くないので、幾つかモチベーションが上がりそうな目標を設定しました。
最低ライン目標 |
既存と同等機能再現 |
|---|---|
チャレンジ目標１ |
変更容易性向上 |
チャレンジ目標２ |
作業効率向上させるアウトプット |
チャレンジ目標３ |
新機能の追加 |
チャレンジ目標１は、既存の課題となっている変更対応スピードの向上となります。
内製化するので、基本対応スピードは上がるものですが、改修担当者の負担が少ない仕組みを検討したいと考えました。(※暫くは自分が担当者なので…)
チャレンジ目標２も、既存の課題となっている作業効率向上を期待できるアウトプットを出すという目標となります。
既存では、Excelファイルで受領し、対象レコードのキー項目値をコピペして、別のシステムで調査と言うような事をやっているので、他システムとの連携やデータの見せ方をよく考慮してみたいと考えてました。
チャレンジ目標３は、今回の取組みに有効、且つ個人的なモチベーションを上げる機能を取込みたいと考えての設定となります。
準備
要件整理
今回対象となる機能は、当社共有のデータを元に、当社が設定する検知条件に適合する取引をピックアップする仕組みである為、必要な情報は全て社内に揃っており、不明点は機能を実現しているロジックのみと言う状況でした。
INとOUT、及び検知条件が明確なので、ロジックは自由にして問題ないと判断し、目標を達成できる構成をゼロベースから検討を行う事にしました。
システム構成
当初、クラウドっぽい構成にしようと考え、各取引毎にプロセスを起動し、各プロセスで検知処理を行おうと考えました。(※初期検討案)
当社規模でも日次取引件数は数十万件あるのですが、『一気に数十万プロセスを起動するとかテンション上がるっ！』と考えたのが構想の発端で、暫くこの想定で検討を進めました。
検討を進める中で、この構成では3点の課題があった為、再検討を行いました。
1. 【運用懸念】初期検討案は大量のログ出力が想定される
2. 【パフォーマンス懸念】Cloud Functions制約540秒内に全取引処理完了するか
3. 【追加費用発生】200万件/月までは無料だが、以降は100万回毎$0.4に費用発生
Cloud Functionsの費用に関しては、許容範囲内ではありますが当社分析基盤上で行われる処理は当処理だけでは無い為、無駄にプロセスを多く発生させるべきでは無いと考えました。
下記、Cloud Functionsの費用に関して仮定での試算となります。
1．日次取引数：50万件
2．総検知条件：500種
初期案 |
採用案 |
|
|---|---|---|
月次 process数 |
15,000,000件 (500,000件 * 30日) |
15,000件 (500種 * 30日) |
有料 process数 |
13,000,000件 (15,000,000件 - 2,000,000件) |
無料枠内 |
| 想定月次費用 | $5.2(約780円) ((13,000,000件/1,000,000回) * $0.4) + 利用時間 |
利用時間 |
下記、今回の仕組みを組込んだ全体処理の流れとなります。
既存では、当社データと一緒に外部からのアウトプットファイルを直接"既存処理"部分で取り込んでいるいますが、今回の取組みは当社データからアウトプットを生成するような流れとなります。
大きなポイントは、外部に依存しない仕組みとなる為、"待ち"時間の削減が期待できる事となります。
処理に関して
利用サービスは以前と同様に、Pub/SubやCloud Function、Cloud Storage、BigQueryを利用したものになるので、ポイントとなる部分にのみ触れていきたいと思います。
実行前SQL文字列置換(python)
以前から利用していたtipsですが、複数環境での外部SQLファイルの共通化に関してとなります。
複数環境で同一コードを利用する場合、BigQueryテーブル指定時のプロジェクトIDは異なりますが、外部SQLファイルを2種類用意するのはナンセンスです。
Cloud Functions内で実行前に変数展開(※"{}"部への置換処理)で置換する事も可能ですが、複数テーブルを利用する場合、テーブル数分プロジェクトIDを記述する事もエンジニアとしてはイケていないと感じますし、可読性低下にも繋がります。
一方で、実行するSQLは日付指定して実行するケースにも対応する必要があります。
当該取引X回前取得方法について
今回の検知条件では、対象取引のX回前の取引に対する条件があり、実現方法としてCloud Functionsなどでロジックを組んでの対応も考えましたが、この対応では日次取引数十万件を処理するパフォーマンスは難しいと感じました。
SQL対応と言う方針自体は直ぐに決めたのですが、SQLでどのように実現するかに関して色々と試行錯誤した結果、下記方法で実現できました。
緯度経度データ取得について
検知条件には距離を条件とするものがある為、取引を行っている口座の登録住所を緯度経度データに変換する必要がありました。
Geocoding APIを利用する事で簡単に緯度経度データを取得する事が出来ますが、利用時に3点程注意が必要です。
1. 無料枠が月28,500件
2. google map APIのレスが意外と遅い ※3秒程度
3. 住所⇒緯度経度データが、取得できないケースがある
緯度経度データは急ぎで必要では無かったので実行回数を引数指定するCloud Functionsを構築し、1回150件取得する処理を6回、1日計900件ずつ程取得するように設定して放置する事にしました。
データを取得出来ないケースは、呼出し時の引数(住所)に問題がある事が多い為、ここは一旦手動で取得&登録を行うようにしました。
2点間距離算出について
必要な緯度経度データを揃える事に一定のハードルがありますが、緯度経度データが揃っていれば下記関数を利用して距離情報を取得出来ます。参考サイト
・ st_distance
・ st_geogpoint
BigQueryの上限について
BigQueryは高性能なサービスですが、稀に注意事項にぶつかる事があります。
今回の上限もそのようなものの一つとなります。
検知処理は、親処理から検知条件分の子処理を呼び出す形となりますが、今回BigQueryの上限制約に躓きました。
処理を実行するCloud Functionsが非常に高速である為、"子"プロセスの呼出しが一瞬で数百件起動する事で、前述の上限エラーを大量に吐く事になりました
この制約の回避方法は、Cloud Functionsの処理に一定の制御を入れ、処理を少し鈍化させる事で回避可能です。
具体的には、一定プロセス呼出し後にwait処理を入れるイメージになります。
検知処理について
今回の処理のキモとなるのが、各検知条件毎に用意するSQLとなります。
検知条件毎に1つのSQLを用意する事は、意外と手間なように感じますが、利用が開始され検知条件の追加や削除、修正作業が発生する事を考えると、各検知条件は個別SQLで実装する事が運用面でやりやすいと判断しました。
また、各検知処理は"親"処理制御下で並列実行する構成としていますが、各検知処理は全取引に対して検索を掛ける為、パフォーマンスも重要になってきます。
"親"処理の540秒制約(Cloud Functions)を鑑みて、"子"プロセスは10秒以内での処理完了を目標としました。
数百件の条件を満たすSQL作成作業は個人作業としてはタフな作業の為、当初は条件を満たす事に主眼を置いていたところ、一部条件の処理で数分掛かってしまうケースが発生し、性能の上に胡坐をかいていてはいけないと痛感しました…
諸々試行錯誤の結果、"子"プロセスの平均処理時間は7秒まで改善は出来ましたが、個々の処理時間をみると最大で15秒秒ほど掛かる処理が複数あり、今後の課題と考えています。
加えて、データ増加に伴い処理時間増加も懸念される為、本格利用前には処理の監視機能作成が必要だと感じています。
処理名 |
処理時間 |
備考 |
|---|---|---|
中間TBL作成 |
55秒 |
|
親プロセス |
2分38秒 |
子プロセス平均7秒 |
全体合計 |
3分33秒 |
可視化に関して
当初はLooker Studioを利用で進めていたのですが、一部当社セキュリティ要件に掛かる事柄があり、Lookerへ切り替える事としました。
一部では既にLooker Studioを利用していた為、作り直しを行う事になりましたが、将来性を考えるとLookerへの切り替えは正しい選択であると感じています。
大きなポイントは、データ構成などを意識せず、利用者側で画面の作成作業を行える事にあります。
利用者からの要望で画面を作成する状況は一般的ですが、一部でも利用者側で改修対応が完結する事は利用者/開発者双方に大きなメリットのあるツールだと感じています。
まだまだ、利用開始したばかりですが、幾つか気が追加事柄やデータの見せ方に関して幾つか試行錯誤した事があったので共有させて頂こうと思います。
カスタムクエリについて
Looker StudioやLookerではカスタムクエリを利用して、画面に必要なデータを自作したSQLで取得する事が出来ます。
Lookerでは、カスタムクエリに加えてmodel内で各テーブルを紐付けて、画面に必要なデータを取得する方法も可能となっています。
Looker利用時に、GCP担当者の方に『カスタムクエリの多用に関してどう考えますか』とお聞きしたところ、『(状況にもよるが)Lookerのメリットである汎用性低下に繋がる懸念がある』との回答を頂きました。
当初はピンと来ていなかったのですが、Lookerではテーブルやカスタムクエリを個々のviewと言う形で保持し、modelで結合する事でviewを再利用する事が可能です。
Lookerの強みであるviewの再利用と言う観点でみると、カスタムクエリは一つの用途に対しては無駄がない構成ですが、その他の用途では無駄が発生するケースが散見されました。
再利用可能なカスタムクエリの構築も可能ですが、再利用するようなものであれば中間テーブルの作成を考えた方がシンプルになるように感じました。
結論としては、Lookerではmodelで各テーブルを結合して利用した方がメリットを活かしやすいと考えています。
画面遷移について
Lookerで画面遷移を実現する方法は複数ありますが、今回は1クリックで画面遷移可能なhtmlパラメータを利用する方法を採用しました。
linkを利用する方法でも実現可能でしたが、linkパラメータでは画面遷移に2クリック必要な為、利用を見送りました。
文字の色変換について
Lookerでは下記ケースであれば画面側で文字色設定が可能ですが、"条件での文字型の書式設定"が画面側では出来ませんでした。
1. 文字型は一括での書式設定が可能
2. 数値型は条件での書式設定が可能
"条件での文字型の書式設定"は、"画面遷移"時と同様にviewでの設定を行う事で実現する事が出来ます。
枠内改行について
今回Lookerで実現したい事として、下記イメージ図(右)のように『条件に適合する文字列項目を1項目に纏めたい』と考えていました。
結果として、こちら実現する事は出来ましたが、個人的にイメージ通りの実現とはならなかった為、利用は見送りとしました。
 |
google map利用について
今回の機能にgoogle mapは必須ではありませんが、個人的に利用したいと考えていた為、遊び心で取り入れてみました。
緯度経度データがあればgoogle mapへの展開自体は簡単ですし、画面の見栄えが急にリッチになるので、おススメな機能です。
目標結果
今回の取組みにあたり設定した、目標達成状況は下記となります。
既存と同等機能再現
現在、日次で既存アウトプットと比較を行っていますが、検知精度としては既存よりも高い検知率となっており、こちらはほぼ達成出来た認識です。
変更容易性向上
仕組み自体を内製化し、検知条件を外部SQLファイルに切出した事で、他への影響を最小限に改修対応を行える状況を整える事が出来ました。
対応する私の状況次第ではありますが、外部組織と調整～対応を行うよりは、対応スピードやコスト面で優位であると考えているので、こちらも達成出来たと考えています。
作業効率向上させるアウトプット
実際に作業効率が向上するかは、利用開始後の検証結果を待つ必要はあります。
既存の仕組みでネックとなる部分を改善し、担当者からの要望を取込んだアウトプットをLookerで作成する事が出来たので、目標を達成できるものと期待できるアウトプットは出せたとは考えていますが、まだ判断できないものと考えています。
新機能の追加
この目標は個人的に何かとなりますが、今回はgoogle mapの活用となります。
google mapを組込んだ画面を担当者にお見せしたところ、前向きなコメントを頂けたので、現状目標達成したと言えるかと考えています。
まとめ
今回の取組みも軽いノリからの一人プロジェクト出発でしたが、当初イメージしていた以上に新しい発見や経験・知識を得る機会となり、個人的にとてもよい取組みとなりました。
BIツールのLookerは、ほぼ0から利用を開始ししましたが、初期設定を含め、ある程度作りたいイメージのモノは実現する事が出来ました。
知らぬ所でこの取組みへの期待値が上がっていた事に、少しドキドキはしていますが、現状期待に沿うものを作れたかと感じています。
ただ、利用開始後は安定稼働が求められる為、一人で新しい全て回せるかと言う新しい課題が見え隠れしていますが、クラウドサービスをうまく利用して前向きに対応を行っていきたいと思います。
今回導入したLookerを介する事で、分析基盤利用者の増加が期待できるので、組織としてデータドリブンな意思決定が出来る風土を作っていきたいと考えています。
ーーー
一緒にGMOあおぞらネット銀行で働いてくれる仲間を募集しています。
社内勉強会はもちろん、GMOグループの勉強会にも参加できます。ご興味のあるエンジニアの方は、当社採用ページをぜひ一度ご覧ください。
