Merpay & Mercoin Tech Fest 2023 は、事業との関わりから技術への興味を深め、プロダクトやサービスを支えるエンジニアリングを知ることができるお祭りで、2023年8月22日（火）からの3日間、開催しました。セッションでは、事業を支える組織・技術・課題などへの試行錯誤やアプローチを紹介していきました。
この記事は、「GoによるSQLクエリテストの取り組み」の書き起こしです。
@a-r-g-v(batta)：GoによるSQLクエリテストの取り組みについて、発表させていただきます。
初めに自己紹介をさせてください。2020年に新卒としてメルペイに入社をしました。そこから3年間ほど、メルペイの加盟店管理のシステムを作るチームに配属されていました。現在は、メルカリの認証認可を担うチームに所属しております。
こちらが本日のアジェンダです。
はじめに、背景を説明します。加盟店管理のチームには、複雑な条件を持つBigQueryのSQLクエリがいくつか存在しています。
例えば、加盟店管理の費用計算などの計算クエリのように、外部環境の変化によって要件が定期的に変更され、マイクロサービス化などのシステム化が難しいクエリがあります。このクエリは、複雑であるだけでなくテスタビリティにも問題がありました。
そのため、開発者がテストを実施することが困難になっており、クエリの変更を安心して行えない状態にありました。
では、このクエリの複雑性とはどのようなものだったのでしょうか？大きく二点ありました。
一つは、抽出条件の複雑さです。契約条項に基づく複雑なビジネス要件が、クエリの複雑さを増す要因となっていました。例えば、加盟店管理費用を計算するビジネス要件においては、正しく費用を計算するために、審査通過日、加盟店獲得後の決済情報、決済用QRコードの要否の情報を組み合わせて、クエリを行う必要があります。この条件がクエリを複雑にしていました。
また、クエリが複数のマイクロサービスのデータベースを横断して参照していることによって、複雑さを増していました。メルペイでは、マイクロサービスアーキテクチャを採用しており、業務ドメイン単位でサービスが分割されています。
例えば、加盟店の申し込み、審査、事業者の情報、決済履歴、QRコードの配送履歴などは、それぞれ別のマイクロサービスとして分割されています。一方で、管理費用を計算するためには、これらのデータベースを横断的に参照する必要があります。また、これらの依存しているマイクロサービスの中には、別のチームが管理しているものもあります。この状況が、クエリを複雑にさせています。
では、チームにはクエリにまつわるどんな課題があったのでしょうか？大きく二つありました。
一つは、クエリに対する開発テストが煩雑であったことです。複数のマイクロサービスのテーブルに依存しているために、投入対象のテーブルの数や、投入データ行数が多くなっていました。また、クエリの抽出条件が複雑であるため、必要なテストパターン数が多く、データとして投入する量も多くなっているという課題がありました。
また、手作業の多さも問題でした。実際のテスト環境のテーブルに対して、マイクロサービスが生成していないデータを投入することは、問題です。そのため、クエリをテストするために、新しくテーブルを作り、テストデータを投入した後にそのテーブルを使用するようにクエリを書き換え、クエリを実行・結果検証・クリーンアップという手順を、都度のクエリごとに行う必要がありました。これを毎回のクエリ改修や、テストパターンごとに行うのが大変であるという課題がありました。
二つ目は、クエリに対する自動テストの欠如です。デグレードを検出する自動テストスイートが存在しませんでした。そのため、クエリの変更を安心して行うことができない状態になっていました。
これらの課題に対する解決方法として、Goのテストから、クエリに対するテストを実装できる仕組みを作成しました。
具体的には、テストコードから参照されるライブラリとして実装しました。これは、テストコードによってテストデータを投入し、BigQuery上でSQL実行を簡単に行える仕組みを組みました。この仕組みによって、可読性・メンテナンス性が高い形でテストを行えるようになりました。
また、このテストデータ作成を支援するために、クエリからGo構造体を自動で生成するツールも作りました。さらに、この方法で作成した自動テストケースを蓄積し、CIで実行することによって、デグレードの検知を自動化しました。
次に、全体の動作イメージを説明します。クエリのテストはGoのテストとして、テストケースを実装するようにしました。
テストケースごとに、こちらを実行します。まず、テスト対象が依存しているテーブルを抽出して、テスト用のデータセット配下にテーブルを1件ずつ作成します。
テストケースで指定されているテストデータを、テスト用のテーブルに挿入します。次に、テスト対象のクエリのFROM句に書かれているテーブル名を、作成したテスト用のテーブルを利用するように書き換えます。
書き換えたクエリを実行して、期待している結果と同じかどうかを確かめ、最後にテストケースのクリーンアップ動作で作成したテスト用のテーブルを全て削除する形で動作します。
また、テストケースから、テストデータの投入を支援するために、クエリが利用しているテーブルをGoの構造体として自動生成する仕組みも作りました。
具体的には、同一リポジトリに存在する全てのSQLファイルを読み、コード生成を行うコマンドを作りました。コマンドは二つの構造体を生成します。
一つは、クエリが依存しているテーブル一覧を表す構造体です。クエリが依存しているテーブルを全部列挙して、対応関係をGoの構造体として生成します。ここの利用テーブルの列挙は、正規表現を用いてFrom句をパーズして行いました。
二つ目は、各テーブルの定義に対応するGoの構造体です。上記で算出したクエリごとに、テーブルのスキーマ定義を実際のテスト環境のBigQueryのテーブル定義を参照し、自動生成するコマンドを作成しました。
それでは、実際に作成したツールを用いて、クエリに対してテストを作成する方法を実現したいと思います。
こちらがデモで使用するテスト対象のSQLクエリです。このクエリは、UsersテーブルとChargeテーブルをユーザーIDで結合して、ReferralTypeがORGANICであるユーザーの決済金額の合計を算出するクエリです。このクエリに対してテストを書きます。
先立って、このUsersテーブルとChargeテーブルのテーブル定義をお見せしたいと思います。
こちらが、BigQuery上に存在するUsersテーブルの定義です。Usersテーブルは、ユーザー1行ごとに行が作成されるテーブルです。
もう一つは、ペイメントサービスのChargeテーブルです。Chargeテーブルは、ユーザーの決済コードを1回ごとに1行作成されるテーブルです。
それでは、実際にテストを変えていきたいと思います。まずテストを書く前に、コード生成のコマンドを実行します。このコマンドを実行することによって、このクエリが依存しているUsersテーブルとChargeテーブルのGoの構造体が自動で生成されます。
また、先ほどのクエリが依存しているGoのテーブルの一覧を表す構造体と、BigQueryに対して、簡単にデータを投入できるヘルパー関数が生成されます。このヘルパー関数を使って、クエリに対してテストを書いていきます。
ヘルパー関数を指定し、Goのテストを書いていきます。スライスの一つ一つが1行に対応するイメージです。先ほどのクエリは、ユーザーIDでジョインしていたテーブルですから、同じユーザーIDを使うために定数を宣言しておきます。
このように同じユーザーIDを使用するようにして、金額も1,000にしておきます。あとユーザーのタイプがオーガニックでないといけないので、そのようにします。このヘルパー関数が生成するクエリをプリントするのと、実際にBigQuery上でこのクエリを実行してみたいと思います。
実行すると、まずクエリが出力されます。ヘルパー関数がテストテーブルを作り、データを挿入して、そのテストテーブルを使用するように、書き換えたクエリが作成されています。
これをBigQueryで実行すると、ここで投入したデータ、つまり「Totalamount：1000」というデータが実際に出力されます。このように、Goのテストの書き味で、クエリに対してテストが書けるようになります。
次にクエリテストの取り組みの効果についてお話します。大きく二つの効果がありました。
一つ目は、開発者テストの実施が容易になり、安心してクエリを変更できるようになったことです。テストデータのコーディングに型の支援を入れるようになったため、列名やデータ種別の誤指定の防止や、IDによるコード補完の恩恵を受けることができるようになりました。また、テストデータの共通化やGoのテーブルテストの活用が可能になり、差分テストケースの追加が簡単になりました。
これにより、境界値テストケースの一つの値だけを変更してテストを行うケースの追加が簡単になりました。また、共通化により、クエリに対してテストを網羅的に実施するコストが低下しました。
二つ目は、開発者テストの蓄積によりデグレード検知ができるようになったことです。自動化されたテストケースが蓄積されたことにより、クエリ変更に際するでグレートの検出が簡単に行えるようになりました。
そのため、より安心感を持ってクエリ変更を行うことが可能になりました。
今後の展望として、追加したい機能や応用の方法を三つ考えています。
一つは、QAテストケースの置き換えの検討です。QAテストにおいても本ツールを応用して、テストデータの投入やQAテストの実施、テストケースのメンテナンスをQAチームが完結して行える仕組みを作成したいです。
二つ目は、クエリのテストケースの網羅性を可視化する方法の検討です。Goの通常のテストでは、コードカバレッジなど、テストケースの網羅性を計算・可視化するためのメトリクスを簡単に利用できます。同様に、SQLクエリに対してテストカバレッジを算出する研究があり、類似の仕組みを本手法にも導入できないか検討したいと考えています。
三つ目は、投入テストデータの正しさの検討です。投入テストデータとマイクロサービスが実際に作成するデータに不一致があると、テストの意味がなくなってしまいます。現状では、クエリテストに利用するテストデータを作成する際、依存マイクロサービスの振る舞いを理解して、データを作成しています。この不一致のリスクを最小限にするために、データインターフェースの明文化などを検討したいと考えております。
以上で発表を終わりにします。ご清聴ありがとうございました。
