Сценарии использования темпоральных таблиц

Область применения: SQL Server

Системные темпоральные таблицы полезны в сценариях, требующих отслеживания изменений данных. Рекомендуется рассмотреть темпоральные таблицы в следующих случаях использования, чтобы повысить производительность.

Аудит данных

Вы можете использовать темпоральную систему управления версиями в таблицах, в которые хранятся критически важные сведения, для отслеживания того, что изменилось и когда, а также для выполнения судебной экспертизы данных в любой момент времени.

Темпоральные таблицы позволяют планировать сценарии аудита данных на ранних этапах цикла разработки или добавлять аудит данных в существующие приложения или решения, когда это необходимо.

На следующей схеме показана Employee таблица с примером данных, включая текущие (помеченные синим цветом) и исторические версии строк (помеченные серым цветом).

Правая часть схемы визуализирует версии строк на оси времени и строки, которые вы выбираете с различными типами запросов в темпоральной таблице, с предложением или без нее SYSTEM_TIME .

Включение системного управления версиями в новой таблице для аудита данных

Если вы определяете информацию, требующую аудита данных, создайте таблицы базы данных в виде системных темпоральных таблиц. В следующем примере показан сценарий с таблицей, называемой Employee в гипотетической базе данных hr:

CREATE TABLE Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2(2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2(2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Различные варианты создания темпоральной таблицы с версиями системы описаны в статье "Создание системной темпоральной таблицы".

Включение системного управления версиями в существующей таблице для аудита данных

Если необходимо выполнить аудит данных в существующих базах данных, используйте ALTER TABLE для расширения непорпоральных таблиц, чтобы стать системными версиями. Чтобы избежать критических изменений в приложении, добавьте столбцы периодов, HIDDENкак описано в статье "Создание системной темпоральной таблицы".

В следующем примере показано включение системного управления версиями существующей Employee таблицы в гипотетической базе данных hr. Она включает системное управление версиями в Employee таблице двумя шагами. Во-первых, новые столбцы периода добавляются как HIDDEN. Затем он создает таблицу журнала по умолчанию.

ALTER TABLE Employee ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) 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 Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Внимание

Точность типа данных datetime2 должна совпадать с исходной таблицей, так как она находится в системной таблице журнала.

После выполнения предыдущего скрипта все изменения данных будут собираться прозрачно в таблице журнала. В типичном сценарии аудита данных запрашиваются все изменения данных, примененные к отдельной строке в течение интересующего периода времени. Таблица журнала по умолчанию создается с кластеризованным деревом B-store для эффективного решения этого варианта использования.

Примечание.

В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Выполнение анализа данных

После включения системного управления версиями с помощью любого из предыдущих подходов аудит данных составляет всего один запрос. Следующий запрос выполняет поиск версий строк для записей в Employee таблице, причем EmployeeID = 1000 они были активны по крайней мере для части периода между 1 января 2021 г. и 1 января 2022 г. (включая верхнюю границу):

SELECT * FROM Employee
FOR SYSTEM_TIME BETWEEN '2021-01-01 00:00:00.0000000'
    AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Замените FOR SYSTEM_TIME BETWEEN...AND на FOR SYSTEM_TIME ALL, чтобы проанализировать весь журнал изменений данных для определенного сотрудника.

SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Чтобы найти версии строк, которые были активны только в течение некоторого периода (но не вне его), используйте предложение CONTAINED IN. Этот запрос эффективен, поскольку запрашивает только таблицу журнала:

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN (
    '2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000'
)
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Наконец, в некоторых сценариях аудита может потребоваться увидеть, как вся таблица выглядела в любой момент времени в прошлом:

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00.0000000';

Системные темпоральные таблицы хранят значения для столбцов периодов в часовом поясе UTC, но может оказаться удобнее работать в локальном часовом поясе, как для фильтрации данных, так и отображения результатов. В следующем примере кода показано, как применить условие фильтрации, указанное в локальном часовом поясе, а затем преобразовано в utc с помощью AT TIME ZONE, которое было введено в SQL Server 2016 (13.x):

/* Add offset of the local time zone to current time*/
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, - 9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
    [Name],
    Position,
    Department,
    [Address],
    [AnnualSalary],
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
    ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee
FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

Предложение AT TIME ZONE удобно использовать во всех сценариях, где применяются таблицы с системным управлением версиями.

Условия фильтрации, указанные в темпоральных предложениях с FOR SYSTEM_TIME поддержкой SARG. SARG обозначает аргумент поиска, а функция SARG означает, что SQL Server может использовать базовый кластеризованный индекс для выполнения поиска вместо операции сканирования. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Server.

Если вы запрашиваете таблицу журнала напрямую, убедитесь, что условие фильтрации также имеет возможность SARG, указав фильтры в виде <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

При применении AT TIME ZONE к столбцам периодов SQL Server выполняет проверку таблицы или индекса, что может быть очень дорогостоящим. Избегайте подобных условий в запросах:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Дополнительные сведения см. в статье "Запрос данных" в системной темпоральной таблице.

Анализ на определенный момент времени (переход во времени)

Вместо того чтобы сосредоточиться на изменениях отдельных записей, сценарии перемещения по времени показывают, как все наборы данных изменяются с течением времени. Иногда переход во времени включает несколько связанных темпоральных таблиц, изменяющихся в независимом темпе, для которых требуется анализировать следующее:

• тренды для важных индикаторов в исторических и текущих данных;
• точный моментальный снимок всех данных на какой-либо момент времени в прошлом (вчера, месяц назад и т. д.);
• различия между двумя интересующими моментами времени (например, между данными месяц назад и данными три месяца назад).

Существует много реальных сценариев, в которых требуется анализ с переходом во времени. Чтобы проиллюстрировать этот сценарий использования, давайте рассмотрим OLTP с автоматически созданным журналом.

OLTP с автоматически созданным журналом данных

В системах обработки транзакций можно проанализировать, как меняются важные метрики с течением времени. В идеальном сценарии анализ журнала не должен ухудшать производительность приложения OLTP, где доступ к актуальному состоянию данных должен осуществляться с минимальной задержкой и блокировкой данных. Чтобы прозрачно хранить полный журнал изменений для дальнейшего анализа отдельно от текущих данных, с минимальным влиянием на основную рабочую нагрузку OLTP, можно воспользоваться темпоральными таблицами с системным управлением версиями.

Для рабочих нагрузок с высокой транзакцией рекомендуется использовать системные темпоральные таблицы с оптимизированными для памяти таблицами, которые позволяют хранить текущие данные в памяти и полную историю изменений на диске экономически эффективным способом.

Для таблицы журнала рекомендуется использовать кластеризованный индекс columnstore по следующим причинам.

• Типичный анализ трендов использует преимущества, предоставляемые кластеризованным индексом columnstore.

• Задача очистки данных с оптимизированными для памяти таблицами при большой рабочей нагрузке OLTP выполняется лучше, когда таблица журнала имеет кластеризованный индекс columnstore.

• Кластеризованный индекс columnstore обеспечивает отличное сжатие, особенно в сценариях, где не все столбцы изменяются в одно и то же время.

Использование темпоральных таблиц с OLTP в памяти сокращает необходимость сохранять весь набор данных в памяти и позволяет легко различать "горячие" и "холодные" данные.

В качестве примеров реальных ситуаций, попадающих в эту категорию, можно среди прочего указать управление запасами и валютные операции.

На следующей схеме показана упрощенная модель данных, используемая для управления запасами.

В следующем примере кода создается ProductInventory в виде временной таблицы с версиями системы в памяти с кластеризованным индексом columnstore в таблице журнала (который фактически заменяет индекс хранилища строк, созданный по умолчанию):

Примечание.

Убедитесь, что база данных позволяет создавать таблицы, оптимизированные для памяти. См. раздел Создание таблицы с оптимизацией памяти и хранимой процедуры, скомпилированной в собственном коде.

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
        SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory] (
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

Для предыдущей модели это процедура поддержания инвентаризации:

CREATE PROCEDURE [dbo].[spUpdateInventory]
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    UPDATE dbo.ProductInventory
    SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location

    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

Хранимая процедура spUpdateInventory либо вставляет новый продукт на склад, либо обновляет количество продуктов для определенного расположения. Бизнес-логика проста и ориентирована на поддержание последней точности состояния в течение всего времени путем увеличения или уменьшения Quantity поля с помощью обновления таблицы, а системные таблицы прозрачно добавляют измерение журнала в данные, как показано на следующей схеме.

Теперь запрос актуального состояния может выполняться эффективно из модуля, скомпилированного в собственном коде:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    SELECT ProductId, LocationID, Quantity, ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId
END;
GO

EXEC [dbo].[spQueryInventoryLatestState];

Анализ изменений данных с течением времени становится простым в FOR SYSTEM_TIME ALL предложении, как показано в следующем примере:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory
AS
SELECT ProductId,
    LocationId,
    Quantity,
    ValidFrom,
    ValidTo
FROM [dbo].[ProductInventory]
FOR SYSTEM_TIME ALL;
GO

SELECT * FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

На следующей схеме показан журнал данных для одного продукта, который можно легко отобразить при импорте предыдущего представления в Power Query, Power BI или аналогичном средстве бизнес-аналитики:

Временные таблицы можно использовать в этом сценарии для выполнения других типов анализа перемещения по времени, таких как восстановление состояния инвентаризации AS OF в любой момент времени в прошлом или сравнение моментальных снимков, принадлежащих разным моментам во времени.

В этом сценарии использования можно также расширить таблицы Product и Location, чтобы стать темпоральными таблицами, чтобы включить последующий анализ журнала изменений UnitPrice и NumberOfEmployee.

ALTER TABLE Product ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 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 Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location] ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DFValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE [Location]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Так как модель данных теперь включает несколько темпоральных таблиц, рекомендуется AS OF создать представление, которое извлекает необходимые данные из связанных таблиц и применяется FOR SYSTEM_TIME AS OF к представлению, так как это значительно упрощает восстановление состояния всей модели данных:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
    PrInv.LocationId,
    P.ProductName,
    L.LocationName,
    PrInv.Quantity,
    P.UnitPrice,
    L.NumberOfEmployees,
    P.ValidFrom AS ProductStartTime,
    P.ValidTo AS ProductEndTime,
    L.ValidFrom AS LocationStartTime,
    L.ValidTo AS LocationEndTime,
    PrInv.ValidFrom AS InventoryStartTime,
    PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
