SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Temporal Table の履歴データの保存期間の管理

leave a comment

SQL Server 2016 で追加された Temporal テーブルですが、Features Supported by the Editions of SQL Server 2016 に記載されているとおり、すべてのエディションで使用することができます。

ただし、履歴側のデータの保有期間を定めたい場合は、システム バージョン管理されたテンポラル テーブルの履歴データの保有期間管理 に書かれているような履歴テーブルのデータ削除を実装する必要がありますので、注意点を軽くまとめてみたいと思います。

現状、保存期間を定める方法はないため、基本的にはデータ削除をする仕組みを考える必要があります。

履歴テーブルの削除ですが、単純な DELETE 文では削除することができません。
これは、Temporal Table として指定している履歴テーブルについては DELETE が実行できないためです。

そのため、DELETE 文で削除を実施したい場合には、一度 Temporal を無効にしてから削除をこなう必要があります。


BEGIN TRAN
EXEC ('ALTER TABLE [dbo].[TemporalTable] SET (SYSTEM_VERSIONING = OFF)')
EXEC ('DELETE FROM  [dbo].[TemporalTableHistory]  WHERE SysEndTime <= DATEADD(dd, 2, GETDATE())')
EXEC ('ALTER TABLE &#91;dbo&#93;.&#91;TemporalTable&#93; SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory,  DATA_CONSISTENCY_CHECK = OFF ))')
COMMIT TRAN
&#91;/sourcecode&#93;
</pre>
</div>

<p>&#160;</p>

<p>このようなクエリを実行することで、削除中のデータの変更を防止したうえで、データを削除することができます。
  <br />ただし、履歴に大量のデータが格納されているような状態ですとこのような削除を行うと、処理に時間がかかってしまい、オンラインに影響が出る可能性があります。</p>

<p>そのため、瞬時にデータの削除を実施したい場合には、Enterprise Edition で使用することができるパーティショニングと組み合わせる必要があります。</p>

<p>以下のようなクエリで、履歴側のテーブルをパーティショニングし、パーティショニングしたテーブルをスイッチするためのアーカイブテーブルを作成します。</p>

<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:42fc5b66-2c25-4f4b-8028-09c4f5e3ff5e" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

IF DB_ID('DemoDB') IS NULL
	CREATE DATABASE DemoDB
GO

USE DemoDB
GO

-- テスト用テーブルの作成
IF OBJECT_ID('TemporalTable_Partition') IS NOT NULL
BEGIN
    ALTER TABLE TemporalTable_Partition SET (SYSTEM_VERSIONING = OFF)
	DROP TABLE TemporalTable_Partition
END
GO

-- テスト用テーブルの作成
IF OBJECT_ID('TemporalTableHistory_Partition') IS NOT NULL
	DROP TABLE TemporalTableHistory_Partition
GO

IF OBJECT_ID('TemporalTableHistory_Archive') IS NOT NULL
	DROP TABLE TemporalTableHistory_Archive
GO


DROP PARTITION SCHEME PS_TemporalTableHistory_Partition
DROP PARTITION FUNCTION PF_TemporalTableHistory_Partition
GO

-- 履歴用テーブルの作成(SysEndTime でパーティショニング)
CREATE TABLE TemporalTableHistory_Partition(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL, 
SysEndTime datetime2 NOT NULL
)
GO

CREATE PARTITION FUNCTION [PF_TemporalTableHistory_Partition](datetime2)
AS RANGE RIGHT FOR VALUES
(N'2016-01-01',N'2017-01-01',N'2018-01-01')
GO

CREATE PARTITION SCHEME [PS_TemporalTableHistory_Partition]
AS PARTITION [PF_TemporalTableHistory_Partition]  ALL TO ([PRIMARY])
GO

-- ベーステーブルの作成
CREATE TABLE TemporalTable_Partition(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL , 
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL ,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory_Partition))
GO


-- 履歴テーブルにインデックスを設定 (履歴テーブルに設定後にインデックスを作成しないと消えてしまっていたので注意)
CREATE CLUSTERED INDEX [CIX_TemporalTableHistory] ON [dbo].[TemporalTableHistory_Partition]  
(  
[SysEndTime] ASC,  
[SysStartTime] ASC  
) 
WITH (DATA_COMPRESSION = PAGE)
ON [PS_TemporalTableHistory_Partition] ([SysEndTime])  
GO

-- 履歴テーブルアーカイブ用
CREATE TABLE TemporalTableHistory_Archive(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL, 
SysEndTime datetime2 NOT NULL
) 
GO
CREATE CLUSTERED INDEX [CIX_TemporalTableArchive] ON [dbo].[TemporalTableHistory_Archive]  
(  
[SysEndTime] ASC,  
[SysStartTime] ASC  
) 
WITH (DATA_COMPRESSION = PAGE)
GO

SELECT
    OBJECT_NAME(sp.object_id)
    , si.name
    , sp.partition_number
    , sp.rows
    , sp.data_compression_desc
    , si.type_desc
FROM
    sys.partitions sp
    LEFT JOIN
        sys.indexes as si
        ON
        si.object_id = sp.object_id
        AND
        si.index_id = sp.index_id
WHERE
    sp.object_id IN (OBJECT_ID(N'dbo.TemporalTableHistory_Partition'))
GO

SELECT * 
FROM 
	sys.partition_range_values prv
	INNER JOIN
	sys.partition_functions pf
	ON 
	prv.function_id = pf.function_id

この状態で以下のようなクエリを流すことで、データの削除をパーティショニングのスイッチにより実施することができます。

USE DemoDB
GO

-- テストデータの確認
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) 
VALUES(NEWID(), NEWID(), N'AAAAAA'), 
(NEWID(), NEWID(), N'AAAAAA'), 
(NEWID(), NEWID(), N'AAAAAA'), 
(NEWID(), NEWID(), N'AAAAAA')

WAITFOR DELAY '00:00:01'

BEGIN TRAN
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
COMMIT TRAN

WAITFOR DELAY '00:00:01'

BEGIN TRAN
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
COMMIT TRAN

WAITFOR DELAY '00:00:01'
GO

UPDATE TemporalTable_Partition SET Col2 = NEWID()
GO


SELECT *,$PARTITION.PF_TemporalTableHistory_Partition(SysEndTime) FROM [TemporalTableHistory_Partition]
SELECT * FROM [TemporalTableHistory_Archive]
GO

-- パーティションのスイッチ
ALTER TABLE [dbo].[TemporalTableHistory_Partition] 
SWITCH PARTITION 2
TO [dbo].[TemporalTableHistory_Archive] 
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE)) 
GO


ALTER PARTITION FUNCTION [PF_TemporalTableHistory_Partition]()   
MERGE RANGE(N'2016-01-01')  
GO

SELECT *,$PARTITION.PF_TemporalTableHistory_Partition(SysEndTime) FROM [TemporalTableHistory_Partition]
SELECT * FROM [TemporalTableHistory_Archive]
GO

TRUNCATE TABLE [TemporalTableHistory_Archive]
GO
SELECT * FROM [TemporalTableHistory_Archive]

 

SQL Database であれば、パフォーマンスレベルに関係なく、パーティショニングが使用できたはずですので、この辺は柔軟に実施できるはずですが、オンプレミスの SQL Server を使用する場合には、履歴側のデータを保有期間と削除方法は、意識しておくと良いかと。

Written by masayuki.ozawa

7月 18th, 2016 at 12:41 am

Posted in SQL Server

Tagged with ,

Leave a Reply

*