متى يكون استخدام Google BigQuery منطقيًا؟

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

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

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

أنتقل إلى Google BigQuery

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

كان الاستعلام التحليلي معقدًا للغاية وانتهى به الأمر للعمل حوالي 50 دقيقة على خادم Postgres (وحدة المعالجة المركزية رباعية النواة مع ذاكرة وصول عشوائي سعتها 16 جيجابايت). لم أتمكن من تشغيله مسبقًا وتخزين النتائج مؤقتًا ، حيث كان الاستعلام يأخذ الرموز البريدية والأدوية كمعلمات إدخال ، لذلك كان هناك الآلاف من المجموعات ، وكان من المستحيل التنبؤ بأي عميل سيختار.

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

في هذا المشروع بالذات ، جربنا بعض الأساليب المختلفة:

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

أخيرًا ، جربنا Google BigQuery. لقد استوفى توقعاتنا وسمح لنا بإنجاز المهمة دون إجراء تغييرات ضخمة قد يتردد العميل في الموافقة عليها. ولكن ما هو Google BigQuery وكيف يعمل؟

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

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

في هذه المقالة ، سأحاول المقارنة بين استخدام Postgres (قاعدة البيانات العلائقية المفضلة لدي) و BigQuery لسيناريوهات حالة الاستخدام في العالم الحقيقي. سأقدم أيضًا بعض الاقتراحات على طول الطريق ، أي رأيي في الوقت الذي يكون فيه استخدام BigQuery منطقيًا.

بيانات العينة

لمقارنة Postgres و Google BigQuery ، أخذت المعلومات الديموغرافية العامة لكل بلد مجمعة حسب البلد والعمر والسنة والجنس (يمكنك تنزيل البيانات نفسها من هذا الرابط).

أضفت البيانات إلى أربعة جداول:

1. populations
2. locations
3. age_groups
4. populations_aggregated

الجدول الأخير هو مجرد بيانات مجمعة من الجداول الثلاثة السابقة. هنا مخطط قاعدة البيانات:

يحتوي جدول populations الذي انتهيت منه على أكثر من 6.9 مليون صف. إنه ليس كثيرًا ، لكنه كان كافياً للاختبار.

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

1. تم تجميع السكان في الولايات المتحدة حسب السنوات.
2. عدد السكان في عام 2019 لجميع البلدان بدءًا من أكبر الدول.
3. أعلى خمس دول "أقدم" كل عام. تشير "الأقدم" إلى البلدان التي تكون فيها النسبة المئوية للأشخاص الذين تزيد أعمارهم عن 60 عامًا من إجمالي عدد الأشخاص هي الأعلى. يجب أن يعطي الاستعلام خمس نتائج في كل عام.
4. تم تجميع الدول الخمس الأولى حسب السنة ، حيث يكون الفرق بين السكان الذكور والإناث هو الأكبر.
5. احصل على متوسط ​​(متوسط) للعمر لكل بلد لكل عام بدءًا من البلدان "الأكبر سنًا" إلى "الأصغر سنًا".
6. اعثر على البلدان الخمسة الأولى "المحتضرة" كل عام. "الموت" يعني البلدان التي يتناقص فيها عدد السكان (يكون هجرة السكان هو الأعلى).

الاستعلامات رقم 1 و 2 و 6 سهلة ومباشرة للغاية ، لكن الاستعلامات رقم 3 و 4 و 5 لم تكن سهلة الكتابة - على الأقل بالنسبة لي. يرجى ملاحظة أنني مهندس خلفية وأن كتابة استعلامات SQL المعقدة ليست من اختصاصي تمامًا ، لذلك من المحتمل أن يقوم أي شخص لديه خبرة أكبر في SQL ببناء استعلامات أكثر ذكاءً. ومع ذلك ، نحتاج في الوقت الحالي إلى التحقق من كيفية معالجة Postgres و BigQuery لطلبات البحث نفسها باستخدام نفس البيانات.

لقد قمت ببناء 24 استفسارًا في المجموع:

• 6 لـ Postgres DB ، والتي تستخدم جداول غير مجمعة ( populations ، locations ، age_groups )
• 6 بالنسبة لـ Postgres DB ، والتي تستخدم جدول populations_aggregated
• 6 + 6 طلبات بحث عن BigQuery تستخدم جداول مجمعة وغير مجمعة.

اسمح لي بمشاركة استعلامي BigQuery # 1 و # 5 للبيانات المجمّعة حتى تتمكن من فهم مدى تعقيد الاستعلامات البسيطة (# 1) و # 5 المعقدة.

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

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

استعلام عن متوسط ​​العمر لكل بلد في كل عام مصنّفًا من الأكبر إلى الأصغر:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

ملاحظة: يمكنك العثور على جميع الاستعلامات في مستودع bitbucket الخاص بي (الرابط في نهاية المقالة).

نتائج الإختبار

لتشغيل الاستعلامات ، استخدمت خادمين مختلفين من Postgres. الأول يحتوي على وحدة معالجة مركزية واحدة وذاكرة وصول عشوائي سعتها 4 جيجابايت ، مدعومًا بمحرك أقراص SSD. يحتوي الثاني على 16 نواة لوحدة المعالجة المركزية ، وذاكرة وصول عشوائي (RAM) بسعة 64 جيجابايت وكان يستخدم أيضًا محرك أقراص SSD (يحتوي الخادم الثاني على 16 ضعفًا من وحدة المعالجة المركزية وذاكرة الوصول العشوائي).

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

في الاختبار الذي أجريته ، حصلت على هذه النتائج:

اسمح لي بعرض هذه النتائج في مخطط شريطي للاستعلام رقم 1 والاستعلام رقم 5.

ملاحظة: كانت قاعدة بيانات Postgres موجودة على خادم في الولايات المتحدة ، وأنا مقيم في أوروبا ، لذلك كان هناك تأخير إضافي في نقل البيانات Postgres.

أداء BigQuery والاستنتاجات

بناءً على النتائج التي حصلت عليها ، توصلت إلى الاستنتاجات التالية:

• في حالة قياس Postgres عموديًا ، حتى عند 16 مرة ، فإنها تعطي فقط 10-25٪ من الأداء في تشغيل استعلام واحد. بعبارة أخرى ، كان خادم Postgres المزود بنواة واحدة فقط لوحدة المعالجة المركزية وذاكرة الوصول العشوائي سعة 4 غيغابايت يشغل استعلامات بوقت مشابه جدًا للوقت المطلوب للخادم الذي يحتوي على 16 مركزًا لوحدة المعالجة المركزية وذاكرة وصول عشوائي تبلغ 64 جيجابايت. بالطبع ، يمكن للخوادم الأكبر حجمًا معالجة مجموعات بيانات أكبر بكثير ، ومع ذلك ، فإن هذا لا يوفر الكثير من التحسن في وقت تنفيذ الاستعلام.
• بالنسبة إلى صلات Postgres مع الجداول الصغيرة (يحتوي جدول locations على حوالي 400 صف و age_groups بها 100 صف) لا تسفر عن فرق كبير بالمقارنة مع الاستعلامات الجارية ضمن البيانات المجمعة الموجودة في جدول واحد. أيضًا ، وجدت أنه بالنسبة للاستعلامات التي تعمل من ثانية إلى ثانيتين ، تكون الاستعلامات ذات الصلات الداخلية أسرع ، ولكن بالنسبة للاستعلامات طويلة المدى ، يكون الوضع مختلفًا.
• في حالة BigQuery مع الصلات مختلفة تمامًا. لا يحب BigQuery الصلات. الفارق الزمني بين الاستعلامات ، التي تستخدم بيانات مجمعة وغير مجمعة ، كبير جدًا (بالنسبة لطلبات البحث رقم 3 و 5 دولارات ، كانت حوالي ثانيتين). وهذا يعني أنه بالنسبة إلى BigQuery ، يمكنك إجراء العديد من الاستعلامات الفرعية كما تريد ، ولكن للحصول على أداء جيد ، يجب أن يستخدم الاستعلام جدولًا واحدًا.
• يعد Postgres أسرع بالنسبة إلى الاستعلامات التي تستخدم تجميعًا بسيطًا أو تصفية أو استخدام مجموعة بيانات صغيرة. لقد اكتشفت أن طلبات البحث التي تستغرق أقل من خمس ثوانٍ في Postgres تعمل بشكل أبطأ في BigQuery.
• يعمل BigQuery بشكل أفضل مع طلبات البحث طويلة الأمد. كلما زاد الاختلاف في حجم مجموعة البيانات ، سيزداد الاختلاف في الوقت الذي تستغرقه هذه الاستعلامات حتى تكتمل.

