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

Статья
06/02/2023

Применимо к:Управляемому экземпляру Базы данных SQL Azure SQL Azure

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

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

Как только политика хранения определяется, База данных SQL Azure и Управляемый экземпляр SQL Azure начинают регулярно проверять, подходят ли исторические строки для автоматической очистки данных. Идентификация совпадающих строк и их удаление из таблицы журналов осуществляется прозрачно, в рамках фоновой задачи, которая планируются и выполняются системой. Условие устаревания строк в таблицах журналов проверяется по столбцу, представляющему окончание периода SYSTEM_TIME. Например, если срок хранения составляет шесть месяцев, для очистки подходят строки, отвечающие следующему условию:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

В приведенном выше примере мы предположили, что столбец ValidTo соответствует окончанию периода SYSTEM_TIME.

Настройка политики хранения

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

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Флаг базы данных is_temporal_history_retention_enabled по умолчанию установлен, однако пользователи могут его изменить с помощью инструкции ALTER DATABASE. Он также автоматически получает значение OFF (Выкл.) после восстановления до точки во времени. Чтобы включить очистку хранения журналов для базы данных, выполните следующую инструкцию:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Важно!

Срок хранения для темпоральных таблиц можно настроить, даже если флаг is_temporal_history_retention_enabled имеет значение OFF, но в этом случае не будет активироваться автоматическая очистка для устаревших строк.

Политика хранения настраивается во время создания таблицы путем указания значения для параметра HISTORY_RETENTION_PERIOD:

CREATE TABLE dbo.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,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

База данных SQL Azure и Управляемый экземпляр SQL Azure позволяют указать срок хранения, используя различные единицы времени: DAYS (дни), WEEKS (недели), MONTHS (месяцы) и YEARS (годы). Если параметр HISTORY_RETENTION_PERIOD опущен, это означает, что срок хранения НЕ ОГРАНИЧЕН (INFINITE). Кроме того, ключевое слово INFINITE можно использовать явным образом.

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

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Важно!

Если для параметра SYSTEM_VERSIONING задать значение OFF, значение срока хранения не сохраняется. Если для параметра SYSTEM_VERSIONING задать значение ON, не указывая явно значение HISTORY_RETENTION_PERIOD, будет установлен неограниченный (INFINITE) срок хранения.

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

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Как удаляются устаревшие строки

Процесс очистки зависит от макета индекса таблицы журналов. Следует отметить, что ограниченный срок хранения можно настроить только для таблиц журналов с кластеризованным индексом (columnstore или сбалансированным деревом). Для очистки устаревших данных во всех темпоральных таблицах с ограниченным периодом хранения создается фоновая задача. Логика очистки для кластеризованного индекса типа rowstore (сбалансированное дерево) удаляет устаревшие строки мелкими фрагментами (не более 10 000), чтобы свести к минимуму нагрузку на журнал базы данных и подсистему ввода-вывода. Несмотря на то что в логике очистки используется индекс сбалансированного дерева, порядок удаления строк, возраст которых превышает срок хранения, может не соблюдаться. В связи с этим не включайте в приложения никакие зависимости от порядка очистки.

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

Clustered columnstore retention

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

Рекомендации по выбору индекса

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

Msg 13765, уровень 16, состояние 1

Установка ограниченного срока хранения для темпоральной таблицы с системным управлением версиями temporalstagetestdb.dbo.WebsiteUserInfo завершилась сбоем, так как таблица исторических данных temporalstagetestdb.dbo.WebsiteUserInfoHistory не содержит требуемый кластеризованный индекс. Создайте в таблице исторических данных кластеризованный индекс columnstore или индекс сбалансированного дерева, начинающийся со столбца, который соответствует окончанию периода SYSTEM_TIME.

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

Msg 13766, level 16, State 1

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

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

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

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Если вы настроите ограниченный срок хранения для таблицы исторических данных с кластеризованным индексом columnstore, вы не сможете создавать дополнительные некластеризованные индексы сбалансированного дерева для этой таблицы:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Попытка выполнить приведенную выше инструкцию завершится следующей ошибкой.

Msg 13772, уровень 16, состояние 1

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

Запросы к таблицам с политикой хранения

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

На следующем рисунке показан план для простого запроса.

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

План запроса содержит дополнительный фильтр, применяемый к столбцу окончания периода (ValidTo) в операторе "Clustered Index Scan" (Сканирование кластеризованного индекса) в таблице исторических данных (выделено). В этом примере предполагается, что для таблицы WebsiteUserInfo задан срок хранения "1 MONTH" (1 месяц).

Retention query filter

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

Querying history without retention filter

Не следует основывать бизнес-логику приложения на считывании таблицы исторических данных по окончании срока хранения. В таком случае вы можете получить несогласованные или непредвиденные результаты. Мы рекомендуем использовать для анализа данных в темпоральных таблицах только темпоральные запросы с предложением FOR SYSTEM_TIME.

Дальнейшие действия

Чтобы узнать, как использовать темпоральные таблицы в приложениях, изучите статью Начало работы с темпоральными таблицами.

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