Начало работы с темпоральными таблицами в Базе данных 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.
Создание новой таблицы
Используйте пункт контекстного меню "New System-Versioned Table" (Новая таблица с системным управлением версиями) в обозревателе объектов SSMS, чтобы открыть редактор запросов с шаблоном сценария временной таблицы, а затем щелкните "Указать значения для параметров шаблона" (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 доступны на уровнях "Критически важный для бизнеса", "Общего назначения" и "Премиум", а также на стандартном уровне (S3 и выше).
В следующем сценарии показано, как индекс по умолчанию в таблице журнала можно изменить на кластеризованный индекс 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 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;
Графическое представление особенно удобно для временных запросов, так как можно отобразить тенденции и закономерности использования доступным и интуитивно понятным способом.
Развитие схемы таблицы
Как правило, во время разработки приложения приходится менять схему временной таблицы. Для этого просто выполните обычные инструкции 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 доступны следующие подходы для управления данными журнала в темпоральной таблице:
Следующие шаги
- Дополнительные сведения о темпоральных таблицах см. в статье Темпоральные таблицы.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по