Komma igång med temporala tabeller i Azure SQL Database och Azure SQL Managed Instance

Gäller för:Azure SQL DatabaseAzure SQL Managed Instance

Temporala tabeller är en programmeringsfunktion i Azure SQL Database och Azure SQL Managed Instance som gör att du kan spåra och analysera hela historiken för ändringar i dina data, utan att behöva anpassad kodning. Tidstabeller håller data nära relaterade till tidskontext så att lagrade fakta kan tolkas som giltiga endast inom den specifika perioden. Den här egenskapen för temporala tabeller möjliggör effektiv tidsbaserad analys och få insikter från datautvecklingen.

Tidsmässigt scenario

Den här artikeln illustrerar stegen för att använda temporala tabeller i ett programscenario. Anta att du vill spåra användaraktivitet på en ny webbplats som utvecklas från grunden eller på en befintlig webbplats som du vill utöka med användaraktivitetsanalys. I det här förenklade exemplet förutsätter vi att antalet besökta webbsidor under en tidsperiod är en indikator som måste registreras och övervakas i webbplatsdatabasen som finns i Azure SQL Database eller Azure SQL Managed Instance. Målet med den historiska analysen av användaraktivitet är att få indata för att omforma webbplatsen och ge bättre upplevelse för besökarna.

Databasmodellen för det här scenariot är mycket enkel – användaraktivitetsmåttet representeras med ett enda heltalsfält, PageVisited, och samlas in tillsammans med grundläggande information om användarprofilen. För tidsbaserad analys skulle du dessutom behålla en serie rader för varje användare, där varje rad representerar antalet sidor som en viss användare besökte inom en viss tidsperiod.

Som tur är behöver du inte göra något i din app för att underhålla den här aktivitetsinformationen. Med tidsmässiga tabeller automatiseras den här processen – vilket ger dig fullständig flexibilitet under webbplatsdesignen och mer tid att fokusera på själva dataanalysen. Det enda du behöver göra är att se till att WebSiteInfo tabellen är konfigurerad som temporal systemversion. De exakta stegen för att använda temporala tabeller i det här scenariot beskrivs nedan.

Steg 1: Konfigurera tabeller som temporala

Beroende på om du startar ny utveckling eller uppgraderar ett befintligt program skapar du antingen temporala tabeller eller ändrar befintliga genom att lägga till temporala attribut. I allmänhet kan ditt scenario vara en blandning av dessa två alternativ. Utför den här åtgärden med hjälp av SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio eller något annat Transact-SQL-utvecklingsverktyg.

Viktigt!

Vi rekommenderar att du alltid använder den senaste versionen av Management Studio för att förbli synkroniserad med uppdateringar av Azure SQL Database och Azure SQL Managed Instance. Uppdatera SQL Server Management Studio.

Skapa ny tabell

Använd snabbmenyobjektet "Ny systemversionstabell" i SSMS Object Explorer för att öppna frågeredigeraren med ett temporal tabellmallskript och använd sedan "Ange värden för mallparametrar" (Ctrl+Skift+M) för att fylla i mallen:

I SSDT väljer du mallen "Temporal Table (System-Versioned)" när du lägger till nya objekt i databasprojektet. Då öppnas tabelldesignern och du kan enkelt ange tabelllayouten:

Du kan också skapa en temporal tabell genom att ange Transact-SQL-uttrycken direkt, som du ser i exemplet nedan. Observera att de obligatoriska elementen i varje temporal tabell är perioddefinitionen och SYSTEM_VERSIONING-satsen med en referens till en annan användartabell som lagrar historiska radversioner:

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));

När du skapar en systemversionstabell skapas automatiskt den tillhörande historiktabellen med standardkonfigurationen. Standardhistoriktabellen innehåller ett grupperat B-trädindex för periodkolumnerna (slut, start) med sidkomprimering aktiverat. Den här konfigurationen är optimal för de flesta scenarier där temporala tabeller används, särskilt för datagranskning.

I det här fallet strävar vi efter att utföra tidsbaserad trendanalys över en längre datahistorik och med större datamängder, så lagringsvalet för historiktabellen är ett grupperat kolumnlagringsindex. Ett grupperat kolumnarkiv ger mycket bra komprimering och prestanda för analysfrågor. Temporala tabeller ger dig flexibiliteten att konfigurera index på de aktuella och temporala tabellerna helt oberoende av varandra.

