Начало работы с темпоральными таблицами

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

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

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

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

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

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

Шаг 1. Настройка таблиц в качестве временных

В зависимости от того, запускаете ли вы новую разработку или обновляете существующее приложение, вы создадите темпоральные таблицы или измените существующие, добавив темпоральные атрибуты. В общем случае может потребоваться сделать и то, и другое. Выполните эти действия с помощью SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), расширения mssql для Visual Studio Code или любого другого средства разработки Transact-SQL.

Создание новой таблицы

• Используйте пункт контекстного меню New System-Versioned Table в SSMS Object Explorer, чтобы открыть редактор запросов со скриптом шаблона временной таблицы, а затем используйте Указать значения параметров шаблона (Ctrl+Shift+M), чтобы заполнить шаблон.

  

• В SSDT при добавлении новых элементов в проект базы данных выберите шаблон "Темпоральная таблица (с системным управлением версиями)". Откроется конструктор таблиц, в котором вы сможете легко указать макет таблицы.

  

• Вы также можете создать темпоральную таблицу, указав инструкции Transact-SQL напрямую, как показано в следующем примере. Обязательные элементы каждой темпоральной таблицы — PERIOD определение и SYSTEM_VERSIONING условие со ссылкой на другую пользовательскую таблицу, которая будет хранить исторические версии строк.

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

Таблица истории по умолчанию

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

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

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

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

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

Преобразование существующей таблицы во временную

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

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. Регулярный запуск рабочей нагрузки

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

Чтобы использовать автоматическое отслеживание изменений для этого конкретного сценария, давайте просто обновляем столбец PagesVisited каждый раз, когда пользователь завершает сеанс на веб-сайте:

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

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

Шаг 3. Анализ данных журнала

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

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

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

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

Чтобы выполнить простой статистический анализ за предыдущий день, используйте следующий пример.

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 StDevVisitedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

Для поиска активности конкретного пользователя в течение периода времени используйте предложение CONTAINED IN.

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;

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

Изменение схемы таблицы

Как правило, во время разработки приложений необходимо изменить схему темпоральной таблицы. Для этого достаточно регулярно выполнять ALTER TABLE инструкции, и База данных SQL Azure или Управляемый экземпляр SQL Azure автоматически передает изменения в таблицу истории.

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

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

Аналогичным образом можно изменить определение столбца при активной рабочей нагрузке.

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

Наконец, можно удалить столбец, который больше не нужен.

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

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

Управление хранением исторических данных

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

• секционирование таблиц;
• пользовательский сценарий очистки.

Замечания

В обоих База данных SQL Azure, настроенных для зеркального отображения в базе данных SQL Fabric и Fabric, можно создавать темпоральные таблицы, но соответствующие таблицы журнала не отражаются в Fabric OneLake. Конкретные действия по настройке флага SYSTEM_VERSIONING в темпоральных таблицах см. в статье "Создание системной темпоральной таблицы".

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

• Темпоральные таблицы