SQLをテストする簡単なフレームワークを作ってみた

概要

前職ではしばしば複雑なSQLを書く必要があり、「そのSQLが間違っていないことをどう確認したらよいのか？」と悩むことがあった（特に分析用のSQLが長くなりがちだった）。そんな課題感から、SQLをテストするフレームワークをさくっと作ったので紹介する。
このツールを使うと「まずテストを書く→テストを通過するSQLを書く」というテスト駆動のコーディングも可能になるので、tdsql（Test Driven SQL）という名称にした。GitHubはこちら。

特徴

• SQLの任意の部分を置き換えて最終的な結果が意図通りかテストできる
• テストはyamlで記載する

「from句など入力部分を置き換えて出力が意図通りか確認する」というのが典型的な使い方になるかと。

使い方

現状BigQueryしか対応していないため、BigQueryの前提で書く。

インストール

pipで簡単にインストールできる。Python3.10以上が必要なはず。

pip install 'tdsql[bigquery]'

準備

BigQueryを使うので認証しておく。

gcloud auth application-default login

# サービスアカウントを使う場合
# export GOOGLE_APPLICATION_CREDENTIALS=/path/to/keyfile.json

動作確認のために簡単なSQLとyamlを準備する。tablenameというテーブルのデータを集計するのだが、まずはSQL自体に間違いがないことをテストしたい、という状況を想定している。

./sample.sql

select json_extract(json_string, '$.data') as extracted_data
-- tdsql-start: replace_here
from `tablename`
-- tdsql-end: replace_here

./tdsql.yaml

database: bigquery
tests:
  - filepath: ./sample.sql
    replace:
      replace_here: |
        from unnest([
          '{"data": "foo"}',
          '{"data": "bar"}'
        ]) as json_string
    expected: select * from unnest(['foo',　'bar']) extracted_data

簡単に説明すると、置換する場所を-- tdsql-xxx:から始まるSQLのコメントで指定している。どのように置換するか＆最終的に期待される結果はyamlファイルに記載している。

実行

tdsqlというコマンドでテストを実行する（先ほどのSQL・yamlファイルと同じディレクトリで実行すること）。すると、次のようなありがちなエラーが見つかる。

/path/to/sample.sql_1: value does not match at line: 1, column: extracted_data
actual: "bar", expected: bar

これは、例えばjson_extract()をjson_extract_scalar()に直せば解決する。その後、もう一度tdsqlを実行すればエラーが出ないことを確認できるだろう。

Tips

発展的な使い方についてはGitHubにサンプルを置いておいた。以下、いくつか抜粋。

• 何GiB以上のクエリは実行しない、など細かい設定もできる
• テストが増えてきたらyamlファイルを分割することもできる
• SQLにコメントを記載せず、yaml側で置換する行を指定することもできる¹

最後に

「このSQLはこういう入力に対してこういう出力を返すんだ！」というテストを明確に書けるのは特徴的だと思う。注意が必要なのは、実際の入力データが意図通りかは別途確認が必要ということ²。

さくっと作ってみたはいいものの、正直需要があるのか自信がないので、何か意見があればコメント歓迎です。

1. チームで自分しかtdsqlを使っていない状況でSQLにコメントを追加すると、「SQLに変なコメントを書くな！」と怒られるだろう。誰にも迷惑をかけずに個人的にtdsqlを使い始めるなら、①tdsql.yamlをGitの管理から外して②tdsql.yamlだけにテストを記載する。 ↩

2. この記事では、tablenameというテーブルをテスト用のデータで置き換える例を紹介した。ここで注意したいのは「tablenameというテーブルに壊れたjson文字列が含まれないか？想定外のnullが含まれないか？その他テーブルの仕様について勘違いはないか？」といったテストはできていないということ。 ↩