Поделиться через

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

Область применения:База данных SQL AzureУправляемый экземпляр SQL Azureбаза данных SQL в Fabric

Темпоральные таблицы могут увеличить размер базы данных больше, чем обычные таблицы, особенно если вы храните исторические данные в течение длительного периода времени. Поэтому политика хранения для исторических данных является важной составляющей управления жизненным циклом любой темпоральной таблицы. Темпоральные таблицы в Базе данных 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 базы данных установлен на ON по умолчанию, но пользователи могут изменить его инструкцией ALTER DATABASE. Он также автоматически получает значение OFF (Выкл.) после восстановления до точки во времени. Чтобы включить очистку хранения журналов для базы данных, выполните следующую инструкцию:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Important

Вы можете настроить хранение темпоральных таблиц, даже если 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, , WEEKSMONTHSи YEARS. Если HISTORY_RETENTION_PERIOD опущено, предполагается бесконечное хранение. Вы также можете явно использовать ключевое INFINITE слово.

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

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

Important

Параметр 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 удаляет полные группы строк за один раз (обычно они содержат по миллиону строк). Это очень эффективный механизм, особенно если исторические данные формируются с высокой скоростью.

Схема хранения кластеризованного хранилища столбцов.

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

Index considerations

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

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

Избегайте перестроения кластеризованного индекса 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) в операторе кластеризованного сканирования индекса в таблице журнала (выделено). В этом примере предполагается, что для таблицы WebsiteUserInfo задан срок хранения "1 MONTH" (1 месяц).

Фильтр запросов хранения.

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

Журнал запросов без фильтра хранения.

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

Рекомендации по восстановлению до точки во времени

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

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

Если вы хотите активировать очистку темпорального хранения, выполните следующую инструкцию Transact-SQL после восстановления до точки во времени:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

  • Last updated on