Dela via

Komma igång med temporala tabeller

gäller för:Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Fabric

Temporala tabeller är en programmeringsfunktion som gör att du kan spåra och analysera den fullständiga 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 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.

Diagram över ett tabellschema för exempeltabellen WebSiteUserinfo.

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), mssql-tillägget för Visual Studio Code eller andra Transact-SQL utvecklingsverktyg.

Viktigt!

Vi rekommenderar att du alltid använder den senaste versionen av SQL Server 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 System-Versioned tabell i SSMS Object Explorer för att öppna frågeredigeraren med ett temporal tabellmallskript och sedan använda Ange värden för mallparametrar (Ctrl+Skift+M) för att fylla i mallen:

    Skärmbild från SSMS för alternativet Ny systemversionstabell.

  • 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:

    Skärmbild från SSMS av dialogrutan Lägg till nytt objekt och alternativet Tidstabell System-Versioned valt.

  • Du kan också skapa en temporal tabell genom att ange Transact-SQL-instruktioner direkt, som du ser i följande exempel. De obligatoriska elementen PERIOD i varje temporal tabell är definitionen 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));

Standardhistoriktabell

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 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.

Anmärkning

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.

Skärmbild från SQL Server Management Studio som visar Objektutforskaren och historiktabellen.

Ändra befintlig tabell till temporal

Vi går igenom det alternativa scenariot där WebsiteUserInfo tabellen 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 uppdatera kolumnen PagesVisited 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.

Diagram över arkitekturen för tidstabellen.

Steg 3: Utföra historisk dataanalys

Nu när temporal systemversionering är aktiverat är historisk dataanalys bara en sökning bort. 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 introduceras med FOR SYSTEM_TIME-satsen .

För att se de 10 användare ordnade efter antalet besökta webbsidor från och med för en timme sedan, kör den här frågan:

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 StDevVisitedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

För att söka efter aktiviteter för en viss användare inom en viss tidsperiod, använd klausulen "INNEHÅLLER I".

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:

Diagram över besökta sidor över tid, baserat på tabelldata för tidstabellhistorik.

Utveckla tabellschema

Normalt behöver du ändra schemat för den tidsmässiga tabellen när du utvecklar appar. Därför kör du helt enkelt 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 genom att påverka prestanda vid tidsfrågehantering. 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:

Anmärkningar

I både Azure SQL Database som konfigurerats för spegling till Fabric - och Fabric SQL-databasen kan du skapa temporala tabeller, men respektive historiktabeller speglas inte i Fabric OneLake. Mer information om hur du ställer in SYSTEM_VERSIONING flaggan för temporala tabeller finns i Skapa en systemversionsbaserad temporal tabell.

Relaterat innehåll