أفضل الممارسات لتجمع SQL بلا خادم في Azure Synapse Analytics

في هذه المقالة، ستجِد مجموعة من أفضل الممارسات لاستخدام تجمع SQL بلا خادم. يُعد تجمع SQL بلا خادم موردًا في Azure Synapse Analytics. إذا كنت تعمل باستخدام تجمع SQL مخصص، فراجع أفضل الممارسات لتجمعات SQL المخصصة للحصول على إرشادات محددة.

يتيح لك تجمع SQL بلا خادم الاستعلام عن الملفات في حسابات Azure Storage. ولا يحتوي على إمكانيات تخزين أو استيعاب محلية. جميع الملفات التي تستهدفها الاستعلامات خارجية بالنسبة إلى تجمع SQL بلا خادم. قد يؤثّر كل ما يتعلق بقراءة الملفات من التخزين على أداء الاستعلام.

بعض المبادئ التوجيهية العامة هي:

• تأكد من أن تطبيقات العميل الخاصة بك تم ترتيبها باستخدام تجمع SQL بلا خادم.
  • إذا كنت تستخدم تطبيقات العميل خارج Azure، فتأكد من أنك تستخدم تجمع SQL بلا خادم في منطقة قريبة من كمبيوتر العميل. تتضمن أمثلة تطبيق العميل Power BI Desktop وSQL Server Management Studio وAzure Data Studio.
• تأكد من أن التخزين وتجمع SQL بلا خادم في نفس المنطقة. تتضمن أمثلة التخزين Azure Data Lake Storage وAzure Cosmos DB.
• حاول تحسين تخطيط التخزين باستخدام تقسيم ملفاتك والاحتفاظ بها في النطاق بين 100 ميغابايت و10 غيغابايت.
• إذا كنت تُعيد عددًا كبيرًا من النتائج، فتأكد من أنك تستخدم SQL Server Management Studio أو Azure Data Studio وليس Azure Synapse Studio. تُعد Azure Synapse Studio أداة ويب غير مصممة لمجموعات النتائج الكبيرة.
• إذا كنت تُجري تصفية النتائج حسب عمود السلسلة، فحاول استخدام ترتيب BIN2_UTF8. لمزيد من المعلومات حول تغيير الترتيب، راجع أنواع الترتيب المدعومة ل Synapse SQL.
• ضع في اعتبارك إجراء التخزين المؤقت للنتائج على جانب العميل باستخدام وضع استيراد Power BI أو Azure Analysis Services، وتحديثها بشكل دوري. لا يمكن لتجمعات SQL بلا خادم توفير تجربة تفاعلية في وضع Power BI Direct Query إذا كنت تستخدم استعلامات معقدة أو تعالج كمية كبيرة من البيانات.
• الحد الأقصى للتزامن غير محدود ويعتمد على تعقيد الاستعلام ومقدار البيانات الممسوحة ضوئيا. يمكن لتجمع SQL واحد بلا خادم معالجة 1000 جلسة نشطة تنفذ استعلامات خفيفة الوزن بشكل متزامن. ستنخفض الأرقام إذا كانت الاستعلامات أكثر تعقيدا أو تفحص كمية أكبر من البيانات، لذلك في هذه الحالة ضع في اعتبارك تقليل التزامن وتنفيذ الاستعلامات على مدى فترة زمنية أطول إن أمكن.

تطبيقات العميل واتصالات الشبكة

تأكّد من اتصال تطبيق العميل الخاص بك بأقرب مساحة عمل Azure Synapse ممكنة مع الاتصال الأمثل.

• وصّل تطبيق العميل بمساحة عمل Azure Synapse. إذا كنت تستخدم تطبيقات مثل Power BI أو Azure Analysis Service، فتأكد أنها في نفس المنطقة التي وضعت فيها مساحة عمل Azure Synapse. إذا لزم الأمر، فأنشئ مساحات عمل منفصلة مقترنة بتطبيقات العميل. قد يؤدي وضعك لتطبيق عميل ومساحة عمل Azure Synapse في مناطق مختلفة إلى زمن انتقال أكبر وتدفق أبطأ للنتائج.
• إذا كنت تقرأ البيانات من التطبيق المحلي، فتأكد من أن مساحة عمل Azure Synapse توجد في المنطقة القريبة من موقعك.
• تأكّد من عدم وجود مشكلات في النطاق الترددي للشبكة في أثناء قراءة كمية كبيرة من البيانات.
• لا تستخدم Azure Synapse Studio لإعادة كمية كبيرة من البيانات. تُعد Azure Synapse Studio أداة ويب تستخدم بروتوكول HTTPS لنقل البيانات. استخدم Azure Data Studio أو SQL Server Management Studio لقراءة كمية كبيرة من البيانات.

