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

Artykuł
07/31/2023

Dotyczy: Azure SQL Database Azure SQL Managed Instance

Tabele czasowe to funkcja programowalności usług 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 czasowe zapewniają ścisłe powiązanie danych 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 internetowej 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 prosty — metryka aktywności użytkownika jest reprezentowana przy użyciu pojedynczego 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 musisz wprowadzać żadnych starań w aplikacji, aby zachować te informacje o działaniu. 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 zostały opisane poniżej.

Krok 1. Konfigurowanie tabel jako czasowych

W zależności od tego, czy uruchamiasz nowe programowanie, czy uaktualniasz istniejącą aplikację, 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 tę akcję 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 do zachowania synchronizacji z aktualizacjami usług Azure SQL Database i Azure SQL Managed Instance. Zaktualizuj program SQL Server Management Studio.

Tworzenie nowej tabeli

Użyj elementu menu kontekstowego "Nowa tabela w wersji systemowej" 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 łatwe określenie 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. 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 zostanie automatycznie utworzona dołączona tabela historii z konfiguracją domyślną. Domyślna tabela historii zawiera klastrowany indeks drzewa B w kolumnach okresu (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, zwłaszcza 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 zestawach danych, więc wybór magazynu dla tabeli historii jest klastrowanym indeksem magazynu kolumn. Klastrowany magazyn kolumn zapewnia 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 tabela historii jest wyświetlana jako węzeł podrzędny.

AlterTable

Zmiana istniejącej tabeli na czas

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 kolumnę 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 usługę 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 od Ciebie. W tym artykule przedstawimy 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

To zapytanie można łatwo zmodyfikować, 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 danym 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:

Czasografia

Ewoluujący schemat tabeli

Zazwyczaj należy zmienić schemat tabeli czasowej podczas tworzenia aplikacji. W tym celu wystarczy uruchomić regularne instrukcje ALTER TABLE i usługę 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 użyj 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 z wersją systemu 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 z powodu czystych kosztów magazynowania, jak i nałożenia 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 usług 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.

Udostępnij za pośrednictwem