Moje próby testowania jednostek SQL

Jan 25 2023

Ostatnio rozmawiałem ze współpracownikiem o tym, jak wykonać testy jednostkowe w SQL i zdałem sobie sprawę, że chociaż wciąż jestem na początku swojej kariery związanej z danymi, już kilka razy spotkałem się z tym problemem i za każdym razem miałem inne podejście do rozwiązać. Pomyślałem, że warto podzielić się moimi hackami, abym mógł skonsolidować to, czego się nauczyłem, a także dowiedzieć się od Ciebie czytelniku, co zrobiłeś i jak poszło.

Ale po co testować jednostki SQL?

Zdjęcie Lucasa Santosa na Unsplash

Ważne jest, aby wyjaśnić, że omawiam tutaj testowanie logiki SQL , a nie testowanie danych . Testowanie danych zapewnia, że przypuszczenia dotyczące właściwości schematu, takie jak dopuszczalność zerowania i unikalność, rzeczywiście obowiązują dla danych wartości. Tradycyjny RDBMS zapewniłby to z ograniczeniami tabel, ale nie mamy tej funkcji w hurtowniach danych, więc musimy faktycznie czytać i sprawdzać warunki wstępne i końcowe w zbiorze danych.

Testowanie jednostkowe SQL jest tym samym, co kod aplikacji do testowania jednostkowego: upewnij się, że element logiki generuje oczekiwane dane wyjściowe dla danego zestawu danych wejściowych. Podczas gdy najlepsze praktyki nakazują, abyśmy przeprowadzali testy jednostkowe prawie całego kodu aplikacji, czy to w C++, czy w Pythonie, rzadko spotykamy się z takimi próbami w przypadku SQL. Dlaczego?

Moim zdaniem pisanie SQL nie jest najtrudniejszą częścią . W większości przypadków transformacje danych są trywialne i powtarzalne, a trudność polega bardziej na odpowiednim modelowaniu danych niż na przenoszeniu bitów z punktu A do punktu B. Jednak gdy w ramach transformacji konieczna jest logika biznesowa, może dojść do koniecznej złożoności, która jest trudna rozdzielić — jak złożone wyrażenie CASE-WHEN do generowania kolumny, z danymi wejściowymi pochodzącymi z zniechęcającego siedmiokierunkowego JOIN i wielowierszową klauzulą WHERE, która gwarantuje, że tabela prawdy pozwoli zrozumieć, co się dzieje…

Z mojego doświadczenia wynika, że upewnienie się, że logika jest rozsądna, polegało wyłącznie na ręcznym testowaniu i podpisaniu przez klienta, co jest 1) delikatne i 2) prawie przestępcze , biorąc pod uwagę, jak dobrze nadaje się do testowania jednostkowego SQL! Podczas gdy aplikacje wymagają wszelkiego rodzaju rusztowań, aby ograniczyć zmienny stan i zapewnić powtarzalność w testach, SQL jest czysty i jawny. Dlaczego więc nie użyć tej samej infrastruktury do sprawdzenia, czy logika działa zgodnie z oczekiwaniami?

Pierwsza próba: analityka

Moja pierwsza próba jednostkowego testowania skryptów SQL miała miejsce, kiedy pracowałem w Google, używając wewnętrznych technologii, takich jak Dremel i Powerdrill (zauważysz, że ich projekty miały nazwy związane z pracą z logami ).

Tworzyliśmy potok analityczny dla nowej aplikacji, przetwarzając dzienniki użytkowników w celu generowania wskaźników ważnych dla menedżerów produktu, takich jak aktywni użytkownicy według kraju lub kohorty. Reprezentatywnym zapytaniem byłoby:

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

Hackowanie frameworka testowego

Chcieliśmy działać lepiej i mieć pewność, że nasze skrypty będą godne zaufania, dlatego stworzyliśmy własny framework testowy:

Każda tabela źródłowa zawierałaby syntetyczny plik CSV z wieloma wypełnionymi użytkownikami i kolumnami. Dla każdego nowego przypadku testowego dodalibyśmy wiersze do odpowiednich tabel źródłowych.
Po wykonaniu testu cały zestaw danych został przesłany do tymczasowych tabel w hurtowni danych.
Dla każdego skryptu źródła były tekstowo zastępowane przez tabelę tymczasową, a skrypt był wykonywany.
Dla każdego skryptu stworzyliśmy oczekiwany wyjściowy plik CSV i porównaliśmy go z rzeczywistym wyjściem, zgłaszając wszelkie różnice.

