Начало работы с темпоральными таблицами в Базе данных SQL Azure и Управляемом экземпляре SQL Azure

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

схема

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

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

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

Важно!

Чтобы обеспечить синхронизацию с обновлениями Базы данных SQL Azure и Управляемого экземпляра SQL Azure, рекомендуется всегда использовать последнюю версию Management Studio. Обновите среду SQL Server Management Studio.

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

используйте пункт контекстного меню "создать таблицу System-Versioned" в обозревателе объектов SSMS, чтобы открыть редактор запросов с помощью скрипта шаблона темпоральной таблицы, а затем для заполнения шаблона используйте команду "указать значения для параметров шаблона" (Ctrl + Shift + M):

SSMSNewTable

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

SSDTNewTable

Временную таблицу также можно создать, непосредственно указав инструкции 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 доступны на уровнях "Критически важный для бизнеса", "Общего назначения" и "Премиум", а также на стандартном уровне (S3 и выше).

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

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

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

AlterTable

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

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

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

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

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

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

TemporalArchitecture

Шаг 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 StDevViistedPages
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;

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

TemporalGraph

Развитие схемы таблицы

Как правило, во время разработки приложения приходится менять схему временной таблицы. Для этого просто выполните обычные инструкции 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 доступны следующие подходы для управления данными журнала в темпоральной таблице:

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