التقسيم والتجميع العنقودي في BigQuery

1. مقدمة

‫BigQuery هو مستودع بيانات مُدار بالكامل ومنخفض التكلفة على نطاق بيتابايت للمؤسسات، وهو مخصّص لإجراء الإحصاءات. ‫BigQuery لا تتضمّن خوادم. ليس عليك إعداد المجموعات وإدارتها.

تتوفّر مجموعة بيانات BigQuery في مشروع على "منصة Google Cloud"، وتحتوي على جدول واحد أو أكثر. يمكنك الاستعلام عن مجموعات البيانات هذه باستخدام SQL.

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

لمزيد من المعلومات عن BigQuery، يُرجى الاطّلاع على مستندات BigQuery.

أهداف الدورة التعليمية

  • كيفية إنشاء جداول مقسّمة ومجمّعة وتنفيذ طلبات بحث فيها
  • مقارنة أداء طلبات البحث مع الجداول المقسّمة والمجمّعة

المتطلبات

لإكمال هذا الدرس التطبيقي، يجب أن يتوفّر لديك ما يلي:

  • أحدث إصدار من Google Chrome
  • حساب فوترة على Google Cloud Platform

2. الإعداد

للعمل باستخدام BigQuery، عليك إنشاء مشروع على Google Cloud Platform أو اختيار مشروع حالي.

إنشاء مشروع

لإنشاء مشروع جديد، اتّبِع الخطوات التالية:

  1. إذا لم يكن لديك حساب على Google (Gmail أو Google Apps)، يمكنك إنشاء حساب.
  2. سجِّل الدخول إلى "وحدة تحكّم Google Cloud Platform" (console.cloud.google.com) وأنشِئ مشروعًا جديدًا.
  3. إذا لم يكن لديك أي مشاريع، انقر على زر إنشاء مشروع:

870a3cbd6541ee86.png

بخلاف ذلك، أنشئ مشروعًا جديدًا من قائمة اختيار المشاريع:

f6dff3437a20cf2.png

  1. أدخِل اسم المشروع وانقر على إنشاء. دوِّن رقم تعريف المشروع، وهو اسم فريد في جميع مشاريع Google Cloud.

1884405a64ce5765.png

3- العمل مع مجموعات البيانات العامة

تتيح لك BigQuery العمل مع مجموعات البيانات العامة، بما في ذلك "أخبار BBC" ومستودعات GitHub وStack Overflow ومجموعات بيانات "الإدارة الوطنية للمحيطات والغلاف الجوي" (NOAA) في الولايات المتحدة. لست بحاجة إلى تحميل مجموعات البيانات هذه إلى BigQuery. ما عليك سوى فتح مجموعات البيانات لتصفّحها والاستعلام عنها في BigQuery. في هذا الدرس التطبيقي حول الترميز، ستعمل على مجموعة البيانات العامة Stack Overflow.

تصفُّح مجموعة بيانات Stack Overflow

تحتوي مجموعة بيانات Stack Overflow على معلومات حول المشاركات والعلامات والشارات والتعليقات والمستخدمين وغير ذلك. لتصفّح مجموعة بيانات Stack Overflow في واجهة مستخدم الويب في BigQuery، اتّبِع الخطوات التالية:

  1. افتح مجموعة بيانات Stack Overflow. يتم فتح واجهة مستخدم BigQuery على الويب في وحدة تحكّم Google Cloud Platform وعرض معلومات حول مجموعة بيانات Stackoverflow.
  2. في لوحة التنقّل، اختَر bigquery-public-data. تتوسّع القائمة لعرض مجموعات البيانات العلنية. تتضمّن كل مجموعة بيانات جدولاً واحدًا أو أكثر.
  3. انتقِل إلى أسفل الصفحة وانقر على stackoverflow. تتوسّع القائمة لعرض الجداول في مجموعة بيانات Stack Overflow.
  4. انقر على الشارات للاطّلاع على مخطط جدول الشارات. دوِّن أسماء الحقول في الجدول.
  5. فوق "أسماء الحقول"، انقر على معاينة للاطّلاع على عيّنة من بيانات جدول الشارات.

لمزيد من المعلومات عن جميع مجموعات البيانات العامة المتوفّرة في BigQuery، يمكنك الاطّلاع على مجموعات بيانات Google BigQuery العامة.

