🤔

BigQueryでユニットテストを実装してみた

2022/02/06に公開約4,300字

背景・課題

11月に一人目のデータ基盤担当者としてJOINし、一発目のデータマート開発をやることになったが、SQLの単体テストをどのように実装すれば良いか悩んでいた。
dbtやdataformなどは後々導入を検討しているものの、デリバリー優先で一旦BigQuery上でテストコードを記述することにした。

やりたいこと

BigQueryのユニットテストをSQLのみで記述し、テストを効率化したい。

やってみる

以下のようなSQLを書いた。

test.sql
declare input_test_code int64 default 1;

begin

-- テストデータ作成
create or replace table function {dataset}.test_data(input_test_code int64) as 
  select
    * except(test_code)
  from
    unnest([
        struct
        ("xxx" as user_id, "apple" as product_name, 120 as price, date("2022-01-11") as purchase_date, 1 as test_code),
        ("yyy", "banana", 100, date("2022-01-11"), 1),
        ("zzz", "orange", 80, date("2022-01-12"), 1)        
    ])
  where
    test_code=input_test_code;

-- 期待データ作成
create or replace table function {dataset}.expected_data(input_test_code int64) as 
  select
    * except(test_code)
  from
    unnest([
        struct
        (date("2022-01-11") as purchase_date, 220 as sum_price, 1 as test_code),
        (date("2022-01-12"), 80, 1)
    ])
  where
    test_code=input_test_code;

-- テストデータに対してSQL実行し、結果データを作成
create or replace table function {dataset}.actual_data(input_test_code int64) as 
  select
    purchase_date,
    sum(price) as sum_price
  from
    {dataset}.test_data(input_test_code)
  group by
    purchase_date;

-- テストデータ実行結果と期待データが一致するかをテストする
WHILE input_test_code <= 1 DO
  assert (
    (
      select count(*) from (
        select * from (select * from {dataset}.actual_data(input_test_code) except distinct select * from {dataset}.expected_data(input_test_code)) union all 
        select * from (select * from {dataset}.expected_data(input_test_code) except distinct select * from {dataset}.actual_data(input_test_code))
      ) 
    ) = 0
  ) as "not equal";
  set input_test_code = input_test_code + 1;
END WHILE;

-- 一時的に作成したリソースを削除
DROP TABLE FUNCTION {dataset}.test_data;
DROP TABLE FUNCTION {dataset}.expected_data;
DROP TABLE FUNCTION {dataset}.actual_data;

end

テストデータと期待結果データを作成

テーブル関数を使って、テストデータと期待結果のテーブルを作成した。
一時関数で作成したいが、テーブル関数は一時関数化できないため、永続関数として作成。
テストコードを引数として渡し、実行したいテストで必要なデータを抜き出す。

-- テストデータ作成
create or replace table function {dataset}.test_data(input_test_code int64) as 
  select
    * except(test_code)
  from
    unnest([
        struct
        ("xxx" as user_id, "apple" as product_name, 120 as price, date("2022-01-11") as purchase_date, 1 as test_code),
        ("yyy", "banana", 100, date("2022-01-11"), 1),
        ("zzz", "orange", 80, date("2022-01-12"), 1)        
    ])
  where
    test_code=input_test_code;

-- 期待データ作成
create or replace table function {dataset}.expected_data(input_test_code int64) as 
  select
    * except(test_code)
  from
    unnest([
        struct
        (date("2022-01-11") as purchase_date, 220 as sum_price, 1 as test_code),
        (date("2022-01-12"), 80, 1)
    ])
  where
    test_code=input_test_code;

結果テーブルを作成

テストデータを差し込んで、ロジックを実行し、結果データを生成するテーブル関数を作成。

-- テストデータに対してSQL実行し、結果データを作成
create or replace table function {dataset}.actual_data(input_test_code int64) as 
  select
    purchase_date,
    sum(price) as sum_price
  from
    {dataset}.test_data(input_test_code)
  group by
    purchase_date;

テスト実行結果と期待実行結果の比較

テストコードを順番に差し込んでいき、試したいテストを一括で実行する。
やってることは単純に期待結果と実行結果が一致しているかをチェックしているだけ。
今回はテストケース一つだけだが、同じSQLに対して複数のテストを追加して実行することもできる。
すべてpassすれば、ジョブステータス=successとなり、一致しない場合「not equal」とエラーが出力される。

-- テストデータ実行結果と期待データが一致するかをテストする
WHILE input_test_code <= 1 DO
  assert (
    (
      select count(*) from (
        select * from (select * from {dataset}.actual_data(input_test_code) except distinct select * from {dataset}.expected_data(input_test_code)) union all 
        select * from (select * from {dataset}.expected_data(input_test_code) except distinct select * from {dataset}.actual_data(input_test_code))
      ) 
    ) = 0
  ) as "not equal";
  set input_test_code = input_test_code + 1;
END WHILE;

リソースの削除

最後に、テストで使ったリソースは必要ないので、削除する。

-- 一時的に作成したリソースを削除
DROP TABLE FUNCTION {dataset}.test_data;
DROP TABLE FUNCTION {dataset}.expected_data;
DROP TABLE FUNCTION {dataset}.actual_data;

参考

https://zenn.dev/silverbirder/articles/3ea8fb43d8d527
https://ichi.pro/bigquery-de-no-sql-yunitto-tesuto-kore-ga-chu-toriaru-desu-6939721773943

Discussion

ログインするとコメントできます