Wprowadzenie do tabel czasowych w usłudze Azure SQL Database i Azure SQL Managed Instance

Dotyczy: Azure SQL Managed Instance usługi Azure SQL Database

Tabele czasowe to funkcja programowania Azure SQL Database i Azure SQL Managed Instance, która umożliwia śledzenie i analizowanie pełnej historii zmian w danych bez konieczności kodowania niestandardowego. Tabele danych czasowych są ściśle powiązane z kontekstem czasu, dzięki czemu przechowywane fakty mogą być interpretowane jako prawidłowe tylko w określonym przedziale czasu. Ta właściwość tabel czasowych umożliwia wydajną analizę czasową i uzyskiwanie szczegółowych informacji z ewolucji danych.

Scenariusz czasowy

W tym artykule przedstawiono kroki korzystania z tabel czasowych w scenariuszu aplikacji. Załóżmy, że chcesz śledzić aktywność użytkowników w nowej witrynie internetowej opracowywanej od podstaw lub w istniejącej witrynie internetowej, którą chcesz rozszerzyć za pomocą analizy aktywności użytkowników. W tym uproszczonym przykładzie przyjęto założenie, że liczba odwiedzanych stron internetowych w danym okresie jest wskaźnikiem, który należy przechwycić i monitorować w bazie danych witryny sieci Web hostowanej w usłudze Azure SQL Database lub Azure SQL Managed Instance. Celem historycznej analizy aktywności użytkowników jest uzyskanie danych wejściowych w celu przeprojektowania witryny internetowej i zapewnienia lepszego środowiska dla odwiedzających.

Model bazy danych dla tego scenariusza jest bardzo prosty — metryka aktywności użytkownika jest reprezentowana za pomocą jednego pola całkowitego, PageVisited i jest przechwytywana wraz z podstawowymi informacjami na temat profilu użytkownika. Ponadto w przypadku analizy opartej na czasie należy zachować serię wierszy dla każdego użytkownika, gdzie każdy wiersz reprezentuje liczbę stron odwiedzanych przez określonego użytkownika w określonym przedziale czasu.

Schemat

Na szczęście nie trzeba wprowadzać żadnych wysiłków w aplikacji, aby zachować te informacje o aktywności. Dzięki tabelom czasowym ten proces jest zautomatyzowany — zapewnia pełną elastyczność podczas projektowania witryny internetowej i więcej czasu na skupienie się na samej analizie danych. Jedyną rzeczą, którą musisz zrobić, jest upewnienie się, że WebSiteInfo tabela jest skonfigurowana jako czasowa wersja systemu. Dokładne kroki korzystania z tabel czasowych w tym scenariuszu opisano poniżej.

Krok 1. Konfigurowanie tabel jako danych czasowych

W zależności od tego, czy rozpoczynasz tworzenie nowych, czy uaktualnianie istniejącej aplikacji, utworzysz tabele czasowe lub zmodyfikujesz istniejące, dodając atrybuty czasowe. Ogólnie rzecz biorąc, scenariusz może być kombinacją tych dwóch opcji. Wykonaj te działania przy użyciu programu SQL Server Management Studio (SSMS), narzędzi SQL Server Data Tools (SSDT), narzędzia Azure Data Studio lub dowolnego innego narzędzia programistycznego Języka Transact-SQL.

Ważne

Zaleca się, aby zawsze używać najnowszej wersji programu Management Studio, aby zachować synchronizację z aktualizacjami usługi Azure SQL Database i Azure SQL Managed Instance. Zaktualizuj program SQL Server Management Studio.

Tworzenie nowej tabeli

Użyj elementu menu kontekstowego "Nowa tabela System-Versioned" w programie SSMS Eksplorator obiektów, aby otworzyć edytor zapytań za pomocą skryptu szablonu tabeli czasowej, a następnie użyj polecenia "Określ wartości parametrów szablonu" (Ctrl+Shift+M), aby wypełnić szablon:

SSMSNewTable

W programie SSDT wybierz szablon "Tabela czasowa (systemowa)" podczas dodawania nowych elementów do projektu bazy danych. Spowoduje to otwarcie projektanta tabel i umożliwienie łatwego określenia układu tabeli:

SSDTNewTable

Tabelę czasową można również utworzyć, określając instrukcje języka Transact-SQL bezpośrednio, jak pokazano w poniższym przykładzie. Należy pamiętać, że obowiązkowe elementy każdej tabeli czasowej to definicja okresu i klauzula SYSTEM_VERSIONING z odwołaniem do innej tabeli użytkownika, która będzie przechowywać historyczne wersje wierszy:

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

Podczas tworzenia tabeli czasowej w wersji systemowej jest automatycznie tworzona towarzysząca tabela historii z konfiguracją domyślną. Domyślna tabela historii zawiera grupowany indeks drzewa B w kolumnach okresów (koniec, początek) z włączoną kompresją strony. Ta konfiguracja jest optymalna w przypadku większości scenariuszy, w których są używane tabele czasowe, szczególnie w przypadku inspekcji danych.