الاستعلام عن مجموعة بيانات Stackoverflow

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

لتنفيذ طلب بحث، أكمِل الخطوات التالية:

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.
  2. في مساحة نص محرّر الاستعلام، انسَخ استعلام SQL التالي والصِقه. يتحقّق BigQuery من صحة طلب البحث، وتعرض واجهة مستخدم الويب علامة اختيار خضراء أسفل مساحة النص للإشارة إلى أنّ بنية طلب البحث صالحة.
SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10
  1. انقر على تشغيل. يعرض الاستعلام عدد المشاركات أو الأسئلة المنشورة على Stack Overflow كل عام.

4. إنشاء جدول جديد

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

إنشاء مجموعة بيانات جديدة

لإنشاء بيانات جدول وتحميلها إلى BigQuery، عليك أولاً إنشاء مجموعة بيانات BigQuery لتخزين البيانات من خلال إكمال الخطوات التالية:

  1. في لوحة التنقّل في وحدة تحكّم Google Cloud Platform، اختَر اسم المشروع الذي تم إنشاؤه كجزء من عملية الإعداد.
  2. في اللوحة التفصيلية على يسار الصفحة، انقر على إنشاء مجموعة بيانات.

acc6378c49622323.png

  1. في مربّع الحوار إنشاء مجموعة بيانات، أدخِل stackoverflow في حقل معرّف مجموعة البيانات. اترك جميع الإعدادات التلقائية الأخرى كما هي وانقر على حسنًا.

7a2dfd8bcb8f259a.png

إنشاء جدول جديد يتضمّن مشاركات StackOverflow لعام 2018

بعد إنشاء مجموعة بيانات BigQuery، يمكنك إنشاء جدول جديد في BigQuery. لإنشاء جدول يتضمّن بيانات من جدول حالي، عليك الاستعلام عن مجموعة بيانات مشاركات Stack Overflow لعام 2018 وكتابة النتائج في جدول جديد، وذلك من خلال إكمال الخطوات التالية:

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.

9ca55f544e8da8bd.png

  1. في مساحة نص محرّر طلبات البحث، انسخ طلب بحث SQL التالي والصقه لإنشاء جدول جديد، وهو عبارة DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. انقر على تشغيل. ينشئ طلب البحث جدولاً جديدًا questions_2018 في مجموعة البيانات stackoverflow ضمن مشروعك يتضمّن البيانات الناتجة عن تنفيذ طلب بحث على مجموعة بيانات BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

طلب البحث في الجدول الجديد باستخدام مشاركات Stack Overflow لعام 2018

بعد إنشاء جدول BigQuery، لننفّذ طلب بحث لعرض مشاركات Stack Overflow التي تتضمّن أسئلة وعناوين بالإضافة إلى بعض الإحصاءات الأخرى، مثل عدد الإجابات والتعليقات والمشاهدات والمفضّلة. أكمِل الخطوات التالية:

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.
  2. في مساحة نص محرّر الاستعلام، انسخ استعلام SQL التالي والصِقه
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. انقر على تشغيل. يعرض الاستعلام أسئلة Stack Overflow التي تم إنشاؤها في شهر يناير 2018 والمصنّفة على أنّها android، بالإضافة إلى السؤال وبعض الإحصاءات الأخرى.
  2. بشكلٍ تلقائي، يخزّن BigQuery نتائج طلب البحث مؤقتًا. نفِّذ طلب البحث نفسه وستلاحظ أنّ BigQuery استغرق وقتًا أقل بكثير لعرض النتائج لأنّه يعرض النتائج من ذاكرة التخزين المؤقت.
  3. نفِّذ طلب البحث نفسه مرة أخرى ولكن مع إيقاف التخزين المؤقت في BigQuery. سنوقف مؤقتًا استخدام ذاكرة التخزين المؤقت لبقية التجربة لضمان عدالة مقارنة الأداء مع الجداول المقسّمة والمجمّعة التي سيتم تشغيلها في الأقسام التالية. في محرّر طلب البحث، انقر على المزيد واختَر إعدادات طلب البحث. إعدادات طلب البحث
  4. ضمن الإعدادات المفضّلة لذاكرة التخزين المؤقت، أزِل العلامة من المربّع بجانب استخدام النتائج المخزّنة مؤقتًا. خيار النتائج المخزَّنة مؤقتًا
  5. في نتائج الاستعلام، من المفترض أن يظهر لك الوقت الذي استغرقه الاستعلام لإكماله وحجم البيانات التي تمت معالجتها للحصول على النتائج.

