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

Относится к: SQL Server 2016 (13.x) и более поздние версии База данных SQL AzureУправляемый экземпляр SQL AzureSQL Database в Microsoft Fabric

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

Аудит данных

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

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

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

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

Схема, иллюстрирующая первый сценарий Temporal Usage.

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

Если вы определяете информацию, требующую аудита данных, создайте таблицы базы данных в виде системных темпоральных таблиц. Следующий пример иллюстрирует сценарий с таблицей, называемой 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));

Important

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

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

Note

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево 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:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS 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, являются SARGable.

Note

Термин SARGable в реляционных базах данных обозначает предикат Search ARGumentable, для которого может использоваться индекс для ускорения выполнения запроса. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Если вы запрашиваете таблицу журнала напрямую, убедитесь, что условие фильтрации также является 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, можно воспользоваться темпоральными таблицами с системным управлением версиями.

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

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

  • Для типичного анализа тенденций полезна высокая производительность запросов, обеспечиваемая кластеризованным columnstore-индексом.

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

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

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

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

Следующая схема показывает упрощённую модель данных, используемую для управления запасами:

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

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

Note

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

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING 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,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
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
        BEGIN
            SET @quantityIncrement = 0;
        END

        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

EXECUTE [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. Это иллюстрирует, что ядро СУБД обрабатывает все сложности при работе с темпоральными связями:

Схема плана выполнения, созданного для запроса SELECT, иллюстрирующая, что SQL Server ядро СУБД обрабатывает все сложности при работе с темпоральными отношениями.

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

DECLARE @dayAgo AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS 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
    )
);

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

Схема покупок с течением времени.

Предполагая, что в обычные дни количество приобретённых товаров имеет небольшую вариативность, следующий запрос выявляет единичные выбросы: образцы, которые значительно (в 2 раза) отличаются от своих непосредственных соседей, тогда как соседние образцы отличаются незначительно (менее чем на 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;

Note

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

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

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

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

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

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

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

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

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

  • Переместите версии замкнутых строк в новую таблицу истории:

    SELECT *
INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

  • Создайте кластерный индекс столбцев — хороший выбор в сценариях хранилища данных:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Удалить предыдущие версии из DimLocation, которая становится текущей таблицей в конфигурации временной версии системы:

    DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

  • Добавить определение периода:

    ALTER TABLE DimLocation
    ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

  • Включите системное управление версиями и привяжите таблицу журнала к DimLocation:

    ALTER TABLE DimLocation
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

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

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

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

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

Следующий запрос должным образом вычисляет требуемые метрики:

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS 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 AS 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;

Considerations

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

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

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

Если вы ожидаете значительное количество исторических строк в таблицах SCD, рассмотрите возможность использования кластерного индекса столбцев в качестве основного варианта хранения для таблицы истории. Использование индекса 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 using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

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

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

Снимок экрана: состояние строки до и после вызова процедуры. 

EXECUTE 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 параметр, выбранная строка в истории, которая была актуальной в указанный момент времени, и новая версия строки в текущей таблице после операции восстановления:

Снимок экрана: сценарий восстановления с условием времени.

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

Схема, показывающая, как можно автоматизировать процесс.

Связанные материалы

  • Last updated on