Tworzenie plików CSV było czasochłonne, ponieważ zależało nam na obliczeniu liczby aktywnych użytkowników w oknach 1-dniowych, 7-dniowych i 28-dniowych, więc musieliśmy utworzyć wiele wierszy ze zdarzeniami w dzienniku, aby przetestować prosty skrypt, który dodał nowy wymiar. W miarę możliwości wykorzystaliśmy ponownie, w tym oczekiwane dane wyjściowe zapisane dla jednego testu jako dane wejściowe do innego, jeśli miało to sens.

Załamanie

Po pewnym czasie dodanie nowego przypadku testowego stało się zbyt kruche , ponieważ potencjalnie można wpłynąć na dowolną liczbę innych testów! Jeśli test A musi obliczyć częstotliwość względną i w tym celu policzył wszystkie wiersze za pomocą funkcji COUNT(*), w momencie, gdy dodamy nowy wiersz do „złotego” zbioru danych dla testu B, oczekiwany wynik testu A ulegnie awarii.

Po wielu westchnieniach ugryzliśmy się w łeb i podzieliliśmy złoty zestaw danych na jeden zestaw danych dla każdego skryptu . Nadal nie było idealnie, ponieważ dodaliśmy wiele przypadków testowych na skrypt, ale stało się łatwiejsze w zarządzaniu. Dzięki temu dowiedzieliśmy się:

Izoluj testy tak często, jak to możliwe . Test jednostkowy ma wartość, ponieważ psuje się, gdy założenia dotyczące pojedynczego składnika już nie obowiązują. Przerwanie go, ponieważ jakiś niepowiązany test zmienił wspólne środowisko, nie jest przydatne.
CSV to okropny format pliku . Poważnie. Gdybyśmy chcieli przetestować, jak zachowywała się logika z łańcuchem NULL i łańcuchem pustym, musielibyśmy wprowadzić specjalną wartość i logikę konwersji do kroku przesyłania. Nic dziwnego, że pandas.read_csv ma 52 argumenty do obsługi wszystkich przypadków, których CSV nie potrafi ujednolicić.
Chociaż testy korzystają z prostoty i jednoznaczności, dane syntetyczne są zbyt szczegółowe i utrudniają dostrzeżenie różnic między testami. W dużej mierze polegaliśmy na komentarzach, aby wyjaśnić, jaki był zamiar każdego wiersza i przypadku testowego (ponownie, nie coś ustandaryzowanego w CSV).

Druga próba: wylogowanie klienta za pomocą JSON

To zdjęcie nie jest połączone z tekstem, ale znalazłem je szukając „spokojnego wzoru” i podoba mi się. Casa Dolors Calm, Thomas Ledl, pobrane z Wikimedia Commons

W drugim doświadczeniu pracowałem jako konsultant ds. inżynierii danych w Zup Innovation, przydzielony do zespołu w Itaú, największym banku w Ameryce Łacińskiej. Pracowaliśmy z lokalnym wdrożeniem Hadoop.

Rodzaj pracy był tutaj bardzo różny od analityki, polegający głównie na spłaszczaniu i deduplikacji rekordów. Główne wyzwanie wynikało z tego, że skrypty były przyrostowe , a dane wyjściowe danej transformacji były zależne od danych wyjściowych poprzedniej. Zwykle testowaliśmy to w dwóch krokach: nazywaliśmy T0 przebiegiem bez poprzedniego stanu, a T1 przebiegiem budującym na T0.

Tak jak poprzednio, cierpieliśmy z powodu znajdowania błędów dopiero po wdrożeniu i chcieliśmy mieć pewność, że wszystkie przypadki narożne zostaną uwzględnione podczas opracowywania. Zespół analityków biznesowych przygotował obszerną listę przypadków testowych, określając dla każdego z nich, czy kolumna będzie miała dobrą, czy złą wartość i jaki był oczekiwany wynik. Zauważ, że były to tylko opisy w tabeli, ale wciąż bardzo zbliżone do sparametryzowanego testu .

Główną trudnością było przetłumaczenie tej listy przypadków testowych na rzeczywiste dane wejściowe Hadoop! Na początku mieliśmy następujący proces:

