Začínáme s dočasnými tabulkami ve službě Azure SQL Database a Azure SQL Managed Instance

Platí pro:Azure SQL DatabaseAzure SQL Managed Instance

Dočasné tabulky jsou programovatelností služby Azure SQL Database a Azure SQL Managed Instance, která umožňuje sledovat a analyzovat úplnou historii změn v datech bez nutnosti vlastního kódování. Dočasné tabulky uchovávají data úzce související s časovým kontextem, aby uložená fakta byla interpretována jako platná pouze v určitém období. Tato vlastnost dočasných tabulek umožňuje efektivní analýzu na základě času a získávání přehledů z vývoje dat.

Dočasný scénář

Tento článek ukazuje postup využití dočasných tabulek ve scénáři aplikace. Předpokládejme, že chcete sledovat aktivity uživatelů na novém webu, který se vyvíjí úplně od začátku, nebo na existujícím webu, který chcete rozšířit o analýzu aktivit uživatelů. V tomto zjednodušeném příkladu předpokládáme, že počet navštívených webových stránek během časového období je indikátorem, který je potřeba zachytit a monitorovat v databázi webu hostované ve službě Azure SQL Database nebo ve spravované instanci Azure SQL. Cílem historické analýzy aktivity uživatelů je získat vstupy pro změnu návrhu webu a poskytovat návštěvníkům lepší prostředí.

Model databáze pro tento scénář je velmi jednoduchý – metrika aktivity uživatele je reprezentována jedním celočíselnou hodnotou, PageVisited a je zachycena spolu se základními informacemi o profilu uživatele. Kromě toho byste pro analýzu na základě času zachovali řadu řádků pro každého uživatele, kde každý řádek představuje počet stránek navštívených konkrétním uživatelem v určitém časovém období.

Naštěstí nemusíte do aplikace zadávat žádné úsilí, abyste tyto informace o aktivitách zachovali. U dočasných tabulek je tento proces automatizovaný – poskytuje vám plnou flexibilitu při návrhu webu a více času, abyste se mohli zaměřit na samotnou analýzu dat. Jedinou věcí, kterou musíte udělat, je zajistit, aby WebSiteInfo byla tabulka nakonfigurovaná jako dočasná systémová verze. Přesný postup využití dočasných tabulek v tomto scénáři je popsaný níže.

Krok 1: Konfigurace tabulek jako dočasných

V závislosti na tom, jestli spouštíte nový vývoj nebo upgradujete existující aplikaci, vytvoříte dočasné tabulky nebo upravíte stávající tabulky přidáním dočasných atributů. Obecně platí, že váš scénář může být kombinací těchto dvou možností. K provedení těchto akcí použijte SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio nebo jakýkoli jiný vývojový nástroj Transact-SQL.

Důležité

Doporučujeme vždy používat nejnovější verzi sady Management Studio, abyste zůstali synchronizovaní s aktualizacemi služby Azure SQL Database a azure SQL Managed Instance. Aktualizovat aplikaci SQL Server Management Studio.

Vytvoření nové tabulky

Pomocí položky místní nabídky New System-Versioned Table v aplikaci SSMS Průzkumník objektů otevřete editor dotazů pomocí skriptu šablony dočasné tabulky a pak pomocí příkazu Zadat hodnoty pro parametry šablony (Ctrl+Shift+M) šablonu naplňte:

V SSDT při přidávání nových položek do databázového projektu zvolte šablonu Dočasná tabulka (systémová verze). Tím otevřete návrháře tabulek a umožníte snadno zadat rozložení tabulky:

Dočasnou tabulku můžete vytvořit také tak, že přímo zadáte příkazy Jazyka Transact-SQL, jak je znázorněno v následujícím příkladu. Všimněte si, že povinné prvky každé dočasné tabulky jsou definice PERIOD a klauzule SYSTEM_VERSIONING s odkazem na jinou uživatelskou tabulku, která bude ukládat historické verze řádků:

CREATE TABLE WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

Při vytváření dočasné tabulky s systémovou verzí se automaticky vytvoří doprovodná tabulka historie s výchozí konfigurací. Výchozí tabulka historie obsahuje skupinový index stromu B na sloupcích období (konec, začátek) s povolenou kompresí stránky. Tato konfigurace je optimální pro většinu scénářů, ve kterých se používají dočasné tabulky, zejména pro auditování dat.

V tomto konkrétním případě se snažíme provádět analýzu trendu na základě času v delší historii dat a s většími sadami dat, takže volba úložiště pro tabulku historie je clusterovaný index columnstore. Clusterovaný columnstore poskytuje velmi dobrou kompresi a výkon analytických dotazů. Dočasné tabulky poskytují flexibilitu při konfiguraci indexů v aktuálních a dočasných tabulkách zcela nezávisle.

Poznámka:

