A temporális táblák használatának első lépései az Azure SQL Database-ben és a felügyelt Azure SQL-példányban

A következőre vonatkozik: Azure SQL DatabaseFelügyelt Azure SQL-példány

A temporális táblák az Azure SQL Database és az Azure SQL Managed Instance programozhatósági funkciója, amely lehetővé teszi az adatok változásainak teljes előzményeinek nyomon követését és elemzését egyéni kódolás nélkül. Az időbeli táblák szorosan kapcsolódnak az időkörnyezethez, így a tárolt tények csak az adott időszakon belül értelmezhetők érvényesként. Az időbeli táblák ezen tulajdonsága lehetővé teszi a hatékony időalapú elemzést és az adatfejlődésből származó megállapítások lekérését.

Időbeli forgatókönyv

Ez a cikk az időbeli táblák alkalmazásforgatókönyvekben való felhasználásának lépéseit mutatja be. Tegyük fel, hogy egy teljesen új webhelyen vagy egy meglévő webhelyen szeretné nyomon követni a felhasználói tevékenységet, amelyet ki szeretne terjeszteni a felhasználói tevékenységelemzéssel. Ebben az egyszerűsített példában feltételezzük, hogy a meglátogatott weblapok száma egy adott időszakban egy olyan mutató, amelyet rögzíteni és figyelni kell az Azure SQL Database-ben vagy felügyelt Azure SQL-példányon üzemeltetett webhelyadatbázisban. A felhasználói tevékenység történeti elemzésének célja, hogy bemeneteket kapjon a webhely újratervezéséhez, és jobb élményt nyújtson a látogatók számára.

A forgatókönyv adatbázismodellje nagyon egyszerű : a felhasználói tevékenység metrikája egyetlen egész számmezővel ( PageVisited) van ábrázolva, és a felhasználói profil alapvető adataival együtt van rögzítve. Emellett az időalapú elemzéshez minden felhasználóhoz sorokat kell tartania, ahol minden sor az adott felhasználó által egy adott időtartamon belül meglátogatott oldalak számát jelöli.

Szerencsére nem kell semmilyen erőfeszítést tennie az alkalmazásban a tevékenységinformációk fenntartása érdekében. A temporális táblákkal ez a folyamat automatizált , így teljes rugalmasságot biztosít a webhely tervezése során, és több időt biztosít arra, hogy magára az adatelemzésre összpontosítson. Az egyetlen teendője annak biztosítása, hogy WebSiteInfo a tábla temporális rendszerverzióként legyen konfigurálva. Az időbeli táblák ebben a forgatókönyvben való felhasználásának pontos lépéseit az alábbiakban ismertetjük.

1. lépés: Táblák konfigurálása időlegesként

Attól függően, hogy új fejlesztést kezd vagy meglévő alkalmazást frissít, ideiglenes táblákat hozhat létre, vagy ideiglenes attribútumok hozzáadásával módosíthatja a meglévőket. Általánosságban elmondható, hogy a forgatókönyv a két lehetőség kombinációját jelentheti. Hajtsa végre ezeket a műveletet az SQL Server Management Studio (SSMS), az SQL Server Data Tools (SSDT), az Azure Data Studio vagy bármely más Transact-SQL fejlesztőeszköz használatával.

Fontos

Javasoljuk, hogy mindig a Management Studio legújabb verzióját használja, hogy szinkronizálva maradjon az Azure SQL Database és az Azure SQL Managed Instance frissítéseivel. Az SQL Server Management Studio frissítése.

Új tábla létrehozása

Az SSMS Object Explorerben az "Új rendszerverziós tábla" helyi menüelem használatával nyissa meg a lekérdezésszerkesztőt egy temporális táblasablonszkripttel, majd a sablon feltöltéséhez használja az "Értékek megadása sablonparaméterekhez" (Ctrl+Shift+M) billentyűkombinációt:

Az SSDT-ben válassza a "Temporal Table (System-Versioned)" sablont, amikor új elemeket ad hozzá az adatbázisprojekthez. Ez megnyitja a táblatervezőt, és lehetővé teszi a táblázat elrendezésének egyszerű megadását:

A Transact-SQL-utasítások közvetlen megadásával is létrehozhat temporális táblát, ahogyan az az alábbi példában látható. Vegye figyelembe, hogy minden temporális tábla kötelező elemei a PERIOD definíció és a SYSTEM_VERSIONING záradék, amely egy másik olyan felhasználói táblára hivatkozik, amely előzménysor-verziókat fog tárolni:

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

Rendszerverziójú temporális tábla létrehozásakor a rendszer automatikusan létrehozza a kísérő előzménytáblát az alapértelmezett konfigurációval. Az alapértelmezett előzménytábla egy fürtözött B-fa indexet tartalmaz az időszakoszlopokon (befejezés, kezdés) az oldaltömörítés engedélyezésével. Ez a konfiguráció optimális azoknak a forgatókönyveknek a többségéhez, amelyekben időbeli táblákat használnak, különösen az adatnaplózáshoz.

