mercariengineering
XLinkedInRSS
XLinkedInRSS

GoによるSQLクエリテストの取り組み

Author:
MukasYuki
MinamotoShosaburo
 , 
GoによるSQLクエリテストの取り組み

この記事は、Merpay Tech Openness Month 2023 の1日目の記事です。

背景

メルペイのバックエンドエンジニアのa-r-g-vとsminamotです。私達はメルペイ加盟店の管理システムを開発しているチームに所属しています。私達のチームには、複雑な条件を持つBigQueryのSQLクエリがいくつか存在しています。例えば、加盟店管理に関する費用計算などの計算クエリのように、外部環境の変化によって要件が定期的に変更され、マイクロサービス化などのシステム化が難しいクエリがあります。このようなクエリは複雑であるだけでなく、テスタビリティにも問題がありました。そのため、開発者がテストを実施することが困難になっており、クエリの変更を安心して行うことができない状態にありました。

クエリの複雑性

抽出条件の複雑さと複数のマイクロサービスへの依存により、クエリが複雑になっていました。

抽出条件の複雑さ

契約条項に基づく複雑なビジネス要件が、クエリの複雑さを増す要因となっていました。例えば加盟店管理費用を計算するビジネス要件においては、正しく費用を計算するために審査通過日 、加盟店獲得後の決済情報、決済用QRコードの要否のような情報を組み合わせてクエリを行う必要があります。このような条件がクエリを複雑にしているのです。

複数のデータベースへの依存

クエリが複数のマイクロサービスのデータベースを横断して参照することが、複雑さを増していました。メルペイではマイクロサービスアーキテクチャを採用しており、業務ドメイン単位でサービスが分割されています。例えば加盟店の申込み、審査、事業者の情報、決済、QRコード配送などは、それぞれ別のマイクロサービスとして分割されています。一方で前述した管理費用を計算するためには、これらのデータベースやテーブルを横断的に参照する必要があります。また、依存しているマイクロサービスの中には、別のチームが管理しているものもあります。このような、依存するテーブル数の多さがクエリを複雑にさせていました。

課題

クエリに対する開発者テストの煩雑さ

開発者テストを煩雑にしていたのは主に以下2つの点でした。

一つ目はテストデータの投入が煩雑であったことです。複数のマイクロサービスのテーブルに依存しているために、投入する対象のテーブルの数や投入データ行数が多くなってしまっていました。また、クエリの抽出条件が複雑であるため、必要なテストパターン数が多く、そのためデータとして投入しないといけない量も多くなっている課題がありました。

二つ目の課題は、手作業が多いことです。実際のテスト環境のテーブルに対して、マイクロサービスが生成していないデータを投入することは問題です。そのため、クエリをテストするために新しくテーブルを作り、そこにテストデータを投入した後に、そのテーブルを使用するようにクエリを書き換え、クエリ実行と結果検証・クリーンアップという手順を行う必要があります。これを毎回のクエリ改修や、テストパターン毎に行うのが大変であるという課題がありました。

クエリに対する自動テストの不存在

クエリに対する自動テストの欠如も課題でした。デグレード（機能低下）を検知できる自動テストスイートが存在しませんでした。そのため、クエリの変更を安心して行うことができない状況でした。

解決策

この問題を解決するために、Go言語を用いてクエリに対するユニットテストを実装する仕組みを作りました。主に、以下の2点を実施しました。

  • Goのテストコードの中でテストデータを投入し、BigQuery上でのSQL実行を簡単に行えるように、専用のヘルパー関数を作成した
  • テストデータ作成を支援するために、クエリからGo構造体を自動生成するツールを作成した

これらにより、クエリの実行結果が意図通りなっていることをGo言語のtesting packageを使って可読性・メンテナンス性が高い形でテストできるようになりました。

動作イメージ

