GoでSQLの複雑なクエリのテストを書いてみた

Go言語 BigQuery Test

ogp

はじめに

こんにちは。ブランドソリューション開発本部FAANSバックエンドブロックの佐野です。普段はサーバーサイドエンジニアとして、FAANSのバックエンドシステムを開発しています。

FAANSとは、弊社が2022年8月に正式ローンチした、アパレル店舗で働くショップスタッフの販売サポートツールです。例えば、コーディネート投稿機能や成果確認機能などを備えています。投稿されたコーディネートはZOZOTOWNやWEAR、Yahoo!ショッピング、ブランド様のECサイトへの連携が可能です。成果確認機能では、投稿されたコーディネート経由のEC売上やコーディネート閲覧数などの成果を可視化しています。

本記事では、成果データの集計処理におけるBigQueryのクエリ実行処理のユニットテストをGoで実装した取り組みと、その際の工夫についてご紹介します。

目次

成果データの集計処理とは

本題へ入る前に、FAANSの成果データの集計処理について簡単にご説明します。

全社のデータ基盤のBigQueryには、例えばWEARやZOZOTOWNのユーザーが「コーディネート画像を閲覧した」といった様々な種類のビジネスイベントのデータが格納されています。それらのイベントデータから、コーディネート画像がどれくらい閲覧されたかや、コーディネート画像経由でどの商品がいくつ購入されたかといった様々な種類の成果のデータをバッチ処理で集計しています。

抱えていた課題

前述の通り、FAANSはZOZOTOWN・WEAR・Yahoo!ショッピング・ブランド様のECサイトと連携しているという特性上、複数のデータソースからデータを抽出する必要があります。

FAANSと連携しているサービス 特に日次のバッチ処理で行っている成果データの集計処理では、複雑な条件によりSQLのクエリが長くなる傾向にあります。長いものだと10テーブルをJOINした上、WHERE句で8つほどの条件を指定しているため、100行以上になることもあります。

そのような複雑なロジックを持つクエリに関して、以下のような課題がありました。

バグが発生しやすい

長いクエリは読み解くのにコストがかかります。WHERE句ひとつとっても、背景を把握していないとなぜこの一文が必要なのかが伝わらない場合もあります。そのようなクエリに対して追加修正が必要となった際に、ロジックを読み違えて意図しない変更を加えてしまう可能性がありました。

動作確認が煩雑になる

動作確認の際にはその都度手作業でデータを用意しなくてはならず、また複数の条件に合致するデータを用意するだけでも確認事項が多いため、そこに多くの時間が取られてしまっていました。その結果、開発効率が落ちてしまったり属人化してしまうという状況にありました。

正しい動作を判断しづらい

100行にもわたるSQLのクエリは、どのような動きが正しいのかを判断しづらく、レビューコストが高いという課題もありました。SQL内のコメントで基本的なロジックについての説明ができても、様々なパターンのデータに対してどのように動作するかを詳細に伝えるのは困難でした。

なぜSQLのテストをGoで書いたのか

FAANSでは、Web APIサーバーやバッチ処理といったバックエンドシステムの全てをGoで実装しています。また、今後も様々な分析データを提供するために、複雑なクエリを用いたデータ抽出処理は増えていくと考えられます。それらの理由から、チームの学習コストや開発生産性を考慮して、他言語のツールを導入するのではなくGoで書くことが適切と判断しました。

テストの実装

では、どのようにテストを書いたか説明していきます。

フィクスチャ

今回は、SQLを組み立ててBigQueryでクエリを実行する処理のユニットテストを実装しました。テストデータの投入はメルカリ社の記事（Goでテストのフィクスチャをいい感じに書く）を参考に用意したフィクスチャを使用しました。

テストケースの分割

テストケースの分割では、網羅性と凝集度を高めることを意識しました。例えば、売上の種類が3つ存在し、それぞれの合計額を取得する処理のテストを書くとします。

悪い例

func TestCalculateSalesAmount(t *testing.T) {
    f := fixture.Build(t,
        fixture.Sales(func(s *model.Sales) {
            s.Type = "A"
            s.Amount = 1000
        }),
        fixture.Sales(func(s *model.Sales) {
            s.Type = "A"
            s.Amount = 2000
        }),
        fixture.Sales(func(s *model.Sales) {
            s.Type = "B"
            s.Amount = 5000
        }),
        fixture.Sales(func(s *model.Sales) {
            s.Type = "C"
            s.Amount = 3000
        }),
        fixture.Sales...
    )
        
    ...
 
    f.Setup(t)

    t.Run("タイプA・タイプB・タイプCの売上データが取得でき、それぞれの合計額は3000円・5000円・10000円である" , func(t *testing.T) {
        ...

        if len(result) != 3 {
            t.Errorf("売上データの件数に過不足がある")
        }
        if result[0].Type != "A" {
            t.Errorf("タイプAの売上データが取得できていない")
        }
        if result[0].SalesAmount != 3000 {
            t.Errorf("タイプAの売上データの合計額に誤りがある")
        }
        if result[1].Type != "B" {
            t.Errorf("タイプBの売上データが取得できていない")
        }
        if ...
    })
}

このように複数のパターンを一度にテストしようとすると、準備するべきデータや判定ロジックが増えてしまいます。すると、凝集度が低く見通しの悪いものとなってしまうため、ひとつのテストケースで確認する必要がないものは、以下のように分割しました。

良い例