f197b022b4276338.png

5- إنشاء جدول مقسَّم والاستعلام عنه

في القسم السابق، أنشأت جدولاً جديدًا في BigQuery يتضمّن بيانات من الجدول posts_questions باستخدام مجموعة البيانات العامة Stack Overflow. أرسلنا طلب بحث إلى مجموعة البيانات هذه مع إيقاف التخزين المؤقت، ولاحظنا أداء طلب البحث. في هذا القسم، ستنشئ جدولاً مقسَّمًا جديدًا من جدول posts_questions نفسه في مجموعة البيانات العامة Stack Overflow، وستلاحظ أداء الاستعلام.

الجدول المقسَّم هو جدول خاص مقسَّم إلى أجزاء تُعرف باسم الأقسام، ما يسهّل إدارة بياناتك والاستعلام عنها. يمكنك عادةً تقسيم الجداول الكبيرة إلى العديد من الأقسام الأصغر باستخدام وقت نقل البيانات أو عمود TIMESTAMP/DATE أو عمود INTEGER. سننشئ جدولاً مقسَّمًا حسب التواريخ.

مزيد من المعلومات حول الجداول المقسّمة

إنشاء جدول مقسَّم جديد يتضمّن مشاركات StackOverflow لعام 2018

لإنشاء جدول مقسَّم يتضمّن بيانات من جدول أو طلب بحث حالي، عليك الاستعلام عن مجموعة بيانات مشاركات Stackoverflow لعام 2018 وكتابة النتائج في جدول جديد، وذلك باتّباع الخطوات التالية:

b9d0ca4df0881f58.png

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.

9ca55f544e8da8bd.png

  1. في مساحة نص محرّر طلبات البحث، انسخ طلب بحث SQL التالي والصقه لإنشاء جدول جديد، وهو عبارة DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. انقر على تشغيل. ينشئ طلب البحث جدولاً جديدًا questions_2018_partitioned في مجموعة البيانات stackoverflow ضمن مشروعك يتضمّن البيانات الناتجة عن تنفيذ طلب بحث على مجموعة بيانات BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

طلب البحث في الجدول المقسَّم الذي يتضمّن مشاركات Stack Overflow لعام 2018

بعد إنشاء جدول مقسَّم في BigQuery، لننفِّذ طلب البحث نفسه، ولكن هذه المرة على الجدول المقسَّم، لعرض مشاركات Stack Overflow التي تتضمّن أسئلة وعناوين بالإضافة إلى بعض الإحصاءات الأخرى، مثل عدد الإجابات والتعليقات والمشاهدات والمفضّلة. أكمِل الخطوات التالية:

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.
  2. في مساحة نص محرّر الاستعلام، انسخ استعلام SQL التالي والصِقه
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. اختَر تنفيذ مع إيقاف التخزين المؤقت في BigQuery (راجِع القسم السابق لمعرفة كيفية إيقاف التخزين المؤقت في BigQuery). يعرض الاستعلام أسئلة Stack Overflow التي تم إنشاؤها في شهر يناير 2018 والمصنّفة على أنّها android، بالإضافة إلى السؤال وبعض الإحصاءات الأخرى.
  2. في نتائج الاستعلام، من المفترض أن يظهر لك الوقت الذي استغرقه الاستعلام لإكماله وحجم البيانات التي تمت معالجتها للحصول على النتائج.

ef01144374069823.png

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

6. إنشاء جدول مقسَّم إلى مجموعات وإجراء طلب بحث فيه

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

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

مزيد من المعلومات حول استخدام الجداول المجمّعة

إنشاء جدول جديد مقسّم إلى مجموعات باستخدام منشورات Stack Overflow لعام 2018

في هذا القسم، ستنشئ جدولاً جديدًا مقسَّمًا حسب creation_date ومجمَّعًا حسب العمود tags استنادًا إلى نمط الوصول إلى الاستعلام. لإنشاء جدول مقسَّم إلى مجموعات باستخدام بيانات من جدول أو طلب بحث حالي، عليك الاستعلام عن جدول مشاركات Stack Overflow لعام 2018 وكتابة النتائج في جدول جديد من خلال إكمال الخطوات التالية:

