開始使用 Azure SQL Database 中的時態表Getting Started with Temporal Tables in Azure SQL Database

時態表是 Azure SQL Database 的一個新的可程式性功能,可讓您追蹤和分析資料變更的完整歷程記錄,而不需要撰寫自訂程式碼。Temporal Tables are a new programmability feature of Azure SQL Database that allows you to track and analyze the full history of changes in your data, without the need for custom coding. 時態表會保持資料與時間內容之間的密切關係,因此只有在特定期間內,才會將預存的事實解譯為有效。Temporal Tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. 時態表的這個屬性允許進行以有效時間為基礎的分析,並可從資料演進中取得獨到見解。This property of Temporal Tables allows for efficient time-based analysis and getting insights from data evolution.

時態表案例Temporal Scenario

本文說明在應用程式案例中使用時態表的步驟。This article illustrates the steps to utilize Temporal Tables in an application scenario. 假設您想要從頭開始追蹤正在開發的新網站上的使用者活動,或您想要使用使用者活動分析擴充的現有網站上的使用者活動。Suppose that you want to track user activity on a new website that is being developed from scratch or on an existing website that you want to extend with user activity analytics. 在這個簡化的範例中,我們假設在一段時間內瀏覽過的網頁數目是必須在裝載於 Azure SQL Database 的網站資料庫中擷取和監視的指標。In this simplified example, we assume that the number of visited web pages during a period of time is an indicator that needs to be captured and monitored in the website database that is hosted on Azure SQL Database. 使用者活動歷史分析的目標是要獲得重新設計網站的意見,並為訪客提供更好的經驗。The goal of the historical analysis of user activity is to get inputs to redesign website and provide better experience for the visitors.

此案例的資料庫模型非常簡單:使用者活動度量是以單一整數欄位 PageVisited 表示,而且會與使用者設定檔上的基本資訊一起被擷取。The database model for this scenario is very simple - user activity metric is represented with a single integer field, PageVisited, and is captured along with basic information on the user profile. 此外,對於以時間為基礎的分析,您要為每個使用者保留一連串的資料列,其中每個資料列都代表一段特定時間內特定使用者瀏覽過的頁數。Additionally, for time-based analysis, you would keep a series of rows for each user, where every row represents the number of pages a particular user visited within a specific period of time.

架構

幸運的是,您不需要在您的 app 上花太多精力,就可以維護此活動資訊。Fortunately, you do not need to put any effort in your app to maintain this activity information. 您可以使用時態表,將此程序自動化:讓您在網站設計期間有完整的彈性以及更多的時間,得以將重點放在資料分析本身。With Temporal Tables, this process is automated - giving you full flexibility during website design and more time to focus on the data analysis itself. 您只需要確保將 WebSiteInfo 資料表設定為 時態系統設定版本The only thing you have to do is to ensure that WebSiteInfo table is configured as temporal system-versioned. 在此案例中使用時態表的確切步驟如下所述。The exact steps to utilize Temporal Tables in this scenario are described below.

步驟 1:將資料表設定為時態表Step 1: Configure tables as temporal

根據您要開始新的開發工作,還是升級現有的應用程式,您將會建立時態表,或透過新增時態屬性來修改現有的資料表。Depending on whether you are starting new development or upgrading existing application, you will either create temporal tables or modify existing ones by adding temporal attributes. 在一般情況下,您的案例可能會混用這兩個選項。In general case, your scenario can be a mix of these two options. 使用 SQL Server Management Studio (SSMS)、SQL Server Data Tools (SSDT) 或其他任何 Transact-SQL 開發工具執行下列動作。Perform these action using SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or any other Transact-SQL development tool.

重要

建議您一律使用最新版本的 Management Studio 保持與 Microsoft Azure 及 SQL Database 更新同步。It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. 更新 SQL Server Management StudioUpdate SQL Server Management Studio.

建立新資料表Create new table