Kommentar

Kolumnlagringsindex är tillgängliga på nivåerna Affärskritisk, Generell användning och Premium och på standardnivån, S3 och senare.

Följande skript visar hur standardindex i historiktabellen kan ändras till det klustrade kolumnarkivet:

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

Temporala tabeller representeras i Object Explorer med den specifika ikonen för enklare identifiering, medan dess historiktabell visas som en underordnad nod.

Ändra befintlig tabell till temporal

Nu ska vi gå igenom det alternativa scenariot där tabellen WebsiteUserInfo redan finns, men inte har utformats för att behålla en historik över ändringar. I det här fallet kan du bara utöka den befintliga tabellen så att den blir tidsmässig, vilket visas i följande exempel:

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);

Steg 2: Kör arbetsbelastningen regelbundet

Den största fördelen med temporala tabeller är att du inte behöver ändra eller justera din webbplats på något sätt för att utföra ändringsspårning. När de har skapats bevarar temporala tabeller transparent tidigare radversioner varje gång du gör ändringar på dina data.

För att kunna använda automatisk ändringsspårning för det här scenariot ska vi bara uppdatera kolumnsidorBesökt varje gång en användare avslutar sin session på webbplatsen:

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

Det är viktigt att observera att uppdateringsfrågan inte behöver veta exakt när den faktiska åtgärden inträffade eller hur historiska data kommer att bevaras för framtida analys. Båda aspekterna hanteras automatiskt av Azure SQL Database och Azure SQL Managed Instance. Följande diagram visar hur historikdata genereras vid varje uppdatering.

Steg 3: Utföra historisk dataanalys

Nu när temporal systemversionering är aktiverat är historisk dataanalys bara en fråga bort från dig. I den här artikeln ger vi några exempel som tar upp vanliga analysscenarier – för att lära dig all information kan du utforska olika alternativ som introducerats med FOR SYSTEM_TIME-satsen .

Kör den här frågan om du vill se de 10 användare som har beställts efter antalet besökta webbsidor för en timme sedan:

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

Du kan enkelt ändra den här frågan för att analysera webbplatsbesöken för en dag sedan, för en månad sedan eller när som helst tidigare.

Använd följande exempel för att utföra grundläggande statistisk analys för föregående dag:

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

Om du vill söka efter aktiviteter för en viss användare använder du IN-satsen CONTAINED IN inom en viss tidsperiod:

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;

Grafisk visualisering är särskilt praktiskt för temporala frågor eftersom du kan visa trender och användningsmönster på ett intuitivt sätt mycket enkelt:

Utveckla tabellschema

Normalt behöver du ändra schemat för tidstabellen medan du utvecklar appar. Därför kan du helt enkelt köra vanliga ALTER TABLE-instruktioner och Azure SQL Database eller Azure SQL Managed Instance sprider ändringarna till historiktabellen på rätt sätt. Följande skript visar hur du kan lägga till ytterligare attribut för spårning:

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

På samma sätt kan du ändra kolumndefinitionen medan din arbetsbelastning är aktiv:

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

Slutligen kan du ta bort en kolumn som du inte behöver längre.

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

Du kan också använda senaste SSDT för att ändra tidstabellschemat när du är ansluten till databasen (onlineläge) eller som en del av databasprojektet (offlineläge).

Kontrollera kvarhållning av historiska data

Med systemversionsbaserade temporala tabeller kan historiktabellen öka databasstorleken mer än vanliga tabeller. En stor och ständigt växande historiktabell kan bli ett problem både på grund av rena lagringskostnader och att en prestandaskatt införs för tidsfråga. Att utveckla en datakvarhållningsprincip för att hantera data i historiktabellen är därför en viktig aspekt av planering och hantering av livscykeln för varje tidstabell. Med Azure SQL Database och Azure SQL Managed Instance har du följande metoder för att hantera historiska data i den temporala tabellen:

• Tabellpartitionering
• Anpassat rensningsskript

Nästa steg

• Mer information om temporala tabeller finns i avsnittet om temporala tabeller.