تصميم الجداول باستخدام تجمع SQL مخصص في Azure Synapse Analytics
توفر هذه المقالة المفاهيم التمهيدية الرئيسية لتصميم الجداول في تجمع SQL مخصص.
تحديد فئة الجدول
ينظم مخطط نجمي البيانات في جداول الحقائق والأبعاد. يتم استخدام بعض الجداول للتكامل أو التقسيم المرحلي للبيانات قبل انتقالها إلى جدول الحقائق أو الأبعاد. أثناء تصميم جدول، حدد ما إذا كانت بيانات الجدول تنتمي إلى جدول حقائق أو أبعاد أو تكامل. ويفيد هذا القرار بنية الجدول المناسبة والتوزيع.
تحتوي جداول الحقائق على بيانات كمية يتم إنشاؤها عادة في نظام المعاملات، ثم يتم تحميلها في تجمع SQL المخصص. على سبيل المثال، تقوم شركة البيع بالتجزئة بإنشاء معاملات مبيعات كل يوم، ثم تقوم بتحميل البيانات في جدول حقائق تجمع SQL مخصص للتحليل.
تحتوي جداول الأبعاد على بيانات سمة قد تتغير، ولكن عادة ما تتغير بشكل غير متكرر. على سبيل المثال، يتم تخزين اسم العميل وعنوانه في جدول أبعاد ويتم تحديثه فقط عند تغيير ملف تعريف العميل. لتقليل حجم جدول حقائق كبير، لا يلزم أن يكون اسم العميل وعنوانه في كل صف من جدول الحقائق. بدلاً من ذلك، يمكن لجدول الحقائق وجدول البُعد مشاركة معرف عميل. يمكن للاستعلام الانضمام إلى الجدولين لربط ملف تعريف العميل وحركاته.
توفر جداول التكامل مكانًا للتكامل أو التقسيم المرحلي للبيانات. يمكنك إنشاء جدول تكامل كجدول عادي أو جدول خارجي أو جدول مؤقت. على سبيل المثال، يمكنك تحميل البيانات إلى جدول مرحلي، وإجراء تحويلات على البيانات في التقسيم المرحلي، ثم إدراج البيانات في جدول إنتاج.
أسماء المخططات والجدول
المخططات هي طريقة جيدة لتجميع الجداول، وتستخدم بطريقة مماثلة، معًا. إذا كنت تقوم بترحيل قواعد بيانات متعددة من حل داخلي إلى تجمع SQL مخصص، فإنه يعمل بشكل أفضل لترحيل جميع جداول الحقائق والأبعاد والتكامل إلى مخطط واحد في تجمع SQL مخصص.
على سبيل المثال، يمكنك تخزين جميع الجداول في نموذج تجمع SQL المخصص لـWideWorldImportersDW ضمن مخطط واحد يسمى wwi. تنشئ التعليمات البرمجية التالية مخططًا معرفًا من قبل المستخدم يسمى wwi.
CREATE SCHEMA wwi;
لإظهار تنظيم الجداول في تجمع SQL المخصص، يمكنك استخدام الحقائق وdim وint كبادئات لأسماء الجداول. يعرض الجدول التالي بعض أسماء المخطط والجدول لـWideWorldImportersDW.
| جدول WideWorldImportersDW | نوع الجدول | تجمع SQL المخصص |
|---|---|---|
| المدينة | البعد | wwi.DimCity |
| الترتيب | الحقيقة | wwi.FactOrder |
استمرار جدول
تخزن الجداول البيانات إما بشكل دائم في Azure Storage، أو مؤقتًا في Azure Storage، أو في مخزن بيانات خارجي إلى تجمع SQL مخصص.
جدول عادي
يخزن الجدول العادي البيانات في Azure Storage كجزء من تجمع SQL المخصص. يستمر الجدول والبيانات بغض النظر عما إذا كانت جلسة العمل مفتوحة أم لا. ينشئ المثال التالي جدولًا عاديًا بعمودين.
CREATE TABLE MyTable (col1 int, col2 int );
جدول مؤقت
يوجد جدول مؤقت لمدة جلسة العمل فقط. يمكنك استخدام جدول مؤقت لمنع المستخدمين الآخرين من رؤية النتائج المؤقتة وأيضًا لتقليل الحاجة إلى التنظيف.
تستخدم الجداول المؤقتة التخزين المحلي لتقديم أداء سريع. لمزيد من المعلومات، راجع الجداول المؤقتة.
جدول خارجي
يشير جدول خارجي إلى البيانات الموجودة في النقطة تخزين Azure أو مخزن بحيرة البيانات Azure. عند استخدامها مع عبارة CREATE TABLE AS SELECT، يتم التحديد من جدول خارجي باستيراد البيانات إلى تجمع SQL مخصص.
على هذا النحو، الجداول الخارجية مفيدة لتحميل البيانات. للحصول على برنامج تعليمي تحميل، راجع استخدام PolyBase لتحميل البيانات من تخزين النقطة Azure.
أنواع البيانات
يدعم تجمع SQL المخصص أنواع البيانات المستخدمة الأكثر شيوعًا. للحصول على قائمة أنواع البيانات المدعومة، راجع أنواع البيانات فـي عبارة CREATE TABLE. للحصول على إرشادات حول استخدام أنواع البيانات، راجع أنواع البيانات.
الجداول الموزعة
إحدى الميزات الأساسية لتجمع SQL المخصص هي الطريقة التي يمكن بها تخزين الجداول والعمل عليها عبر التوزيعات. يدعم تجمع SQL المخصص ثلاث طرق لتوزيع البيانات: round-robin (افتراضي) والتجزئة والنسخ المتماثل.
الجداول الموزعة باستخدام نمط التجزئة
يوزع جدول التجزئة الموزع الصفوف استنادًا إلى القيمة الموجودة في عمود التوزيع. تم تصميم جدول التجزئة الموزع لتحقيق أداء عالٍ للاستعلامات على الجداول الكبيرة. هناك عدة عوامل يجب مراعاتها عند اختيار عمود توزيع.
لمزيد من المعلومات، راجع إرشادات التصميم للجداول الموزعة.
الجداول المنسوخة بشكل متماثل
يحتوي الجدول المنسوخ نسخا متماثلا على نسخة كاملة من الجدول المتوفر على كل عقدة حساب. تعمل الاستعلامات بسرعة على الجداول المنسوخة نسخًا متماثلًا نظرًا لأن الصلات على الجداول المنسوخة نسخا متماثلا لا تتطلب حركة البيانات. النسخ المتماثل يتطلب تخزينًا إضافيًا، على الرغم من ذلك، وليس عمليًا للجداول الكبيرة.
لمزيد من المعلومات، راجع إرشادات التصميم للجداول المنسوخة نسخًا متماثلاً.
جداول بطريقة راوند روبن
يوزع جدول بطريق راوند روبن صفوف الجدول بالتساوي عبر جميع التوزيعات. يتم توزيع الصفوف عشوائيًا. تحميل البيانات في جدول الترتيب الدوري سريع. ضع في اعتبارك أن الاستعلامات يمكن أن تتطلب حركة بيانات أكثر من أساليب التوزيع الأخرى.
لمزيد من المعلومات، راجع إرشادات التصميم للجداول الموزعة.
طرق التوزيع الشائعة للجداول
غالباً ما تحدد فئة الجدول الخيار الذي تختاره لتوزيع الجدول.
| فئة الجدول | خيار التوزيع الموصى به |
|---|---|
| الحقيقة | استخدام تجزئة التوزيع مع فهرس مخزن أعمدة متفاوتة المسافات. يتحسن الأداء عندما يتم ربط جدوليّ التجزئة على عمود التوزيع نفسه. |
| البُعد | استخدم نسخاً متماثلاً لجداول أصغر. إذا كانت الجداول كبيرة للغاية لتخزين على كل عقدة Compute فاستخدم التجزئة الموزعة. |
| التشغيل المرحلي | استخدم round-robin لطاولة التدريج. يكون الحمل مع CTAS سريعاً. بمجرد أن تكون البيانات في الجدول المرحلي، استخدم INSERT...SELECT لنقل البيانات إلى جداول الإنتاج. |
ملاحظة
للحصول على توصيات حول أفضل إستراتيجية توزيع جدول لاستخدامها استنادًا إلى أحمال العمل الخاصة بك، راجع Azure Synapse SQL Distribution Advisor.
أقسام الجدول
يخزن الجدول المقسم وينفذ العمليات على صفوف الجدول وفقا لنطاقات البيانات. على سبيل المثال، يمكن تقسيم جدول حسب اليوم أو الشهر أو السنة. يمكنك تحسين أداء الاستعلام من خلال إزالة القسم، ما يحد من فحص الاستعلام للبيانات داخل القسم. يمكنك أيضًا الاحتفاظ بالبيانات من خلال تبديل القسم. نظرًا لأن البيانات في تجمع SQL موزعة بالفعل، يمكن للعديد من الأقسام إبطاء أداء الاستعلام. لمزيد من المعلومات، راجع إرشادات التقسيم. عند تبديل القسم إلى أقسام جدول غير فارغة، ضع في اعتبارك استخدام خيار TRUNCATE_TARGET في عبارة ALTER TABLE إذا كان سيتم اقتطاع البيانات الموجودة. تقوم التعليمات البرمجية أدناه بالتبديل في البيانات اليومية المحولة إلى SalesFact الكتابة فوق أي بيانات موجودة.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
مؤشرات تخزين الأعمدة
بشكل افتراضي، يخزن تجمع SQL المخصص جدولاً كمؤشر تخزين أعمدة بنظام المجموعة. يحقق هذا الشكل من تخزين البيانات ضغطًا عاليًا للبيانات وأداءً عاليًا للاستعلام على جداول كبيرة.
عادة ما يكون مؤشر تخزين الأعمدة بنظام المجموعة هو الخيار الأفضل، ولكن في بعض الحالات يكون المؤشر بنظام المجموعة أو كومة الذاكرة المؤقتة هي بنية التخزين المناسبة.
تلميح
يمكن أن يكون جدول كومة الذاكرة المؤقتة مفيدًا بشكل خاص لتحميل البيانات العابرة، مثل جدول مرحلي يتم تحويله إلى جدول نهائي.
للحصول على قائمة بميزات تخزين الأعمدة، راجع ما الجديد في فهارس تخزين الأعمدة. لتحسين أداء مؤشر تخزين الأعمدة، راجع تكبير جودة مجموعة الصفوف لمؤشرات تخزين الأعمدة.
الإحصائيات
يستخدم محسّن الاستعلامات إحصائيات على مستوى العمود عند إنشاء الخطة لتنفيذ استعلام.
لتحسين أداء الاستعلام، من المهم الحصول على إحصائيات حول الأعمدة الفردية، خاصة الأعمدة المستخدمة في عمليات ربط الاستعلام. يحدث إنشاء الإحصائيات تلقائيًا.
لا يحدث تحديث الإحصائيات تلقائيًا. تحديث الإحصائيات بعد إضافة عدد كبير من الصفوف أو تغييرها. على سبيل المثال، تحديث الإحصائيات بعد التحميل. لمزيد من المعلومات، راجع دليل الإحصائيات.
المفتاح الأساسي والمفتاح الفريد
يتم دعم المفتاح الأساسي فقط عند استخدام كل من NONCLUSTERED و NOT ENFORCED. يتم دعم قيد UNIQUE فقط مع NOT ENFORCED. تحقق من قيود جدول تجمع SQL المخصص.
أوامر لإنشاء جداول
يمكنك إنشاء جدول كجدول فارغ جديد. يمكنك أيضًا إنشاء جدول وملئه بنتائج عبارة تحديد. فيما يلي أوامر T-SQL لإنشاء جدول.
| عبارة T-SQL | الوصف |
|---|---|
| إنشاء جدول | يقوم بإنشاء جدول فارغ عن طريق تعريف كافة أعمدة الجدول وخياراته. |
| إنشاء جدول خارجي | يقوم بإنشاء جدول خارجي. يتم تخزين تعريف الجدول في تجمع SQL مخصص. يتم تخزين بيانات الجدول في تخزين Azure Blob أو Azure Data Lake Store. |
| إنشاء جدول كما محدد | يقوم بتعبئة جدول جديد بنتائج عبارة تحديد. تستند أعمدة الجدول وأنواع البيانات إلى نتائج عبارة التحديد. لاستيراد البيانات، يمكن تحديد هذه العبارة من جدول خارجي. |
| CREATE EXTERNAL TABLE AS SELECT | إنشاء جدول خارجي جديد عن طريق تصدير نتائج عبارة تحديد إلى موقع خارجي. الموقع إما تخزين Azure Blob أو Azure Data Lake Store. |
محاذاة بيانات المصدر مع تجمع SQL مخصص
يتم ملء جداول تجمع SQL المخصصة عن طريق تحميل البيانات من مصدر بيانات آخر. لتنفيذ تحميل ناجح، يجب أن يتوافق عدد وأنواع البيانات للأعمدة في البيانات المصدر مع تعريف الجدول في تجمع SQL المخصص. قد يكون محاذاة البيانات أصعب جزء في تصميم الجداول.
إذا كانت البيانات واردة من مخازن بيانات متعددة، يمكنك تحميل البيانات في تجمع SQL المخصص وتخزينها في جدول تكامل. بمجرد أن تكون البيانات في جدول التكامل، يمكنك استخدام قوة تجمع SQL المخصص لتنفيذ عمليات التحويل. بمجرد إعداد البيانات، يمكنك إدراجها في جداول الإنتاج.
ميزات الجدول غير المعتمدة
يدعم تجمع SQL المخصص العديد من ميزات الجدول التي تقدمها قواعد البيانات الأخرى، وليس كلها. تعرض القائمة التالية بعض ميزات الجدول غير المعتمدة في تجمع SQL المخصص:
- مفتاح خارجي، التحقق من قيود الجدول
- الأعمدة المحسوبة
- طرق العرض المفهرسة
- التسلسل
- أعمدة متفرقة
- مفتاح بديل. تنفيذ باستخدام الهوية.
- المرادفات
- أزرار التشغيل
- الفهارس الفريدة
- أنواع User-Defined
استعلامات حجم الجدول
ملاحظة
للحصول على عدد دقيق من الاستعلامات في هذا القسم، تأكد من أن صيانة الفهرس تحدث بشكل منتظم وبعد تغييرات البيانات الكبيرة.
إحدى الطرق البسيطة لتحديد المساحة والصفوف التي يستهلكها جدول في كل توزيع من توزيعات 60، هي استخدام DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
ومع ذلك، يمكن أن يكون استخدام أوامر DBCC محدودًا للغاية. تظهر طرق عرض الإدارة الديناميكية (DMVs) تفاصيل أكثر من أوامر DBCC. ابدأ بإنشاء طريقة العرض هذه:
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
GETDATE() AS [execution_time]
, DB_NAME() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]
, nt.[name] AS [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]
, tp.[distribution_policy_desc] AS [distribution_policy_name]
, c.[name] AS [distribution_column]
, nt.[distribution_id] AS [distribution_id]
, i.[type] AS [index_type]
, i.[type_desc] AS [index_type_desc]
, nt.[pdw_node_id] AS [pdw_node_id]
, pn.[type] AS [pdw_node_type]
, pn.[name] AS [pdw_node_name]
, di.name AS [dist_name]
, di.position AS [dist_position]
, nps.[partition_number] AS [partition_nmbr]
, nps.[reserved_page_count] AS [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]
, nps.[in_row_data_page_count]
+ nps.[row_overflow_used_page_count]
+ nps.[lob_used_page_count] AS [data_space_page_count]
, nps.[reserved_page_count]
- (nps.[reserved_page_count] - nps.[used_page_count])
- ([in_row_data_page_count]
+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]
, nps.[row_count] AS [row_count]
from
sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
ON nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
ON nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
ON nt.[object_id] = nps.[object_id]
AND nt.[pdw_node_id] = nps.[pdw_node_id]
AND nt.[distribution_id] = nps.[distribution_id]
AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
ON cdp.[object_id] = c.[object_id]
AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
[execution_time]
, [database_name]
, [schema_name]
, [table_name]
, [two_part_name]
, [node_table_name]
, [node_table_name_seq]
, [distribution_policy_name]
, [distribution_column]
, [distribution_id]
, [index_type]
, [index_type_desc]
, [pdw_node_id]
, [pdw_node_type]
, [pdw_node_name]
, [dist_name]
, [dist_position]
, [partition_nmbr]
, [reserved_space_page_count]
, [unused_space_page_count]
, [data_space_page_count]
, [index_space_page_count]
, [row_count]
, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]
, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]
, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]
, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]
, ([unused_space_page_count] * 8.0) AS [unused_space_KB]
, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]
, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]
, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]
, ([data_space_page_count] * 8.0) AS [data_space_KB]
, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]
, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]
, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]
, ([index_space_page_count] * 8.0) AS [index_space_KB]
, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]
, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]
, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
ملخص مساحة الجدول
يقوم هذا الاستعلام بإرجاع الصفوف والمسافة حسب الجدول. يسمح لك بالاطلاع على الجداول التي تعد أكبر الجداول وما إذا كانت مستديرة الترتيب أو منسوخة نسخًا متماثلًا أو موزعة على التجزئة. بالنسبة للجداول الموزعة على التجزئة، يعرض الاستعلام عمود التوزيع.
SELECT
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
, COUNT(distinct partition_nmbr) as nbr_partitions
, SUM(row_count) as table_row_count
, SUM(reserved_space_GB) as table_reserved_space_GB
, SUM(data_space_GB) as table_data_space_GB
, SUM(index_space_GB) as table_index_space_GB
, SUM(unused_space_GB) as table_unused_space_GB
FROM
dbo.vTableSizes
GROUP BY
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
ORDER BY
table_reserved_space_GB desc
;
مساحة الجدول حسب نوع التوزيع
SELECT
distribution_policy_name
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;
مساحة الجدول حسب نوع الفهرس
SELECT
index_type_desc
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;
ملخص مساحة التوزيع
SELECT
distribution_id
, SUM(row_count) as total_node_distribution_row_count
, SUM(reserved_space_MB) as total_node_distribution_reserved_space_MB
, SUM(data_space_MB) as total_node_distribution_data_space_MB
, SUM(index_space_MB) as total_node_distribution_index_space_MB
, SUM(unused_space_MB) as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY distribution_id
ORDER BY distribution_id
;
الخطوات التالية
بعد إنشاء الجداول لتجمع SQL المخصص، فإن الخطوة التالية هي تحميل البيانات في الجدول. للحصول على برنامج تعليمي للتحميل، راجع Loading data to dedicated SQL pool وراجع Data loading strategies for dedicated SQL pool in Azure Synapse Analytics.
الملاحظات
قريبًا: خلال عام 2024، سنتخلص تدريجيًا من GitHub Issues بوصفها آلية إرسال ملاحظات للمحتوى ونستبدلها بنظام ملاحظات جديد. لمزيد من المعلومات، راجع https://aka.ms/ContentUserFeedback.
إرسال الملاحظات وعرضها المتعلقة بـ