Czymże jest ów tytułowy dynamiczny SQL? Otóż zwyczajowo na tę okoliczność można usłyszeć, że jest to pewna technika “programowania”, która umożliwia “tworzenie” instrukcji SQL niejako w locie, tj. dopiero na etapie wykonywania programu (runtime). Tym sposobem potencjalnie zyskujemy możliwość konstruowanie bardziej uniwersalnych i elastycznych instrukcji SQL, ponieważ może zdarzyć się i tak, że pełny zestaw informacji niezbędnych do poprawnego wykonania kodu może być nieznany podczas kompilacji. Na ten przykład możemy czy wręcz musimy użyć dynamicznego SQL chociażby na okoliczność budowy procedury, której jednym ze składowych elementów może być pobranie danych z tabeli, której nazwa nie jest znana do czasu odpalenia kodu (dajmy na to, że nazwa interesującej nas tabeli przekazywana jest jako parametr do naszej procedury).
Brzmi tajemniczo i niejasno? Zapewne tak jest w rzeczy samej, ale to w sumie smutny standard w mojej pisaninie, w której klarowny i precyzyjny wywód jest towarem deficytowym. Zapewniam jednak (i nie robię tego na wyrost jak mam to w zwyczaju), że pod koniec tekstu całość powinna być odpowiednio zrozumiała, co zamierzam osiągnąć między innymi posiłkując się przykładem z mojego zawodowego życia zaczerpniętego.
Tym niemniej zanim zahaczymy o wątki “autobiograficzne” dobrze byłoby pokazać na prostym przykładzie o co te całe “halo”. Zrobię to na bazie składni charakterystycznej dla dialektu SQL od Google, którą ten ostatni udostępnia na potrzeby pracy w ramach BigQuery. Tym niemniej zasada działania dynamicznego SQL w każdym z dialektów jest taka sama, czyli przekazujemy do dedykowanej instrukcji łańcuch znaków, który następnie ma zostać przekonwertowany na działający kod SQL. W przypadku Google SQL służy do polecenie EXECUTE IMMEDIATE i całość w najprostszej formie mogłaby wyglądać tak jak niżej:
EXECUTE IMMEDIATE
(
'''
SELECT
COUNT(*)
FROM
bigquery-public-data.google_analytics_sample.ga_sessions_20170801
'''
)Oczywiście w tym konkretnym przykładzie nie ma za wiele sensu, ponieważ brak tu jakiegokolwiek uzasadnienia do użycia dynamicznego SQL w przypadku tak banalnej kwerendy. Tym niemniej przywołałem go, by na zasadzie kontrastu zaprezentować prawdziwą siłę tego rozwiązania. To na co ewentualnie zwróciłbym uwagę w kodzie widocznym wyżej to użycie potrójnych apostrofów, ponieważ tego rodzaju zabieg w przypadku Google SQL pozwala zapisać ciąg znaków w kilku liniach (jest to sposób zapisu znany chociażby użytkownikom Pythona).
Prawdziwa siła dynamicznego SQL objawia się w przypadkach takich jak niżej, gdzie – jak wspomniałem wcześniej – część kodu, który ma być wykonany, nie jest dostępna w momencie pisania kodu.
DECLARE v_table_name STRING;
SET v_table_name = 'bigquery-public-data.google_analytics_sample.ga_sessions_20170801';
EXECUTE IMMEDIATE(CONCAT('SELECT COUNT(*) FROM ', v_table_name ));
--EXECUTE IMMEDIATE('SELECT COUNT(*) FROM ' || v_table_name );Na potrzeby tego ćwiczenia przyjmijmy, że zmienna v_table_name to wartość parametru przekazanego do jakiejś procedury z zewnątrz, zaś polecenie EXECUTE IMMEDIATE jest częścią tej procedury, gdzie jednym z kroków jest konieczność pobrania liczby wierszy z tabeli, której to nazwa została przekazana we wspominanym parametrze. Bez dynamicznego SQL nie byłoby możliwości realizowania tego rodzaju rozwiązań.
Oczywiście ktoś nie bez racji może stwierdzić, że to bardzo specyficzny scenariusz i w praktyce – zwłaszcza gdy nasza aktywność sprowadza się wyłącznie do pisania zapytań wybierających dane – ta technika dla przeciętnego użytkownika SQL jest całkowicie zbędna. W rzeczy samej tak rzeczywiście jest bardzo często, czego moja skromna osoba może być najlepszym dowodem.
Otóż jako “bazodanowiec” z bożej łaski przez lata wiedziałem, że jest dostępne tego rodzaju rozwiązanie, ale nie miało to dla mnie większego znaczenia. Tak naprawdę przez te wszystkie lata dynamiczny SQL kojarzył mi się tylko z jednym, a mianowicie z pewnym – dość topornym jak sądziłem – obejściem problemu występującego w przypadku procedur tworzonych w PL/SQL (języku będącym proceduralnym rozszerzeniem SQL dla baz danych “made in Oracle”). Otóż twórcy tego DBMS przyjęli jako regułę, że używanie poleceń DDL (czyli takich jak na ten przykład CREATE TABLE) w ramach procedur będzie odgórnie zablokowane. Tym niemniej jeśli nasz program mimo wszystko wymagał takiego zabiegu w ostateczności można było sięgnąć po wytrych w postaci użycia dynamiczny właśnie SQL. Tym sposobem po prostu mogliśmy zapisać tego rodzaju polecenia jako łańcuch znaków, a następnie przekazać go do polecenia EXECUTE IMMEDIATE, które było uruchamiane w ramach rzeczonej procedury.
Jednakże niemal równo rok temu po raz pierwszy dane mi było Otóż w ówczesnym miejscu pracy dostałem zadanie wgrania konfiguracji do jednej z istniejących tabel. Samo zadanie nie było specjalnie złożone, choć już nawet dobrze nie pamiętam, czego dotykała ta modyfikacja (wydaje mi się, że poziom złożoności nie był o wiele wyższy niż na ten przykład uzupełnienie kalendarza zapisanego w jednej z tabel o dni wolne w danym roku kalendarzowym). Jednakże prawdziwym wyzwaniem była liczba instancji aplikacji, które wymagały takiego update’u, ponieważ czekało mnie – jak sądziłem wówczas – połączenie się z grubo ponad 50 bazami danych w celu dokonania jakiegoś inserta bodajże. Na szczęście jeden z kolegów zlitował się nad piszącym te słowa i pokazał sposób, w jaki zwyczajowo radzono sobie z podobnymi zadaniami w przeszłości.
Otóż na jednym z testowych zasobów zapisana była tabela zawierająca konfigurację (typu adres IP, nazwa instancji BD) wszystkich serwerów i baza danych na nich posadowionych, z których korzystała rzeczona aplikacja. W efekcie wystarczyło odpowiednio zmodyfikować przygotowaną przeze mnie kwerendę właśnie za pomocą dynamicznego SQL, tak by w odpowiednie miejsca – za pomocą zmiennych – podstawiły się dane pobrane ze wspomnianej tabeli konfiguracyjnej i tym sposobem za pomocą automatycznie wykreowanych DB Linków wykonać całość pracy przy jednorazowym odpaleniu skryptu. Tym sposobem całość zajęła mi kilka minut zamiast połowy dnia, choć gwoli ścisłości zaznaczę, że od sympatycznego kolegi dostałem gotowca, w ramach którego po prostu musiałem tylko podstawić nową kwerendę.
Kolejny przypadek, kiedy wykorzystałem dynamiczny SQL w celu wykonania pewnego zadania, miał miejsce relatywnie niedawno w obecnym miejscu zatrudnienia. Otóż w ramach jednych z wielu zadań realizowanych w moim zespole znajduje się przygotowywane zestawienia danych, które następnie służą – przyjmijmy na potrzeby tego wpisu – do komunikacji marketingowej skierowanej do wybranych grup klientów. Końcowym efektem tego ćwiczenia jest powstanie kilkunastu plików tekstowych, które następnie ładowane są do dedykowanego narzędzia odpowiedzialnego za wysyłkę stosownych powiadomień.
Niestety – z perspektywy każdego wykonawcy tego zadania – u zarania tego procesu została podjęta decyzja, że na potrzeby tego ćwiczenie będzie za każdym razem powstawało prawie 20 różnych tabel, których nazwa będzie zawierała za każdy razem między innymi datę generowania danych (czyli bieżącą de facto). Co więcej całość działa tak, że każda kolejna tabela zazwyczaj korzysta z informacji zapisanych w tych wcześniej utworzonych. Na bazie tych założeń ktoś kiedyś przygotował skrypt przekazywany z pokolenia na pokolenie kolejnych analityków i tak to się kręciło dopóki nie przyszła pora na mości Borciugnera.
Niestety już po drugim razie, gdy mierzyłem się z tym zadaniem, stwierdziłem, że tak dłużej być nie może, ponieważ całość zajmowała mi każdorazowo ponad 2 godziny czasu, a ponadto cały czas byłem narażony na banalną ludzką pomyłkę, gdyż jednym z kolejnych założeń było to, że nazwa wygenerowanych plików miała odpowiadać nazwie tabeli, z której te dane zostały pobrane. Wystarczyło się więc machnąć w kopiowaniu nazwy tabeli do pliku, by komunikaty trafiły do błędnych adresatów. To ostatnie zagrożenie łatwo było zaadresować ponieważ na jednym z projektów mam na szczęście możliwość korzystania z usługi Google Store, która pozwala między innymi na eksport do plików danych wygenerowanych za pomocą zapytań SQL. Wystarczyło więc w skrypcie dopisać kawałek kodu – oczywiście nikt wcześniej na to nie wpadł – odpowiadający za wrzucenie wyniku kwerendy dla każdej z tych tabel do pliku przy pomocy polecenia EXPORT DATA. Oczywiście to rodziło kolejne wyzwania, ale o tym może już przy innej okazji.
Tym niemniej nierozwiązanym problemem była stale zmieniające się nazwy tabel związana z koniecznością zapisywania każdorazowo bieżącej daty w jej ramach . Tu właśnie z pomocą przyszedł dynamiczny SQL, zaś całość wyglądała mniej więcej tak:
DECLARE v_data_current DATE;
DECLARE v_date_table, v_query, v_export STRING;
SET v_data_current = current_date();
SET v_date_table = REPLACE(STRING(v_data_current), "-", "_");
SET v_query =
''' CREATE OR REPLACE TABLE `nazwaProjektu.nazwaDataSet.Tabela_1_''' || v_date_table ||'''`
AS
SELECT
lista_pol
FROM
lista_tabel
WHERE
lista_warunkow
'''
;
EXECUTE IMMEDIATE v_query;
SET v_query =
''' CREATE OR REPLACE TABLE `nazwaProjektu.nazwaDataSet.Tabela_2_''' || v_date_table ||'''`
AS
SELECT
lista_pol
FROM
lista_tabel
WHERE
lista_warunkow
'''
;
EXECUTE IMMEDIATE v_query;
SET v_export = '''EXPORT DATA OPTIONS (
uri='gs://borciugner/Tabela_1_''' || v_date_table || '''_*.csv',
overwrite=true,
EXECUTE IMMEDIATE v_export;
format='CSV',
header=true,
field_delimiter=';'
) AS
SELECT
*
FROM
`nazwaProjektu.nazwaDataSet.Tabela_1_''' || v_date_table || '''`
'''
;
EXECUTE IMMEDIATE v_export;
SET v_export = '''EXPORT DATA OPTIONS (
uri='gs://borciugner/Tabela_1_''' || v_date_table || '''_*.csv',
overwrite=true,
format='CSV',
header=true,
field_delimiter=';'
) AS
SELECT
*
FROM
`nazwaProjektu.nazwaDataSet.Tabela_1_''' || v_date_table || '''`
'''
;
EXECUTE IMMEDIATE v_export;W praktyce całość była nieco bardziej złożona, ponieważ w rzeczywistości potrzebna była dodatkowa logika biznesowa do wyliczania pewnych wymaganych parametrów (głównie dat), której pojawiały się później w kodzie. Natomiast co do zasady skrypt wyglądał mniej więcej jak ten wyżej. Czyli na początek utworzone zostały 4 zmienne. Do pierwszej z nich v_data_current przypisywana jest data bieżąca przy pomocy funkcji current_date. Ponieważ nazwa tabeli – zgodnie z założeniami – wymagała daty jej wygenerowania w nieco innym formacie niż ten domyślny (np. 2024-11-01 powinien się prezentować jako 2024_11_01) użyta została zmienna v_date_table, której zadaniem jest przechowywanie daty bieżącej w oczekiwanym formacie (przekształcenie wykonane za pomocą funkcji replace).
Są też dwie zmienne typu znakowego (STRING), których zadaniem jest podstawienie do polecenia EXECUTE IMMEDIATE zestawu instrukcji do wykonania. Oczywiście równie dobrze wystarczyłaby jedna zmienna, ale dla pewnej przejrzystości przyjąłem, że osobna zmienna (v_query) będzie dotyczyła instrukcji DDL, zaś druga poleceń związanych z eksportem danych do Google Store (v_export). Rzecz jasna nic nie stoi na przeszkodzie (oprócz potencjalnej czytelności kodu), by utworzyć tyle zmiennych do takiego podstawienia ile kwerend/eksportów chcemy finalnie użyć w ramach dynamicznego SQL.
W każdym razie uzbrojeni we wszystkie wspomniane zmienne możemy wygenerować niezbędne tabele i zapisać je do plików przy pomocy dynamicznego SQL, każdorazowo tworząc zmiennę składającą się z właściwej listy instrukcji, uzupełnioną informacjami ze zmiennych (tutaj wyłącznie mamy do czynienia z wartością przypisaną do zmiennej v_date_table. Tym sposobem dochodzimy do momentu, kiedy to każdorazowo uruchamiamy skrypt, by osiągnąć zamierzony efekt. No może nie do końca tak było w moim przypadku i do tego celu musiałem dorobić skrypt napisany w bashu, ale o tym już w kolejnym wpisie.
