“T” w “ETL”

https://xpil.eu/7yt

Jako że zawodowo jestem od zarania dziejów Bazylem, nieustannie dziubię w najróżniejszego rodzaju bazach danych. Po drodze wyspecjalizowałem się w budowaniu hurtowni danych, co jest o tyle wygodne, że (A) pojęcie "hurtownia danych" jest bardzo nieostre i każdy projekt jest inny oraz (B) mnóstwo firm potrzebuje hurtowni danych lub przynajmniej czegoś wystarczająco podobnego więc (C) zajęcia mi nie brakuje no i najważniejsze: (D) nie ma nudy, bo (A).

Hurtownia danych ma dane ^{[citation needed]}. Te dane trzeba skądsik zassać. Proces owego zasysania wabi się "ETL" czyli z angielskiego "Extract-Transform-Load": najsampierw "Extract", czyli wychachmęcamy dane ze źródełka, potem "Transform", czyli przekształcamy je do bardziej jadalnej postaci, wreszcie "Load" czyli zapisujemy przekształcone dane do hurtowni właściwej.

Ewentualnie ELT: najpierw zaciągamy dane ze źródełka, potem zapisujemy je lokalnie bez żadnych zmian i dopiero wtedy grzebiemy z transformacjami.

O ile "E" i "L" są już od dawna zestandaryzowane i w zasadzie wymagają minimum wysiłku intelektualnego, o tyle "T" bywa skomplikowane. Czasem trzeba tabelkę obrócić o 90 stopni, czasem powyciągać dane z paru tabelek i posklejać, albo wręcz przeciwnie - wyjąć dane z jakiejś kolumny i je pokawałkować, czasem rozbebeszyć jakiegoś dżejsona czy innego iksemela, do tego jeszcze policzyć jakąś średnią czy inne odchylenie. Różnie.

Owo "T" to dość cenny kawałek w tej trójcy, bo przekształcenia danych mogą być całkiem złożone, w dodatku w każdym projekcie hurtowni będą one inne, raczej unikalne. Wiele projektów hurtowni danych zapada się pod ciężarem zbyt dużego i skomplikowanego "T".

Główny gość dzisiejszego wpisu, narzędzie DBT firmy - uwaga, niespodzianka - DBT Labs próbuje ustandardyzować owo nieszczęsne "T" i sprawić, żeby transformacje były łatwe, proste i przyjemne. Do tego jeszcze bierze pod uwagę współczesne "zachmurzenie" biznesu, a więc nieobce mu są wszelakie awuesy, azury i inne kubernety - aczkolwiek oczywiście z lokalną instancją MSSQL czy SQLite też zabangla.

Na DBT natknąłem się całkiem przypadkiem, w okolicach pi x drzwi listopada zeszłego roku, kiedy to nazwa ta wypłynęła w rozmowie z kolegą z innego teamu, który z kolei usłyszał ją od klienta przy okazji jakiegoś tam projektu. Zaintrygowało mnie to, spróbowałem się przez chwilę tym wynalazkiem pobawić, ale bez większych sukcesów, bo zawalony robotą oraz wydarzającym się wokół szeroko pojętym Życiem po prostu nie miałem czasu żeby wrzucić sobie na tapet kolejną zabawkę.

Jednak całkiem niedawno pojawił mi się na służbowym półmisku inny projekt, który ugrzązł był właśnie na etapie budowania przekształceń danych lądujących w hurtowni. Przypomniałem sobie wtedy o DBT, zasiadłem doń tym razem już nieco porządniej, przegryzłem się przez dokumentację (nie całą - na to potrzebowałbym z pół życia) i zabrałem się za eksperymentowanie.

Efekty?

Póki co czuję się trochę jak dziesięciolatek, który po pięciu lekcjach pływania na lokalnym basenie musi teraz wskoczyć do oceanu. Czyli: przy samym brzegu, nie dalej niż do kolan, koniecznie w kamizelce. Pomalutku, na rozpaczliwca, czasem śmieszno, czasem straszno - ale jakoś płynę 🙂

Początkowo planowałem zrobić tutaj szczegółowy przepis na szarlotkę z DBT, ale potem się jak zwykle okazało, że skończył mi się rozpęd łamane przez czas, więc dziś tylko opowiem o narzędziu ogólnie, a bardziej szczegółowe instrukcje być może zamieszczę w jakimś przyszłym wpisie.

