A temporális táblák használatának első lépései

A következőkre vonatkozik:Azure SQL DatabaseAzure SQL Managed InstanceSQL-adatbázis a Fabricben

Az időbeli táblák olyan programozhatósági funkciók, amelyek lehetővé teszik az adatok változásainak teljes előzményeinek nyomon követését és elemzését anélkül, hogy egyéni kódolásra van szükség. 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 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 lesz 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.

A WebSiteUserinfo mintatábla táblázatsémájának diagramja.

Szerencsére nem kell semmilyen erőfeszítést tennie az alkalmazásban, hogy megőrizze ezeket a tevékenységadatokat. 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 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), a Visual Studio Codemssql-bővítménye vagy bármely más Transact-SQL fejlesztőeszköz használatával.

Important

Mindig az SQL Server Management Studio legújabb verzióját használja.

Új tábla létrehozása

  • Az SSMS Object Explorer Új System-Versioned tábla helyi menüelemével nyissa meg a lekérdezésszerkesztőt egy ideiglenes táblasablonszkripttel, majd a sablon kitöltéséhez használja az Értékek megadása sablonparaméterekhez (Ctrl+Shift+M) parancsot:

    Képernyőkép az SSMS új rendszer verziózott tábla opciójáról.

  • Az SSDT-ben válassza a "Temporális tábla (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:

    Képernyőkép az Új elem hozzáadása párbeszéd SSMS-éről, valamint a temporális táblázatról, System-Versioned lehetőség van kiválasztva.

  • A temporális táblát úgy is létrehozhatja, hogy közvetlenül megadja a Transact-SQL utasításokat, ahogyan az alábbi példában látható. Minden temporális tábla kötelező elemei a PERIOD definíció és a SYSTEM_VERSIONING záradék egy másik felhasználói táblára való hivatkozással, 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));

Alapértelmezett előzménytábla

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 a legtöbb olyan forgatókönyv esetében, amelyben 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 fürtözött oszloptárak 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.

Note

Az oszlopalapú indexek az üzletileg kritikus, az általános célú és a prémium szinteken, valamint a standard S3-as és magasabb 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.

Képernyőkép az SQL Server Management Studióból, amelyen az Object Explorer és az előzménytábla látható.

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

Vegyük sorra 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 beállítania a webhelyét semmilyen módon a változáskövetés elvégzéséhez. 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 automatikus változáskövetést használhassunk ehhez az esethez, frissítsük az oszlopot PagesVisited 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 az előzményadatok hogyan lesznek megőrizve a jövőbeli elemzéshez. A rendszer mindkét szempontot automatikusan kezeli. Az alábbi ábra bemutatja, hogyan jönnek létre az előzményadatok minden frissítés során.

A temporális táblaarchitektúra diagramja.

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

Amikor az időbeli rendszer-verziózás engedélyezve van, az előzményadatok elemzése csak egy lekérdezéssel elérhető az ön számára. Ebben a cikkben bemutatunk néhány példát a gyakori elemzési forgatókönyvekre – 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 StDevVisitedPages
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:

A meglátogatott lapok időbeli táblázatelőzmény-adatain alapuló grafikonja.

Táblázatséma fejlesztése

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

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ák sémáját az adatbázisban (online mód) vagy az adatbázisprojekt keretében (offline mód).

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

A rendszer által verziózott temporális táblák esetében az előzménytábla növelheti az adatbázis méretét a normál tábláknál. 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. Vegye figyelembe az előzményadatok időbeli táblában való kezelésére vonatkozó alábbi módszereket:

Remarks

A Fabric- és Fabric SQL-adatbázisbavaló tükrözéshez konfigurált Azure SQL Database-ben is létrehozhat időbeli táblákat, de a megfelelő előzménytáblák nem lesznek tükrözve a Fabric OneLake-hez. A jelölő időbeli táblákon való beállításával SYSTEM_VERSIONING kapcsolatos konkrét viselkedésért lásd: Rendszerverziójú temporális tábla létrehozása.

Kapcsolódó tartalom

  • Last updated on