Mulai menggunakan tabel temporal di Azure SQL Database dan Azure SQL Managed Instance

Artikel
06/02/2023

Berlaku untuk: Azure SQL Database Azure SQL Managed Instance

Tabel Temporal adalah fitur programmability Azure SQL Database dan Azure SQL Managed Instance yang memungkinkan Anda melacak dan menganalisis riwayat lengkap perubahan dalam data Anda, tanpa perlu pengkodean khusus. Tabel Temporal menjaga data yang terkait erat dengan konteks waktu sehingga fakta yang disimpan dapat ditafsirkan sebagai valid hanya dalam periode tertentu. Properti tabel temporal ini memungkinkan analisis berbasis waktu yang efisien dan mendapatkan wawasan dari evolusi data.

Skenario temporal

Artikel ini menggambarkan langkah-langkah untuk menggunakan tabel temporal dalam skenario aplikasi. Misalkan Anda ingin melacak aktivitas pengguna di situs web baru yang sedang dikembangkan dari awal atau di situs web yang sudah ada yang ingin Anda perluas dengan analitik aktivitas pengguna. Dalam contoh yang disederhanakan ini, kami berasumsi bahwa jumlah halaman web yang dikunjungi selama periode waktu tertentu adalah indikator yang perlu ditangkap dan dipantau dalam database situs web yang dihosting di Azure SQL Database atau Azure SQL Managed Instance. Tujuan dari analisis riwayat aktivitas pengguna adalah untuk mendapatkan masukan untuk mendesain ulang situs web dan memberikan pengalaman yang lebih baik bagi pengunjung.

Model database untuk skenario ini sederhana - metrik aktivitas pengguna diwakili dengan satu bidang bilangan bulat, PageVisited, dan diambil bersama dengan informasi dasar pada profil pengguna. Selain itu, untuk analisis berbasis waktu, Anda akan menyisihkan serangkaian baris untuk setiap pengguna, dan setiap baris mewakili jumlah halaman yang dikunjungi pengguna tertentu dalam periode waktu tertentu.

Skema

Untungnya, Anda tidak perlu melakukan upaya apa pun di aplikasi untuk mempertahankan informasi aktivitas ini. Dengan tabel temporal, proses ini otomatis - memberi Anda fleksibilitas penuh selama desain situs web dan lebih banyak waktu untuk fokus pada analisis data itu sendiri. Satu-satunya hal yang harus Anda lakukan adalah memastikan bahwa tabel WebSiteInfo telah dikonfigurasi sebagai temporal system-versioned. Langkah-langkah tepat untuk menggunakan tabel temporal dalam skenario ini dijelaskan di bawah.

Langkah 1: Mengonfigurasi tabel sebagai temporal

Bergantung pada apakah Anda memulai pengembangan baru atau memutakhirkan aplikasi yang ada, Anda akan membuat tabel temporal atau memodifikasi yang sudah ada dengan menambahkan atribut temporal. Pada umumnya, skenario Anda bisa menjadi campuran dari dua opsi ini. Lakukan tindakan ini menggunakan SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio,atau alat pengembangan Transact-SQL lainnya.

Penting

Disarankan agar Anda selalu menggunakan versi terbaru Management Studio untuk tetap tersinkron dengan pembaruan untuk Azure SQL Database dan Azure SQL Managed Instance. Perbarui SQL Server Management Studio.

Buat tabel baru

Gunakan item menu konteks "Tabel System-Versioned Baru" di Object Explorer SSMS atau Management Studio untuk membuka editor kueri dengan skrip templat tabel temporal, lalu gunakan "Tentukan Nilai Parameter Templat" (Ctrl+Shift+M) untuk mengisi templat:

SSMSNewTable

Di SSDT atau SQL Server Data Tools, pilih templat "Tabel Temporal (Versi Sistem)" saat menambahkan item baru ke proyek database. Hal itu akan membuka desainer tabel dan memungkinkan Anda untuk dengan mudah menentukan tata letak tabel:

SSDTNewTable

Anda juga dapat membuat tabel temporal dengan menentukan pernyataan Transact-SQL secara langsung, seperti yang ditunjukkan pada contoh di bawah ini. Elemen wajib dari setiap tabel temporal adalah definisi PERIOD dan klausa SYSTEM_VERSIONING dengan referensi ke tabel pengguna lain yang akan menyimpan versi baris historis:

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

Saat Anda membuat tabel temporal versi sistem, tabel riwayat yang menyertainya dengan konfigurasi default akan dibuat secara otomatis. Tabel riwayat default berisi indeks pohon B yang berkelompok pada kolom periode (akhir, mulai) dengan kompresi halaman diaktifkan. Konfigurasi ini optimal untuk sebagian besar skenario di mana tabel temporal digunakan, terutama untuk audit data.

Dalam hal ini, kami berusaha untuk melakukan analisis tren berbasis waktu atas riwayat data yang lebih panjang dan dengan kumpulan data yang lebih besar, sehingga pilihan penyimpanan untuk tabel riwayat adalah indeks columnstore yang berkelompok. Penyimpan kolom berkluster menyediakan kompresi dan performa yang baik untuk kueri analitik. Tabel temporal memberi Anda fleksibilitas untuk mengonfigurasi indeks pada tabel saat ini dan temporal sepenuhnya independen.

