مشاركة عبر

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

  • مقالة

في هذه المقالة، ستجد توصيات وتحسينات الأداء لتحميل البيانات.

إعداد البيانات في Azure Storage

لتقليل زمن الانتقال، قم بتخصيص طبقة التخزين الخاصة بك وتجمع SQL المخصص.

عند تصدير البيانات إلى تنسيق ملف ORC، قد تحصل على أخطاء Java خارج الذاكرة عند وجود أعمدة نص كبيرة. للتغلب على هذا القيد، قم بتصدير مجموعة فرعية فقط من الأعمدة.

لا يمكن لـPolyBase تحميل الصفوف التي تحتوي على أكثر من 1000000 بايت من البيانات. عند وضع البيانات في الملفات النصية في تخزين Azure Blob أو Azure Data Lake Store، يجب أن يكون لديها أقل من 1000000 بايت من البيانات. تقييد البايت هذا صحيح بغض النظر عن مخطط الجدول.

جميع تنسيقات الملفات لها خصائص أداء مختلفة. للحصول على أسرع تحميل، استخدم الملفات النصية المضغوطة المحددة. الفرق بين أداء UTF-8 وUTF-16 ضئيل.

تقسيم الملفات المضغوطة الكبيرة إلى ملفات مضغوطة أصغر.

تشغيل الأحمال مع حوسبة كافية

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

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

إنشاء مستخدم تحميل

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

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

الاتصال بوعاء SQL المخصص وإنشاء مستخدم. تفترض التعليمة البرمجية التالية أنك كنت متصلاً بقاعدة بيانات تسمى mySampleDataWarehouse. وتوضح كيفية إنشاء مستخدم يسمى loader وتعطي المستخدم أذونات لإنشاء الجداول والتحميل باستخدام عبارة COPY. ثم تصنف المستخدم إلى مجموعة حمل العمل DataLoads بأقصى قدر من الموارد.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



هام

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

لتشغيل تحميل مع موارد لمجموعة تحميل حمل العمل، سجل الدخول كمحمل وقم بتشغيل التحميل.

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

غالبًا ما تكون هناك حاجة إلى تحميل عدة مستخدمين للبيانات في مستودع بيانات. يتطلب التحميل باستخدام CREATE TABLE AS SELECT (Transact-SQL) أذونات CONTROL لقاعدة البيانات. يمنح إذن CONTROL حق الوصول إلى كافة المخططات. قد لا ترغب في أن يكون لجميع المستخدمين الذين يتم تحميلهم حق الوصول إلى التحكم في جميع المخططات. للحد من الأذونات، استخدم عبارة DENY CONTROL.

على سبيل المثال، ضع في اعتبارك مخططات قاعدة البيانات، schema_A لـdept A، schema_B للمخطط B. السماح لمستخدمي قاعدة البيانات user_A user_B يكونوا مستخدمين لتحميل PolyBase في قسم A وB، على التوالي. تم منحهما أذونات قاعدة بيانات CONTROL. يقوم منشئو المخطط A وB الآن بتأمين مخططاتهم باستخدام DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

يتم الآن تأمين User_A user_B من مخطط قسم آخر.

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

لتحقيق أسرع سرعة تحميل لنقل البيانات إلى جدول مستودع بيانات، قم بتحميل البيانات في جدول مرحلي. حدد الجدول المرحلي ككومة الذاكرة المؤقتة واستخدم الترتيب الدوري لخيار التوزيع.

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

التحميل إلى فهرس تخزين الأعمدة

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

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

زيادة حجم الدفعة عند استخدام واجهة برمجة تطبيقات SQLBulkCopy أو BCP

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

تلميح

حجم الدفعة بين 100 K إلى 1M هو الأساس الموصى به لتحديد السعة المثلى لحجم الدفعة.

إدارة فشل التحميل

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

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

إدراج البيانات في جدول إنتاج

قد يؤدي التحميل لمرة واحدة إلى جدول صغير مع عبارة INSERT، أو حتى إعادة التحميل الدورية للبحث بشكل جيد بما يكفي مع عبارة مثل INSERT INTO MyLookup VALUES (1, 'Type 1'). ومع ذلك، فإن إدراجات مفردة ليست فعالة مثل تنفيذ التحميل المجمع.

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

إنشاء إحصائيات بعد التحميل

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

للحصول على شرح مفصل للإحصاءات، راجع الإحصائيات. يوضح المثال التالي كيفية إنشاء إحصائيات يدويًا على خمسة أعمدة من الجدول Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

تدوير مفاتيح التخزين

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

لتدوير مفاتيح حساب Azure Storage:

لكل حساب تخزين تم تغيير مفتاحه، قم بإصدار ALTER DATABASE SCOPED CREDENTIAL.

مثال:

يتم إنشاء المفتاح الأصلي

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

تدوير المفتاح من المفتاح 1 إلى المفتاح 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

لا توجد حاجة إلى تغييرات أخرى على مصادر البيانات الخارجية الأساسية.

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