Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği'da 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ı 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 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, zaman tabanlı analiz için her kullanıcı için bir satır serisi tutarsınız; burada her satır belirli bir süre içinde belirli bir kullanıcının ziyaret ettiği sayfa sayısını temsil eder.

Neyse ki, bu etkinlik bilgilerini korumak için uygulamanıza herhangi bir çaba harcamanız gerekmez. Zamansal tablolarla bu işlem otomatikleştirilir ve web sitesi tasarımı sırasında tam esneklik ve veri analizinin kendisine odaklanmanız için daha fazla zaman sağlar. Yapmanız gereken tek şey, tablonun zamansal sistem sürümü olarak yapılandırıldığından WebSiteInfo emin olmaktır. Bu senaryoda zamana bağlı tabloları kullanma 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, zamansal tablolar oluşturacak veya zamana bağlı öznitelikler ekleyerek mevcut tabloları değiştireceksiniz. Genel olarak senaryonuz bu iki seçeneğin bir karışımı olabilir. 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 bu eylemi gerçekleştirin.

Yeni tablo oluşturma

SSMS Nesne Gezgini'de bağlam menü öğesini "Yeni Sistem Sürümü Oluşturulan Tablo" 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:

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 belirtmenizi sağlar:

Aşağıdaki örnekte gösterildiği gibi Transact-SQL deyimlerini doğrudan belirterek de zamansal tablo oluşturabilirsiniz. Her zamansal tablonun zorunlu öğeleri 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ümcesidir:

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ü oluşturulmuş zamana bağlı 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 etkinleştirilmiş dönem 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ığı çoğu senaryo 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ş sütun deposu analiz sorguları için 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.

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

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

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

WebsiteUserInfo tablosunun mevcut olduğu ancak değişikliklerin geçmişini tutmak için tasarlanmamış olan alternatif senaryoyu ele alalım. Bu durumda, aşağıdaki örnekte gösterildiği gibi mevcut tabloyu geçici 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

Zamansal 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 olmamanızdır. Zamansal tablolar oluşturulduktan sonra, verilerinizde değişiklik yaptığınız her seferde ö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ş verilerin gelecekteki analiz 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, geçmiş verilerinin her güncelleştirmede nasıl oluşturulduğu gösterilmektedir.

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

Zamansal sistem sürümü oluşturma etkinleştirildiğinde, geçmiş veri analizi sizden yalnızca bir sorgu uzaktadı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üne yönelik 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 aramak için, belirli bir süre içinde 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 özellikle zamansal sorgular için kullanışlıdır çünkü eğilimleri ve kullanım desenlerini sezgisel bir şekilde çok kolay bir şekilde gösterebilirsiniz:

Gelişen tablo şeması

Genellikle, uygulama geliştirmeyi yaparken zamana bağlı tablo şemasını değiştirmeniz gerekir. Bunun için normal ALTER TABLE deyimlerini çalıştırmanız yeterlidir ve Azure SQL Veritabanı veya Azure SQL Yönetilen Örneği değişiklikleri geçmiş tablosuna uygun şekilde yayılım. 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 (çevrimdışı mod) bir parçası olarak 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:

• Tablo Bölümleme
• Özel Temizleme Betiği

Sonraki adımlar

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