Catatan

Indeks columnstore tersedia di tingkat Bisnis Kritis, Keperluan Umum, dan Premium dan di tingkat Standar, S3 ke atas.

Skrip berikut ini memperlihatkan bagaimana indeks default pada tabel riwayat dapat diubah ke columnstore yang berkelompok:

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

Tabel temporal diwakili di Object Explorer dengan ikon tertentu demi mempermudah identifikasi, sementara tabel riwayatnya ditampilkan sebagai node anak.

AlterTable

Mengubah tabel yang ada menjadi temporal

Mari kita bahas skenario alternatif di mana tabel WebsiteUserInfo sudah ada, tetapi tidak dirancang untuk menyimpan riwayat perubahan. Dalam hal ini, Anda cukup memperluas tabel yang ada untuk menjadi temporal, seperti yang ditunjukkan pada contoh berikut:

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

Langkah 2: Jalankan beban kerja Anda secara teratur

Keuntungan utama tabel temporal adalah Anda tidak perlu mengubah atau menyesuaikan situs web Anda dengan cara apa pun untuk melakukan pelacakan perubahan. Setelah dibuat, tabel temporal secara transparan bertahan pada versi baris sebelumnya setiap kali Anda melakukan modifikasi pada data Anda.

Untuk memanfaatkan pelacakan perubahan otomatis untuk skenario khusus ini, mari kita perbarui kolom PagesVisited setiap kali pengguna mengakhiri sesinya di situs web:

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

Penting untuk diperhatikan bahwa kueri pembaruan tidak perlu mengetahui waktu yang tepat ketika operasi aktual terjadi atau bagaimana data historis akan dipertahankan untuk analisis di masa mendatang. Kedua aspek secara otomatis ditangani oleh Azure SQL Database dan Azure SQL Managed Instance. Diagram berikut ini menggambarkan bagaimana data riwayat dihasilkan pada setiap pembaruan.

TemporalArchitecture

Langkah 3: Melakukan analisis data riwayat

Sekarang ketika pembuatan versi sistem temporal diaktifkan, analisis data riwayat hanya memerlukan satu kueri dari Anda. Dalam artikel ini, kami akan memberikan beberapa contoh yang membahas skenario analisis umum - untuk mempelajari semua detail, menjelajahi berbagai opsi yang diperkenalkan dengan klausa FOR SYSTEM_TIME .

Untuk melihat 10 pengguna teratas yang diurutkan berdasarkan jumlah halaman web yang dikunjungi per jam yang lalu, jalankan kueri ini:

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

Anda dapat dengan mudah memodifikasi kueri ini untuk menganalisis kunjungan situs pada hari yang lalu, sebulan yang lalu atau pada titik mana pun di masa lalu yang Anda inginkan.

Untuk melakukan analisis statistik dasar untuk hari sebelumnya, gunakan contoh berikut:

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

Untuk mencari aktivitas pengguna tertentu, dalam jangka waktu tertentu, gunakan klausa 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;

Visualisasi grafis sangat nyaman untuk kueri temporal karena Anda dapat menunjukkan tren dan pola penggunaan dengan cara yang intuitif dengan sangat mudah:

TemporalGraph

Mengembangkan Skema tabel

Biasanya, Anda harus mengubah skema tabel temporal saat melakukan pengembangan aplikasi. Untuk itu, hanya cukup dengan menjalankan pernyataan ALTER TABLE reguler dan Azure SQL Database atau Azure SQL Managed Instance dengan tepat menyebarkan perubahan ke tabel riwayat. Skrip berikut menunjukkan bagaimana Anda dapat menambahkan atribut tambahan untuk pelacakan:

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

Dengan cara yang sama, Anda dapat mengubah definisi kolom saat beban kerja Anda aktif:

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

Terakhir, Anda dapat menghapus kolom yang tidak Anda butuhkan lagi.

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

Atau, gunakan SSDT terbaru untuk mengubah skema tabel temporal saat Anda tersambung ke database (mode online) atau sebagai bagian dari proyek database (mode offline).

Mengontrol penyimpanan data riwayat

Dengan tabel temporal versi sistem, tabel riwayat mungkin meningkatkan ukuran database lebih dari tabel biasa. Tabel riwayat yang besar dan terus berkembang dapat menjadi masalah baik karena biaya penyimpanan murni serta menerapkan pajak kinerja pada kueri temporal. Oleh karena itu, mengembangkan kebijakan retensi data untuk mengelola data dalam tabel riwayat adalah aspek penting dalam merencanakan dan mengelola siklus hidup setiap tabel temporal. Dengan Azure SQL Database dan Azure SQL Managed Instance, Anda memiliki pendekatan berikut untuk mengelola data riwayat dalam tabel temporal:

Langkah berikutnya

Untuk informasi selengkapnya tentang tabel temporal, lihat Tabel Temporal.

Bagikan melalui