Aan de slag met tijdelijke tabellen in Azure SQL Database en Azure SQL Managed Instance
Van toepassing op:
Azure SQL DatabaseAzure SQL Managed Instance
Tijdelijke tabellen zijn een programmeerfunctie van Azure SQL Database en Azure SQL Managed Instance waarmee u de volledige geschiedenis van wijzigingen in uw gegevens kunt bijhouden en analyseren, zonder dat u aangepaste codering nodig hebt. Tijdelijke tabellen houden gegevens nauw verwant aan tijdcontext, zodat opgeslagen feiten alleen binnen de specifieke periode als geldig kunnen worden geïnterpreteerd. Deze eigenschap van tijdelijke tabellen biedt efficiënte op tijd gebaseerde analyse en het verkrijgen van inzichten uit gegevensontwikkeling.
Tijdelijk scenario
Dit artikel illustreert de stappen voor het gebruik van tijdelijke tabellen in een toepassingsscenario. Stel dat u gebruikersactiviteiten wilt bijhouden op een nieuwe website die volledig nieuw wordt ontwikkeld of op een bestaande website die u wilt uitbreiden met analyse van gebruikersactiviteiten. In dit vereenvoudigde voorbeeld wordt ervan uitgegaan dat het aantal bezochte webpagina's gedurende een bepaalde periode een indicator is die moet worden vastgelegd en bewaakt in de websitedatabase die wordt gehost in Azure SQL Database of Azure SQL Managed Instance. Het doel van de historische analyse van gebruikersactiviteit is om invoer te krijgen om de website opnieuw te ontwerpen en een betere ervaring voor de bezoekers te bieden.
Het databasemodel voor dit scenario is zeer eenvoudig: metrische gegevens over gebruikersactiviteit worden weergegeven met één geheel getal, PageVisited en worden vastgelegd samen met basisinformatie over het gebruikersprofiel. Daarnaast houdt u voor een op tijd gebaseerde analyse een reeks rijen voor elke gebruiker, waarbij elke rij het aantal pagina's vertegenwoordigt dat een bepaalde gebruiker binnen een bepaalde periode heeft bezocht.
Gelukkig hoeft u geen moeite te doen in uw app om deze activiteitsgegevens te behouden. Met tijdelijke tabellen wordt dit proces geautomatiseerd, waardoor u volledige flexibiliteit krijgt tijdens het ontwerpen van websites en meer tijd om u te richten op de gegevensanalyse zelf. Het enige wat u moet doen, is ervoor te zorgen dat WebSiteInfo de tabel is geconfigureerd als tijdelijke systeemversie. De exacte stappen voor het gebruik van tijdelijke tabellen in dit scenario worden hieronder beschreven.
Stap 1: Tabellen configureren als tijdelijk
Afhankelijk van of u nieuwe ontwikkeling start of een bestaande toepassing bijwerkt, maakt u tijdelijke tabellen of wijzigt u bestaande tabellen door tijdelijke kenmerken toe te voegen. In het algemeen kan uw scenario een combinatie van deze twee opties zijn. Voer deze actie uit met behulp van SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio of een ander Transact-SQL-ontwikkelhulpprogramma.
Belangrijk
Het is raadzaam altijd de nieuwste versie van Management Studio te gebruiken om gesynchroniseerd te blijven met updates voor Azure SQL Database en Azure SQL Managed Instance. SQL Server Management Studio bijwerken.
Nieuwe tabel maken
Gebruik contextmenu-item 'Nieuwe systeemversietabel' in SSMS Objectverkenner om de queryeditor te openen met een tijdelijke tabelsjabloonscript en gebruik vervolgens 'Waarden opgeven voor sjabloonparameters' (Ctrl+Shift+M) om de sjabloon te vullen:
Kies in SSDT de sjabloon 'Tijdelijke tabel (systeemversief)' wanneer u nieuwe items toevoegt aan het databaseproject. Hiermee opent u de ontwerpfunctie voor tabellen en kunt u eenvoudig de tabelindeling opgeven:
U kunt ook tijdelijke tabel maken door de Transact-SQL-instructies rechtstreeks op te geven, zoals wordt weergegeven in het onderstaande voorbeeld. Houd er rekening mee dat de verplichte elementen van elke tijdelijke tabel de definitie PERIOD en de SYSTEM_VERSIONING-component zijn met een verwijzing naar een andere gebruikerstabel die historische rijversies opslaat:
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));
Wanneer u een tijdelijke tabel met systeemversies maakt, wordt de bijbehorende geschiedenistabel met de standaardconfiguratie automatisch gemaakt. De standaardgeschiedenistabel bevat een geclusterde B-tree-index op de puntkolommen (einde, begin) met paginacompressie ingeschakeld. Deze configuratie is optimaal voor de meeste scenario's waarin tijdelijke tabellen worden gebruikt, met name voor gegevenscontrole.
In dit specifieke geval streven we ernaar om op tijd gebaseerde trendanalyse uit te voeren in een langere gegevensgeschiedenis en met grotere gegevenssets, zodat de opslagkeuze voor de geschiedenistabel een geclusterde columnstore-index is. Een geclusterde columnstore biedt zeer goede compressie en prestaties voor analytische query's. Tijdelijke tabellen bieden u de flexibiliteit om indexen voor de huidige en tijdelijke tabellen volledig onafhankelijk te configureren.
Notitie
Columnstore-indexen zijn beschikbaar in de Bedrijfskritiek-, Algemeen gebruik- en Premium-lagen en in de Standard-laag, S3 en hoger.
In het volgende script ziet u hoe de standaardindex in de geschiedenistabel kan worden gewijzigd in de geclusterde columnstore:
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);
Tijdelijke tabellen worden weergegeven in de Objectverkenner met het specifieke pictogram voor eenvoudigere identificatie, terwijl de geschiedenistabel wordt weergegeven als een onderliggend knooppunt.
Bestaande tabel wijzigen in tijdelijk
Laten we het alternatieve scenario bespreken waarin de tabel WebsiteUserInfo al bestaat, maar niet is ontworpen om een geschiedenis van wijzigingen bij te houden. In dit geval kunt u de bestaande tabel eenvoudig uitbreiden om tijdelijk te worden, zoals wordt weergegeven in het volgende voorbeeld:
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);
Stap 2: uw workload regelmatig uitvoeren
Het belangrijkste voordeel van tijdelijke tabellen is dat u uw website niet hoeft te wijzigen of aan te passen om wijzigingen bij te houden. Zodra de tijdelijke tabellen zijn gemaakt, blijven eerdere rijversies transparant behouden telkens wanneer u wijzigingen aan uw gegevens uitvoert.
Om automatische wijzigingen bij te houden voor dit specifieke scenario, kunnen we kolom PagesVisited bijwerken telkens wanneer een gebruiker de sessie op de website beëindigt:
UPDATE WebsiteUserInfo SET [PagesVisited] = 5
WHERE [UserID] = 1;
Het is belangrijk om te weten dat de updatequery niet precies hoeft te weten wanneer de werkelijke bewerking heeft plaatsgevonden, noch hoe historische gegevens worden bewaard voor toekomstige analyse. Beide aspecten worden automatisch afgehandeld door Azure SQL Database en Azure SQL Managed Instance. In het volgende diagram ziet u hoe geschiedenisgegevens worden gegenereerd voor elke update.
Stap 3: Historische gegevensanalyse uitvoeren
Wanneer tijdelijke systeemversiebeheer is ingeschakeld, is historische gegevensanalyse slechts één query van u verwijderd. In dit artikel geven we enkele voorbeelden die betrekking hebben op veelvoorkomende analysescenario's: voor meer informatie over alle details, het verkennen van verschillende opties die zijn geïntroduceerd met de COMPONENT FOR SYSTEM_TIME .
Voer deze query uit om de tien belangrijkste gebruikers te zien die zijn besteld op het aantal bezochte webpagina's vanaf een uur geleden:
DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC
U kunt deze query eenvoudig wijzigen om de sitebezoeken te analyseren vanaf een dag geleden, een maand geleden of op elk gewenst moment in het verleden.
Als u een eenvoudige statistische analyse wilt uitvoeren voor de vorige dag, gebruikt u het volgende voorbeeld:
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
Als u binnen een bepaalde periode wilt zoeken naar activiteiten van een specifieke gebruiker, gebruikt u de COMPONENT IN 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;
Grafische visualisatie is vooral handig voor tijdelijke query's, omdat u trends en gebruikspatronen op een intuïtieve manier heel eenvoudig kunt weergeven:
Veranderend tabelschema
Normaal gesproken moet u het tijdelijke tabelschema wijzigen terwijl u app-ontwikkeling uitvoert. Hiervoor voert u gewoon gewone ALTER TABLE-instructies uit en worden wijzigingen in de geschiedenistabel correct doorgegeven aan Azure SQL Database of Azure SQL Managed Instance. In het volgende script ziet u hoe u extra kenmerk voor het bijhouden kunt toevoegen:
/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';
Op dezelfde manier kunt u de kolomdefinitie wijzigen terwijl uw workload actief is:
/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
ALTER COLUMN UserName nvarchar(256) NOT NULL;
Ten slotte kunt u een kolom verwijderen die u niet meer nodig hebt.
/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
DROP COLUMN TemporaryColumn;
U kunt ook de nieuwste SSDT gebruiken om het tijdelijke tabelschema te wijzigen terwijl u bent verbonden met de database (onlinemodus) of als onderdeel van het databaseproject (offlinemodus).
Retentie van historische gegevens beheren
Met tijdelijke tabellen met systeemversies kan de geschiedenistabel de database groter maken dan gewone tabellen. Een grote en steeds groeiende geschiedenistabel kan een probleem worden vanwege pure opslagkosten en het opleggen van een prestatiebelasting voor tijdelijke query's. Daarom is het ontwikkelen van een beleid voor gegevensretentie voor het beheren van gegevens in de geschiedenistabel een belangrijk aspect van het plannen en beheren van de levenscyclus van elke tijdelijke tabel. Met Azure SQL Database en Azure SQL Managed Instance hebt u de volgende benaderingen voor het beheren van historische gegevens in de tijdelijke tabel:
Volgende stappen
- Zie Tijdelijke tabellen voor meer informatie over tijdelijke tabellen.