Azure SQL 데이터베이스와 Azure SQL Managed Instance에서 temporal 테이블 시작

적용 대상:Azure SQL 데이터베이스Azure SQL Managed Instance

temporal 테이블은 사용자 지정 코딩 없이 데이터의 전체 변경 기록을 추적하고 분석할 수 있는 Azure SQL 데이터베이스와 Azure SQL Managed Instance의 프로그래밍 가능 기능입니다. temporal 테이블은 시간 컨텍스트와 밀접하게 관련된 데이터를 유지하므로 저장된 사실이 특정 기간 내에서만 유효한 것으로 해석될 수 있습니다. 이러한 temporal 테이블 속성을 통해 시간 기반 분석을 효율적으로 수행하고 데이터 진화에서 인사이트를 얻을 수 있습니다.

temporal 시나리오

이 문서에서는 애플리케이션 시나리오에서 temporal 테이블을 활용하는 단계를 보여줍니다. 처음부터 개발 중인 새 웹 사이트 또는 사용자 활동 분석을 사용하여 확장하려는 기존 웹 사이트에서 사용자 활동을 추적하려고 한다고 가정해 보겠습니다. 이 간소화된 예제에서는 일정 기간 동안 방문한 웹 페이지 수가 Azure SQL 데이터베이스나 Azure SQL Managed Instance에서 호스트되는 웹 사이트 데이터베이스에서 캡처하고 모니터링해야 하는 지표라고 가정합니다. 사용자 활동 기록 분석의 목표는 웹 사이트를 다시 디자인하고 방문자에게 더 나은 환경을 제공하기 위한 입력을 얻는 것입니다.

이 시나리오에 대한 데이터베이스 모델은 매우 간단합니다. 사용자 작업 메트릭은 단일 정수 필드인 PageVisited로 표시되고 사용자 프로필에 대한 기본 정보와 함께 캡처됩니다. 또한 시간 기반 분석의 경우 각 사용자에 대해 일련의 행을 유지하며, 여기서 모든 행은 특정 기간 내에 특정 사용자가 방문한 페이지 수를 나타냅니다.

다행히 이 활동 정보를 유지하기 위해 앱에 어떠한 활동도 수행할 필요는 없습니다. temporal 테이블을 사용하면 이 프로세스가 자동화되므로 웹 사이트를 유연하게 디자인하고 데이터 분석 자체에 더 많은 시간을 할애할 수 있습니다. 이를 위해 WebSiteInfo 테이블을 임시 시스템 버전으로 구성하기만 하면 됩니다. 다음에서는 이 시나리오에서 temporal 테이블을 활용하는 정확한 단계를 설명합니다.

1단계: 임시로 테이블 구성

새 개발을 시작하거나 기존 애플리케이션을 업그레이드하는지 여부에 따라 temporal 테이블을 만들거나 temporal 특성을 추가하여 기존 temporal 테이블을 수정합니다. 일반적으로 시나리오에는 이러한 두 옵션이 혼합될 수 있습니다. SSMS(SQL Server Management Studio), SSDT(SQL Server Data Tools), Azure Data Studio 또는 기타 Transact-SQL 개발 도구를 사용하여 이러한 작업을 수행합니다.

Important

Azure SQL Database 및 Azure SQL Managed Instance에 대한 업데이트와 동기화 상태를 유지하려면 항상 최신 버전의 Management Studio를 사용하는 것이 좋습니다. SQL Server Management Studio를 업데이트합니다.

새 테이블 만들기

SSMS 개체 탐색기에서 상황에 맞는 메뉴 항목인 "새 시스템 버전 테이블"을 사용하여 임시 테이블 템플릿 스크립트로 쿼리 편집기를 열고 "템플릿 매개 변수에 대한 값 지정"(Ctrl+Shift+M)을 사용하여 템플릿을 채웁니다.

SSDT에서 새 항목을 데이터베이스 프로젝트에 추가하는 경우 "임시 테이블(시스템 버전 있음)" 템플릿을 선택합니다. 그러면 테이블 디자이너가 열리고 테이블 레이아웃을 쉽게 지정할 수 있습니다.

아래 예제와 같이 Transact-SQL 문을 직접 지정하여 임시 테이블을 만들 수도 있습니다. 모든 임시 테이블의 필수 요소는 기간 정의이며 다른 사용자 테이블에 대한 참조를 포함하는 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));

시스템 버전 임시 테이블을 만들면 기본 구성이 포함된 기록 테이블이 자동으로 만들어집니다. 기본 기록 테이블은 페이지 압축을 사용하여 기간 열(종료, 시작)에 클러스터된 B-트리 인덱스를 포함합니다. 이 구성은 임시 테이블이 사용되는 대부분의 시나리오, 특히 데이터 감사에 대해 최적화됩니다.

