SQL単体テストの試み

Jan 25 2023

最近、SQL で単体テストを実行する方法について同僚と話し合っていたところ、私はまだデータのキャリアの初期段階にあるものの、すでにこの問題に何度か遭遇しており、そのたびに異なるアプローチをとっていることに気づきました。それを解決してください。私が学んだことを定着させるために、また、あなたが何をしたのか、そしてそれがどのようになったのかを読者から学ぶために、私のハックを共有することが役立つかもしれないと考えました。

最近、SQL で単体テストを実行する方法について同僚と話し合っていたところ、私はまだデータのキャリアの初期段階にあるものの、すでにこの問題に何度か遭遇しており、そのたびに異なるアプローチをとっていることに気づきました。それを解決してください。私が学んだことを定着させるために、また、あなたが何をしたのか、そしてそれがどのようになったのかを読者から学ぶために、私のハックを共有することが役立つかもしれないと考えました。しましょうか？

しかし、なぜ SQL の単体テストを行うのでしょうか?

Unsplash のルーカス・サントスによる写真

ここで説明しているのはデータのテストではなく、SQL ロジックのテストについてであることを明確にすることが重要です。データをテストすると、null 可能性や一意性などのスキーマプロパティの仮定が、手元の値に実際に当てはまることが確認されます。従来の RDBMS ではテーブル制約を使用してこれを保証しますが、データウェアハウスにはこの機能がないため、データセットの事前条件と事後条件を実際に読み取ってチェックする必要があります。

SQL の単体テストは、アプリケーションコードの単体テストと同じです。ロジックの一部が、指定された入力セットに対して期待される出力を生成することを確認します。ベストプラクティスでは、C++ であれ Python であれ、ほぼすべてのアプリケーションコードの単体テストを行うことが定められていますが、SQL でこれが試みられることはほとんどありません。なぜ？

私にとっての答えは、SQL を書くのが最も難しい部分ではないということです。ほとんどの場合、データ変換は簡単で反復的なものであり、ビットを A から B に移動することよりも、データを適切にモデル化することが難しいのです。ただし、変換内にビジネスロジックが必要な場合は、必要な複雑さが生じて困難になる可能性があります。分割するには、複雑な CASE-WHEN 式を使用して列を生成し、入力は気の遠くなるような 7 方向の JOIN から行われ、何が起こっているかを理解するための真理値表を保証する複数行の WHERE 句を使用します。

私の経験では、ロジックが健全であることを確認するのは手動テストとクライアントのサインオフのみに依存します。SQL が単体テストに非常に適していることを考えると、これは 1) 脆弱で、2) ほとんど犯罪的です。アプリでは変更可能な状態を制限し、テストでの再現性を確保するためにあらゆる種類のスキャフォールディングが必要ですが、SQL は純粋かつ明示的です。では、同じインフラストラクチャを使用して、ロジックが期待どおりに動作することを検証してはどうでしょうか?

最初の試み: 分析

私が SQL スクリプトの単体テストに初めて挑戦したのは、Google で働いていたときで、DremelやPowerdrillなどの社内テクノロジーを使用していました (彼らのプロジェクトには、ログの操作に関連した名前が付けられていたことがわかります)。

私たちは新しいアプリの分析パイプラインを構築し、ユーザーログを処理して、国別またはコホート別のアクティブユーザー数など、プロダクトマネージャーにとって重要な指標を生成していました。代表的なクエリは次のとおりです。

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

テストフレームワークのハッキング

私たちは、より良い結果を出し、スクリプトが信頼できるものであることを確認したいと考え、独自のテストフレームワークを構築しました。

各ソーステーブルには、多数のユーザーと列が入力された合成 CSV が含まれます。新しいテストケースごとに、適切なソーステーブルに行を追加します。
テストが実行されると、データセット全体がデータウェアハウスの一時テーブルにアップロードされました。
各スクリプトでは、ソースがテキスト的に一時テーブルに置き換えられ、スクリプトが実行されます。
スクリプトごとに、予想される出力 CSV を作成し、それを実際の出力と比較して、相違点を報告しました。

CSV の作成には時間がかかりました。1 日、7 日、28 日の期間にわたるアクティブユーザー数を計算することに興味があったため、単純なスクリプトをテストするためだけにログイベントを含む行を多数作成する必要がありました。新しい次元を追加しました。意味があれば、あるテスト用に書かれた予想される出力を別のテストの入力として使用するなど、可能な限り再利用しました。

壊す

しばらくすると、他の多数のテストに影響を与える可能性があるため、新しいテストケースを追加することは非常に脆弱になりました。テスト A が相対頻度を計算する必要があり、そのために COUNT(*) ですべての行をカウントした場合、テスト B の「ゴールデン」データセットに新しい行を追加した瞬間に、テスト A からの予期された出力が壊れてしまいます。

ため息をついた後、私たちは思い切って、ゴールデンデータセットをスクリプトごとに 1 つのデータセットに分割しました。スクリプトごとに複数のテストケースを追加したため、まだ完璧ではありませんでしたが、より管理しやすくなりました。それにより、私たちは次のことを学びました。