在 SSMS 的 [物件總管] 中使用內容功能表項目 [新系統設定版本的資料表] 開啟查詢編輯器與時態表範本指令碼,然後使用 [指定範本參數的值] (Ctrl + Shift + M) 填入範本:Use context menu item “New System-Versioned Table” in SSMS Object Explorer to open the query editor with a temporal table template script and then use “Specify Values for Template Parameters” (Ctrl+Shift+M) to populate the template:

SSMSNewTable

在 SSDT 中,將新項目新增至資料庫專案時,請選擇 [時態表 (系統建立版本)] 範本。In SSDT, choose “Temporal Table (System-Versioned)” template when adding new items to the database project. 這將會開啟資料表設計工具,並讓您輕鬆地指定資料表配置︰That will open table designer and enable you to easily specify the table layout:

SSDTNewTable

您也可以透過直接指定 Transact-SQL 陳述式來建立時態表,如下列範例所示。You can also create temporal table by specifying the Transact-SQL statements directly, as shown in the example below. 請注意,每個時態表的必要元素為 PERIOD 定義以及可參照將儲存歷史資料列版本的另一個使用者資料表的 SYSTEM_VERSIONING 子句︰Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

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

當您建立系統設定版本的時態表時,會自動建立隨附預設組態的歷程記錄資料表。When you create system-versioned temporal table, the accompanying history table with the default configuration is automatically created. 預設的歷程記錄資料表包含期間資料行 (結束、開始) 上啟用頁面壓縮的叢集 B 型樹狀目錄索引。The default history table contains a clustered B-tree index on the period columns (end, start) with page compression enabled. 此組態適合使用時態表的大部分案例,特別是用於 資料稽核This configuration is optimal for the majority of scenarios in which temporal tables are used, especially for data auditing.

在此特殊案例中,我們的目標是針對一段較長的資料歷程記錄以及較大的資料集,執行以時間為基礎的趨勢分析,因此歷程記錄表格的儲存體選擇為叢集資料行存放區索引。In this particular case, we aim to perform time-based trend analysis over a longer data history and with bigger data sets, so the storage choice for the history table is a clustered columnstore index. 叢集資料行存放區為分析查詢提供很好的壓縮和效能。A clustered columnstore provides very good compression and performance for analytical queries. 時態表提供您完全獨立地設定目前資料表和時態表的索引的彈性。Temporal Tables give you the flexibility to configure indexes on the current and temporal tables completely independently.

注意

資料行存放區索引可用於進階層和標準層 S3 和更新版本。Columnstore indexes are available in the Premium tier and in the Standard tier, S3 and above.

下列指令碼示範如何將歷程記錄資料表的預設索引變更為叢集資料行存放區︰The following script shows how default index on history table can be changed to the clustered columnstore:

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

時態表在 [物件總管] 中會以特定圖示表示,讓您更容易識別,而其歷程記錄資料表則會以子節點顯示。Temporal Tables are represented in the Object Explorer with the specific icon for easier identification, while its history table is displayed as a child node.

AlterTable

將現有的資料表變更為時態表Alter existing table to temporal

讓我們來看看替代案例,其中 WebsiteUserInfo 資料表已存在,但不是針對保留變更的歷程記錄而設計。Let’s cover the alternative scenario in which the WebsiteUserInfo table already exists, but was not designed to keep a history of changes. 在此情況下,您只能擴充現有的資料表,使其成為時態表,如以下範例所示︰In this case, you can simply extend the existing table to become temporal, as shown in the following example:

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:定期執行您的工作負載Step 2: Run your workload regularly

時態表的主要優點是您不需要以任何方式變更或調整您的網站,就可以執行變更追蹤。The main advantage of Temporal Tables is that you do not need to change or adjust your website in any way to perform change tracking. 一旦建立時態表之後,當您每次對資料進行修改時,便會自動保存先前的資料列版本。Once created, Temporal Tables transparently persist previous row versions every time you perform modifications on your data.