W tym konkretnym przypadku dążymy do przeprowadzenia analizy trendów opartych na czasie w dłuższej historii danych i większych zestawów danych, więc wybór magazynu dla tabeli historii jest indeksem klastrowanego magazynu kolumn. Klastrowany magazyn kolumn zapewnia bardzo dobrą kompresję i wydajność zapytań analitycznych. Tabele czasowe zapewniają elastyczność konfigurowania indeksów w bieżących i czasowych tabelach całkowicie niezależnie.

Uwaga

Indeksy magazynu kolumn są dostępne w warstwach Krytyczne dla działania firmy, Ogólnego przeznaczenia i Premium oraz w warstwie Standardowa, S3 i nowszych.

Poniższy skrypt pokazuje, jak można zmienić domyślny indeks tabeli historii na klastrowany magazyn kolumn:

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

Tabele czasowe są reprezentowane w Eksplorator obiektów z określoną ikoną w celu łatwiejszej identyfikacji, podczas gdy jej tabela historii jest wyświetlana jako węzeł podrzędny.

Altertable

Zmienianie istniejącej tabeli na dane czasowe

Omówimy alternatywny scenariusz, w którym tabela WebsiteUserInfo już istnieje, ale nie została zaprojektowana tak, aby zachować historię zmian. W takim przypadku można po prostu rozszerzyć istniejącą tabelę, aby stała się czasowa, jak pokazano w poniższym przykładzie:

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

Krok 2. Regularne uruchamianie obciążenia

Główną zaletą tabel czasowych jest to, że nie trzeba zmieniać ani dostosowywać witryny internetowej w żaden sposób do przeprowadzania śledzenia zmian. Po utworzeniu tabele czasowe w sposób przezroczysty utrwalają poprzednie wersje wierszy za każdym razem, gdy przeprowadzasz modyfikacje danych.

Aby skorzystać z automatycznego śledzenia zmian w tym konkretnym scenariuszu, zaktualizujmy kolumny PagesVisited za każdym razem, gdy użytkownik kończy sesję w witrynie internetowej:

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

Ważne jest, aby zauważyć, że zapytanie aktualizacji nie musi znać dokładnej godziny, kiedy wystąpiła rzeczywista operacja, ani jak dane historyczne zostaną zachowane na potrzeby przyszłej analizy. Oba aspekty są automatycznie obsługiwane przez usługę Azure SQL Database i Azure SQL Managed Instance. Na poniższym diagramie przedstawiono sposób generowania danych historii w każdej aktualizacji.

Czasowaarchitektura

Krok 3. Wykonywanie analizy danych historycznych

Teraz, gdy włączono tymczasowe przechowywanie wersji systemu, analiza danych historycznych to tylko jedno zapytanie. W tym artykule udostępnimy kilka przykładów, które dotyczą typowych scenariuszy analizy — aby poznać wszystkie szczegóły, zapoznać się z różnymi opcjami wprowadzonymi w klauzuli FOR SYSTEM_TIME .

Aby wyświetlić 10 pierwszych użytkowników uporządkowanych według liczby odwiedzonych stron internetowych od godziny temu, uruchom następujące zapytanie:

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

Możesz łatwo zmodyfikować to zapytanie, aby analizować wizyty w witrynie dzień temu, miesiąc temu lub w dowolnym momencie w przeszłości.

Aby wykonać podstawową analizę statystyczną dla poprzedniego dnia, użyj następującego przykładu:

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

Aby wyszukać działania określonego użytkownika, w okresie, użyj klauzuli ZAWARTE 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;

Wizualizacja graficzna jest szczególnie wygodna w przypadku zapytań czasowych, ponieważ można bardzo łatwo wyświetlać trendy i wzorce użycia:

Czasoznawz

Ewoluujący schemat tabeli

Zazwyczaj podczas tworzenia aplikacji należy zmienić schemat tabeli czasowej. W tym celu wystarczy uruchomić regularne instrukcje ALTER TABLE i Azure SQL Database lub Azure SQL Managed Instance odpowiednio propagować zmiany do tabeli historii. Poniższy skrypt pokazuje, jak dodać dodatkowy atrybut do śledzenia:

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

Podobnie możesz zmienić definicję kolumny, gdy obciążenie jest aktywne:

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

Na koniec możesz usunąć kolumnę, której już nie potrzebujesz.

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

Alternatywnie możesz użyć najnowszego narzędzia SSDT , aby zmienić schemat tabeli czasowej podczas nawiązywania połączenia z bazą danych (tryb online) lub w ramach projektu bazy danych (tryb offline).

Kontrolowanie przechowywania danych historycznych

W przypadku tabel czasowych w wersji systemowej tabela historii może zwiększyć rozmiar bazy danych więcej niż zwykłe tabele. Duża i stale rosnąca tabela historii może stać się problemem zarówno ze względu na czyste koszty magazynowania, jak i nakładanie podatku od wydajności na wykonywanie zapytań czasowych. W związku z tym opracowanie zasad przechowywania danych na potrzeby zarządzania danymi w tabeli historii jest ważnym aspektem planowania i zarządzania cyklem życia każdej tabeli czasowej. W przypadku Azure SQL Database i Azure SQL Managed Instance istnieją następujące podejścia do zarządzania danymi historycznymi w tabeli czasowej:

Następne kroki

  • Aby uzyskać więcej informacji na temat tabel czasowych, zobacz Tabele czasowe.