Utworzyliśmy udostępniony plik programu Excel, w którym każda karta była tabelą, z pierwszego (T0) lub drugiego uruchomienia (T1). Każdy przypadek testowy stałby się 0–2 wierszami w każdej tabeli.
Wyprowadzamy każdą tabelę T0 jako plik CSV, przeprowadzamy kosmetyczne transformacje, przesyłamy je do Hadoop i wykonujemy skrypt SQL. Wyjście T0 jest zbierane.
Wyprowadzamy każdą tabelę T1 jako plik CSV, przeprowadzamy transformacje kosmetyczne, przesyłamy je do Hadoop i wykonujemy skrypt SQL. Wyjście T1 jest zbierane.

Hakowanie generatora tabel

Sfrustrowany powtarzającymi się błędami, zbudowałem mały syntezator, który pobierał jako dane wejściowe pliki JSON opisujące przypadek testowy , a następnie generował jednocześnie wszystkie potrzebne nam tabele . Na początku miałem nadzieję, że będzie używany przez zespoły eng i QA, ale jeden z analityków biznesowych był doświadczony technicznie i rozumiał JSON i pomógł przekonwertować własne przypadki testowe na ten format! Plik z przypadkiem testowym wyglądał tak:

{
  "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": [...]
}

Narzędzie zebrało wszystkie wiersze z każdego przypadku testowego i utworzyło pliki Companies_t0.csv, Companies_t1.csv itd., które następnie zostały przesłane do Hadoop. Gdy błąd został wykryty, mogliśmy przejść do pliku JSON i sprawdzić, czy nie był to błąd w kodzie przypadku testowego, co było znacznie łatwiejsze niż szukanie konkretnego wiersza w wielu zakładkach Excela, który mógł mieć wpływ na dany przypadek testowy .

Kolejną cechą tego narzędzia było umożliwienie każdemu wierszowi „dziedziczenia” kolumn z danego szablonu. Dzięki temu przypadki testowe były przyjemne w czytaniu, ponieważ zawierały tylko odpowiednie dla nich kolumny.

Nowe lekcje

Nie mogę powiedzieć, że to narzędzie odniosło sukces. Po pierwsze, niewiele osób go przyjęło, a ja powinienem był lepiej komunikować się z inżynierami i działem kontroli jakości, jak można go dopasować do całego procesu. Po drugie, wprowadzono nowe możliwości ręcznego popełniania błędów, takie jak tworzenie spraw ze zduplikowanymi identyfikatorami, pozostawianie niezmienionych kolumn podczas kopiowania i wklejania lub edytowanie plików CSV bezpośrednio zamiast ich przodków JSON.

Wyciągnęłam z tego następujące lekcje:

Biznes myśli w przypadkach obejmujących tabele, a kod myśli w tabelach zawierających przypadki testowe . Podczas tworzenia wiązki testowej należy wykonać między nimi transpozycję.
To, że klient pisze dla Ciebie przypadki testowe, jest złotem . Nikt nie wie najlepiej, jakie są istotne dane wejściowe i oczekiwane wyniki niż ekspert w dziedzinie! Mogą nie myśleć o każdym przypadku brzegowym, który musi obsłużyć kod, ale mogą zapewnić wgląd w garść przypadków testowych dotyczących trudnych transformacji. Ponadto ustanowienie wspólnego języka wejść-wyjść z nimi pomaga później komunikować te skrajne przypadki!
Jednoczesne uruchamianie wszystkich przypadków testowych utrudnia sprawdzanie i debugowanie . Musieliśmy zachować mapowanie między przypadkiem testowym identyfikatora użytkownika, aby zmapować wyniki z powrotem do przypadków testowych i je sprawdzić. Próbowaliśmy dodać numer przypadku testowego do identyfikatora użytkownika, tagując dane „w paśmie”, ale to tylko przyniosło więcej problemów z nieprawidłowymi identyfikatorami. Byłoby lepiej albo uruchamiać każdy przypadek osobno, albo mieć sposób na śledzenie, któremu przypadkowi odpowiada dany wynik.
JSON nie jest dobrym formatem dla ludzi. W powyższym przykładzie nie jest jasne, co jest testowane, a JSON nie pozwala na wyjaśnianie tego w komentarzach. Tak, możemy użyć "_comment": "blah"pola, ale jest to hack, który należy cofnąć podczas konwersji do CSV. Ponadto śledzenie przecinków i cytowanie kluczy obiektów jest lekko irytujące .
Szablony wierszy znacznie poprawiają czytelność. Chociaż chcielibyśmy, aby testy były bardzo wyraźne i miały mało ukrytej logiki, użycie szablonu do wypełnienia nieciekawych kolumn bardzo pomaga w wykrywaniu różnic między wartościami, które są rzeczywiście ważne dla danego przypadku testowego.

