Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği'nde zamana bağlı tabloları kullanmaya başlama

Şunlar için geçerlidir: Azure SQL Veritabanı Azure SQL Yönetilen Örneği

Zamana bağlı tablolar, Azure SQL Veritabanı'nın ve Azure SQL Yönetilen Örneği özel kodlamaya gerek kalmadan verilerinizdeki değişikliklerin tam geçmişini izlemenize ve analiz etmenize olanak tanıyan bir programlama özelliğidir. Zamana bağlı tablolar, depolanan olguların yalnızca belirli bir süre içinde geçerli olarak yorumlanabilmesi için verileri zaman bağlamı ile yakından ilişkili tutar. Zamana bağlı tabloların bu özelliği, verimli zaman tabanlı analize ve veri evriminden içgörüler elde etmenize olanak tanır.

Zamana bağlı senaryo

Bu makalede, bir uygulama senaryosunda zamana bağlı tabloları kullanma adımları gösterilmektedir. Sıfırdan geliştirilen yeni bir web sitesinde veya kullanıcı etkinlik analiziyle genişletmek istediğiniz mevcut bir web sitesinde kullanıcı etkinliğini izlemek istediğinizi varsayalım. Bu basitleştirilmiş örnekte, belirli bir süre boyunca ziyaret edilen web sayfalarının sayısının, Azure SQL Veritabanı veya Azure SQL Yönetilen Örneği üzerinde barındırılan web sitesi veritabanında yakalanması ve izlenmesi gereken bir gösterge olduğunu varsayıyoruz. Kullanıcı etkinliğinin geçmiş analizinin amacı, web sitesini yeniden tasarlamak ve ziyaretçiler için daha iyi bir deneyim sağlamak için girişler almaktır.

Bu senaryonun veritabanı modeli çok basittir. Kullanıcı etkinliği ölçümü PageVisited adlı tek bir tamsayı alanıyla temsil edilir ve kullanıcı profilindeki temel bilgilerle birlikte yakalanır. Buna ek olarak, zamana dayalı analiz için her kullanıcı için bir dizi satır tutarsınız; burada her satır belirli bir kullanıcının belirli bir süre içinde ziyaret ettiği sayfa sayısını temsil eder.

Şema

Neyse ki, bu etkinlik bilgilerini korumak için uygulamanıza herhangi bir çaba harcamanız gerekmez. Geçici tablolarda bu işlem otomatikleştirilir ve web sitesi tasarımı sırasında tam esneklik ve veri analizinin kendisine odaklanmak için daha fazla zaman sağlar. Yapmanız gereken tek şey, tablonun zamana bağlı sistem sürümü olarak yapılandırıldığındanWebSiteInfo emin olmaktır. Bu senaryoda zamana bağlı tabloları kullanmak için tam adımlar aşağıda açıklanmıştır.

1. Adım: Tabloları zamansal olarak yapılandırma

Yeni geliştirme başlatıp başlatmadığınıza veya mevcut uygulamayı yükseltip yükseltmediğinize bağlı olarak, zamana bağlı tablolar oluşturur veya zamansal öznitelikler ekleyerek mevcut tabloları değiştirirsiniz. Genel olarak senaryonuz bu iki seçeneğin bir karışımı olabilir. Bu eylemi SQL Server Management Studio (SSMS), SQL Server Veri Araçları (SSDT), Azure Data Studio veya başka bir Transact-SQL geliştirme aracını kullanarak gerçekleştirin.

Önemli

Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği güncelleştirmeleriyle eşitlenmiş durumda kalmak için her zaman En son Management Studio sürümünü kullanmanız önerilir. SQL Server Management Studio’yu güncelleyin.

Yeni tablo oluşturma

SSMS Nesne Gezgini'de "Yeni System-Versioned Tablosu" bağlam menü öğesini kullanarak sorgu düzenleyicisini zamana bağlı tablo şablonu betiğiyle açın ve ardından şablonu doldurmak için "Şablon Parametreleri için Değerleri Belirtin" (Ctrl+Shift+M) kullanın:

SSMSNewTable

SSDT'de, veritabanı projesine yeni öğeler eklerken "Geçici Tablo (Sistem Sürümü oluşturulmuş)" şablonunu seçin. Bu, tablo tasarımcısını açar ve tablo düzenini kolayca belirtmenize olanak tanır:

SSDTNewTable

Aşağıdaki örnekte gösterildiği gibi Transact-SQL deyimlerini doğrudan belirterek de zamansal tablo oluşturabilirsiniz. Her geçici tablonun zorunlu öğelerinin PERIOD tanımı ve geçmiş satır sürümlerini depolayacak başka bir kullanıcı tablosuna başvuru içeren SYSTEM_VERSIONING yan tümcesi olduğunu unutmayın:

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));

Sistem sürümüne sahip geçici tablo oluşturduğunuzda, varsayılan yapılandırmaya sahip eşlik eden geçmiş tablosu otomatik olarak oluşturulur. Varsayılan geçmiş tablosu, sayfa sıkıştırma etkinken nokta sütunlarında (bitiş, başlangıç) kümelenmiş bir B ağacı dizini içerir. Bu yapılandırma, özellikle veri denetimi için zamansal tabloların kullanıldığı senaryoların çoğu için idealdir.

Bu özel durumda, daha uzun bir veri geçmişi üzerinde ve daha büyük veri kümeleriyle zamana dayalı eğilim analizi gerçekleştirmeyi hedefliyoruz, bu nedenle geçmiş tablosunun depolama seçimi kümelenmiş bir columnstore dizinidir. Kümelenmiş columnstore analiz sorguları için çok iyi sıkıştırma ve performans sağlar. Zamana bağlı tablolar, geçerli ve zamansal tablolardaki dizinleri tamamen bağımsız olarak yapılandırma esnekliği sağlar.