全体の動作イメージを説明します。クエリのテストはGoのテストとしてテストケースを実装するようにしました。テストケースごとに、以下を実行します。

  1. テスト対象のクエリが利用しているテーブルを抽出し、テスト用データセット配下にテーブルを1件ずつ作成します。
  2. テストケースで指定されているテストデータを、テスト用テーブルに挿入します。
  3. テスト対象のクエリのFROM句に書かれているテーブル名を、上記で作成したテスト用テーブルを利用するように書き換えます。
  4. 書き換えたクエリを実行し、期待している結果と同じか確かめます。
  5. テストケースのクリーンアップ動作で、作成したテスト用テーブルをすべて削除します。

また、テストケースからのデータ投入を支援するために、クエリが利用しているテーブルを Goの構造体として自動生成する仕組み https://github.com/ginokent/bqschema-gen-go をベースに作成しました。具体的には、同一リポジトリに存在する全てのSQLファイルを読み、コード生成を行うコマンドを作りました。コマンドは2つの構造体を生成します。

  • クエリが利用しているテーブル一覧を表すGo構造体
    • クエリが利用しているテーブルを全列挙し、対応関係をGoの構造体として生成します。利用テーブルの列挙は正規表現を利用し、FROM句をパーズして行います。
  • クエリが利用しているテーブル定義に対応するGo構造体
    • クエリが利用しているテーブルのスキーマ定義を実際のステージング環境のBigQueryテーブル定義を参照し生成します。

例えば、以下のようなクエリがあったとします。

SELECT SUM(Charge.Amount) As TotalAmount FROM
   `querytest-demo.user_service.Users` Users INNER JOIN
   `querytest-demo.payment_service.Charge` Charge ON Users.UserID = Charge.UserID
WHERE Users.ReferralType = "ORGANIC"

ここから、コード生成コマンドを実行すると、以下のような2つの構造体が生成されます。

// Code generated by bigqueryschema; DO NOT EDIT.
package bigqueryschema

import "cloud.google.com/go/bigquery"

// Charge is BigQuery Table `querytest-demo:payment_service.Charge` schema struct.
type Charge struct {
  ChargeID  bigquery.NullString    `bigquery:"ChargeID"`
  UserID    bigquery.NullString    `bigquery:"UserID"`
  Amount    bigquery.NullInt64     `bigquery:"Amount"`
  Status    bigquery.NullString    `bigquery:"Status"`
  CreatedAt bigquery.NullTimestamp `bigquery:"CreatedAt"`
  UpdatedAt bigquery.NullTimestamp `bigquery:"UpdatedAt"`
}

// Users is BigQuery Table `querytest-demo:user_service.Users` schema struct.
type Users struct {
  UserID       bigquery.NullString    `bigquery:"UserID"`
  Name         bigquery.NullString    `bigquery:"Name"`
  ReferralType bigquery.NullString    `bigquery:"ReferralType"`
  CreatedAt    bigquery.NullTimestamp `bigquery:"CreatedAt"`
  UpdatedAt    bigquery.NullTimestamp `bigquery:"UpdatedAt"`
}
// Code generated by gentestqueries; DO NOT EDIT.
package testqueries
import (
  "context"
  "testing"

  "github.com/stretchr/testify/require"

  "github.com/a-r-g-v/querytest-demo/src/bigquery"
  "github.com/a-r-g-v/querytest-demo/src/bigqueryschema"
  "github.com/a-r-g-v/querytest-demo/src/querytest"
)

var QueryQueriesTotalUserAmount = querytest.NewQuery("queries/total_user_amount.sql")

type QueriesTotalUserAmountParams struct {
  Charge []bigqueryschema.Charge
  Users  []bigqueryschema.Users
}

func (i *QueriesTotalUserAmountParams) ToMap() map[string]interface{} {
  return map[string]interface{}{
     "querytest-demo.payment_service.Charge": i.Charge,
     "querytest-demo.user_service.Users":     i.Users,
  }
}
func QueriesTotalUserAmount(t *testing.T, bq *bigquery.Client, i *QueriesTotalUserAmountParams, options ...querytest.Option) *querytest.QueryTest {
  t.Helper()

  qt, err := querytest.NewQueryTest(t, context.Background(), bq, QueryQueriesTotalUserAmount, i.ToMap(), options...)
  require.NoError(t, err)
  return qt
}

