Преобразование исторической таблицы в темпоральную таблицу с системным управлением версиями
Темпоральные таблицы были введены в SQL Server 2016. Чтобы воспользоваться преимуществами этой функции, вы можете начать с новой таблицы. Однако возможно преобразовать существующую таблицу с накопленной историей в новую функциональность. В этой статье мы покажем, как создавать темпоральные таблицы для обоих сценариев.
В SQL Server было много разных методов для отслеживания изменений в данных. Существуют старомодные протоколирование на основе триггеров, сбор данных изменений и отслеживание изменений. Все эти функции позволяют вам видеть, как изменились данные с разной степенью того, как далеко вы можете вернуться назад, чтобы увидеть изменения. Однако никогда не было возможности увидеть, как выглядела вся таблица в любой определенный момент времени. Это то, что делают темпоральные таблицы - они регистрируют каждое изменение, которое происходит с таблицей. Когда вы хотите запросить его в течение какого-то определенного периода времени, SQL Server выполнит тяжелую работу по получению снимка того, как данные во всей таблице выглядели в то время.
Отличное введение в то, как настроить темпоральные таблицы различными способами с различными ограничениями, можно найти здесь. В этой статье вы узнаете, как настроить управление версиями при создании новой таблицы и как преобразовать таблицу с существующей историей в темпоральную таблицу с системным управлением версиями.
Создание темпоральной таблицы с системным управлением версиями
Управление версиями таблиц может быть создано на совершенно новых или существующих таблицах. Таблица истории или таблица, в которой регистрируются изменения, может быть:
- Совершенно новая «анонимная» таблица без указания имени, в этом случае SQL Server создает таблицу и назначает имя,
- Таблица «по умолчанию» с именем по вашему желанию,
- Существующая таблица с данными, которые вы теперь хотите использовать в качестве журнала истории.
Для начала создайте совершенно новую таблицу и версионируйте ее.
|
CREATE TABLE [dbo].[Region] (RegionID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Region PRIMARY KEY CLUSTERED, RegionDescription VARCHAR(100) NULL, StartDateTime datetime2 generated always as row start NOT NULL, EndDateTime datetime2 generated always as row end NOT NULL, PERIOD FOR SYSTEM_TIME (StartDateTime, EndDateTime)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History)); INSERT INTO [dbo].[Region] (RegionDescription) VALUES ('North USA') INSERT INTO [dbo].[Region] (RegionDescription) VALUES ('South USA'), ('NorthEast USA') SELECT * FROM [dbo].[Region] |
Легко заметить, что столбец StartDateTime заполнен текущей датой и временем в UTC, а EndDateTime является максимальным значением, которое можно указать для типа данных datetime2. Они не указаны в операторах вставки и не имеют определенных значений по умолчанию, они заполняются автоматически. Обратите внимание на синтаксис в операторе CREATE TABLE, который всегда генерируется как начало строки и генерируется всегда как конец строки.
Теперь посмотрим, что было зарегистрировано в таблице истории:
|
SELECT * FROM [dbo]. [Region_History] |
Это ничего не возвращает. Это связано с тем, что ведение журнала ограничено обновлениями и удалениями и не регистрирует вставки.
Теперь, если вы запустите обновление, а затем посмотрите на таблицу истории, вы увидите, что предыдущая строка была зарегистрирована. Значения StartDateTime и EndDateTime точно определяют, когда эта строка была активной.
|
UPDATE [dbo].[Region] SET RegionDescription = 'NorthEast US' WHERE RegionDescription = 'NorthEast USA' SELECT * FROM [dbo].[Region_History] |
Если вы посмотрите на основную таблицу для той же строки, вы увидите, что у нее есть новая дата начала, которая совпадает с датой, когда предыдущая версия строки была удалена.
|
SELECT * FROM [dbo].[Region] |
Удаление строки также работает аналогично. Когда дата окончания удаленной строки совпадает с датой, когда она была удалена, и в основной таблице нет соответствующей строки.
|
DELETE FROM [dbo].[Region] WHERE RegionID = 3 SELECT * FROM [dbo].[Region_History] WHERE RegionID = 3 |
Добавление управления версиями существующей таблицы
Следующий сценарий заключается в переводе существующей таблицы истории в управление версиями. Отслеживание изменений на основе триггера все еще является очень распространенным и простым в реализации процессом, используемым во многих местах. В этом примере рассматривается простой способ реализации и использование одной и той же таблицы без изменения или удаления каких-либо данных для реализации контроля версий.
|
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Region')) BEGIN ALTER TABLE dbo.Region SET (system_versioning = off) DROP TABLE dbo.Region DROP TABLE dbo.Region_History END go CREATE TABLE [dbo].[Region]( [RegionID] [int] IDENTITY(1,1) NOT NULL, [RegionDescription] [varchar](100) NULL, [CreateUser] [nvarchar](100) NOT NULL default (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())), [CreateDate] DateTime NOT NULL default getdate(), [UpdateUser] [nvarchar](100) NOT NULL default (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())), [UpdateDate] DateTime NOT NULL default getdate() CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED ( [RegionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) CREATE TABLE [dbo].[Region_History]( [RegionHistoryID] [int] IDENTITY(1,1) NOT NULL, [RegionID] [int] NOT NULL, [RegionDescription] [varchar](100) NULL, [CreateUser] [nvarchar](100) NOT NULL , [CreateDate] DateTime NOT NULL , [UpdateUser] [nvarchar](100) NOT NULL default (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())), [UpdateDate] DateTime NOT NULL default getdate() ) GO |
Существует два простых триггера, один для обновлений и один для удаления, чтобы отслеживать изменения в таблице.
|
CREATE TRIGGER [dbo].[Region_Update] on [dbo].[Region] AFTER UPDATE AS BEGIN INSERT INTO dbo.Region_History (RegionId,RegionDescription,CreateUser,CreateDate, UpdateUser,UpdateDate) SELECT i.RegionId,i.RegionDescription,i.CreateUser,i.CreateDate, SUSER_SNAME(), getdate() from dbo.Region r inner join inserted i on r.RegionID=i.RegionID END GO CREATE TRIGGER [dbo].[Region_Delete] ON [dbo].[Region] AFTER DELETE AS INSERT INTO [dbo].[Region_History] ([RegionID],[RegionDescription],[CreateUser], [CreateDate],UpdateUser,UpdateDate ) SELECT [RegionID],[RegionDescription],[CreateUser],[CreateDate], SUSER_SNAME(), getdate() FROM DELETED GO --Now insert data into the main table. INSERT INTO [dbo].[Region] (RegionDescription) values ('Northeast') ,('Southwest') ,('West') ,('Southeast') ,('Midwest'); SELECT * FROM [dbo].[Region] |
Преднамеренно изменяйте одни и те же записи несколько раз, чтобы таблица истории содержала приличный объем данных. Этот сценарий займет около 10 минут, пока вы воссоздаете таблицу истории с несколькими обновлениями с разными временными метками.
|
DECLARE @counter INT SELECT @COUNTER = 100 WHILE @counter > 0 BEGIN UPDATE [dbo].[Region] SET RegionDescription = 'NorthEast' WHERE RegionDescription = 'Northeast' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'Southwest ' WHERE RegionDescription = 'Southwest' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'Southeast ' WHERE RegionDescription = 'Southeast' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'Midwest ' WHERE RegionDescription = 'Midwest' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'MidWest' WHERE RegionDescription = 'Midwest ' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'SouthWest' WHERE RegionDescription = 'Southwest ' WAITFOR DELAY '00:00:01' UPDATE [dbo].[Region] SET RegionDescription = 'SouthEast' WHERE RegionDescription = 'Southeast ' SELECT @counter = @counter - 1 END |
Также удалите пару записей из основной таблицы.
|
DELETE FROM [dbo].[Region] WHERE RegionDescription = 'West' DELETE FROM [dbo].[Region] WHERE RegionDescription = 'MidWest' SELECT * FROM dbo.Region |
Вы увидите 702 строки в таблице истории.
|
SELECT * FROM dbo.Region_History |
Цель состоит в том, чтобы перевести эти две таблицы во временные таблицы, сохранив эти данные без изменений и допуская традиционные запросы, а также запросы с использованием методологии временных таблиц.
В качестве первого шага добавьте даты начала и окончания в обе таблицы:
|
ALTER TABLE dbo.Region ADD [StartDate] [datetime2] NOT NULL DEFAULT (getdate()), [EndDate] [datetime2] NOT NULL DEFAULT (convert(datetime2, '9999-12-31 23:59:59.9999999')) ALTER TABLE dbo.Region_History ADD [StartDate] [datetime2] NOT NULL DEFAULT (getdate()), [EndDate] [datetime2] NOT NULL DEFAULT (convert(datetime2, '9999-12-31 23:59:59.9999999')) |
Структуры таблицы истории и основной таблицы должны быть идентичны для включения управления версиями. Поскольку в таблице истории есть один столбец regionhistoryid, которого нет в основной таблице, вы можете либо избавиться от него в таблице истории, либо добавить его в основную таблицу. Избавление от этого будет означать, что у таблицы истории нет ключа для более старого метода. Это не идеально, если вы хотите запросить более старые данные с помощью этого метода. Вместо этого добавьте его в основную таблицу. Вы не будете использовать его, просто добавьте его, чтобы обеспечить соответствие для этой цели.
|
ALTER TABLE [dbo].[Region] ADD RegionHistoryId int; |
Следующим шагом является добавление периода для подключения двух новых полей в основной таблице, а затем попытка включить управление версиями.
|
ALTER TABLE dbo.Region ADD PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]) ALTER TABLE dbo.Region SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, DATA_CONSISTENCY_CHECK = ON)) |
Это возвращает ошибку, как показано ниже:
SQL Server не допускает использование столбцов идентификаторов в таблице истории. Свойство идентичности должно быть удалено, но данные в этом столбце необходимы. Чтобы решить эту проблему, создайте другой столбец, переместите туда данные, удалите этот столбец и переименуйте новый столбец старым именем.
|
ALTER TABLE [dbo].[Region_History] ADD RegionHistId int; GO UPDATE [dbo].[region_history] SET regionhistid = regionhistoryid; GO ALTER TABLE [dbo].[region_history] DROP COLUMN regionhistoryid; GO EXEC sp_RENAME 'dbo.region_history.RegionHistid' , 'RegionHistoryID', 'COLUMN'; GO |
Теперь, когда столбец идентификаторов удален из таблицы истории, попробуйте снова включить управление версиями. На этот раз вы получите еще одну ошибку.
|
ALTER TABLE dbo.Region SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, DATA_CONSISTENCY_CHECK = ON)) |
Проверка непротиворечивости данных запускает DBCC CHECKCONSTRAINT изнутри и выявляет проблемы, если ограничения не проверяются. Значением по умолчанию для нового столбца EndDate является максимальная дата системы, которая, конечно же, находится в будущем.
Есть несколько способов решить эту проблему. Один из способов - включить управление версиями, но пропустить проверки. Не запускайте этот код, но имейте его ввиду:
|
ALTER TABLE dbo.Region SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, DATA_CONSISTENCY_CHECK = OFF)) |
По сути, это означает, что данные, которые в данный момент находятся в таблице истории, не могут быть запрошены с помощью методов, используемых для запроса временных таблиц. Вы также можете столкнуться с проблемами при запросе любых новых данных, потому что старые данные плохие и имеют периоды перекрытия. Этот метод не рекомендуется, так как он несет большой риск.
Вместо этого лучше исправить данные за периоды времени, чтобы они соответствовали ожидаемым при использовании методологий временных таблиц. Каждая запись истории должна иметь дату начала и окончания в прошлом, в течение которого строка была действительной. Дата начала каждой записи истории должна совпадать с датой окончания предшествующей, а дата окончания должна быть датой начала следующей и т. д. Дата начала основной записи таблицы должна совпадать с последней датой окончания ее истории. Очистка данных таким образом гарантирует отсутствие временных разрывов.
Чтобы выполнить очистку, выполните следующие три шага:
Шаг 1: Найдите первую запись истории для каждой строки в основной таблице и установите дату начала, равную дате создания и дате окончания, равной дате обновления.
|
UPDATE dbo.region_history SET startdate = createdate, enddate = updatedate --select a.regionid,a.regionhistoryid,b.slno FROM dbo.region_history a INNER JOIN (SELECT regionid,regionhistoryid, RANK() OVER (PARTITION BY regionid ORDER BY regionhistoryid) AS slno FROM dbo.region_history) b ON a.regionid = b.regionid AND a.regionhistoryid = b.regionhistoryid AND b.slno = 1 |
Шаг 2: Найдите записи, которые датированы после первой и обновите их последовательно, дата начала каждой записи должна быть равна дате окончания предыдущей.
|
UPDATE dbo.region_history SET startdate = b.priorupdatedate, enddate = a.updatedate --select a.*,b.priorupdatedate, b.slno FROM dbo.region_history a INNER JOIN (SELECT regionid,regionhistoryid,updatedate, LAG(updatedate) OVER (PARTITION BY RegionId order by updatedate) AS priorupdatedate, RANK() OVER (PARTITION BY regionid ORDER BY regionhistoryid) AS slno FROM dbo.region_history) b ON a.regionid = b.regionid AND a.regionhistoryid = b.regionhistoryid AND b.slno > 1 and b.priorupdatedate IS NOT NULL |
Шаг 3: Последняя дата самой последней записи в истории должна совпадать с начальной датой в основной таблице той же записи. Помните, что у вас все еще включены старые триггеры, поэтому любые изменения, внесенные в основную таблицу, будут снова зарегистрированы. Итак, сначала вы должны сбросить эти триггеры. Вы также должны временно удалить период.
|
DROP TRIGGER [dbo].[Region_Delete] DROP TRIGGER [dbo].[Region_Update] ALTER TABLE dbo.region DROP PERIOD FOR system_time; |
Затем запустите обновление, чтобы связать историю с таблицей истории и главной таблицей.
|
WITH RegionCTE AS ( SELECT RegionID, maxupdatedate = MAX(updatedate) FROM dbo.Region_History GROUP BY regionid ) UPDATE dbo.region SET startdate = b.maxupdatedate, enddate = '9999-12-31 23:59:59.9999999' --select a.*,b.priorstartdate FROM dbo.region a INNER JOIN RegionCTE b ON a.regionid = b.regionid |
На этом этапе вы можете удалить столбцы createuser, createate, updatedate, updateuser и regionhistoryid из обеих таблиц. Если у вас есть более старые запросы, использующие эти столбцы, это может быть нежелательно.
Теперь, когда вы добавляете период обратно и включаете управление версиями, это работает как чудо. Вы также охватили все связанные с этим временные промежутки, чтобы запросы с использованием как старого метода, так и нового метода управления версиями работали одинаково.
|
ALTER TABLE dbo.Region ADD PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]) ALTER TABLE dbo.Region SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, DATA_CONSISTENCY_CHECK = ON)) SELECT * FROM dbo.Region FOR SYSTEM_TIME AS OF '2019-03-10 14:07:29.2366667' ; |
Дает результаты, как показано ниже:
|
DECLARE @ADayAgo datetime2 SET @ADayAgo = DATEADD (day, -2, getdate()) /*Comparison between two points in time for subset of rows*/ SELECT D_1_Ago.[RegionID], D.[RegionID], D_1_Ago.[RegionDescription], D. RegionDescription, D_1_Ago.[StartDate], D.[StartDate], D_1_Ago.[EndDate], D.[EndDate] FROM [dbo].[Region] FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago JOIN [dbo].[Region] AS D ON D_1_Ago.[RegionID] = [D].[RegionID] AND D_1_Ago.[RegionID] BETWEEN 1 and 4 ; |
(Возвращаемые результаты могут зависеть от того, когда вы выполняете этот запрос - по сравнению с тем, когда были созданы данные, поэтому используйте правильную дату для переменной @Adayago).
Преобразование ваших данных
Очистка данных, с целью привести их в соответствие с временными таблицами системной версии, может быть довольно сложной, а ваш сценарий может быть еще более сложным. Вот несколько вещей, которые нужно иметь в виду:
- Дата начала всегда должна быть меньше даты окончания в обеих таблицах.
- Если у вас есть несколько записей истории для одной родительской записи в основной таблице, даты начала и окончания также должны быть последовательными в порядке возрастания без перекрытия периодов.
- Дата окончания последней строки в таблице истории должна совпадать с датой начала активной записи в родительской таблице.
Удаление данных, которые не подчиняются этим условиям, также является возможным решением. Так как это уничтожает цель преобразования даже существующей таблицы в историю, это не рекомендуется. Вместо этого вы можете сохранить эту таблицу как есть и использовать новую таблицу для хранения истории версий.
Также следует отметить, что управление версиями таблицы не позволяет определить, кто внес изменения. Это то, что вам, возможно, придется сделать вручную, если вам нужно получить эту информацию. Этот обходной путь, основанный на триггерах, предложенный MVP Аароном Бертраном, является хорошим способом включить это.
Удаление версий
В конце концов у вас может возникнуть сценарий, в котором вам нужно удалить таблицы или полностью удалить версии. Причины могут заключаться в том, что таблица собирает слишком много истории или ее размер не является доступным.
Чтобы сделать это, вам нужно установить выключение версий системы и период отбрасывания для system_time. Вы также можете удалить столбцы даты, поскольку они не имеют особого значения, если таблица не использует их, но это необязательно.
Есть несколько шагов для этого процесса, и следующий скрипт может пригодиться.
|
DECLARE @DefaultConstraint nvarchar(200) DECLARE @Tablename nvarchar(200) DECLARE @startdatecolumnname nvarchar(200) DECLARE @enddatecolumnname nvarchar(200) SELECT @Tablename = 'dbo.Region' SELECT @startdatecolumnname = 'SysStartTime' SELECT @enddatecolumnname = 'SysEndTime' EXEC('ALTER TABLE ' + @Tablename + ' SET (system_versioning = off)') EXEC('ALTER TABLE ' + @Tablename + ' DROP PERIOD FOR system_time;') SELECT @DefaultConstraint = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@Tablename) AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = @startdatecolumnname AND object_id = OBJECT_ID(@Tablename)) IF @DefaultConstraint IS NOT NULL EXEC('ALTER TABLE ' + @Tablename + ' DROP CONSTRAINT ' + @DefaultConstraint) EXEC('ALTER TABLE ' + @Tablename + ' DROP COLUMN IF EXISTS ' + @startdatecolumnname) SELECT @DefaultConstraint = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@Tablename) AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = @enddatecolumnname AND object_id = OBJECT_ID(@Tablename)) IF @DefaultConstraint IS NOT NULL EXEC('ALTER TABLE ' + @Tablename + ' DROP CONSTRAINT ' + @DefaultConstraint) EXEC('ALTER TABLE ' + @Tablename + ' DROP COLUMN IF EXISTS ' + @enddatecolumnname) |
Резюме
Управление версиями данных является сложным процессом, и нет двух способов управления версиями, которые бы работали одинаково. Есть много ситуаций, с которыми вы можете столкнуться, если переходите от более старого метода к временным таблицам. Знание того, что ожидает SQL Server, поможет этому переходу пройти гладко. Временные таблицы являются отличной функцией и очень просты в использовании, когда мы преодолеваем препятствия при их настройке.