INNER JOIN dbo.Product AS P
    ON PrInv.ProductId = P.ProductID
INNER JOIN dbo.Location AS L
    ON PrInv.LocationId = L.LocationID;
GO

SELECT * FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF '2022-01-01';

На приведенном ниже снимке экрана показан план выполнения, созданный для запроса SELECT. Это иллюстрирует, что ядро СУБД обрабатывает все сложности при работе с темпоральными связями:

Для сравнения состояния складских запасов в два момента времени (день назад и месяц назад) используйте следующий код:

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo DATETIME2 = DATEADD (MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
    inventoryDayAgo.ProductName,
    inventoryDayAgo.LocationName,
    inventoryDayAgo.Quantity AS QuantityDayAgo,
    inventoryMonthAgo.Quantity AS QuantityMonthAgo,
    inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
    inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
INNER JOIN vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Обнаружение аномалий

Обнаружение аномалий (или обнаружение выбросов) представляет собой выявление элементов, которые не соответствуют ожидаемому шаблону или другим элементам в наборе данных. Для обнаружения аномалий, возникающих периодически или нерегулярно, можно использовать темпоральные таблицы с системным управлением версиями, поскольку вы можете быстро находить определенные шаблоны с помощью темпоральных запросов. Вид аномалии зависит от собираемого типа данных и бизнес-логики.

В следующем примере показана упрощенная логика для обнаружения "всплесков" в цифрах продаж. Предположим, что вы работаете с темпоральной таблицей, в которой собирается журнал приобретенных продуктов.

CREATE TABLE [dbo].[Product] (
    [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] [varchar](100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

На следующей схеме показаны покупки с течением времени.

Предположим, что в течение обычных дней количество приобретенных продуктов имеет небольшую дисперсию, следующий запрос определяет одноэлементные выбросы: выборки, различия которых по сравнению с их непосредственными соседями значительны (2x), в то время как окружающие образцы не отличаются значительно (менее 20%):

WITH CTE (
    ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo
    )
AS (
    SELECT ProdId,
        LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
        DailySales,
        LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
        ValidFrom,
        ValidTo
    FROM Product
    FOR SYSTEM_TIME ALL
    )
SELECT ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo,
    ABS(PrevValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN NextValue > PrevValue THEN PrevValue
                ELSE NextValue
            END)) AS PrevToNextDiff,
    ABS(CurrentValue - PrevValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > PrevValue THEN PrevValue
                ELSE CurrentValue
            END)) AS CurrentToPrevDiff,
    ABS(CurrentValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > NextValue THEN NextValue
                ELSE CurrentValue
            END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (
        CASE
            WHEN NextValue > PrevValue THEN PrevValue
            ELSE NextValue
        END) < 0.2
    AND ABS(CurrentValue - PrevValue) / (
        CASE
            WHEN CurrentValue > PrevValue THEN PrevValue
            ELSE CurrentValue
        END) > 2
    AND ABS(CurrentValue - NextValue) / (
        CASE
            WHEN CurrentValue > NextValue THEN NextValue
            ELSE CurrentValue
        END) > 2;

Примечание.

Этот пример преднамеренно упрощен. В рабочих сценариях вы, скорее всего, будете использовать расширенные статистические методы для определения примеров, которые не соответствуют общему шаблону.

Медленно изменяющиеся измерения

Измерения в хранилищах данных обычно содержат относительно статические данные о сущностях, таких как географические расположения, клиенты и продукты. Однако в некоторых сценариях требуется отслеживание изменений данных и в таблицах измерений. Учитывая, что изменения в измерениях происходят гораздо реже, непредсказуемо и вне рамок расписания регулярного обновления, которое применяется к таблицам фактов, такие типы таблиц измерений называются медленно изменяющимися измерениями (SCD).

Существует несколько категорий медленно изменяющихся измерений, выделяемых на основе того, как сохраняется журнал изменений.

Тип аналитики	Сведения
Тип 0	Журнал не сохраняется. Атрибуты измерений отражают исходные значения.
Тип 1	Атрибуты измерений отражают последние значения (предыдущие значения перезаписываются).
Тип 2	Каждая версия элемента измерения представлена в виде отдельной строки таблицы, обычно со столбцами, представляющими период действительности.
Тип 3	Сохранение ограниченного журнала для выбранных атрибутов с помощью дополнительных столбцов в одной строке
Тип 4	Хранение журнала в отдельной таблице. При этом исходная таблица измерения поддерживает последние (текущие) версии элементов измерений.

При выборе стратегии SCD это ответственность за уровень ETL (Extract-Transform-Load) для обеспечения точности таблиц измерений, которые обычно требуют более сложного кода и дополнительного обслуживания.

Чтобы значительно снизить сложность кода, можно использовать темпоральные таблицы с системным управлением версиями, поскольку журнал данных сохраняется автоматически. Темпоральные таблицы наиболее близки к SCD типа 4, учитывая, что они реализуются с помощью двух таблиц. Однако поскольку темпоральные запросы позволяют ссылаться только на текущую таблицу, можно также рассмотреть применение темпоральных таблиц в средах, где планируется использовать SCD типа 2.

Для преобразования обычного измерения в SCD можно создать новую или изменить существующую таблицу, чтобы она стала темпоральной таблицей с системным управлением версиями. Если существующая таблица измерения содержит исторические данные, создайте отдельную таблицу, переместите в нее исторические данные и сохраните текущие (действующие) версии измерения в исходной таблице измерения. Затем используйте ALTER TABLE синтаксис для преобразования таблицы измерений в системную темпоральную таблицу с предварительно определенной таблицей журнала.

В следующем примере показан этот процесс и предполагается, что таблица измерения DimLocation уже имеет столбцы ValidFrom и ValidTo с типом datetime2, не допускающие значения NULL, которые заполняются процессом ETL:

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Дополнительный код не требуется для обслуживания SCD во время процесса загрузки хранилища данных после его создания.

На следующем рисунке показано, как использовать темпоральные таблицы в простом сценарии с двумя SCD (DimLocation и DimProduct) и одной таблицей фактов.

Чтобы использовать предыдущие SCD в отчетах, необходимо эффективно настроить запросы. Например, можно вычислить общий объем продаж и среднее количество проданных продуктов на человека за последние шесть месяцев. Для обеих метрик требуется корреляция важных для анализа данных из таблицы фактов и измерений, атрибуты которых могли измениться (DimLocation.NumOfCustomers, DimProduct.UnitPrice). Следующий запрос должным образом вычисляет требуемые метрики:

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo DATETIME2;

SET @sixMonthsAgo = DATEADD(month, - 12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
    DimLocation_History.LocationId,
    SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
    AVG(F.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimLocation
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
    ON DimLocation_History.LocationId = F.LocationId
    AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimProduct
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
    ON DimProduct_History.ProductId = F.ProductId
    AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Рекомендации

Использование темпоральных таблиц с системным управлением версиями для SCD допустимо, если срок действия, вычисленный на основе времени транзакции базы данных, согласуется с вашей бизнес-логикой. При загрузке данных со значительной задержкой время транзакции может быть неприемлемо.

По умолчанию системные темпоральные таблицы не позволяют изменять исторические данные после загрузки (после настройки SYSTEM_VERSIONING OFFможно изменить журнал). Это может стать ограничением в случаях, когда изменение данных журнала происходит регулярно.

Темпоральные таблицы с системным управлением версиями формируют версию строки при любом изменении столбца. Если вы хотите запретить создание новых версий при изменении определенных столбцов, необходимо включить это ограничение в логику ETL.

Если ожидается значительное число исторических строк в таблицах SCD, рассмотрите возможность использования кластеризованного индекса columnstore в качестве основного хранилища для таблицы журнала. Использование индекса columnstore сокращает объем таблицы журнала и ускоряет аналитические запросы.

Восстановление повреждения данных на уровне строк

Вы можете использовать исторические данные в темпоральных таблицах с системным управлением версиями, чтобы быстро восстанавливать отдельные строки в любое из ранее записанных состояний. Это свойство темпоральных таблиц полезно, если вы сможете найти затронутые строки и (или) при обнаружении времени изменения нежелательных данных. Эти знания позволяют эффективно выполнять восстановление без работы с резервными копиями.

Такой подход дает несколько преимуществ.

• Вы можете точно управлять областью восстановления. Записи, которые не были затронуты, должны остаться в актуальном состоянии, что часто является критическим требованием.

• Операция является эффективной, и база данных остается доступной для всех рабочих нагрузок с использованием данных.

• Сама операция восстановления поддерживает управление версиями. Имеется журнал аудита для самой операции восстановления, так что при необходимости позже можно проанализировать произошедшее.

Действие восстановления можно автоматизировать с относительной легкостью. Ниже приведен пример кода хранимой процедуры, которая выполняет восстановление данных для таблицы Employee, используемой в сценарии аудита данных.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord
    @EmployeeID INT,
    @versionNumber INT = 1
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmployeeID
            ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID
)
/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

Эта хранимая процедура принимает входные параметры @EmployeeID и @versionNumber. Эта процедура по умолчанию восстанавливает состояние строки до последней версии из журнала (@versionNumber = 1).

На следующем рисунке показано состояние строки до и после вызова процедуры. Красный прямоугольник отмечает текущую, неправильную версию строки, а зеленый прямоугольник отмечает правильную версию из журнала.

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1;

Можно определить эту хранимую процедуру восстановления таким образом, чтобы она принимала точное время вместо версии строки. Он восстанавливает строку до любой версии, активной для указанной точки во времени (то есть AS OF на определенный момент времени).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf
    @EmployeeID INT,
    @asOf DATETIME2
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
SET [Position] = History.[Position],
    [Department] = History.Department,
    [Address] = History.[Address],
    AnnualSalary = History.AnnualSalary
FROM Employee AS E
INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
    ON E.EmployeeID = History.EmployeeID
WHERE E.EmployeeID = @EmployeeID;

На следующем рисунке показан сценарий восстановления для тех же данных с условием времени. Здесь выделены параметр @asOf, выбранная строка в журнале, которая была действующей на указанный момент времени, и новая версия строки в текущей таблице после операции восстановления:

Корректировка данных может стать частью автоматической загрузки данных в системах хранения данных и подготовки отчетов. Если только что обновленное значение неправильно, для устранения этого во многих сценариях достаточно восстановить предыдущую версию из журнала. На следующей схеме показано, как этот процесс можно автоматизировать.

Связанный контент

• Темпоральные таблицы
• Начало работы с системными темпоральными таблицами
• Проверки согласованности систем темпоральных таблиц
• Секционирование с темпоральными таблицами
• Рекомендации и ограничения темпоральной таблицы
• Безопасность темпоральной таблицы
• Системные темпоральные таблицы с оптимизированными для памяти таблицами
• Представления и функции темпоральных метаданных таблицы