Ebben a konkrét esetben egy hosszabb adatelőzmények és nagyobb adatkészletek esetében szeretnénk időalapú trendelemzést végezni, így az előzménytáblához választott tárolási lehetőség egy csoportosított oszlopcentrikus index. A csoportosított oszloptárak nagyon jó tömörítést és teljesítményt biztosítanak az elemzési lekérdezésekhez. A temporális táblák rugalmasan konfigurálják az aktuális és az időbeli táblák indexeit teljesen függetlenül.

Megjegyzés:

Az oszlopcentrikus indexek a üzletileg kritikus, az Általános célú és a Prémium szinten, valamint a Standard, S3 és újabb szinteken érhetők el.

Az alábbi szkript bemutatja, hogyan módosítható az előzmények táblázatának alapértelmezett indexe a fürtözött oszloptárra:

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

A temporális táblák az Objektumkezelőben jelennek meg az adott ikonnal a könnyebb azonosítás érdekében, míg az előzménytáblája gyermekcsomópontként jelenik meg.

Meglévő tábla módosítása időbelire

Tekintsük át azt az alternatív forgatókönyvet, amelyben a WebsiteUserInfo tábla már létezik, de nem úgy lett kialakítva, hogy megőrizze a változások előzményeit. Ebben az esetben egyszerűen kiterjesztheti a meglévő táblát időlegessé, ahogyan az alábbi példában látható:

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

2. lépés: A számítási feladat rendszeres futtatása

A temporális táblák fő előnye, hogy nem kell módosítania vagy módosítania a webhelyét semmilyen módon a változáskövetés végrehajtásához. A létrehozás után az időbeli táblák transzparensen megőrzik a korábbi sorverziókat minden alkalommal, amikor módosításokat végez az adatokon.

Ahhoz, hogy az adott forgatókönyv automatikus változáskövetését használhassa, frissítsük a PagesVisited oszlopot minden alkalommal, amikor egy felhasználó befejezi a munkamenetet a webhelyen:

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

Fontos megfigyelni, hogy a frissítési lekérdezésnek nem kell tudnia, hogy pontosan mikor történt a tényleges művelet, és hogy a rendszer hogyan őrizze meg az előzményadatokat a jövőbeli elemzéshez. Mindkét szempontot automatikusan kezeli az Azure SQL Database és az Azure SQL Managed Instance. Az alábbi ábra bemutatja, hogyan jönnek létre az előzményadatok minden frissítés során.

3. lépés: Előzményadatok elemzése

Most, hogy engedélyezve van az időbeli rendszer verziószámozása, az előzményadatok elemzése csak egy lekérdezést jelent öntől. Ebben a cikkben bemutatunk néhány példát, amelyek a gyakori elemzési forgatókönyveket ismertetik – az összes részlet megismeréséhez, a FOR SYSTEM_TIME záradékkal bevezetett különféle lehetőségek megismeréséhez.

Az egy órával ezelőtti látogatott weblapok száma alapján rendezett 10 felhasználó megtekintéséhez futtassa ezt a lekérdezést:

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

Ezt a lekérdezést egyszerűen módosíthatja úgy, hogy elemezze a webhelylátogatásokat egy nappal ezelőtt, egy hónappal ezelőtt vagy a múlt bármely pontján.

Az előző napi alapszintű statisztikai elemzéshez használja a következő példát:

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

Egy adott felhasználó tevékenységeinek kereséséhez egy adott időszakon belül használja a CONTAINED IN záradékot:

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;

A grafikus vizualizáció különösen kényelmes az időbeli lekérdezésekhez, mivel intuitív módon jelenítheti meg a trendeket és a használati mintákat:

Változó táblaséma

Az alkalmazásfejlesztés során általában módosítania kell a temporális táblázat sémáját. Ehhez egyszerűen futtassa a normál ALTER TABLE-utasításokat, és az Azure SQL Database vagy az Azure SQL Managed Instance megfelelően propagálja a módosításokat az előzménytáblára. Az alábbi szkript bemutatja, hogyan adhat hozzá további attribútumokat a nyomon követéshez:

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

Hasonlóképpen módosíthatja az oszlopdefiníciót, amíg a számítási feladat aktív:

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

Végül eltávolíthat egy olyan oszlopot, amelyekre már nincs szüksége.

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

Másik lehetőségként a legújabb SSDT használatával módosíthatja a temporális táblázat sémáját, miközben csatlakozik az adatbázishoz (online mód) vagy az adatbázisprojekt részeként (offline mód).

Előzményadatok megőrzésének szabályozása

A rendszer által verziózott temporális táblák esetén az előzménytábla nagyobb mértékben növelheti az adatbázis méretét, mint a hagyományos táblák. A nagy méretű és egyre növekvő előzménytáblák a tiszta tárolási költségek, valamint az időbeli lekérdezések teljesítményadója miatt is problémát jelenthetnek. Ezért az előzménytáblában lévő adatok kezelésére szolgáló adatmegőrzési szabályzat kialakítása fontos szempont minden időbeli tábla életciklusának tervezésében és kezelésében. Az Azure SQL Database és a felügyelt Azure SQL-példány esetében a következő módszerekkel kezelheti az előzményadatokat a temporális táblában:

• Táblaparticionálás
• Egyéni törlési szkript

Következő lépések

• Az időtáblákról további információt a Temporális táblák című témakörben talál.