Tabele temporalne

https://xpil.eu/ekr 📋

Już w latach 90 zeszłego stulecia firma Postgres próbowała zrealizować ideę tabel temporalnych, jednak ze względu na bardzo wysokie koszty składowania ogromnych ilości danych pomysł zarzucono.

W roku 2013 tabele temporalne trafiły do kolejnej wersji specyfikacji języka SQL.

Od niedawna wszyscy więksi dostawcy baz danych przejęli się tematem i zaczęli implementować tę opcję w swoich produktach. Jak na razie udało się to kilku firmom: Oracle, Postgres, Teradata, IBM DB2, Microsoft, Cockroach DB oraz MarkLogic (ten ostatni nie jest bazą stricte SQL-ową, ale mimo wszystko zaimplementowali "temporalność" w swoim produkcie).

Na czym polega trick?

Trick polega na tym, że w zapytaniu typu SELECT możemy dodać na końcu operator AS OF, a zaraz po nim podać przedział czasu (w szczególności: punkt w czasie), z którego chcemy dostać wyniki.

Czyli taka jakby maszyna czasu.

Oczywiście ZANIM zaczniemy używać tej składni, musimy najpierw włączyć "temporalność" dla danej tabeli.

Jak to wygląda w praktyce?

No cóż. Różni dostawcy zrealizowali to lepiej lub gorzej. Pokażę przykład Microsoftu - może nie najbardziej elegancki, ale najbliższy memu bazylowemu sercu. Uwaga: poniższy przykład jest bardzo uproszczony względem tego, co utworzylibyśmy w świecie rzeczywistym. Chcę tylko pokazać sam mechanizm.

CREATE TABLE dbo.Ksiazki
(
	ID BIGINT NOT NULL PRIMARY KEY CLUSTERED
	, TYTUL NVARCHAR(5700) NOT NULL
	, ISBN CHAR(13) NOT NULL
	, WYDANIE int NOT NULL
	, WAZNE_OD DATETIME2 NOT NULL
	, WAZNE_DO DATETIME2 NOT NULL
	, PERIOD FOR SYSTEM TIME(WAZNE_OD, WAZNE_DO)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.KsiazkiHist));

Powyższe zapytanie utworzy dwie tabele: Ksiazki oraz KsiazkiHist. Dzięki temu, że użyliśmy słówka PERIOD na końcu listy kolumn, a także włączyliśmy wersjonowanie ("WITH (SYSTEM_VERSIONING = ON..."), od tej pory wszelkie zmiany do tabeli Ksiazki będą automatycznie rejestrowane w tabeli KsiazkiHist. Ale to nie wszystko.

Otóż składnia języka SQL została odrobinę zmodyfikowana w celu uproszczenia wyciągania danych historycznych. Zamiast jawnie wysyłać zapytanie do tabeli KsiazkiHist, możemy zrobić tak:

SELECT * from Ksiazki
AS OF '2016-07-02';

Takie zapytanie zwróci nam wszystkie rekordy z tabeli Ksiazki, które istniały tam o północy drugiego lipca 2016.

Inny przykład:

SELECT * from Ksiazki
AS OF FROM '2016-07-02 12:34:55.000' TO '2016-07-02 12:38:51.000';

Tutaj pytamy o wszystkie rekordy, które były aktywne drugiego lipca między godziną 12:34:55 a 12:38:51. To zapytanie (i wszystkie w przykładach poniżej) może nam zwrócić kilka rekordów dla tej samej książki, jeżeli w podanym przedziale czasu rekord danej książki był modyfikowany wielokrotnie.

Jeszcze inny przykład:

SELECT * from Ksiazki
AS OF BETWEEN '2016-07-02 12:34:55.000' AND '2016-07-02 12:38:51.000';

Tutaj dostaniemy wyniki identyczne, jak w poprzednim zapytaniu, z drobną (ale istotną) różnicą: jeżeli ktoś zmodyfikował dany rekord dokładnie o godzinie 12:38:51, zobaczymy ten zmodyfikowany rekord (w poprzednim przykładzie z FROM...TO... taki rekord zostanie odrzucony z wyników).

I ostatni przykład:

SELECT * from Ksiazki
AS OF CONTAINED IN ('2016-07-02 12:34:55.000', '2016-07-02 12:38:51.000');

Tu mamy istotną zmianę względem poprzednich przykładów: zapytanie z operatorem CONTAINED IN zwróci wszystkie rekordy historyczne, które zostały uaktywnione a następnie zdezaktywowane w podanym przedziale czasu.

Jeżeli więc zmieniliśmy książce o ID=34765 tytuł o godzinie 12:34:55.244, a następnie zmieniliśmy ISBN o godzinie 12:34:55:761 i jeszcze potem numer wydania o godzinie 12:38:21.000 (i nic ponadto), wówczas powyższe zapytanie zwróci dla tej książki rekord zmieniony o 12:34:55.244 oraz 12:34:55:761, ale już nie ten zmieniony o 12:38:21.000 (bo nie został on zdezaktywowany w podanym przedziale czasowym).

Na zakończenie odpowiem jeszcze na pytanie, które z pewnością ciśnie się na usta Czytelnikowi: A co w sytuacji, kiedy mamy strasznie dużo danych historycznych, a chcemy zachować tylko historię z, dajmy na to, ostatniego roku?

No cóż. Tutaj też implementacji jest kilka, jedne lepsze, inne gorsze. W Cockroach DB na ten przykład definiujemy sobie po prostu maksymalny okres, w którym dane "temporalne" mają być przechowywane i gotowe - silnik bazy "sam" dba o to, żeby kasować "stare" dane. Bardzo higienicznie.

A jak to zrobił Microsoft?

Trochę powiązał zagadnienie sznurkami i pozbijał starymi, zardzewiałymi gwoździami. Czyszczenie danych historycznych z tabel temporalnych odbywa się w sposób następujący:

1. Wyłączamy temporalność dla danej tabeli:

ALTER TABLE dbo.Ksiazki SET (SYSTEM_VERSIONING = OFF);

2. Kasujemy dane historyczne (tyle, ile potrzebujemy):

DELETE FROM dbo.Ksiazki WHERE WAZNE_DO <= '2015-07-05';

3. Włączamy z powrotem temporalność:

ALTER TABLE dbo.Ksiazki SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE  = dbo.KsiazkiHist, DATA_CONSISTENCY_CHECK = OFF));

Jak widać w punkcie 3. musimy przy ponownym włączaniu temporalności wyłączyć sprawdzanie spójności danych w tabeli historycznej.

Trochę to pachnie średniowieczem, ale skoro działa, nie będę narzekał...

https://xpil.eu/ekr 📋