Mulai menggunakan tabel temporal di Azure SQL Database dan Azure SQL Managed Instance
Berlaku untuk:
Azure SQL DatabaseAzure 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 sangat sederhana - metrik aktivitas pengguna diwakili dengan bidang satu bilangan bulat, PageVisited, dan ditangkap 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.
Untungnya, anda tidak perlu berusaha keras dalam aplikasi anda untuk menjaga 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
Tergantung 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:
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:
Anda juga dapat membuat tabel temporal dengan menentukan pernyataan Transact-SQL secara langsung, seperti yang ditunjukkan pada contoh di bawah ini. Perhatikan bahwa elemen wajib dari setiap tabel temporal adalah definisi PERIOD dan klausa SYSTEM_VERSIONING dengan referensi ke tabel pengguna lain yang akan menyimpan versi baris riwayat:
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. Columnstore yang berkelompok memberikan kompresi dan kinerja yang sangat baik untuk kueri analitis. 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.
Mengubah tabel yang ada menjadi temporal
Mari kita bahas skenario alternatif, yaitu jika 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 sebenarnya ketika operasi benar-benar terjadi atau bagaimana data riwayat akan dipertahankan untuk dianalisis nantinya. 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.
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, mengeksplorasi berbagai opsi yang diperkenalkan dengan for SYSTEM_TIME klausul.
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:
Mengembangkan Skema tabel
Biasanya, Anda harus mengubah skema tabel temporal saat Anda 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 perlukan lagi.
/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
DROP COLUMN TemporaryColumn;
Atau, gunakan SQL Server Data Tools (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 dapat 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.
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk