Aracılığıyla paylaş

Zamana bağlı tabloları kullanmaya başlama

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

Zamana bağlı tablolar, ö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.

WebSiteUserinfo örnek tablosu için tablo şeması diyagramı.

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), Visual Studio Code için mssql uzantısı veya başka bir Transact-SQL geliştirme aracı kullanarak bu eylemi gerçekleştirin.

Important

Her zaman SQL Server Management Studio'nun en son sürümünü kullanın.

Yeni tablo oluşturma

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

    SSMS'den Yeni Sistem Sürümüyle Versiyonlanan Tablo seçeneğinin ekran görüntüsü.

  • 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:

    Yeni Öğe Ekle iletişim kutusu ve Geçici Tablo System-Versioned seçeneğinin seçili olduğu SSMS'nin ekran görüntüsü.

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

Varsayılan geçmiş tablosu

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.

Note

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

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.

Nesne Gezgini'ni ve geçmiş tablosunu gösteren SQL Server Management Studio'dan ekran görüntüsü.

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

Şimdi tablonun zaten var olduğu WebsiteUserInfo 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 izlemeyi kullanmak için, bir kullanıcı web sitesindeki oturumunu her sonlandırışında sütunu PagesVisited 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 otomatik olarak işlenir. Aşağıdaki diyagramda, geçmiş verilerinin her güncelleştirmede nasıl oluşturulduğu gösterilmektedir.

Zamana bağlı tablo mimarisinin diyagramı.

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 StDevVisitedPages
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:

Zaman içindeki ziyaret edilen sayfaların grafiği, zamansal tablo geçmişi tablo verilerine göre.

Tablo şemasını geliştirme

Genellikle, uygulama geliştirmeyi yaparken zamana bağlı tablo şemasını değiştirmeniz gerekir. Bunun için normal ALTER TABLE deyimlerini çalıştırın ve Azure SQL Veritabanı veya Azure SQL Yönetilen Örneği değişiklikleri geçmiş tablosuna uygun şekilde aktarır.

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. Zamana bağlı tabloda geçmiş verileri yönetmek için aşağıdaki yaklaşımları göz önünde bulundurun:

Remarks

Doku ve Doku SQL veritabanına yansıtmak için yapılandırılmış her iki Azure SQL Veritabanı de zamansal tablolar oluşturabilirsiniz, ancak ilgili geçmiş tabloları Fabric OneLake'e yansıtılamaz. Zamansal tablolarda bayrağı ayarlamayla SYSTEM_VERSIONING ilgili belirli davranışlar için bkz . Sistem sürümüne sahip bir zamana bağlı tablo oluşturma.

İlgili içerik

  • Last updated on