テストを可能な限り分離します。単体テストに価値があるのは、単一コンポーネントの前提が当てはまらなくなったときに機能しなくなるからです。無関係なテストによって共有環境が変更されたために機能が停止するのは役に立ちません。
CSV はひどいファイル形式です。真剣に。NULL 文字列と空の文字列でロジックがどのように動作するかをテストしたい場合は、特別な値と変換ロジックをアップロードステップに導入する必要があります。CSV が標準化できないすべてのケースを処理するために、 pandas.read_csv に 52 個の引数があるのも不思議ではありません。
テストは単純さと明示性の恩恵を受けますが、合成データは冗長すぎるため、テスト間の違いを見つけるのが困難になります。各行とテストケースの意図を明確にするために、コメントに大きく依存しました(繰り返しになりますが、CSV で標準化されたものではありません)。

2 回目の試行: JSON を使用したクライアントのサインオフ

本文とは関係ありませんが、「落ち着いた柄」を探していたところ見つけたので気に入っています。Casa Dolors Calm、トーマス・レッドル作、ウィキメディア・コモンズで入手

2 番目の経験では、私は Zup Innovation でデータエンジニアコンサルタントとして働いており、ラテンアメリカ最大の銀行であるイタウーのチームに配属されました。私たちはオンプレミスの Hadoop デプロイメントを使用していました。

ここでの作業の種類は分析とは大きく異なり、主にレコードの平坦化と重複排除で構成されていました。主な課題は、スクリプトがインクリメンタルであることに由来し、特定の変換の出力は前の変換の出力に依存します。通常、これを 2 つのステップでテストします。前の状態を持たない実行を T0 と呼び、T0 に基づいて構築される実行を T1 と呼びます。

以前と同様に、私たちは展開後にのみバグを見つけることに苦労しており、開発中にすべての例外的なケースを確実にカバーしたいと考えていました。ビジネスアナリストチームは、列の値が良いか悪いか、および期待される出力は何かを指定した、テストケースの広範なリストを作成しました。これらはテーブル上の説明にすぎませんが、それでもパラメータ化されたテストにかなり近いことに注意してください。

主な困難は、このテストケースのリストを実際の Hadoop 入力に変換することでした。最初は次のようなプロセスがありました。

最初の実行 (T0) または 2 回目の実行 (T1) から、各タブがテーブルとなる共有 Excel ファイルを作成しました。各テストケースは、各テーブルの 0 ～ 2 行になります。
各 T0 テーブルを CSV として出力し、表面変換を実行して、Hadoop にアップロードして、SQL スクリプトを実行します。T0 出力が収集されます。
各 T1 テーブルを CSV として出力し、表面変換を実行して、Hadoop にアップロードして、SQL スクリプトを実行します。T1 出力が収集されます。

テーブルジェネレータのハッキング

これらのエラーによるやり取りにイライラして、私はテストケースを記述した JSON ファイルを入力として受け取り、必要なすべてのテーブルを一度に出力する小さな合成ツールを構築しました。最初は、エンジニアチームと QA チームがこれを使用することを期待していましたが、ビジネスアナリストの 1 人が技術的に精通しており、JSON を理解しており、独自のテストケースをこの形式に変換するのを手伝ってくれました。テストケースを含むファイルは次のようになります。

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

このツールは各テストケースからすべての行を収集し、companys_t0.csv、companies_t1.csv などのファイルを作成し、Hadoop にアップロードしました。エラーが見つかった場合は、JSON ファイルにドロップして、テストケースコードの間違いかどうかを確認できます。これは、特定のテストケースに影響を与えた可能性のある Excel の多くのタブ内で特定の行を探すよりもはるかに簡単です。。

このツールのもう 1 つの機能は、各行が特定のテンプレートから列を「継承」できることです。これにより、テストケースに関連する列のみが含まれるため、テストケースが読みやすくなりました。

新しいレッスン

このツールが成功したとは言えません。まず、それを採用する人がほとんどいなかったので、プロセス全体にどのように適合させるかをエンジニアや QA ともっとよく伝えるべきでした。次に、重複する ID を持つケースの作成、コピー＆ペーストで列を変更しないままにする、JSON プロジェニタではなく CSV ファイルを直接編集するなど、新たな手動エラーの機会が発生しました。

そこから次のような教訓を得ました。

