الشروع في استخدام جداول المعالجة الزمنية في قاعدة بيانات Azure SQL ومثيل SQL Azure المدار

ينطبق على: قاعدة بيانات Azure SQL مثيل Azure SQL المُدار

جداول المعالجة الزمنية هي ميزة برمجة لقاعدة بيانات Azure SQL ومثيل SQL Azure المدار التي يسمح لك بتتبع وتحليل التاريخ الكامل للتغييرات في بياناتك، دون الحاجة إلى تعليمة برمجية مخصصة. وتبقي جداول المعالجة الزمنية البيانات مرتبطة ارتباطًا وثيقًا بالسياق الزمني بحيث يمكن تفسير الوقائع المخزنة على أنها صحيحة فقط في غضون الفترة المحددة. تسمح خاصية جداول المعالجة الزمنية هذه بإجراء تحليل فعال يستند إلى الوقت والحصول على نتائج تحليلات من تطور البيانات.

السيناريو الزمني

توضح هذه المقالة الخطوات لاستخدام جداول المعالجة الزمنية في سيناريو تطبيق. لنفترض أنك تريد تتبع نشاط المستخدم على موقع ويب جديد يتم تطويره من البداية أو على موقع ويب موجود تريد توسيعه باستخدام تحليلات نشاط المستخدم. في هذا المثال المبسط، نفترض أن عدد صفحات الويب التي تمت زيارتها خلال فترة زمنية هو مؤشر يحتاج إلى التقاط ومراقبة في قاعدة بيانات موقع الويب التي يتم استضافتها على قاعدة بيانات Azure SQL ومثيل SQL Azure المدار. الهدف من التحليل التاريخي لنشاط المستخدم هو الحصول على إدخالات لإعادة تصميم الموقع وتوفير تجربة أفضل للزوار.

نموذج قاعدة البيانات لهذا السيناريو بسيط جدا - يتم تمثيل مقياس نشاط المستخدم بحقل عدد صحيح واحد، PageVisited، ويتم التقاطه مع المعلومات الأساسية في ملف تعريف المستخدم. بالإضافة إلى ذلك، للتحليل المستند إلى الوقت، يمكنك الاحتفاظ بسلسلة من الصفوف لكل مستخدم، حيث يمثل كل صف عدد الصفحات التي زارها مستخدم معين خلال فترة زمنية محددة.

لحسن الحظ، لا تحتاج إلى بذل أي جهد في التطبيق الخاص بك للحفاظ على معلومات النشاط هذه. باستخدام جداول المعالجة الزمنية، هذه العملية آلية -- مما يتيح لك المرونة الكاملة خلال تصميم الموقع والمزيد من الوقت للتركيز على تحليل البيانات نفسها. الشيء الوحيد الذي عليك القيام به هو التأكد من أن WebSiteInfoالجدول يتم تكوينه على أنهنظام مؤقت للإصدار. ويرد أدناه وصف للخطوات الدقيقة لاستخدام جداول المعالجة الزمنية في هذا السيناريو.

الخطوة 1: تكوين الجداول كزمني

اعتمادًا على ما إذا كنت تقوم ببدء تطوير جديد أو ترقية التطبيق الحالي، ستقوم إما بإنشاء جداول مؤقتة أو تعديل الجداول الموجودة عن طريق إضافة سمات زمنية. بشكل عام، يمكن أن يكون السيناريو الخاص بك مزيجًا من هذين الخيارين. نفذ هذه الإجراءات باستخدام SQL Server Management Studio (SSMS) أو SQL Server Data Tools (SSDT) ​​أو Azure Data Studio أو أي أداة تطوير أخرى لـ Transact-SQL.

هام

من المستحسن أن تستخدم دائمًا أحدث إصدار من Management Studio لتبقى متزامنة مع التحديثات في قاعدة بيانات Azure SQL ومثيل SQL Azure المدار. تحديث SQL Server Management Studio.