متى يكون من المنطقي استخدام BigQuery

الآن ، دعنا نعود إلى المشكلة الأساسية التي تمت مناقشتها في هذه المقالة: متى يجب أن تستخدم Google BigQuery بالفعل؟ بناءً على استنتاجاتي ، أقترح استخدام BigQuery عند استيفاء الشروط التالية:

• استخدمه عندما يكون لديك استعلامات تعمل لأكثر من خمس ثوانٍ في قاعدة بيانات علائقية. تقوم فكرة BigQuery بتشغيل استعلامات تحليلية معقدة ، مما يعني أنه لا فائدة من تشغيل الاستعلامات التي تقوم بالتجميع أو التصفية البسيطة. يعد BigQuery مناسبًا لطلبات البحث "الثقيلة" ، تلك التي تعمل باستخدام مجموعة كبيرة من البيانات. كلما زادت مجموعة البيانات ، زاد احتمال تحقيقك للأداء باستخدام BigQuery. كانت مجموعة البيانات التي استخدمتها 330 ميجابايت فقط (ميجابايت ، ولا حتى جيجابايت).
• لا يحب BigQuery عمليات الصلات ، لذا يجب عليك دمج بياناتك في جدول واحد للحصول على وقت تنفيذ أفضل. يسمح BigQuery بحفظ نتائج الاستعلام في جدول جديد ، لذلك لإنشاء جدول مجمع جديد ، ما عليك سوى تحميل جميع بياناتك إلى BigQuery وتشغيل استعلام لدمج جميع البيانات وحفظها في جدول جديد.
• يعد BigQuery مفيدًا للسيناريوهات التي لا تتغير فيها البيانات كثيرًا وتريد استخدام ذاكرة التخزين المؤقت ، حيث تحتوي على ذاكرة تخزين مؤقت مدمجة. ماذا يعني هذا؟ إذا قمت بتشغيل نفس الاستعلام ولم يتم تغيير (تحديث) البيانات الموجودة في الجداول ، فسيستخدم BigQuery النتائج المخزنة مؤقتًا ولن يحاول تنفيذ الاستعلام مرة أخرى. بالإضافة إلى ذلك ، لا يفرض BigQuery رسومًا مالية مقابل الاستعلامات المخزنة مؤقتًا. ملاحظة: حتى الاستعلامات المخزنة مؤقتًا تستغرق 1-1.2 ثانية لإرجاع النتائج.
• يمكنك أيضًا استخدام BigQuery عندما تريد تقليل الحمل على قاعدة البيانات الارتباطية. الاستعلامات التحليلية "ثقيلة" ويمكن أن يؤدي الإفراط في استخدامها ضمن قاعدة بيانات علائقية إلى مشكلات في الأداء. لذلك ، قد تضطر في النهاية إلى التفكير في توسيع نطاق الخادم الخاص بك. ومع ذلك ، باستخدام BigQuery ، يمكنك نقل هذه الاستعلامات قيد التشغيل إلى خدمة جهة خارجية ، حتى لا تؤثر على قاعدة البيانات العلائقية الرئيسية.

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

الروابط

يمكنك العثور على البيانات النموذجية المستخدمة في هذه المقالة هنا ، بينما يمكن الوصول إلى الاستعلامات والبيانات بتنسيق CSV هنا.