ビジネスは複数のテーブルにまたがるケースを考慮し、コードはテストケースを含むテーブルを考慮します。テストハーネスを作成するときに、それらの間で転置を行う必要があります。
クライアントにテストケースを書いてもらうのは最高です 。関連する入力と期待される出力が何であるかを最もよく知っているのは、ドメインの専門家です。コードで処理する必要があるエッジケースをすべて考慮しているわけではありませんが、難しい変換に関するいくつかのテストケースから洞察を得ることができるかもしれません。また、インプットとアウトプットの共通言語を彼らと確立することは、後でそれらのエッジケースを伝えるのに役立ちます。
すべてのテストケースを一度に実行すると、チェックとデバッグが難しくなります。結果をテストケースにマッピングしてチェックするには、ユーザー ID とテストケースの間のマッピングを保持する必要がありました。ユーザー ID にテストケース番号を追加し、データを「インバンド」でタグ付けしようとしましたが、これは無効な ID に関するさらなる問題を引き起こすだけでした。各ケースを個別に実行するか、特定の出力がどのケースに対応するかを追跡する方法があれば良かったでしょう。
JSON は人間にとって適切な形式ではありません。上記の例では、何がテストされているかは明示されておらず、JSON ではそれを説明するコメントが許可されていません。はい、フィールドを使用できます"_comment": "blah"が、これは CSV への変換時に元に戻す必要があるハックです。また、コンマを追跡したり、オブジェクトキーを引用符で囲んだりするのは、少々腹立たしいものです。
行テンプレートにより、可読性が大幅に向上します。テストは非常に明示的で、隠されたロジックがほとんどないようにしたいと考えていますが、テンプレートを使用して興味のない列に記入すると、当面のテストケースにとって実際に重要な値間の違いを見つけるのに非常に役立ちます。

この 3 回目で最後の経験は、不動産会社にデータ接続を提供する私の前勤務先 Cherre で起こりました。私たちは主に dbt を使用して、Dremel の商用製品である Google BigQuery で変換を作成しました。クライアントから、ロジックを大幅に変更する (ありがたいことに、よりシンプルになりました!) というリクエストがあり、意図しない変更が導入されないようにしたいと考えていました。

dbt モデルの再利用

既存のロジックは単体テストされていなかったため、単純に入力を作成し、それらに何も問題がないことを確認するスモークテストから始めました。テスト対象のモデルの依存関係グラフは深く、最大 20 のソースで終わるため、ソースごとにモックするのは大変な作業です。代わりに、適切な DAG カットオフを選択し、SQL 自体で作成された合成データで ~7 参照だけを置き換えました。


WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

入力と出力をより適切に視覚化して共有するために、CTE のそれぞれ (つまり、中間テーブルのそれぞれ) のスクリプトのコピーを作成し、最後に追加しましたSELECT * FROM <cte>。次に、各コピーを実行して、各中間変換 (foo.csv、bar.csv、model_x.csv など) の CSV を取得しました。これは、DAG の各ステップのデバッグに役立つだけでなく、技術者以外のユーザーと共有する方法も提供しました。いくつかの合成入力が与えられた場合に、ロジックが変更された結果がどうなるかを CSV 形式で説明します。

限られた利益

最終的に、このテストは、既存のロジックと新しいロジックに同じ入力が使用されたため、ロジックの変更に伴うエラーが追加されないことを確認することに成功しました。また、合成データを使用して問題を再現できるため、後で本番環境の問題をデバッグするのにも役立ちました。残念ながら、これは特定の目的に基づいて構築されているため、他のモデルでテストを作成できるように一般化することは試みませんでした。

これまでの主な教訓は次のとおりです。

SQL は、合成データを宣言するための抵抗の不一致が最も少ないオプションです。純粋な SQL で合成データを記述することにより、キャスト、NULL、コメント、読みやすさについて心配する必要がなくなります。言語構文自体がそれらをすでに提供しているためです。
参照をモックすることが重要です。SQL ロジックの最も複雑な部分は大規模な DAG の最後にあり、各ソースをモックする必要があるのは逆効果です。ソースをモックすると、すべての中間モデルをテストできます…しかし、すべてをテストするつもりはなく、興味深いモデルだけをテストしました。
行をテーブルごとにグループ化するよりも、テストケースごとにグループ化する方が適しています。これについては以前の経験ですでに学習していましたが、SQL でコメント付きのテストケースを作成した場合でも、ケースを読み取ったり、出力からテストにバックトラッキングしたりするときに、一部のコンテキストが依然として失われることを繰り返しておきたいと思います。

著者からの写真、CC-BY 4.0 に基づいてライセンス供与

SQL 単体テストの完璧な解決策に到達できるかどうかはわかりませんが、それでも夢を見ることはできます。

テストケースは分離して実行されます。
各出力は、それがどのケースから来たのかを示します。
興味のない列は自動入力または無視できます。
すべてのロジックが再利用されるため、テストのために調整する必要はありません。
依存関係グラフのカットオフポイントは任意です。
テストケースとその結果は、ドメインの専門家/ビジネススペシャリストによって理解可能であり、書き込み可能です。

エピローグ

以上です、読んでいただきありがとうございます! ここまで読んだ方は、この記事が同じような他の人にも見てもらえるように、10 回拍手をお願いします。最も重要なことは、データエンジニアリングにおける単体テストに関するこれまでの経験を共有してください。私はまだ始めたばかりなので、どんなレポートも歓迎します。

この投稿の草稿を閲覧し、レビューしてくださった David Barer 氏、Bruno Santos 氏、Rodrigo Donizetti 氏に感謝します。