ETL などで BigQuery を頻繁に活用するため、複雑なクエリを扱うことが増えてきました。私はコードを書くとき、テストも書くのが習慣になっています。そうなってくるとクエリのテストが欲しくなるのは必然です。
作ったツール
作ったツールの特徴
- BigQuery に実際にクエリを発行する
- テストデータを BigQuery のストレージに、保存しない
- クエリによるデータ走査量は0バイト
弱点
- 実際にリクエストを送るため、実行速度が遅い
課題解決のために考えたアプローチ
- BigQuery でクエリのテストをするとき、テスト用のテーブルをあらかじめ専用のデータセットに置いておく
- BigQuery でテストすることは諦めてMySQLなど別のDBを使ったテストにする
上記アプローチの問題点
BigQuery でクエリのテストをするとき、テスト用のテーブルをあらかじめ専用のデータセットに置いておく
- テスト用テーブルを更新することが手間
- BigQuery のストレージに置いておくと、費用が嵩む
BigQuery でテストすることは諦めてMySQLなど別のDBを使ったテストにする
- BigQuery との細かい挙動の違いに翻弄される(とくにデータ型)
- BigQuery にはあってほかのDBにはない機能をテストすることが難しい
選んだアプローチ
- BigQuery の
WITH
とSTRUCT
を使ってデータを作成し、そのデータに対してクエリを発行する
BigQuery の WITH
と STRUCT
を使ってデータを作成し、そのデータに対してクエリを発行する
重要なポイント
以下のようなクエリは、データ走査量が0バイトになる。
WITH table1 AS (
SELECT * FROM UNNEST(ARRAY
[("ddd")]
))
SELECT * FROM table1
つまり、その場限りでテストデータと、期待するデータを作り、テストデータに対してクエリを発行し、結果と期待するデータを比較できれば、データ走査量を0バイトのまま、クエリをテストできることに気づきました。
つまり、BigQueryのクエリビルダーもどきを作ればいいという結論に達して、今回ツールを作ることを決めました。
ツールの使い方
期待するテーブルのスキーマ・データと入力となるテーブルのスキーマ・データを辞書(dict)で与えます。
検査対象となるクエリも辞書で、 query
キーにはクエリ文字列を入れます。 params
には クエリパラメータを入れます。
スキーマが長ったらしく見えますが、BigQueryのテーブルスキーマをJSONで吐き出すと同じような形で出力されるため、それをそのままスキーマとして与えられるようになっています。
tables
という変数には、クエリ中に現れるテーブル名をキーにした辞書を入れておきます。クエリ中にあるテーブル名は、 tables
のキー名と突合され置換されます。この処理のおかげで、クエリをテスト用に書き直す必要はありません。
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
テーブルの行が何もなければ差がないとして、テスト成功と判定できる仕組みになっています。
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/ をどうぞ!