e7d9acc0dc3b9d79.png

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.

9ca55f544e8da8bd.png

  1. في مساحة نص محرّر طلبات البحث، انسخ طلب بحث SQL التالي والصقه لإنشاء جدول جديد، وهو عبارة DDL.
#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. انقر على "تشغيل". ينشئ طلب البحث جدولاً جديدًا questions_2018_clustered في مجموعة البيانات stackoverflow ضمن مشروعك يتضمّن بيانات ناتجة عن تنفيذ طلب بحث على جدول BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. يتم تقسيم الجدول الجديد حسب creation_date وتجميعه حسب عمود العلامات.

طلب البحث في الجدول المجمّع الذي يتضمّن مشاركات Stack Overflow لعام 2018

بعد إنشاء جدول مقسّم إلى مجموعات في BigQuery، لننفّذ طلب البحث نفسه مرة أخرى، ولكن هذه المرة على الجدول المقسّم والمجمّع، لعرض مشاركات Stack Overflow التي تتضمّن أسئلة وعناوين بالإضافة إلى بعض الإحصاءات الأخرى، مثل عدد الإجابات والتعليقات والمشاهدات والمفضّلة. أكمِل الخطوات التالية:

  1. بالقرب من أعلى يسار وحدة تحكّم Google Cloud Platform، انقر على إنشاء طلب بحث جديد.
  2. في مساحة نص محرّر الاستعلام، انسخ استعلام SQL التالي والصِقه
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. اختَر تنفيذ مع إيقاف التخزين المؤقت في BigQuery (راجِع القسم السابق لمعرفة كيفية إيقاف التخزين المؤقت في BigQuery). يعرض الاستعلام أسئلة Stack Overflow التي تم إنشاؤها في شهر يناير 2018 والمصنّفة على أنّها android، بالإضافة إلى السؤال وبعض الإحصاءات الأخرى.
  2. في نتائج الاستعلام، من المفترض أن يظهر لك الوقت الذي استغرقه الاستعلام لإكماله وحجم البيانات التي تمت معالجتها للحصول على النتائج.

85e3c30d6fb3d547.png

باستخدام جدول مقسَّم ومجمَّع، فحص طلب البحث بيانات أقل من الجدول المقسَّم أو الجدول غير المقسَّم. إنّ طريقة تنظيم البيانات من خلال التقسيم والتجميع تقلّل من كمية البيانات التي يتم فحصها بواسطة العاملين في الفتحات، ما يؤدي إلى تحسين أداء الاستعلامات وتحسين التكاليف.

7. تنظيف

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

حذف مجموعة بيانات BigQuery

لحذف مجموعة بيانات BigQuery، اتّبِع الخطوات التالية:

  1. اختَر مجموعة بيانات stackoverflow من لوحة التنقّل على الجانب الأيمن في BigQuery .
  2. في لوحة التفاصيل، انقر على حذف مجموعة البيانات. 67b0f5cb740cb2ec.png
  3. في مربّع الحوار حذف مجموعة البيانات، أدخِل stackoverflow وانقر على حذف لتأكيد رغبتك في حذف مجموعة البيانات.

حذف المشروع

لحذف مشروع Google Cloud Platform الذي أنشأته لهذا الدرس العملي، اتّبِع الخطوات التالية:

  1. في قائمة التنقّل في Google Cloud Platform، اختَر إدارة الهوية وإمكانية الوصول والمشرف.
  2. في لوحة التنقّل، اختَر الإعدادات.
  3. في لوحة التفاصيل، تأكَّد من أنّ مشروعك الحالي هو المشروع الذي أنشأته لهذا الدرس العملي، ثم انقر على إيقاف.
  4. في مربّع الحوار إيقاف المشروع، أدخِل رقم تعريف المشروع (وليس اسم المشروع) ثم انقر على إيقاف للتأكيد.

تهانينا! لقد تعرّفت الآن على

  • كيفية استخدام واجهة مستخدم الويب في BigQuery لإنشاء جدول جديد من جداول حالية
  • كيفية إنشاء جداول مقسّمة ومجمّعة والاستعلام عنها
  • كيف يؤدي التقسيم والتجميع إلى تحسين أداء طلب البحث والتكاليف؟

يُرجى العِلم أنّه لم يكن عليك إعداد المجموعات أو إدارتها للعمل مع مجموعات البيانات.