تخطيط التخزين والمحتوى

فيما يلي أفضل الممارسات لتخطيط التخزين والمحتوى في تجمع SQL بلا خادم.

توصيل التخزين الخاص بك بتجمع SQL بلا خادم

لتقليل زمن الانتقال، وصّل حساب Azure Storage أو التخزين التحليلي لـ Azure Cosmos DB بنقطة نهاية تجمع SQL بلا خادم. توجد حسابات التخزين ونقاط النهاية التي تم توفيرها في أثناء إنشاء مساحة العمل في نفس المنطقة.

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

تقييد Azure Storage

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

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

تلميح

لتنفيذ الاستعلام الأمثل، لا تضغط على حساب التخزين مع أحمال العمل الأخرى في أثناء تنفيذ الاستعلام.

إعداد الملفات للاستعلام

إذا كان ذلك ممكنًا، بإمكانك إعداد الملفات للحصول على أداء أفضل:

• حوّل ملفات CSV وJSON كبيرة الحجم إلى Parquet. يُعد Parquet تنسيقًا عموديًا. نظرًا إلى أنه مضغوط، فإن أحجام ملفاته أصغر من ملفات CSV أو JSON التي تحتوي على نفس البيانات. يتجاوز تجمع SQL بلا خادم الأعمدة والصفوف غير المطلوبة في استعلام إذا كنت تقرأ ملفات بتنسيق Parquet. يحتاج تجمع SQL بلا خادم إلى وقت أقل وطلبات تخزين أقل لقراءته.
• إذا كان الاستعلام يستهدف ملفًا كبيرًا واحدًا، فستستفيد من تقسيمه إلى ملفات أصغر متعددة.
• حاول الاحتفاظ بحجم ملف CSV بين 100 ميغابايت و10 غيغابايت.
• من الأفضل أن يكون لديك ملفات متساوية الحجم لمسار OPENROWSET فردي أو موقع جدول خارجي.
• قسّم بياناتك عن طريق تخزين الأقسام إلى مجلدات أو أسماء ملفات مختلفة. راجع استخدام دوال filename وfilepath لاستهداف أقسام معينة.

توصيل التخزين التحليلي لـ Azure Cosmos DB بتجمع SQL بلا خادم

تأكّد من وضع التخزين التحليلي لـ Azure Cosmos DB في نفس المنطقة مثل مساحة عمل Azure Synapse. قد تتسبب الاستعلامات عبر المناطق في إنشاء زمن انتقال كبير. استخدم خاصية المنطقة في سلسلة الاتصال لتحديد المنطقة التي يُوضع المخزن التحليلي فيها بشكل صريح (راجع الاستعلام عن Azure Cosmos DB باستخدام تجمع SQL بلا خادم): account=<database account name>;database=<database name>;region=<region name>'

تحسينات CSV

يرد فيما يلي أفضل الممارسات لاستخدام ملفات CSV في تجمع SQL بلا خادم.

استخدام PARSER_VERSION 2.0 للاستعلام عن ملفات CSV

يمكنك استخدام محلل تحسين الأداء عند الاستعلام عن ملفات CSV. للتفاصيل، راجع PARSER_VERSION.

إنشاء الإحصائيات لملفات CSV يدويًا

يعتمد تجمع SQL بلا خادم على الإحصائيات لإنشاء خطط تنفيذ الاستعلام المُثلى. يتم إنشاء الإحصائيات تلقائيا للأعمدة التي تستخدم أخذ العينات، وفي معظم الحالات ستكون النسبة المئوية لأخذ العينات أقل من 100٪. هذا التدفق هو نفسه لكل تنسيق ملف. ضع في اعتبارك أنه عند قراءة CSV مع أخذ عينات الإصدار 1.0 من المحلل غير مدعوم ولن يحدث الإنشاء التلقائي للإحصائيات مع النسبة المئوية لأخذ العينات أقل من 100٪. بالنسبة للجداول الصغيرة ذات العلاقة الأساسية المنخفضة المقدرة (عدد الصفوف) سيتم تشغيل إنشاء الإحصائيات التلقائية بنسبة أخذ العينات 100٪. وهذا يعني أنه يتم تشغيل fullscan ويتم إنشاء إحصائيات تلقائية حتى ل CSV مع الإصدار 1.0 من المحلل. في حالة عدم إنشاء الإحصائيات تلقائيا، قم بإنشاء إحصائيات يدويا للأعمدة التي تستخدمها في الاستعلامات، خاصة تلك المستخدمة في DISTINCT و JOIN و WHERE و ORDER BY و GROUP BY. تحقق من الإحصائيات في تجمع SQL بلا خادم للاطلاع على التفاصيل.

أنواع البيانات