To trzecie i ostatnie doświadczenie miało miejsce u mojego poprzedniego pracodawcy, firmy Cherre, która zapewnia łącza danych dla firm z branży nieruchomości. Używaliśmy głównie dbt do pisania transformacji w Google BigQuery, komercyjnej ofercie Dremel. Klient poprosił nas o znaczne zmodyfikowanie logiki (na szczęście uproszczenie jej!) i chcieliśmy się upewnić, że nie wprowadzimy żadnych niezamierzonych zmian.

Ponowne wykorzystanie modeli dbt

Ponieważ istniejąca logika nie była testowana jednostkowo, zacząłem od testu dymu , który po prostu tworzy dane wejściowe i upewnia się, że nic się z nimi nie zepsuje. Wykres zależności testowanego modelu był głęboki i kończył się na około 20 źródłach, więc wyśmiewanie źródła po źródle byłoby kłopotliwe. Zamiast tego wybrałem odpowiednie odcięcie DAG i zastąpiłem tylko ~ 7 referencji syntetycznymi danymi, utworzonymi za pomocą samego SQL :


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)
)

Aby lepiej zwizualizować i udostępnić dane wejściowe i wyjściowe, stworzyłem kopię skryptu dla każdego z CTE (tj. każdej z pośrednich tabel) i dodałem SELECT * FROM <cte>na końcu. Następnie wykonałem każdą kopię, uzyskując plik CSV dla każdej pośredniej transformacji — foo.csv, bar.csv, model_x.csv itd. Pomogło to w debugowaniu każdego etapu DAG, a także umożliwiło udostępnianie osobom nietechnicznym ludziom, jakie byłyby wyniki zmian logicznych, biorąc pod uwagę niektóre syntetyczne dane wejściowe, w formacie CSV.

Ograniczone zyski

Ostatecznie ten test zakończył się sukcesem, upewniając się, że nie dodamy żadnych błędów przy zmianie logiki, ponieważ te same wejścia zostały użyte dla istniejącej i nowej logiki. Pomogło to również w późniejszym debugowaniu problemu produkcyjnego, umożliwiając nam odtworzenie go za pomocą danych syntetycznych. Niestety, ponieważ został zbudowany w określonym celu, nie próbowaliśmy uogólnić go do pisania testów w innych modelach.

Główne lekcje do tej pory to:

SQL jest opcją o najmniejszym niedopasowaniu oporu do deklarowania danych syntetycznych. Pisząc dane syntetyczne w czystym SQL nie musimy się martwić o rzutowanie, NULL, komentarze, czytelność… ponieważ sama składnia języka już je zapewnia!
Ważne jest, aby kpić z refów. Najbardziej złożona część logiki SQL znajduje się na końcu dużego DAG, a konieczność wyśmiewania każdego ze źródeł przyniosłaby efekt przeciwny do zamierzonego. Kpiąc ze źródeł, moglibyśmy przetestować wszystkie modele pośrednie… ale nie chciałem testować wszystkich, tylko te interesujące!
Grupowanie wierszy według przypadków testowych jest lepsze niż według tabeli. Nauczyłem się tego już w poprzednim doświadczeniu, ale chciałbym tylko powtórzyć, że nawet w przypadku pisania przypadków testowych w języku SQL z komentarzami nadal traci się pewien kontekst podczas czytania przypadków i cofania się od danych wyjściowych do testów.

Zdjęcie autorstwa autora, na licencji CC-BY 4.0

Nie wiem, czy uda nam się znaleźć idealne rozwiązanie do testów jednostkowych SQL, ale wciąż możemy o tym pomarzyć:

Przypadki testowe byłyby uruchamiane w izolacji;
Każde wyjście wskazywałoby, z jakiego przypadku pochodzi;
Nieciekawe kolumny mogą być automatycznie uzupełniane i/lub ignorowane;
Cała logika jest ponownie wykorzystywana, żadne adaptacje nie byłyby konieczne do testowania;
Punkt odcięcia wykresu zależności może być dowolny;
Przypadki testowe i ich wyniki byłyby zrozumiałe — i możliwe do zapisania — przez eksperta dziedzinowego/specjalistę biznesowego;

Epilog

To tyle, dzięki za przeczytanie! Jeśli dotarłeś tak daleko, zostaw 10 klaśnięć, aby ten artykuł był widoczny dla innych osób takich jak Ty. Co najważniejsze, podziel się swoimi doświadczeniami z testów jednostkowych w inżynierii danych, dopiero zaczynam i wszelkie raporty są mile widziane!