🤝

BigQueryで行単位でチェックしながら集計する方法

2021/12/09に公開

こんにちは @glassmonekeyです。
この記事はBigQueryアドベントカレンダー9 日目の記事です。

普段はアプリケーションエンジニアをしているのですが、最近データエンジニアっぽいこともしています。
特に最近仕事で BigQuery を使うことが多く、データのデバックやクエリの検証で四苦八苦してます。

今回は四苦八苦した事例のうち、集計途中の行単位で検証をすることで救われたことがあったので、その方法を紹介します。

TL:DR

そもそも、みなさん BigQuery のデータの検証ってどのように行なっていますでしょうか？
複数テーブルの突合だったり様々なやりかたがあると思います。

BiqQuery の機能には Assert 文と Error 関数があるので紹介します。

公式ではデバッグステートメントと紹介されています。文なので Select 文と同じような形で使うようです。

ちなみに筆者はユースケースが思いつかなかったので、もし使ってる方いましたら情報ください。

公式の例では以下のような例が紹介されています。
以下のように assert の引数が 真 の状態なら、

ASSERT (
  (SELECT COUNT(*) FROM UNNEST([1, 2, 3, 4, 5, 6])) > 5
) AS 'Table must contain more than 5 rows.';

このように完了と出ます。

一方で、下記のように偽となるような場合だと、

ASSERT (
  (SELECT COUNT(*) FROM UNNEST([1, 2, 3, 4, 5, 6])) < 5
) AS 'Table must contain more than 5 rows.';

エラーが AS 以降で記述したメッセージとともにでます。

テーブルの状態監視などのユースケースなどには使えそうかなと思いつつ、私は使えていません。

公式ではdebugging functionとして紹介されています。
タイトルの 行単位でチェックしながら集計する方法 とはずばりこれを使うことです。

例えば、以下のような形に通常使用するカラム(今回は n)の検証をします。今回は行の制約として n < 6 であるとします。

その場合以下の case 式で Error 関数を加えます。 (else のところは何でも良い)

SELECT
n,
CASE
    WHEN n >= 6 THEN ERROR(concat('nが6未満じゃないよ。n=', n))
ELSE "ok"
END AS ok
FROM UNNEST([1, 2, 3, 4, 5, 6]) AS n

すると今回 n=6 の時に違反するので以下のようなダイアログボックスが表示されます。

このように行の一意性を示すものをメッセージとして込めると、どの行に誤りがあるのか分かって便利です。
実際の業務では CTE 句を使って多段的にデータを扱ってたりするので、どの段階でデータの誤りがあったか特定するのに大変はかどりました。

データの正当性は難しい領域だと思います。テスト環境で気づきづらく、本番で初めて気づくケースも多々あるでしょう。いい感じのチェック方法とかご存知の方は知見ください。

普段はデータエンジニアっぽいことはつぶやいていませんが、もし良かったら@glassmonekeyをフォローしていただけると喜びます。