يرد فيما يلي أفضل الممارسات لاستخدام أنواع البيانات في تجمع SQL بلا خادم.

استخدام أنواع البيانات المناسبة

تؤثّر أنواع البيانات التي تستخدمها في الاستعلام على الأداء والتزامن. بإمكانك الحصول على أداء أفضل إذا اتبعت هذه الإرشادات:

• استخدم أصغر حجم للبيانات يمكن أن يستوعب أكبر قيمة ممكنة.
  • إذا كان الحد الأقصى لطول قيمة الحرف هو 30 حرفًا، فاستخدم نوع بيانات حرف طوله 30.
  • إذا كانت كافة قيم أعمدة الأحرف ذات حجم ثابت، فاستخدم char أو nchar. وإلا، استخدم varchar أو nvarchar.
  • إذا كانت قيمة عمود العدد الصحيح القصوى هي 500، فاستخدم smallint لأنه أصغر نوع بيانات بإمكانه أن يستوعب هذه القيمة. لمزيد من المعلومات، راجع نطاقات أنواع البيانات الصحيحة.
• إذا كان ذلك ممكنًا، فاستخدم varchar وchar بدلاً من nvarchar وnchar.
  • استخدم نوع varchar مع بعض ترتيب UTF8 إذا كنت تقرأ البيانات من Parquet أو Azure Cosmos DB أو Delta Lake أو CSV وذلك باستخدام ترميز UTF-8.
  • استخدم نوع varchar بدون ترتيب UTF8 إذا كنت تقرأ البيانات من ملفات CSV غير Unicode (على سبيل المثال، ASCII).
  • استخدم نوع nvarchar إذا كنت تقرأ البيانات من ملف CSV UTF-16.
• استخدم أنواع البيانات المستندة إلى عدد صحيح إذا أمكن. تكتمل عمليات SORT وJOIN وGROUP BY بشكل أسرع على الأعداد الصحيحة أكثر من بيانات الأحرف.
• إذا كنت تستخدم استنتاج المخطط، فتحقق من أنواع البيانات المُستنتجة وتجاوزها بشكل صريح مع الأنواع الأصغر إذا كان ذلك ممكنًا.

التحقق من أنواع البيانات المُستنتجة

يساعدكاستنتاج المخططعلى كتابة الاستعلامات بسرعة واستكشاف البيانات عند عدم معرفتك لمخططات الملفات. تكلفة هذا الملاءمة هي أن أنواع البيانات المُستنتجة قد تكون أكبر من أنواع البيانات الفعلية. يحدث هذا التناقض عند عدم وجود معلومات كافية في ملفات المصدر للتأكد من استخدام نوع البيانات المناسب. على سبيل المثال، ملفات Parquet لا تحتوي على بيانات تعريف حول الحد الأقصى لطول عمود الأحرف. لذلك، يستنتج تجمع SQL بلا خادم أنه varchar(8000).

ضع في اعتبارك أن الموقف يمكن أن يكون مختلفا في حالة جداول Spark المدارة والخارجية القابلة للمشاركة المكشوفة في محرك SQL كجداول خارجية. توفر جداول Spark أنواع بيانات مختلفة عن محركات Synapse SQL. يمكن العثور على التعيين بين أنواع بيانات جدول Spark وأنواع SQL هنا.

يمكنك استخدام الإجراء المخزن للنظام sp_describe_first_results_set للتحقق من أنواع البيانات الناتجة من الاستعلام.

يوضح المثال التالي كيف يمكنك تحسين أنواع البيانات المُستنتجة. يُستخدَم هذا الإجراء لإظهار أنواع البيانات المُستنتجة:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

ترد فيما يلي مجموعة النتائج:

is_hidden	column_ordinal	الاسم	system_type_name	max_length
0	1	vendor_id	varchar(8000)	8000
1	2	pickup_datetime	التاريخ والوقت 2 (7)	8
1	3	passenger_count	العدد الصحيح	4

بعد أن تعرف أنواع البيانات المُستنتجة للاستعلام، يمكنك تحديد أنواع البيانات المناسبة:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

تحسين عامل التصفية

يرد فيما يلي أفضل الممارسات لاستخدام الاستعلامات في تجمع SQL بلا خادم.

دفع أحرف البدل إلى مستويات أقل في المسار

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

استخدام دوال filename وfilepath لاستهداف أقسام معينة

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

لمزيد من المعلومات، اقرأ عن دوال filename وfilepath وراجع الأمثلة على الاستعلام عن ملفات معينة.

تلميح

حوّل نتائج الدوال filepath وfilename بشكل دائم إلى أنواع البيانات المناسبة. إذا كنت تستخدم أنواع بيانات الأحرف، فتأكد من استخدام الطول المناسب.

