Procedury składowe i widoki w hurtowniach danych

Mimo dużej popularności ruchu NoSQL i rozwoju związanych z nim technologii, bazy relacyjne mają się dobrze i wciąż stanowią lwią część eksploatowanych systemów. A tam gdzie bazy relacyjne, tam i język SQL. Na temat wad i zalet SQL-a napisano całe tomy, zwolennicy i przeciwnicy twardo okopali się na swoich pozycjach i nie są skłonni do zmiany obozu. Kto wygra czas pokaże. Jednak tu i teraz mamy to co mamy i jeśli przyszło nam pracować z bazą relacyjną lub wielowymiarową nie mamy zbyt wielkiego wyboru -  musimy zrobić wszystko, by działała sprawnie i wydajne. 

A co wpływa na sprawne działanie bazy? Adekwatne zasoby sprzętowe, poprawny model, dobra strategia indeksowania i wydajne zapytania. Doświadczenie uczy, że najprościej rozwiązać problemy sprzętowe – tu ograniczeniem są niemalże wyłącznie środki finansowe jakimi dysponujemy. Biorąc pod uwagę stały spadek cen zarówno dysków twardych, jak i pamięci, możemy na tym polu uzyskiwać coraz więcej inwestując coraz mniej. Przy założeniu, że model opracowano starannie, pozostaje nam jeden element – zapytania języka SQL. I tu zaczynają się schody.

Wielką bolączką administratorów baz danych jest cecha języka SQL, uważana przez wielu za jego największą zaletę – deklaratywność.

Deklaratywność oznacza, że programista określa tylko co chce uzyskać, nie określa jednak jak ma być to zrobione. Niektóre systemy zarządzania bazami danych są wyposażone w mechanizm dyrektyw optymalizatora – w tym przypadku twórca rozwiązania przynajmniej częściowo może wpłynąć na wybór ścieżki wykonania (na przykład, wskazując, czy i jakie indeksy mają być użyte), inne systemy takiego mechanizmu nie mają – trzeba wtedy naprawdę dobrze znać motor baz danych oraz różne zawodowe sztuczki i triki, by przygotować naprawdę wydajny SQL. Trudno takich umiejętności wymagać od programisty Javy (choć oczywiście miło jeśli taką wiedze posiada), który pisze kod aplikacji często w oderwaniu od konkretnego motoru, zrzucając problem „przełożenia” części bazodanowej na sterownik JDBC. Co więcej, od programisty często wymaga się tworzenia kodu uniwersalnego, nie powiązanego z konkretną technologią baz danych. Jest to oczywiście tylko przykład. Podobny problem pojawia się na styku programista-baza danych bez względu na użyty język (być może poza 4GL, ale kto go jeszcze pamięta).

Naturalnym rozwiązaniem zagadnienia wydaje się przerzucenie ciężaru tworzenia zapytań języka SQL z programistów na specjalistów zajmujących się bazami danych. Podejście takie wręcz samo narzuca się w przypadku hurtowni danych, gdzie właściwie wszystko obraca się wokół bazy.
Jednym ze sposobów przeniesienia zapytań języka SQL z aplikacji na stronę bazy danych jest wykorzystanie widoków i procedur składowanych.
Oba mechanizmy pozwalają zapisać przygotowany kod w bazie danych, by go później wielokrotnie wykorzystywać. Procedury składowane mają jednak wielką przewagę nad widokami - można przekazywać parametry, a w kodzie można używać zmiennych, pętli i instrukcji iteracyjnych.
Procedury składowane szczególnie dobrze sprawdzają się gdy mamy do czynienia z powtarzalnym przetwarzaniem wsadowym: transformacją danych, agregacją, czyszczeniem i weryfikacją – wszystkie te czynności są immanentną częścią procesu ETL. Tym samy potwierdza się teza, że procedury składowane to idealne rozwiązanie dla hurtowni.

Zarządzenie kodem

Procedury składowane są częścią bazy danych co znacznie upraszcza śledzenie zależności między modelem a zapytaniami języka SQL. Gdy baza danych podlega modyfikacji, takiej jak dodanie lub usunięcie kolumny, zmiana typu danych, dodanie lub usunięcie ograniczenia NOT NULL, zdefiniowanie nowej relacji lub usunięcie istniejącej, osoba odpowiedzialna za wprowadzenie zmian bez trudu wyszuka wszystkie powiązane obiekty – procedury składowane oraz widoki i wprowadzi w nich odpowiednie zmiany. Programista dostaje do dyspozycji gotowy, przetestowany i działający kod. Nie musi martwić się zmianami w bazie danych, które często pozostają dla niego transparentne. Nie musi, na przykład wiedzieć, że jakaś kolumna została przeniesiona do innej tabeli, lub że w celu usunięcia niepoprawnej relacji wiele do wielu między dwie tabele wstawiono trzecią.

