ERROR関数を使ったBigQueryデータ異常検知例

はじめに

DBのデータ異常検知というと、検知したいデータを抽出して
それをDB以外の何かしらの処理で判定するイメージですが
BigQueryにはERROR関数という関数でも検知できるのでご紹介します。

ERROR関数について

こちらに詳細があるので参照ください。
この関数は出力するとBigQueryのクエリ実行結果がエラーになります。

これがどう検知に繋がる？ってことで例をご紹介します。

例

データ重複

重複を検知したい場合
例：idが重複した場合は異常

SELECT
    ERROR('id is not unique.')
FROM
    table
GROUP BY
    id
Having
    count(1) > 1

ERROR関数が出力されると以下のように実行結果がエラーとなります。
クエリが失敗したらアラートを投げる要領で異常検知ができるのがSQL書く人には嬉しいですね。

異常件数

指定件数より少ないなら異常としたい場合

例：10,000件より少ない場合は異常

SELECT
    CASE
        WHEN count(1) < 10000 THEN ERROR('abnormal count.')
    END
FROM
    table

特定の値の件数が指定割合より少ないなら異常としたい場合

例：statusがNewの割合が全体の10%より低い場合は異常

SELECT
    CASE
        WHEN status = 'New' and rate < 10 THEN ERROR('abnormal rate.')
    END AS check
FROM (
    SELECT
      status
      ,(count(1) * 100)/(select count(1) from table) as rate
    FROM
      table
    GROUP BY
      rate
)

おまけ：ASSERT

ERROR関数に用途が似ているASSERTというステートメントがあります。

結果をboolで返す必要があり、falseの場合エラーとなるステートメントです。
これを使っても先の例と同等の検知は可能です。

ただし、ASSERTの場合はbool型の1レコードで返さないといけないので
先の"指定割合より少ないなら異常としたい場合"例だと
case文にelse true/falseつけないとboolにならないとか
group by checkしないとレコードが一意にならないとかで
正常にASSERTを通すためだけに、本来の目的よりも余分なクエリを書く必要があります。

目的が特定の異常検知だけであれば、ERROR関数の方が目的に沿った検知だけできて
お手軽だな。という使ってみての所感です。

テスト目的でデータ全体の事前評価をするとかであれば
ASSERTの方が網羅性があうケースも多々あるでしょうし
用途や書き方によってはASSERTの方がスッキリ書けることもあるかと思います。

お好みや用途に合わせてお使いいただくと良さそうです

最後に

BigQueryのデータ異常検知の手段として
SQL書く人にとってはお手軽に作れる粋な関数の紹介でした

特にデータの抽出内容を高頻度で変えてる場合は
仕込んでおくと盾になってくれるやもしれません。

ご参考までにの例のご紹介でした。