إنشاء جدول جديد

استخدم عنصر قائمة السياق "جدول صادر من النظام جديد" في مستكشف عناصر Management Studio لفتح محرر الاستعلام مع برنامج نصي قالب جدول المعالجة الزمنية ثم استخدم "تحديد قيم معلمات قالب" (Ctrl + Shift + M) لملء القالب:

في SSDT، اختر قالب "جدول المعالجة الزمنية (صادر من النظام)" عند إضافة عناصر جديدة إلى مشروع قاعدة البيانات. سيؤدي ذلك إلى فتح مصمم الجدول وتمكينك من تحديد تخطيط الجدول بسهولة:

يمكنك أيضًا إنشاء جدول المعالجة الزمنية عن طريق تحديد عبارات Transact-SQL مباشرة، كما هو موضح في المثال أدناه. لاحظ أن العناصر الإلزامية لكل جدول معالجة زمينة هي تعريف PERIOD و جملة SYSTEM_VERSIONING مع الإشارة إلى جدول مستخدم آخر سيتم تخزين محفوظات إصدارات الصف:

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

عند إنشاء جدول معالجة زمنية من إصدار النظام، يتم إنشاء جدول المحفوظات المصاحبة مع التكوين الافتراضي تلقائيًا. يحتوي جدول المحفوظات الافتراضي على فهرس B-tree متفاوت في أعمدة الفترة (نهاية، بداية) مع تمكين ضغط الصفحة. هذا التكوين هو الأمثل بالنسبة لمعظم السيناريوهات التي تستخدم فيها جداول المعالجة الزمنية، وخاصة لتدقيق البيانات.

في هذه الحالة بالذات، نهدف إلى إجراء تحليل الاتجاه القائم على الوقت عبر تاريخ بيانات أطول ومع مجموعات بيانات أكبر، وبالتالي فإن خيار التخزين لجدول المحفوظات هو مؤشر تخزين الأعمدة متفاوت المسافات. يوفر مؤشر تخزين الأعمدة متفاوت المسافات ضغطًا وأداءً جيدًا جدًا في الاستعلامات التحليلية. تمنحك جداول المعالجة الزمنية المرونة لتكوين الفهارس على الجداول الحالية والزمنية بشكل مستقل تمامًا.

ملاحظة

تتوفر مؤشرات تخزين الأعمدة في فئات الأعمال الهامة والأغراض العامة والمتميزة وفي المستوى القياسي، S3 وما فوق.

يوضح البرنامج النصي التالي كيفية تغيير المؤشر الافتراضي على جدول المحفوظات إلى مخزن الأعمدة متفاوت المسافات:

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

يتم تمثيل جداول المعالجة الزمنية في مستكشف عناصر SQL Server مع رمز معين لتعريف أسهل، بينما يتم عرض جدول المحفوظات الخاص به كعقدة تابعة.

تغيير الجدول الحالي إلى جدول زمني

دعونا تغطي السيناريو البديل الذي يوجد جدول WebsiteUserInfo بالفعل، ولكن لم يتم تصميمه للحفاظ على محفوظات التغييرات. في هذه الحالة، يمكنك ببساطة توسيع الجدول الموجود ليصبح زمنيًا، كما هو موضح في المثال التالي:

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: تشغيل حمل العمل بانتظام

الميزة الرئيسية لجداول المعالجة الزمنية هي أنك لا تحتاج إلى تغيير أو ضبط موقعك بأي شكل من الأشكال لإجراء تتبع التغيير. بمجرد إنشائها، تستمر جداول المعالجة الزمنية بشفافية في إصدارات الصف السابقة في كل مرة تقوم فيها بإجراء تعديلات على بياناتك.

من أجل الاستفادة من تتبع التغيير التلقائي لهذا السيناريو بالذات، دعنا فقط تحديث العمود PagesVisited في كل مرة ينهي فيها المستخدم جلسته على موقع الويب:

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