Na dzień dobry instalujemy sobie DBT - najlepiej od razu dla takiej technologii bazodanowej, jakiej będziemy używać w docelowym projekcie. Na stronie producenta jest szczegółowa lista wszystkich obsługiwanych technologii. W chwili kiedy piszę te słowa jest tam 10 wtyczek "oficjalnych" oraz 28 "nieoficjalnych" tj. napisanych przez ludzi spoza firmy, ale zatwierdzonych przez producenta do ogólnego użytku.

Potem tworzymy sobie folder roboczy i wydajemy w nim magiczną komendę dbt init <mój projekt>, która utworzy folder o nazwie <mój projekt>, a w nim szereg podfolderów do różnych rzeczy - nas najbardziej będzie interesować podfolder models. Zanim jednak zaczniemy zabawę właściwą musimy upewnić się, że mamy porządnie zdefiniowane połączenia do danych, które należy skonfigurować w pliku o nazwie profiles.yml znajdującym się w ukrytym folderze ~/.dbt

Tylda oznacza tu nasz katalog domowy czyli pod linuksem /home/nazwa_uzytkownika a w Windows C:\Users\nazwa_uzytkownika.

Powód, dla którego plik ten trzymany jest na zewnątrz naszego projektu jest bardzo prosty: projekt z założenia będzie siedział w jakimś GIT-cie, a nie chcemy przecież trzymać tam naszych haseł dostępu do danych. Można zmienić lokalizację tego pliku, ale trzeba wtedy pamiętać o poinformowaniu DBT stosowną opcją w wierszu poleceń.

Zawartość / składnia pliku profiles.yml zależy od tego, do jakiego źródła danych się łączymy - szczegóły można znaleźć w oficjalnej dokumentacji. Najlepiej jednak wyguglać jakiś gotowy, konkretny przykład, ponieważ te pokazane na stronie producenta są zazwyczaj nieco za bardzo szczegółowe, bo chcą zaprezentować wszystkie możliwe opcje.

Prawdę powiedziawszy porządne ustawienie wszystkiego w profiles.yml było - przynajmniej dla mnie - najtrudniejszym kawałkiem do nauczenia się. Dalej już z górki.

W folderze z projektem znajdziemy podfolder models, w którym będziemy tworzyć pliki .sql o nazwach odpowiadających tabelom docelowym naszej hurtowni, oraz o zawartości składającej się z pojedynczego polecenia SELECT... poprzedzonego (opcjonalnie) dyrektywami typu klucze sortujące, indeksy unikalne, strategie aktualizacji danych i tak dalej. DBT będzie potem te nasze SELECT-y uruchamiać, a ich wyniki zapisywać do tabeli docelowej.

Na początku łatwo jest przeoczyć jedną rzecz: polecenie SELECT... w pliku .sql w folderze models jest, i owszem, napisane przy użyciu składni SQL właściwej dla konkretnego silnika bazy danych, ale można w nim używać dodatkowych - umieszczonych w podwójnych nawiasach wąsatych - "magicznych" wyrażeń zgodnych ze składnią DBT, które wzbogacają nasz skrypt i dają dodatkowego kopa. Każdorazowe uruchomienie projektu (poleceniem dbt run) tak naprawdę najpierw "kompiluje" te nasze pliki .sql do folderu target, a dopiero potem uruchamia te skompilowane wersje.

Oprócz tego w folderze models można też (i warto!) tworzyć pliki .yml sparowane nazwą z plikami .sql. W tych plikach .yml (lokalni wymawiają to "yaml") definiujemy dokumentację poszczególnych kolumn (typy danych, opisy itd), rozmaite testy, jakie DBT ma uruchamiać na naszych danych, na przykład unikalność kluczy, NULL-e, ale też mnóstwo innych, bardziej zaawansowanych (na przykład można sobie utworzyć test, który przejdzie jeżeli odsetek NULL-i w jakiejś kolumnie jest mniejszy niż x%) i kupę innego śmiecia.

