SodaSQLを使ってBigQueryのデータをテストする
これは何か
SodaSQLを触ってみて、BigQuery上のデータのテストを実行してみたので、所感を書いてみる。
sodaSQLとは
データ品質監視のためのツール。
公式ページのコンセプトを見た感じ、データの品質維持を行うためには、カタログ化・テスト・モニタリング・プロファイリングを行った上で、observabilityを保つ必要があるが、SodaSQLはそれらを行う機能を提供してくれるらしい。
CLI版とマネージドのCloud版がある。
準備
開発環境準備
soda SQLを実行する環境を準備する。
FROM python:3.8-slim
RUN pip install --upgrade pip && pip install soda-sql-bigquery
RUN apt-get -y update && apt-get install -y vim nano
CMD ["/bin/bash"]
今回はBigQuery上でのデータをテストするので、soda-sql-bigqueryをインストールする。
公式ページに互換性があるDWHやDBのリストがあるので、自分の環境に合わせてインストールを行う。
version: "3.9"
services:
soda_sql_project:
build:
context: .
dockerfile: ./Dockerfile
container_name: soda_sql_project
working_dir: /workspace
volumes:
- ./scr/:/workspace
tty: true
ports:
- 8001:5432
secrets:
- gcp_secret
secrets:
gcp_secret:
file:
{BigQueryを実行するキーファイルのPATH}
データ準備
データについては、以前の記事で使ったcustomerデータから、男性だけ抽出した(gender_cd="0")のテーブル「customers_men」をBigQuery上に用意した。
実行
コンテナにログインし、SodaSQLを動かしてみる。
データストア登録
まず、接続したいBigQueryを登録する。
soda create bigquery
成功すると、カレントディレクトリにwarehouse.yml、rootディレクトリ配下にenv_vars.ymlができます。
warehouse.ymlはデータストアへのコネクション情報を格納するファイル 、env_vars.ymlはwarehouseへのログインに必要なクレデンシャルを格納するファイルです。
name: bigquery
connection:
type: bigquery
account_info_json_path: /run/secrets/gcp_secret
auth_scopes:
- https://www.googleapis.com/auth/bigquery
- https://www.googleapis.com/auth/cloud-platform
dataset: mydataset
自分はenv_vars.ymlの設定は特にせず、warehouse.ymlのaccount_info_json_pathからシークレットキーを渡しました。
テーブル登録
次のコマンドを実行して、テストしたいテーブルを登録します。
soda analyze
上記を実行すると、warehouse.ymlで指定したデータセットすべてを登録してしまうため、
soda analyze --include customer
のように実行すれば、テーブル名に「customer」と付くテーブルだけを登録します。
命名規則を揃えていれば、仮想ビューは除外し、実体化されたテーブルだけテスト対象にできそう。
成功すると、tables/というディレクトリが新規に作成され、その配下にテーブルごとのymlファイルが作成されます。
テスト記述
customers_men.ymlを以下のように編集しました。
table_name: customers_men
metrics:
- row_count
- uniqueness
sql_metrics:
- sql: |
select count(*) as error_cnt
from `mydataset.customers_men`
where gender_cd = "0" and gender != "男性"
tests:
- row_count > 0
- error_cnt == 0
columns:
customer_id:
tests:
- invalid_percentage == 0
- uniqueness == 100
gender_cd:
valid_format: number_whole
tests:
- invalid_percentage == 0
metricsで各カラムで算出するメトリクスを定義します。（例えばuniquenessはユニークな値がカラムの中で何％くらいあるかの比率）
tests:
- row_count > 0
で、テーブル全体に対して空じゃないかをテストしている。
もちろん個別カラムでテストを設定することもでき、
customer_id:
tests:
- invalid_percentage == 0
- uniqueness == 100
gender_cd:
valid_format: number_whole
tests:
- invalid_percentage == 0
では、customer_idカラムに対してはユニークキーかどうかを、gender_cdに対しては数字フォーマットかどうかをテストしている。（gender_cdはSTRING型のはずだが、整数フォーマットならいいのか？）
また、組み込みの機能で対応できない場合は、SQLを記述してテストすることもできる。
sql_metrics:
- sql: |
select count(*) as error_cnt
from `mydataset.customers_men`
where gender_cd = "0" and gender != "男性"
tests:
- error_cnt == 0
sql_metricsを用いて、gender_cdが0だがgenderが男性でない行をerrorとしてカウントし、errorが0であるかを判定させている。
テスト実行
以下のコマンドを実行して、テストを実行する。
soda scan warehouse.yml tables/customers_men.yml
ログからどのようなSQLが投げられているか見ることができる。
| Executed 14 queries in 0:00:16.691059
| Scan summary ------
| 135 measurements computed
| 5 tests executed
| All is good. No tests failed.
| Exiting with code 0
最後に上記のようにテストがパスしているログが表示されたら成功。
失敗が正常に動くかを確認するために、下記のようにテストを書き換える。本来であれば正常なので、error_cnt>0以上となり、テストは失敗するはず。
sql_metrics:
- sql: |
select count(*) as error_cnt
from `mydataset.customers_men`
where gender_cd = "0" and gender == "男性"
tests:
- error_cnt == 0
実行してみると、該当箇所で失敗することが確認できる。
| Executed 13 queries in 0:00:14.650229
| Scan summary ------
| 134 measurements computed
| 5 tests executed
| 1 of 5 tests failed:
| Test test(error_cnt == 0) failed with measurements null
| Errors occurred!
| [error] Exception during sql metric query select count(*) as error_cnt
from `mydataset.customers_men`
where gender_cd = "0" and gender == "男性"
| [test_execution_error] Test "error_cnt == 0" failed
| Exiting with code 1
感想
前回Great Expectationsを触ってみて、高機能だが癖が強く取っ付きづらい印象だったが、SodaSQLはかなりシンプルかつライトに動かすことができた。
とはいえGreat Expectationsとは違って、結果を静的ページとして公開したりなどは、有償のSoda Cloudを使わないとできなさそう。
PrefectやAirflowなどのオーケストラレーションツールを使ってSodaSQLを実行し、出力結果を受けて通知を出したりは簡単にできそう。
Discussion