この 2つのファイルを利用して、コーダーは以下のようなテストコードを書くことができます。

package test

import (
  "context"
  "fmt"
  "testing"

  "cloud.google.com/go/bigquery"
  "github.com/a-r-g-v/querytest-demo/src/bigqueryschema"
  "github.com/a-r-g-v/querytest-demo/test/testqueries"
  "github.com/google/uuid"
  "github.com/stretchr/testify/assert"
  "github.com/stretchr/testify/require"
)

func TestAmount(t *testing.T) {
  userID := uuid.NewString()
  qt := testqueries.QueriesTotalUserAmount(t, bigQueryClient, &testqueries.QueriesTotalUserAmountParams{
     Charge: []bigqueryschema.Charge{
        {
           ChargeID:  bigquery.NullString{},
           UserID:    ValidString(userID),
           Amount:    ValidInt64(1000),
           Status:    bigquery.NullString{},
           CreatedAt: bigquery.NullTimestamp{},
           UpdatedAt: bigquery.NullTimestamp{},
        },
     },
     Users: []bigqueryschema.Users{
        {
           UserID:       ValidString(userID),
           Name:         bigquery.NullString{},
           ReferralType: ValidString("ORGANIC"),
           CreatedAt:    bigquery.NullTimestamp{},
           UpdatedAt:    bigquery.NullTimestamp{},
        },
     },
  })
  result, err := bigQueryClient.RunQuery(context.Background(), qt.Query())
  require.NoError(t, err)

  assert.Equal(t, 1000, result[0]["TotalAmount"])
}

クエリテストの動作デモ

効果

この仕組みを導入したことにより、以下の効果がありました。

開発エンジニアによるテストへの効果

  • 開発者テストの実施が容易になり安心してクエリを変更できるようになった
    • テストデータのコーディングに型の支援を得られるようになった
      • 列名やデータ種別の誤指定の防止
      • IDEによるコード補完の恩恵
    • テストデータの共通化やテーブルテストの活用が可能になり差分テストケースの追加が簡単になった
      • 境界値のテストケースのような 1つの値だけを変更してテストを行うというようなケースの追加が簡単になった
      • 共通化によりクエリに対するテストを網羅的に実施するコストが低下した
  • 開発者テストケースの蓄積によりデグレート検知できるようになった
    • 自動化されたテストケースが蓄積されたことによりクエリ変更に際するデグレートの検出が簡単に行えるようになった
    • より安心感を持ってクエリ変更を行うことが可能になった

QA エンジニアによるテストへの効果

テストデータの作成が効率化された

関係するマイクロサービスが多いこともあり、テストデータを作成するためにかなりコストがかかっていました。例えばテストしたいパターンが100通りある場合、手動でテストデータを100通り作成する必要があったのですが、この仕組みによりQA エンジニアはテストデータのパターンを考え、テストデータの投入をお願いするという形になりテストデータ作成にかかっていた工数はかなり削減されました。
※今後はQAエンジニアでテストデータ投入まで行えるようになる予定です。

より精度の高いテストが行えるようになった

今まではテストデータの作成が困難で諦めていたテストパターンについてもテストが行えるようになりました。例えば時間の条件として2023年4月1日 0:00:00という条件があった場合、2023年3月31日 23:59:59と2023年4月1日 0:00:00のテストデータを作成する必要があります。ただ、こういったテストデータを手動で作成することは不可能に近く、厳密な境界値でのテストは諦めていました。

この仕組みを活用することでこのようなテストデータの作成も容易になり、今まで諦めていたテストパターンについてもテストが行えるようになったため、より精度の高いテストが行えるようになりました。

導入後の課題