Na osobną uwagę zasługuje funkcja {{ ref('...') }}, która najczęściej trafia zaraz po FROM ewentualnie JOIN, a która definiuje zależności między danymi. Na przykład zamiast pisać SELECT * FROM edw.tabela piszemy SELECT * FROM {{ ref('tabela') }} dzięki czemu DBT "rozumie", że kod zapisujący dane do edw.tabela musi zostać uruchomiony najpierw. Ponadto jeżeli zmieni nam się schema docelowa (w tym przypadku: edw) to zmianę tę wykonujemy tylko w jednym miejscu (plik konfiguracyjny projektu) i od tej pory DBT automagicznie będzie wołać tę tabelę z właściwego miejsca.

Jeszcze innym ważnym elementem DBT są strategie aktualizacji danych. Nie udało mi się póki co przetestować wszystkich, te najważniejsze to:

• table - kasuje i tworzy całą tabelę docelową od zera za każdym razem.
• incremental - dopisuje nowe dane i aktualizuje zmienione rekordy w oparciu o klucze i inne metadane.
• ephemeral - tworzy "wirtualną" tabelę na potrzeby bieżącej sesji, która nie jest nigdzie materializowana w bazie danych, ale można jej używać w projekcie. Coś jakby CTE tylko na poziomie samego narzędzia.
• view - tworzy w bazie docelowej widok zamiast tabeli.

Te strategie również definiuje się w pliku konfiguracyjnym projektu, ale można je potem nadpisywać w konfiguracji poszczególnych tabel (czyli na przykład ustawiamy cały projekt na incremental a potem jeżeli chcemy jakąś konkretną tabelę mieć w modelu table, ustawiamy to w konfiguracji tej jednej tabeli).

Prawdziwym hitem jest komenda dbt docs, która - w zależności od wybranej opcji - generuje bądź serwuje w postaci lokalnego serwera www - pełną dokumentację naszego projektu, a więc wszystkie elementy, które mamy w naszym projekcie, włączając w to wykresy zależności między tabelami. Najprościej wydać komendę dbt docs serve --port xyz (gdzie xyz to numer portu TCP, na którym będzie można oglądać dokumentację) - a następnie w przeglądarce wejść na localhost:xyz i podziwiać. Można zamiast tego zrobić dbt docs generate, która wyprodukuje statyczną stronę www w folderze target (nie jest to tak wygodne jak dbt docs serve, ale działa).

Innym bardzo pożytecznym poleceniem jest komenda dbt test, która nie modyfikuje żadnych danych, ale uruchamia wszystkie testy, jakie zdefiniowaliśmy w naszym projekcie i wyrzuca na wyjściu szczegółowy raport. Dzięki temu możemy na przykład wyłapać niezgodności między naszym projektem a faktyczną strukturą danych w bazie, błędy w składni SQL-a i kupę innych rzeczy.

Zarówno dbt test jak i dbt run akceptują opcję --select po której podajemy nazwę tabeli - w takim przypadku narzędzie uruchomi przetwarzanie (lub testowanie) wyłącznie dla tej jednej tabeli. Jest to przydatne zwłaszcza przy dużych projektach, gdzie każdorazowe przetwarzanie całości mogłoby zająć zbyt wiele czasu (i / lub zasobów).

Na razie bawię się tym narzędziem dopiero od mniej więcej tygodnia, ale już widzę mnóstwo interesujących zastosowań. Najlepsze jest to, że tak naprawdę przekazujemy "władzę" jeżeli chodzi o pisanie zapytań analitycznych w ręce "ludu" (czyli analityków) - każdy użytkownik znający SQL może sobie tworzyć swoje pliki z zapytaniami do hurtowni i po chwili dostać wynik - w postaci nowej tabeli lub widoku - a co najważniejsze wszystko to siedzi sobie ładnie w systemie kontroli wersji więc jak ktoś coś spieprzy, od razu wiadomo kto, co i kiedy 🙂

Trochę mi ten wpis wyszedł chaotyczny, ale to pewnie dlatego, że próbuję tutaj opisać wszystko na raz, na wariata. Jeżeli czas i samozaparcie pozwolą, nie wykluczam osobnej, bardziej uporządkowanej serii wpisów o DBT. Póki co - zachęcam do samodzielnego eksperymentowania z tym narzędziem. Zapewniam - warto.

https://xpil.eu/7yt