LoginSignup
10

More than 3 years have passed since last update.

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

Last updated at Posted at 2020-02-25

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

作ったツール

作ったツールの特徴

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

弱点

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

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

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

上記アプローチの問題点

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

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

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

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

選んだアプローチ

  • BigQuery の WITHSTRUCT を使ってデータを作成し、そのデータに対してクエリを発行する

BigQuery の WITHSTRUCT を使ってデータを作成し、そのデータに対してクエリを発行する

重要なポイント

以下のようなクエリは、データ走査量が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.table1test.table2 はランダム文字列のテーブルに書き換えられ、中身は tables にすり替えられます。

diff テーブルでは、 ACTUALEXPECTED の差分の計算を行っています。 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/ をどうぞ!

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
10