上記のクエリテストの仕組みを導入することで複雑なクエリに対してもテストを行うことができ、クエリの修正時も安心感を持って修正作業を行うことができるようになりました。
一方でテストコードが拡充していく中で次のような問題に直面しました

  • テストケースが増えることによるテスト実行時間の増加
  • テスト実行時間を抑えるためにテストの並列化を行ったことでBigQueryの最大同時実行クエリ数を超える割り当てエラーの発生

エミュレータの導入

上記の課題を解決するために、BigQueryのエミュレータを導入することにしました。エミュレータを利用することで、テストケースやテスト内で実行するクエリ数が増えても、BigQuery自体にリクエストが行われないため、安定したパフォーマンスが期待できます。

BigQueryでは公式のエミュレータが提供されていません。そこでメルペイ Architect の@goccy により作成されOSSとして公開されている bigquery-emulator を利用しました。
bigquery-emulator はGoで実装されたBigQueryのエミュレータサーバです。betaプロジェクトではありますが、すでに多くの機能が実装されています。
テストと同一のプロセスでエミュレータを起動することができるため、テストの前処理としてエミュレータサーバを起動し、BigQueryクライアントのリクエスト先に起動したエミュレータサーバを指定するように変更しました。

package test

import (
    "context"
    "testing"

    "cloud.google.com/go/bigquery"
    "github.com/goccy/bigquery-emulator/server"
    "github.com/goccy/bigquery-emulator/types"
    "google.golang.org/api/option"
)

func NewClient(t *testing.T, useBQEmulator bool, projectID, datasetID string) (*bigquery.Client, error) {
    t.Helper()

    var opts []option.ClientOption

    if useBQEmulator {
        bqServer, err := server.New(server.TempStorage)
        if err != nil {
            return nil, err
        }
        if err := bqServer.Load(
            server.StructSource(
                types.NewProject(
                    projectID,
                    types.NewDataset(
                        datasetID,
                    ),
                ),
            ),
        ); err != nil {
            return nil, err
        }
        ts := bqServer.TestServer()
        t.Cleanup(ts.Close)

        opts = append(opts, option.WithEndpoint(ts.URL), option.WithoutAuthentication())
    }

    ctx := context.Background()
    return bigquery.NewClient(ctx, projectID, opts...)
}

エミュレータの導入により、BigQueryの最大同時実行クエリ数を超える割り当てエラーを起こすことなくテストを実行できるようになり、テストの実行速度も改善され導入前後で約55%のテスト実行時間の削減が実現できました。

今後の展望

今後、さらに追加したい機能や応用の方法については以下の3つを考えています。

QAテストケースの置き換えの検討

クエリテストをQAフローにも導入することによりQAテストにおけるテストデータ投入の効率化ができましたが、現状はQAチームが作ったテストケースをもとにエンジニアがデータ投入用のテストロジックを作成・実行し、再度QAチーム側でそのデータを利用した確認を行っています。

QAテストにおいてもテストケースに応じて柔軟かつより容易なテストデータ投入からQAテストの実施、テストケースのメンテナンスをQAチーム側で完結できる仕組みを作成したいと考えています。

クエリテストのケース網羅性可視化

クエリのテストケースの網羅性を可視化するためのメトリクスを導入したいとチームメンバーで議論しています。

Goの通常のテストでは、コードカバレッジ等のテストケース網羅性を計算・可視化するためのメトリクスを簡単に利用できます。SQLクエリに対してMC/DCカバレッジを使用する研究があり、類似の仕組みを本手法にも導入していきたいです。

投入テストデータの正しさの検討

投入テストデータとマイクロサービスが実際に生成するデータに不一致がある場合、テストの意味がなくなってしまいます。現状はクエリテストに利用するテストデータを作成する際、依存マイクロサービスの振る舞いを理解してデータを作成しています。この不一致のリスクを最小限にするために、データインターフェースの明文化を検討したいと考えています。
  • X
  • Facebook
  • linkedin
  • このエントリーをはてなブックマークに追加

Related article