BigQueryのクエリをテストするためのツールbqqtestを作った

ETL などで BigQuery を頻繁に活用するため、複雑なクエリを扱うことが増えてきました。私はコードを書くとき、テストも書くのが習慣になっています。そうなってくるとクエリのテストが欲しくなるのは必然です。

# 作ったツール

* PyPI: https://pypi.org/project/bqqtest/
* GitHub: https://github.com/tamanobi/bq-query-unittest

## 作ったツールの特徴

* BigQuery に実際にクエリを発行する
* テストデータを BigQuery のストレージに、保存しない
* クエリによるデータ走査量は0バイト


## 弱点

* 実際にリクエストを送るため、実行速度が遅い

# 課題解決のために考えたアプローチ

* BigQuery でクエリのテストをするとき、テスト用のテーブルをあらかじめ専用のデータセットに置いておく
* BigQuery でテストすることは諦めてMySQLなど別のDBを使ったテストにする

# 上記アプローチの問題点

## BigQuery でクエリのテストをするとき、テスト用のテーブルをあらかじめ専用のデータセットに置いておく

* テスト用テーブルを更新することが手間
* BigQuery のストレージに置いておくと、費用が嵩む

## BigQuery でテストすることは諦めてMySQLなど別のDBを使ったテストにする

* BigQuery との細かい挙動の違いに翻弄される（とくにデータ型）
* BigQuery にはあってほかのDBにはない機能をテストすることが難しい

# 選んだアプローチ

* BigQuery の `WITH` と `STRUCT` を使ってデータを作成し、そのデータに対してクエリを発行する

# BigQuery の `WITH` と `STRUCT` を使ってデータを作成し、そのデータに対してクエリを発行する

## 重要なポイント

以下のようなクエリは、データ走査量が0バイトになる。

```python

WITH table1 AS (
SELECT * FROM UNNEST(ARRAY
[("ddd")]
))
SELECT * FROM table1
```

つまり、その場限りでテストデータと、期待するデータを作り、テストデータに対してクエリを発行し、結果と期待するデータを比較できれば、データ走査量を0バイトのまま、クエリをテストできることに気づきました。

つまり、BigQueryのクエリビルダーもどきを作ればいいという結論に達して、今回ツールを作ることを決めました。

## ツールの使い方

期待するテーブルのスキーマ・データと入力となるテーブルのスキーマ・データを辞書(dict)で与えます。

検査対象となるクエリも辞書で、 `query` キーにはクエリ文字列を入れます。 `params` には [クエリパラメータ](https://cloud.google.com/bigquery/docs/parameterized-queries?hl=ja)を入れます。

スキーマが長ったらしく見えますが、BigQueryのテーブルスキーマをJSONで吐き出すと同じような形で出力されるため、それをそのままスキーマとして与えられるようになっています。

`tables` という変数には、クエリ中に現れるテーブル名をキーにした辞書を入れておきます。クエリ中にあるテーブル名は、 `tables` のキー名と突合され置換されます。この処理のおかげで、クエリをテスト用に書き直す必要はありません。

```python

from bqqtest import QueryTest
from google.cloud import bigquery


# expected
expected_schema = [
    {"name": "item", "type": "STRING", "mode": "NULLABLE"},
    {"name": "value", "type": "INT64", "mode": "NULLABLE"},
]
expected_datum = [["abc", 100], ["bbb", 333], ["xxxx", 888], ["zzzz", 999]]
expected = {"schema": expected_schema, "datum": expected_datum}

# actual
target_schema = [
    {"name": "item", "type": "STRING", "mode": "NULLABLE"},
    {"name": "value", "type": "INT64", "mode": "NULLABLE"},
]
target_datum1 = [["abc", 100], ["bbb", 333]]
target_datum2 = [["xxxx", 888], ["zzzz", 999]]
tables = {
    "test.table1": {"schema": target_schema, "datum": target_datum1},
    "test.table2": {"schema": target_schema, "datum": target_datum2},
}
eval_query = {
    "query": "SELECT * FROM `test.table1` UNION ALL SELECT * FROM `test.table2`",
    "params": [],
}

qt = QueryTest(bigquery.Client(), expected, tables, eval_query)
success, diff = qt.run()
success  # True
```

## ツールが生成するSQL

今回作ったツールではSQLをASTで扱っておらず、文字列として扱っています。バグは多いと予想しているが、現状ではなんとなく動いています。 PR歓迎です。

このツールは結局のところ、SQLビルダーです。上記のコードから生成されるSQLを以下に載せて軽く解説をします。

`ACTUAL` というテーブルがクエリによって得られるテーブル、 `EXPECTED` というテーブルが期待するテーブルです。検査対象のクエリ中にあった `test.table1` や `test.table2` はランダム文字列のテーブルに書き換えられ、中身は `tables` にすり替えられます。

`diff` テーブルでは、 `ACTUAL` と `EXPECTED` の差分の計算を行っています。 `diff` テーブルの行が何もなければ差がないとして、テスト成功と判定できる仕組みになっています。

```sql

WITH kAUWcgAetWVWoGWt AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<item STRING, value INT64>>
[("abc",100),("bbb",333)]
)
),ZeHyJiIxCaoDspdJ AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<item STRING, value INT64>>
[("xxxx",888),("zzzz",999)]
)
),EXPECTED AS (
SELECT * FROM UNNEST(ARRAY<STRUCT<item STRING, value INT64>>
[("abc",100),("bbb",333),("xxxx",888),("zzzz",999)]
)
),ACTUAL AS (SELECT * FROM `kAUWcgAetWVWoGWt` UNION ALL SELECT * FROM `ZeHyJiIxCaoDspdJ`),diff AS (
SELECT "+" AS mark , * FROM (SELECT *, ROW_NUMBER() OVER() AS n FROM ACTUAL EXCEPT DISTINCT SELECT *, ROW_NUMBER() OVER() AS n FROM EXPECTED) UNION ALL
SELECT "-" AS mark , * FROM (SELECT *, ROW_NUMBER() OVER() AS n FROM EXPECTED EXCEPT DISTINCT SELECT *, ROW_NUMBER() OVER() AS n FROM ACTUAL) ORDER BY n ASC
) SELECT * FROM diff
```

ほかの使い方は https://pypi.org/project/bqqtest/ をどうぞ！