الدوال المستخدمة لإزالة الأقسام ومسار الملف واسم الملف غير مدعومة حاليًا للجداول الخارجية، بخلاف تلك التي تم إنشاؤها تلقائيًا لكل جدول تم إنشاؤه في Apache Spark لـ Azure Synapse Analytics.

إذا لم تكن بياناتك المخزنة مُقسّمة، ففكر في تقسيمها. بهذه الطريقة يمكنك استخدام هذه الدوال لتحسين الاستعلامات التي تستهدف هذه الملفات. عند الاستعلام عن جداول Apache Spark المُقسّمة لـ Azure Synapse من تجمع SQL بلا خادم، يستهدف الاستعلام تلقائيًا الملفات الضرورية فقط.

استخدام الترتيب المناسب للاستفادة من دفع دالة التقييم لأعمدة الأحرف

تُنظّم البيانات في ملف Parquet في مجموعات الصفوف. يتخطى تجمع SQL بلا خادم مجموعات الصفوف استنادًا إلى دالة التقييم المحددة في عبارة WHERE، ما يقلل من IO. وتكون النتيجة هي زيادة أداء الاستعلام.

يُدعم دفع دالة التقييم لأعمدة الأحرف في ملفات Parquet لترتيب Latin1_General_100_BIN2_UTF8 فقط. بإمكانك تحديد ترتيب لعمود معين باستخدام عبارة WITH. إذا لم تحدد هذا الترتيب باستخدام عبارة WITH، يُستخدم ترتيب قاعدة البيانات.

تحسين الاستعلامات المُتكررة

يرد فيما يلي أفضل الممارسات لاستخدام CETAS في تجمع SQL بلا خادم.

استخدام CETAS لتحسين أداء الاستعلام وعمليات الانضمام

CETAS هي واحدة من أهم الميزات المتوفّرة في تجمع SQL بلا خادم. تُعد CETAS عملية متوازية تعمل على إنشاء بيانات تعريف جدول خارجي وتصدير نتائج استعلام SELECT إلى مجموعة من الملفات في حساب التخزين الخاص بك.

بإمكانك استخدام CETAS لتجسيد أجزاء الاستعلامات المستخدمة بشكل متكرر، مثل جداول المراجع المنضمة، إلى مجموعة جديدة من الملفات. بعد ذلك يمكنك الانضمام إلى هذا الجدول الخارجي الفردي بدلاً من تكرار عمليات الانضمام المشتركة في استعلامات متعددة.

بينما تنشئ CETAS ملفات Parquet، يتم إنشاء الإحصائيات تلقائيًا عندما يستهدف الاستعلام الأول هذا الجدول الخارجي. وتكون النتيجة هي تحسين الأداء للاستعلامات اللاحقة التي تستهدف الجدول الذي تم إنشاؤه باستخدام CETAS.

الاستعلام عن بيانات Azure

تمكنك تجمعات SQL بلا خادم من الاستعلام عن البيانات في Azure Storage أو Azure Cosmos DB باستخدام الجداول الخارجية ودالة OPENROWSET. تأكد من أن لديك الإذن المناسب الذي تم إعداده على التخزين الخاص بك.

الاستعلام عن بيانات CSV

تعرف على كيفية الاستعلام عن ملف CSV واحد أو مجلدات وملفات CSV متعددة. يمكنك أيضاً الاستعلام عن الملفات المقسمة

الاستعلام عن ملفات Parquet

تعرف على كيفية الاستعلام عن ملفات Parquet مع الأنواع المتداخلة. يمكنك أيضاً الاستعلام عن الملفات المقسمة.

استعلام Delta Lake

تعرف على كيفية الاستعلام عن ملفات Delta Lake مع الأنواع المتداخلة.

الاستعلام عن بيانات Azure Cosmos DB

تعرف على كيفية الاستعلام عن المخزن التحليلي لـ Azure Cosmos DB. يمكنك استخدام منشئ عبر الإنترنت لإنشاء عبارة WITH استناداً إلى نموذج مستند Azure Cosmos DB. يمكنك إنشاء طرق عرض أعلى حاويات Azure Cosmos DB.

الاستعلام عن بيانات JSON

تعرف على كيفية الاستعلام عن ملفات JSON. يمكنك أيضاً الاستعلام عن الملفات المقسمة.

إنشاء طرق عرض وجداول وعناصر قاعدة بيانات أخرى

تعرف على كيفية إنشاء طرق العرض والجداول الخارجية واستخدامها أو إعداد الأمان على مستوى الصف. إذا كانت لديك ملفات مقسمة، فتأكد من استخدام طرق العرض المقسمة.

نسخ وتحويل البيانات (CETAS)

تعرف على كيفية تخزين نتائج الاستعلام في التخزين باستخدام الأمر CETAS.

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

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