Miroslav Holec
Premium

Temporal Tables aneb sledujeme historii změn v Azure SQL

Miroslav Holec   31. října 2016

Tento článek byl napsán v roce 2016. Vývojářské technologie se neustále inovují a článek již nemusí popisovat aktuální stav technologie, ideální řešení a můj současný pohled na dané téma.

Azure SQL nově podporuje funkci Temporal Tables. Díky této funkci lze automaticky uchovávat historii změn v databázových tabulkách. Snadno se tak lze dívat na data ve vybraných tabulkách vzhledem k zvolenému časovému okamžiku. Implementace je přitom snadná, rychlá a nevyžaduje žádnou změnu na straně aplikačního kódu.

Kromě možnosti sledovat konkrétní množinu dat v určitém čase lze analyzovat i trendy, různé anomálie nebo historická data použít k nápravě v případě narušení integrity dat. Z logiky věci je samozřejmě nutné počítat s nárůstem velikosti databáze. Ta se odvíjí především od tzv. history retention period (doba, po kterou se historie uchovává).

Nastavení Temporal Tables

Nastavení Temporal Tables je relativně snadné. Stačí rozšířit vybranou tabulku o časové razítko a následně k ní aktivovat verzování do nové databázové tabulky s určitou dobou uchování dat. Nově vzniklá tabulka bude nejprve prázdná, ale s každou změnou v originální tabulce do ní budou přibývat nové záznamy.

Příklad

Chci uchovat data o změnách v článcích v tabulce Articles a to po dobu 1 měsíce. Tabulka vypadá takto:

Articles
--------
ArticleId
Title
Description

Nejprve rozšířím tabulku o časové razítko (od - do).

ALTER TABLE dbo.Articles
ADD 
	PeriodStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
	PeriodEndTime   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT
     CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (PeriodStartTime, PeriodEndTime)

Takto vytvořené sloupečky nejsou standardně viditelné a pokud je chci zahrnout do SELECT dotazů, musím je explicitně uvést:

SELECT *, PeriodStartTime, PeriodEndTime FROM Articles

A následně aktivuji verzování

ALTER TABLE dbo.Articles
SET 
(
	SYSTEM_VERSIONING = ON 
	(
		HISTORY_TABLE = dbo.Articles_History,
		HISTORY_RETENTION_PERIOD = 1 MONTH
	)
);

Nyní se už mohu podívat na data v libovolném čase. Na příkladu níže jsou vidět změny za poslední 2 hodiny. Zároveň jej lze snadno opravit na libovolný interval.

DECLARE @from datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
DECLARE @to datetime2 = DATEADD(HOUR, 0, SYSUTCDATETIME());
SELECT * FROM dbo.Articles
FOR SYSTEM_TIME CONTAINED IN (@from, @to)

Pro sestavování dotazů z historie je klíčová klauzule FOR SYSTEM_TIME, které se předává časový interval. Všechny možnosti vypisuji přímo z MSDN dokumentace:

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time> , <end_date_time>)
  • ALL

Omezení

Kromě zmíněných výhod je třeba upozornit i na některá omezení, která s používáním Temporal Tables vznikají. Jedním z nich je například nemožnost kaskádových operací (ON DELETE CASCADE, ON UPDATE CASCADE) nebo omezené možnosti replikace. Pro úplný přehled omezení doporučuji přečíst MSDN článek