為了利用此特定案例的自動變更追蹤, 讓我們在每次使用者于網站上結束會話時, 就更新資料行pagesvisited 即可:In order to leverage automatic change tracking for this particular scenario, let’s just update column PagesVisited every time a user ends their session on the website:

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

請務必注意,更新查詢不需要知道實際作業進行的時間,也不需要知道如何保留歷史資料以供未來分析之用。It is important to notice that the update query doesn’t need to know the exact time when the actual operation occurred nor how historical data will be preserved for future analysis. Azure SQL Database 會自動處理這兩方面。Both aspects are automatically handled by the Azure SQL Database. 下圖說明如何在每次更新時產生歷程記錄資料。The following diagram illustrates how history data is being generated on every update.

TemporalArchitecture

步驟 3:執行歷史資料分析Step 3: Perform historical data analysis

現在當時態系統設定版本功能啟用時,您只需要一個查詢,就可以進行歷史資料分析。Now when temporal system-versioning is enabled, historical data analysis is just one query away from you. 在本文中,我們將提供一些解決常見分析案例的範例。若要了解所有詳細資料,請瀏覽使用 FOR SYSTEM_TIME 子句所導入的各種選項。In this article, we will provide a few examples that address common analysis scenarios - to learn all details, explore various options introduced with the FOR SYSTEM_TIME clause.

若要查看依瀏覽網頁次數排序的前 10 名使用者,請執行以下查詢︰To see the top 10 users ordered by the number of visited web pages as of an hour ago, run this query:

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

您可以輕鬆地修改此查詢,以分析截至一天前、一個月前,或您希望的任何過去時間點的網站瀏覽記錄。You can easily modify this query to analyze the site visits as of a day ago, a month ago or at any point in the past you wish.

若要進行前一天的基本統計分析,請使用以下範例︰To perform basic statistical analysis for the previous day, use the following example:

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

若要搜尋特定使用者在某段時間的活動,請使用 CONTAINED IN 子句︰To search for activities of a specific user, within a period of time, use the CONTAINED IN clause:

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;

圖形視覺效果對於時態查詢特別方便,因為您可以以直覺方式,非常輕鬆地顯示趨勢和使用模式︰Graphic visualization is especially convenient for temporal queries as you can show trends and usage patterns in an intuitive way very easily:

TemporalGraph

不斷演進的資料表結構描述Evolving table schema

一般而言,您必須在開發 app 的同時,變更時態表結構描述。Typically, you will need to change the temporal table schema while you are doing app development. 因此,只要執行一般 ALTER TABLE 陳述式,Azure SQL Database 就會適當地傳播歷程記錄資料表的變更。For that, simply run regular ALTER TABLE statements and Azure SQL Database will appropriately propagate changes to the history table. 下列指令碼示範如何新增要追蹤的其他屬性︰The following script shows how you can add additional attribute for tracking:

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

同樣地,您可以在您的工作負載正在作用中時,變更資料行定義︰Similarly, you can change column definition while your workload is active:

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

最後,您可以移除您不再需要的資料行。Finally, you can remove a column that you do not need anymore.

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

或者,當您連線到資料庫 (線上模式),或您屬於資料庫專案的一部分 (離線模式) 時,使用最新的 SSDT 變更時態表結構描述。Alternatively, use latest SSDT to change temporal table schema while you are connected to the database (online mode) or as part of the database project (offline mode).

控制歷史資料的保留期Controlling retention of historical data

透過系統設定版本的時態表,歷程記錄資料表可以將資料庫大小增加到超過一般資料表。With system-versioned temporal tables, the history table may increase the database size more than regular tables. 一個大型且不斷成長的歷程記錄表格可能會因為單純的儲存體成本,以及對時態查詢效能所徵收的稅額而變成一個問題。A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. 因此,開發資料保留原則來管理歷程記錄資料表中的資料是規劃及管理每個時態表生命週期的重要環節。Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table. 使用 Azure SQL Database 時,您有下列方法可以管理時態表中的歷史資料︰With Azure SQL Database, you have the following approaches for managing historical data in the temporal table:

後續步驟Next steps