이 특별한 경우 오랜 기간 동안의 데이터 기록과 더 큰 데이터 집합을 사용한 시간 기반 추세 분석 수행이 목표이므로 기록 테이블을 위한 스토리지로 클러스터형 columnstore 인덱스가 선택됩니다. 클러스터형 columnstore는 분석 쿼리에 매우 적합한 압축과 성능을 제공합니다. temporal 테이블은 현재 및 temporal 테이블에서 완전히 독립적으로 인덱스를 구성할 수 있는 유연성을 제공합니다.

참고 항목

중요 비즈니스용, 범용 및 프리미엄 계층과 표준 계층 S3 이상에서 columnstore 인덱스를 사용할 수 있습니다.

다음 스크립트에서는 기록 테이블의 기본 인덱스가 클러스터형 columnstore로 변경되는 방법을 보여줍니다.

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

해당 기록 테이블이 자식 노드로 표시되는 한편, temporal 테이블은 쉽게 식별할 수 있도록 개체 탐색기에 특정 아이콘으로 표시됩니다.

기존 테이블을 임시 테이블로 변경

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단계: 정기적으로 워크로드 실행

temporal 테이블의 주요 장점은 변경 내용 추적을 수행하기 위해 웹 사이트를 변경하거나 조정할 필요가 없다는 점입니다. 만들면 temporal 테이블은 데이터를 수정할 때마다 이전 행 버전을 투명하게 유지합니다.

특정 시나리오에 대한 자동 변경 내용 추적을 활용하기 위해 사용자가 웹 사이트에서 세션을 종료할 때마다 PagesVisited 열을 업데이트하겠습니다.

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

실제 작업이 발생 했을 때 정확한 시간 및 기록 데이터가 이후 분석을 위해 유지되는 방법을 업데이트 쿼리에서 알 필요는 없습니다. 두 측면 모두 Azure SQL Database 및 Azure SQL Managed Instance에 의해 자동으로 처리됩니다. 다음 다이어그램에서는 모든 업데이트에서 기록 데이터가 생성되는 방법을 보여줍니다.

3단계: 기록 데이터 분석 수행

이제 temporal 시스템 버전 관리를 사용하도록 설정되면 기록 데이터 분석은 쿼리 하나에 불과합니다. 이 문서에서는 일반적인 분석 시나리오를 처리하는 몇 가지 예제를 제공합니다. 모든 세부 정보를 알아보려면 FOR SYSTEM_TIME 절에 도입된 다양한 옵션을 살펴봅니다.

1시간 전의 방문한 웹 페이지 수를 기준으로 정렬된 상위 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;

그래픽 시각화를 사용하면 추세와 사용 패턴을 직관적인 방식으로 매우 쉽게 표시할 수 있으므로 이 그래픽 시각화는 특히 임시 쿼리에 편리합니다.

테이블 스키마 진화

일반적으로 앱 개발 중에 temporal 테이블 스키마를 변경해야 합니다. 이를 위해 일반 ALTER TABLE 문을 실행하기만 하면 됩니다. 그러면 Azure SQL 데이터베이스나 Azure SQL Managed Instance에서 변경 내용을 기록 테이블에 적절하게 전파합니다. 다음 스크립트에서는 추적을 위해 추가 특성을 추가하는 방법을 보여줍니다.

/*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 를 사용하여 데이터베이스(온라인 모드) 또는 데이터베이스 프로젝트(오프라인 모드)의 일부에 연결되어 있는 동안 임시 테이블 스키마를 변경합니다.

기록 데이터의 보존 제어

기록 테이블에서는 시스템 버전 임시 테이블로 일반 테이블보다 데이터베이스 크기를 늘릴 수 있습니다. 계속 증가하는 대형 기록 테이블은 순수 스토리지 비용뿐만 아니라 temporal 쿼리에 성능 세금 부과로 인해 issue가 될 수 있습니다. 따라서 기록 테이블에서 데이터를 관리하기 위한 데이터 보존 정책을 개발하는 것은 모든 임시 테이블의 수명 주기를 계획하고 관리하는 중요한 측면입니다. Azure SQL Database 및 Azure SQL Managed Instance로 temporal 테이블에서 과거 데이터를 관리하는 데 다음 방법 중 하나를 사용할 수 있습니다.

• 테이블 분할
• 사용자 지정 정리 스크립트

다음 단계

• temporal 테이블에 대한 자세한 내용은 temporal 테이블 체크 아웃을 참조하세요.