Indexy columnstore jsou k dispozici v úrovních Pro důležité obchodní informace, Pro obecné účely a Premium a ve vrstvě Standard, S3 a vyšší.

Následující skript ukazuje, jak se dá výchozí index tabulky historie změnit na clusterovaný columnstore:

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Dočasné tabulky jsou reprezentovány v Průzkumník objektů s konkrétní ikonou pro snadnější identifikaci, zatímco její tabulka historie se zobrazuje jako podřízený uzel.

Změna existující tabulky na dočasnou

Podívejme se na alternativní scénář, ve kterém tabulka WebsiteUserInfo již existuje, ale nebyla navržena tak, aby uchovávala historii změn. V tomto případě můžete stávající tabulku jednoduše rozšířit tak, aby se stala dočasnou, jak je znázorněno v následujícím příkladu:

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Krok 2: Pravidelné spouštění úloh

Hlavní výhodou dočasných tabulek je, že ke sledování změn nemusíte měnit ani upravovat web. Po vytvoření dočasné tabulky transparentně uchovávají předchozí verze řádků pokaždé, když provedete změny dat.

Abychom mohli využít automatické sledování změn pro tento konkrétní scénář, pojďme aktualizovat sloupec PagesVisited pokaždé, když uživatel ukončí svoji relaci na webu:

UPDATE WebsiteUserInfo  SET [PagesVisited] = 5
WHERE [UserID] = 1;

Je důležité si všimnout, že aktualizační dotaz nemusí znát přesný čas, kdy došlo k skutečné operaci, ani způsob zachování historických dat pro budoucí analýzu. Obě aspekty se automaticky zpracovávají službou Azure SQL Database a službou Azure SQL Managed Instance. Následující diagram znázorňuje, jak se při každé aktualizaci generují data historie.

Krok 3: Provedení historické analýzy dat

Když je teď povolená dočasná správa verzí systému, analýza historických dat je od vás jen jeden dotaz. V tomto článku poskytneme několik příkladů, které řeší běžné scénáře analýzy – abyste se dozvěděli všechny podrobnosti, prozkoumali různé možnosti představené v klauzuli FOR SYSTEM_TIME .

Pokud chcete zobrazit prvních 10 uživatelů seřazených podle počtu navštívených webových stránek před hodinou, spusťte tento dotaz:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

Tento dotaz můžete snadno upravit tak, aby analyzoval návštěvy webu před dnem, před měsícem nebo v libovolném okamžiku v minulosti.

Pokud chcete provést základní statistickou analýzu za předchozí den, použijte následující příklad:

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());

SELECT UserID, SUM (PagesVisited) as TotalVisitedPages, AVG (PagesVisited) as AverageVisitedPages,
MAX (PagesVisited) AS MaxVisitedPages, MIN (PagesVisited) AS MinVisitedPages,
STDEV (PagesVisited) as StDevViistedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

Pokud chcete vyhledat aktivity konkrétního uživatele, použijte během určitého časového období klauzuli CONTAINED IN:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
DECLARE @twoHoursAgo datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
SELECT * FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME CONTAINED IN (@twoHoursAgo, @hourAgo)
WHERE [UserID] = 1;

Grafická vizualizace je obzvláště vhodná pro dočasné dotazy, protože můžete zobrazit trendy a vzory použití intuitivním způsobem velmi snadno:

Vývoj schématu tabulky

Při vývoji aplikací budete obvykle muset změnit schéma dočasné tabulky. Za tímto účelem jednoduše spusťte běžné příkazy ALTER TABLE a Azure SQL Database nebo Azure SQL Managed Instance odpovídajícím způsobem rozšíří změny do tabulky historie. Následující skript ukazuje, jak přidat další atribut pro sledování:

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

Podobně můžete změnit definici sloupce, když je vaše úloha aktivní:

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

Nakonec můžete odebrat sloupec, který už nepotřebujete.

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

Případně můžete použít nejnovější SSDT ke změně schématu dočasné tabulky, když jste připojení k databázi (online režim) nebo jako součást databázového projektu (offline režim).

Řízení uchovávání historických dat

U dočasných tabulek se systémovou verzí může tabulka historie zvětšit velikost databáze více než běžné tabulky. Velká a stále rostoucí tabulka historie se může stát problémem z důvodu čistých nákladů na úložiště i uložení daně z výkonu pro dočasné dotazování. Proto je vývoj zásad uchovávání dat pro správu dat v tabulce historie důležitým aspektem plánování a správy životního cyklu každé dočasné tabulky. Se službou Azure SQL Database a službou Azure SQL Managed Instance máte následující přístupy ke správě historických dat v dočasné tabulce:

• Dělení tabulky
• Vlastní skript pro vyčištění

Další kroky

• Další informace o dočasných tabulkách najdete v části Dočasné tabulky.