Gdyby zapytanie SQL znajdowało się nie w bazie, lecz kodzie aplikacji zadanie byłoby trudniejsze pod względem organizacyjnym, gdyż wymagałoby koordynacji pracy kilku zespołów. Jest to szczególnie ważne w obecnych czasach, gdy zespoły programistyczne są mocno rozproszone – poszczególne osoby znajdują się często w oddalonych od siebie miejscach, często nawet w różnych strefach czasowych.
I jeszcze jeden ważny aspekt zarządzania kodem. Zdarza się, że najlepszą wydajność zapytania uzyskujemy korzystając z wybranych rozszerzeń motoru. Bywają one na tyle specyficzne, że mechanizmy komunikacja aplikacja-baza danych nie wspierają ich. Najprostszym rozwiązaniem jest „schowanie” rozszerzeń w widoku lub procedurze składowanej.

Już słyszę głosy oburzenie mówiące, że takie rozwiązanie jest mało elastyczne, związane z konkretną technologią i nie da się go wykorzystać gdy zdecydujemy się zmienić motor. Tak, to wszystko prawda, ale... Gdyby ktoś zainstalował na komputerze zaawansowany edytor tekstu, a następnie używał tylko Notatnika, czy takie postępowanie uznalibyśmy za logiczne? Na pewno nie! Po co płacić za wyrafinowane narzędzie, jeśli nie korzystamy z jego możliwość?! A przecież logika byłaby taka sama: dokument utworzony z wykorzystaniem zaawansowanych funkcji formatowania jest mało elastyczny i nieprzenaszalny - może nie dać się odczytać w innym edytorze. Rozwijając tę myśl – po co płacić za motor baz danych pełen zaawansowanych funkcji, jeśli w imię uniwersalności kodu nie zamierzamy z nich korzystać?! Oczywiście, wszystko jest kwestią wyboru i okoliczności. W pewnych sytuacjach lepiej jest postawić na uniwersalność, w innych na specjalizację. Jednak przed podjęciem decyzji trzeba mieć pełną świadomość plusów i minusów wszystkich możliwych wariantów.

Wydajność

Potęga języka SQL polega między innymi na tym, że został on opracowany z myślą o wydajnym przetwarzaniu dużych zestawów danych. Próba uzyskania takiego samego efektu w kodzie innego języka wymaga zazwyczaj użycia instrukcji iteracyjnych – takie rozwiązanie jest na ogól zdecydowanie wolniejsze. Dlaczego więc nie wybrać rozwiązania, które działa lepiej?

Wspominałam już, o ważnej roli strategii indeksowania dla wydajności systemu. Gdy logika odwołania do danych tworzona jest po stronie bazy – jako procedura składowana lub widok – administrator może od razu utworzyć odpowiednie indeksy. Gdy to samo zadanie zakodowane jest po stronie aplikacji, osoba odpowiedzialna za bazę może o problemie nie wiedzieć, gdyż nie objawią się one ani podczas testów funkcjonalnych, ani podczas testowania jakości oprogramowania na małej lub średniej ilości danych. Kłopot powoli narasta podczas eksploatacji wraz ze zwiększaniem sie wolumenu danych, aż któregoś dnia wydajność spada poniżej akceptowalnej granicy. O tym, jak wiele może zdziałać poprawne indeksowanie przekonałam się kiedyś na własnej skórze. Poproszono mnie o optymalizację pewnego, niezwykle czasochłonnego przetwarzania wsadowego. Po dodaniu zaledwie dwóch indeksów, czas przetwarzania skrócił się z 34 dni (tak, tak, dni!) do 2,5 godzimy.

Testowanie

A jeśli już o testowaniu mowa... Umieszczenie kodu w bazie danych – w widoku lub procedurze składowanej znacznie upraszcza testowanie. Testowany element jest dobrze wyizolowany, wiemy więc, że na jego działanie – zarówno pod kątem realizacji określonego zadania jak i wydajności – nie wpływają czynniki zewnętrze. Gdy testowany kod wywoływany jest z poziomu aplikacji, na jego działanie ma wpływ szereg dodatkowych czynników:

- błędy w kodzie aplikacji (na przykład wywoływanie instrukcji SQL wewnątrz niepoprawnie zdefiniowanej instrukcji iteracyjnej),
- błędy w konfiguracji aplikacji (na przykład, gdy bufor aplikacji jest zbyt mały by przechować pobrane dane – zdarza się to szczególnie często, jeśli do aplikacji trafia nie gotowy wynik jak to ma miejsce w przypadku stosowania widoków i procedur składowanych lecz robocze zbiory danych, które należy przetworzyć po stronie klienta),
- błędy sterownika (miałam kiedyś, na przykład, do czynienia z sytuacją, gdy sterownik JDBC źle obsługiwał łańcuch znakowe – obcinał wszystko powyżej 64 znaku, co było powodem wielu, poważnych kłopotów),
- problemy z siecią (w przypadku kłopotów z wydajnością musimy najpierw ustalić, czy problem leży po stronie bazy, czy jest związany ze zbyt długo trwającym transferem danych).

Gwoli ścisłości trzeba jednak wspomnieć i dwóch minusach testowania wyizolowanego zapytanie SQL.
Po pierwsze, wybór narzędzi przeznaczonych od testowania procedur składowanych jest, delikatnie rzecz ujmując, ubogi, podczas gdy twórcy aplikacji mają do dyspozycji przebogaty zestaw modułów wspomagających analizę kodu.
Pod drugie, nigdzie nie uciekniemy od testowania rozwiązania całościowego. Wcześniej czy później trzeba będzie zintegrować kod SQL-a z aplikacją, a następnie sprawdzić, czy integracja dała poprawne wyniki. W rezultacie dochodzi nam dodatkowy element testów.

Bezpieczeństwo

Wielokrotnie spotykam się z stwierdzeniem, że ograniczenia dostępu do zasobów należy definiować na poziomie aplikacji, a nie bazy danych. Prawdę mówiąc, do dziś pozostaje dla mnie zagadką, skąd bierze się pomysł na przeciwstawianie sobie tych dwóch mechanizmów. To tak, jakby dyskutować, czy należy zamykać sejf, czy drzwi wejściowe. Oba rodzaje zabezpieczeń uzupełniają się, a nie wykluczają. Co więcej, stwierdzenie, że zabezpieczenia na poziomie aplikacji wystarczą, jest z gruntu błędne. Wróćmy do przykładu z drzwiami i sejfem. Co z tego, że zamkniemy drzwi, jeśli włamywacz dostanie się do środka przez okno? Bez trudu ukradnie pieniądze leżące bez zabezpieczenia na biurku. Co innego, jeśli ukryjemy gotówkę w zamkniętym sejfie – nasz złoczyńca będzie miał znacznie utrudnione zadanie. (Nie oznacza to, bynajmniej, że wychodząc mamy nie zamykać drzwi na klucz).

Podobnie z danymi – jeśli dostępu strzeże tylko aplikacja, kto ochroni dane gdy włamywacz podłączy się innym narzędziem?

Dzięki wykorzystaniu widoków można w prosty i wygodny sposób zrealizować dowolną strategię dostępu do danych – wystarczy zdefiniować odpowiedni zestaw widoków i skonfigurować przywileje tak, aby użytkownicy nie mogli korzystać bezpośrednio z tabel lecz wyłącznie z widoków. I tak, definiując odpowiednio klauzulę WHERE można udostępnić konkretnym użytkownikom tylko wybrany podzbiór danych (na przykład, każdy z kierowników ma dostęp wyłącznie do danych pracowników swojego działu). Podobnie, na liście SELECT możemy umieścić tylko te kolumny, które chcemy udostępnić wybranej grupie (na przykład, niektórzy użytkownicy mogą widzieć wszystkie atrybuty pracownika, a inni tylko wybrane).
O ile zaimplementowanie takiej strategii bezpieczeństwa w aplikacji operacyjnej, wspierającej różne, nieraz bardzo skomplikowane i rozbudowane procesy biznesowe, bywa trudne, o tyle w przypadku hurtowni nie powinno stanowić większego problemu. Sprawa staje się jeszcze łatwiejsza jeśli dla hurtowni wybraliśmy podejście wielowymiarowe, charakteryzujące się prostym schematem i relatywnie niedużą liczbą tabel.