func TestCalculateSalesAmount_TypeA(t *testing.T) {
    f := fixture.Build(t,
        fixture.Sales(func(s *model.Sales) {
            s.Type = "A"
            s.Amount = 1000
        }),
        fixture.Sales(func(s *model.Sales) {
            s.Type = "A"
            s.Amount = 2000
        }),
        fixture.Sales(func(s *model.Sales) {
            s.Type = "B" // 合算の対象にならないことを確認するために作成
            s.Amount = 5000
        }),
    )
        
    ...
 
    f.Setup(t)

    t.Run("タイプAの売上データが取得でき、合計額は3000円である" , func(t *testing.T) {
        ...
     
        if result[0].Type != "A" {
            t.Errorf("タイプAの売上データが取得できていない")
        }
        if result[0].SalesAmount != 3000 {
            t.Errorf("タイプAの売上データの合計額に誤りがある")
        }
    })
}

分割したことで準備するデータや確認項目も減り、テストケースの意図が明確になりました。このように分けることで、TestXXX_TypeB、TestXXX_TypeCとテストコードが増えることにはなりますが、テストケースの凝集度を高めると、結果的にメンテナンス性も向上します。

テストケースごとにテーブルを作成

前項で示したように、複数のテストケースが存在していると、別のテストケースで用意したデータを参照してしまい、意図した結果とならないという問題が起きます。テストケース同士の関連やデータの競合について考慮しながらメンテナンスしていくのは難しいため、テスト用のヘルパーを用意してテストケースごとにテーブルを作成・削除するという方法をとりました。

テストケースごとにテーブルを作成

まず、作成したいテーブルのスキーマをコピーして、テスト用のBigQueryのデータセットにテーブルを作成します。テーブル名はテストケース間で重複しないように、本来の名前のsuffixにランダムなIDを追加したものとしました。なお、必要なデータを投入してテストを実行した後、作成したテーブルを削除します。

テストケースごとにテーブルを分けたことにより、並列実行が可能になるというメリットもありました。実行時間の短縮のためにも、Goの標準パッケージであるtestingパッケージのt.Parallel()メソッドを使って、それぞれのテストケースを並列で動かすようにしました。

Goのtemplateを使って接続先の差し替えを容易に

テスト対象の処理では、SQLはファイルに切り出し、Goの標準パッケージであるtext/templateパッケージを用いて組み立てるようにしました。そうすることで、環境ごとに接続先のBigQueryのテーブルを切り替えることが可能になりました。また、指定するテーブルの情報はメソッドの外から渡すようにして、テスト時も差し替えがしやすい作りを意識しました。

SELECT
    ...
FROM
    {{.project_id}}.{{.dataset_id}}.{{.table_id}}
WHERE
    ...
// templateを使ったSQLの組み立て
func BuildSQL(path string, params any) (string, error) {
    body := &bytes.Buffer{}

    t, err := template.ParseFS(templates, path)
    if err != nil {
        ...
    }
    if err := t.Execute(body, params); err != nil {
        ...
    }

    return body.String(), nil
}


// 呼び出し側
sql, err := query.BuildSQL("hoge.sql", map[string]string{
    "project_id":   "hoge", // GCPのプロジェクトID
    "dataset_id":   "fuga", // BigQueryのデータセットID
    "table_id":     "piyo", // BigQueryのテーブルID
})

結果

実際にテストを書いてみたところ、1つのクエリに対するテストで検証する項目が最大20パターン存在しました。これら全ての動作確認を手作業でデータを用意して行うのは非現実的です。また、テスト対象は一度しか実行されないクエリではなく定常的に実行されるもので、今後プロダクトが成長するにつれて、新たなカラムが追加されたり仕様が変わったりする可能性も大いにあります。そのような処理に対するテストの実装をしたことで、以下のような効果がありました。

開発効率が上がった

フィクスチャの実装によって手動でデータを投入しなくてよくなり、煩雑な作業を無くすことができました。また、SQLに修正を加えてロジックが変更されても、テストデータの変更が容易になったため、すぐに動作確認ができました。テストを動かすための仕組みができるまでには一定のコストがかかりましたが、一度作ってしまえばその後の開発は進めやすくなるということを体感しました。

バグが発生しにくくなった

境界値のテストが簡単にできるようになり、細部までテストを書くことでバグの発生リスクを下げることができました。変更を加えた際に予期せぬ影響があっても、テストの失敗でそれに気付くことができ、心理的なハードルも下がりました。実際に、最近リリースした案件で新たに実装した成果データの集計処理では、QA（品質保証）でSQLの実装が原因のバグは見つかりませんでした。

レビュアーの負担が減った

テストを書くまでは、レビュアーは仕様書と実際の処理を見比べながら、複雑なクエリを読み解く必要がありました。しかし、テストケース名から仕様の概要を把握できるようになり、効率よくレビューできるようになりました。テストケースの不足から考慮漏れに気付きやすくなり、レビューの質も向上しました。

QAの負担が減った

最大20パターンの検証項目があると、QAの際に必要となるデータのパターンも多岐に渡ります。ユニットテストでエッジケースを担保できるようになったため、QAの工数の削減に繋がりました。

今後の展望

今回の取り組みにより一定の効果が得られた一方で、技術的負債の影響でテストが書けていない箇所もまだ存在します。動作を確認しているとはいえ、テストを書けていない機能のリリースには不安が残るため、負債を解消しながらこの取り組みを継続的に行っていきたいと考えています。また、今後は以下の観点でも改善していきたいです。

ボイラープレートの自動生成

現状では、作成するテーブルのスキーマやフィクスチャのボイラープレートを手動で書いています。ある程度テストを書いてパターンが見えてきたため、今後はこれらを自動生成して、テスト作成の効率を上げていきたいです。

QA用テストデータ作成の環境づくり

ユニットテストがあるとはいえ、QAではシナリオテストを実施したいと考えています。その場合、必要なデータの準備にはまだまだ改善の余地があります。データ投入を簡単に行えるツールを作成するなど、より良い方法を模索していければと考えています。

さいごに

ZOZOでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください。

corp.zozo.com

カテゴリー