المصفوفات في BigQuery - كيفية تحسين أداء الاستعلام وتحسين التخزين

من خلال هذا المنشور ، أريد توضيح مزايا استخدام صفائف Bigquery لتسخير قوة BigQuery في حالات الاستخدام التحليلي على نماذج البيانات التقليدية.

خيارات DataModel للمستودعات

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

التحفيز

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

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

  1. نموذج بيانات عادي حيث يتم تخزين أسطر الطلب والطلب كجدولين مختلفين
  2. نموذج بيانات تم إلغاء تطبيعه حيث يتم تخزين أسطر الطلب والطلب في نفس الجدول حيث يتم تكرار بيانات الطلب على مستوى الخط
  3. نموذج بيانات غير عادي يستفيد من صفائف BigQuery حيث يتم تخزين أسطر الطلب والطلب في نفس الجدول ، ولكن لا تتكرر بيانات الطلب

صفائف في BigQuery

المصفوفات عبارة عن أعمدة متعددة القيم في BigQuery ، حيث يحتوي حقل الصفيف على أكثر من قيمة واحدة (من نفس نوع البيانات)

يمكن أن يحتوي كل صف على أعمدة متعددة القيم

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

مجموعة نتائج نمط SQL ، مناسبة لتطبيق أي نوع من أنواع تحليل SQL
ملاحظة: من خلال تخزين الثدييات (16 بايت) والزواحف (18 بايت) مرة واحدة ، قمنا بحفظ (2 * 16 + 3 * 18 = 86) بايت تخزين (72٪) على عمود واحد فقط. إسقاط هذا لجدول مقياس تيرابايت مع أعمدة متعددة ، وهذا يترجم على الفور إلى توفير تكاليف التخزين.

راجع سعر BigQuery Storage لحساب حجم التخزين لأنواع البيانات والمزيد على المصفوفات هنا

بيانات نموذجية

باستخدام سحر المصفوفات والأرقام العشوائية ، يحاكي هذا الاستعلام نظام الطلب عن طريق إنشاء طلب كل 5 ثوانٍ ، مما يمنحنا ما يقرب من 518 ألف طلب في الشهر. يمتد الاستعلام إلى عام واحد ، ويولد 6.3 مليون طلب عشوائي بإجمالي 22 مليون أمر ، مما يخلق حجمًا كافيًا من الطلبات لاختبار أداء نماذج البيانات الثلاثة.

إعداد البيانات

قم بتنزيل هذا الريبت github وقم بتشغيل setup.sh تقوم البرامج النصية بتعبئة البيانات لـ 3 نماذج بيانات.

نموذج البيانات

نموذج البيانات المعياري

علاقة نموذجية بالتفصيل بين النظام والخطوط

نموذج بيانات غير طبيعي

نموذج غير طبيعي مع الطلب ومعرفات العميل وتاريخ الطلب المكرر لكل سطر

تم إلغاء تطبيعه مع BigQuery Arrays

تم حفظ الترتيب والخطوط في نفس الصف ، ولكن لم يتم تكرارها لكل سطر

تجربة أداء

يتيح تشغيل بعض استعلامات إعداد التقارير لاختبار نماذج البيانات هذه من خلال البحث

  1. إجمالي الطلب حسب العميل
  2. إجمالي الطلب حسب الشهر
  3. إجمالي الطلب حسب العنصر

فيما يلي مقارنة بين مقاييس الاستعلام الرئيسية التي تم التقاطها لكل استعلام

حققت النماذج التي تم إلغاء تطبيعها أداءً أفضل على جميع المقاييس باستخدام نموذج BQ Arrays الذي يستهلك أقل وحدات البايت التي تمت معالجتها والوقت المستغرق

مقارنة حجم الجداول حسب نموذج البيانات

مقاييس الاستعلام

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

ملاحظات

  • استهلك النموذج المعياري أكبر قدر من وحدات البايت واستغرق وقتًا أطول لتقديم نتيجة. تحتوي الاستعلامات على أكبر قدر من وحدات البايت المخلوطة (أوامر بحجم أكبر من النموذج المعياري) ، نتيجة الربط بين الجداول. أثبت أنه باهظ التكلفة.
  • استهلك كلا النموذجين De-Normalized الحد الأدنى من وحدات البايت المخلوطة ، حيث أن البيانات تقع في نفس الصف ، ولا توجد وصلات مطلوبة
  • نموذج مع BigQuery Arrays لديه أفضل المقاييس في وحدات البايت التي تمت معالجتها والوقت المستغرق

التوصيات

عند تحديد نماذج البيانات ، ضع في الاعتبار ما يلي للحصول على الأداء الأمثل

  1. استخدم المصفوفات لتخزين الجداول مع علاقات الوالدين / الطفل. كلما زادت الكاردينالية ، زادت الفوائد
  2. استخدم نموذج De-Normalized إذا كان استخدام Arrays يعقد الاستفسارات إما للمطورين أو أنه ينطوي على منحنى تعلم حاد للمستخدمين / المحللين الذين يستخدمون BigQuery
  3. تحسين أنواع البيانات: تحديد أنواع البيانات المناسبة عند تحديد الجداول. فكر في البدائل عند الاقتضاء.

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

مزيد من القراءة / الممارسة

  • تصور خطط BigQuery Query - تمثل أداء هذه الاستعلامات بشكل مرئي ، أخطط لتوضيح خطط تنفيذ الاستعلام باستخدام هذه الأداة الرائعة بواسطة Stephan Meyn
  • كيفية استخدام وظيفة UNNEST في BigQuery لتحليل معلمات الأحداث في Analytics - إليك مقالة باستخدام ممتاز لـ UNNEST بما في ذلك رسم توضيحي جميل بواسطة Todd Kerpelman