Not

Columnstore dizinleri İş Açısından Kritik, Genel Amaçlı ve Premium katmanlarında ve Standart katman, S3 ve üzeri katmanlarda kullanılabilir.

Aşağıdaki betik, geçmiş tablosundaki varsayılan dizinin kümelenmiş columnstore olarak nasıl değiştirilebileceğini gösterir:

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

Zamana bağlı tablolar Nesne Gezgini daha kolay tanımlanması için belirli bir simgeyle gösterilirken, geçmiş tablosu bir alt düğüm olarak görüntülenir.

AlterTable

Mevcut tabloyu zamana bağlı olarak değiştirme

WebsiteUserInfo tablosunun zaten mevcut olduğu ancak değişikliklerin geçmişini tutmak için tasarlanmadığı alternatif senaryoyu ele alalım. Bu durumda, aşağıdaki örnekte gösterildiği gibi mevcut tabloyu zamana bağlı olacak şekilde genişletebilirsiniz:

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. Adım: İş yükünüzü düzenli olarak çalıştırma

Zamana bağlı tabloların temel avantajı, değişiklik izleme gerçekleştirmek için web sitenizi herhangi bir şekilde değiştirmenize veya ayarlamanıza gerek olmamasıdır. Oluşturulduktan sonra, verilerinizde değişiklik yaptığınızda zamana bağlı tablolar önceki satır sürümlerini saydam bir şekilde kalıcı hale getirir.

Bu senaryo için otomatik değişiklik izlemeden yararlanmak için, bir kullanıcı web sitesindeki oturumunu her sonlandırışında Görüntülenen Sayfalar sütununu güncelleştirelim:

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

Güncelleştirme sorgusunun gerçek işlemin ne zaman gerçekleştiğini veya geçmiş verilerinin gelecekteki analizler için nasıl korunacağını tam olarak bilmesi gerekmeyen bir durum olduğunu fark etmek önemlidir. Her iki özellik de Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği tarafından otomatik olarak işlenir. Aşağıdaki diyagramda, her güncelleştirmede geçmiş verilerinin nasıl oluşturulduğu gösterilmektedir.

TemporalArchitecture

3. Adım: Geçmiş veri analizi gerçekleştirme

Zamansal sistem sürümü oluşturma etkinleştirildiğinde geçmiş veri analizi yalnızca bir sorgu uzağınızdadır. Bu makalede, yaygın analiz senaryolarını ele alan birkaç örnek sağlayacağız. Tüm ayrıntıları öğrenmek için FOR SYSTEM_TIME yan tümcesiyle sunulan çeşitli seçenekleri keşfedin.

Bir saat önce ziyaret edilen web sayfalarının sayısına göre sıralanmış ilk 10 kullanıcıyı görmek için şu sorguyu çalıştırın:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

Bir gün önce, bir ay önce veya geçmişte istediğiniz herhangi bir noktada site ziyaretlerini analiz etmek için bu sorguyu kolayca değiştirebilirsiniz.

Önceki gün için temel istatistiksel analiz gerçekleştirmek için aşağıdaki örneği kullanın:

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

Belirli bir kullanıcının etkinliklerini belirli bir süre içinde aramak için CONTAINED IN yan tümcesini kullanın:

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;

Grafik görselleştirme, eğilimleri ve kullanım desenlerini sezgisel bir şekilde çok kolay bir şekilde gösterebildiğiniz için özellikle geçici sorgular için kullanışlıdır:

TemporalGraph

Gelişen tablo şeması

Genellikle, uygulama geliştirmeyi yaparken zamansal tablo şemasını değiştirmeniz gerekir. Bunun için, normal ALTER TABLE deyimlerini çalıştırmanız ve Veritabanı'nı Azure SQL veya değişiklikleri geçmiş tablosuna uygun şekilde yaydığını Azure SQL Yönetilen Örneği. Aşağıdaki betik, izleme için nasıl ek öznitelik ekleyebileceğinizi gösterir:

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

Benzer şekilde, iş yükünüz etkinken sütun tanımını değiştirebilirsiniz:

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

Son olarak, artık ihtiyacınız olmayan bir sütunu kaldırabilirsiniz.

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

Alternatif olarak, veritabanına bağlıyken (çevrimiçi mod) veya veritabanı projesinin bir parçası olarak (çevrimdışı mod) geçici tablo şemasını değiştirmek için en son SSDT'yi kullanın.

Geçmiş verilerin elde tutulmasını denetleme

Sistem sürümüne sahip zamana bağlı tablolarda, geçmiş tablosu veritabanı boyutunu normal tablolardan daha fazla artırabilir. Büyük ve sürekli büyüyen bir geçmiş tablosu, hem saf depolama maliyetleri hem de zamana bağlı sorgulamaya performans vergisi uygulama nedeniyle sorun haline gelebilir. Bu nedenle, geçmiş tablosundaki verileri yönetmek için bir veri saklama ilkesi geliştirmek, her zamansal tablonun yaşam döngüsünü planlamanın ve yönetmenin önemli bir yönüdür. Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği ile, zamana bağlı tabloda geçmiş verileri yönetmek için aşağıdaki yaklaşımlara sahipsiniz:

Sonraki adımlar

  • Zamana bağlı tablolar hakkında daha fazla bilgi için bkz. Geçici Tablolar.