من المهم ملاحظة أن استعلام التحديث لا يحتاج إلى معرفة الوقت الدقيق الذي حدثت فيه العملية الفعلية ولا كيف سيتم الاحتفاظ ببيانات المحفوظات للتحليل المستقبلي. تتم معالجة كلا الجانبين تلقائيُا بواسطة قاعدة بيانات Azure SQL ومثيل SQL Azure المدار. يوضح الرسم التخطيطي التالي كيف يتم إنشاء بيانات المحفوظات في كل تحديث.

الخطوة 3: إجراء تحليل بيانات المحفوظات

الآن عندما يتم تمكين إصدار النظام الزمني، فإن تحليل بيانات المحفوظات على بعد استعلام واحد منك. في هذه المقالة، سوف نقدم بعض الأمثلة التي تتناول سيناريوهات التحليل المشترك - لمعرفة جميع التفاصيل، واستكشاف الخيارات المختلفة المقدمة مع عبارة for SYSTEM_TIME.

لمشاهدة أكثر 10 مستخدمين مرتبة حسب عدد صفحات الويب التي تمت زيارتها منذ ساعة، قم بتشغيل هذا الاستعلام:

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

يمكنك بسهولة تعديل هذا الاستعلام لتحليل زيارات الموقع اعتبارًا من يوم مضى، أو قبل شهر، أو في أي وقت تريده في الماضي.

لإجراء تحليل إحصائي أساسي لليوم السابق، استخدم المثال التالي:

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

للبحث عن أنشطة مستخدم معين، في غضون فترة من الزمن، استخدم عبارة 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;

يعد التصور الرسومي مناسبًا بشكل خاص للاستعلامات المؤقتة حيث يمكنك إظهار الاتجاهات وأنماط الاستخدام بطريقة بديهية بسهولة شديدة:

تطوير مخطط الجدول

عادة، سوف تحتاج إلى تغيير مخطط جدول المعالجة الزمنية أثناء القيام بتطوير التطبيق. لذلك، ببساطة تشغيل عبارات ALTER TABLE العادية وقاعدة بيانات Azure SQL أو مثيل SQL Azure المدار بشكل مناسب ينشر التغييرات إلى جدول المحفوظات. يوضح البرنامج النصي التالي كيفية إضافة سمة إضافية للتعقب:

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

وبالمثل، فإنه يمكنك تغيير تعريف العمود أثناء تنشيط حمل العمل:

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

وأخيرًا، يمكنك إزالة عمود لا تحتاجه بعد الآن.

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

بدلًا من ذلك، استخدم أحدث SSDT لتغيير مخطط جدول المعالجة الزمنية أثناء الاتصال بقاعدة البيانات (وضع الاتصال) أو كجزء من مشروع قاعدة البيانات (وضع عدم الاتصال).

التحكم في الاحتفاظ ببيانات المحفوظات

باستخدام جداول المعالجة الزمنية التي أصدرها النظام، قد يزيد جدول المحفوظات من حجم قاعدة البيانات أكثر من الجداول العادية. يمكن أن يصبح جدول المحفوظات الكبير والمتزايد مشكلة بسبب تكاليف التخزين البحتة وكذلك فرض ضريبة الأداء على الاستعلام الزمني. ومن ثم، فإن وضع نهج استبقاء البيانات لإدارة البيانات في جدول المحفوظات هو جانب هام من جوانب تخطيط وإدارة دورة حياة كل جدول معالجة زمنية. قاعدة بيانات Azure SQL ومثيل SQL Azure المدار، لديك الطرق التالية لإدارة بيانات المحفوظات في جدول المعالجة الزمنية:

• تقسيم الجدول
• برنامج نصي للتنظيف المخصص

الخطوات التالية

• لمزيد من المعلومات حول جداول المعالجة الزمنية، راجع Temporal Tables.