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

1. مقدمة

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

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

في هذا الدرس التطبيقي حول الترميز، ستستخدم واجهة مستخدم الويب في BigQuery في وحدة تحكم GCP لفهم التقسيم والتجميع في 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 News ومستودع 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.

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

يعد تصفح مجموعة بيانات طريقة جيدة لفهم البيانات التي تعمل عليها، لكن الاستعلام عن مجموعات البيانات هو المكان الذي تتألق فيه 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 Posts لعام 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.

الاستعلام عن الجدول الجديد باستخدام 2018 Stack Overflow Posts

الآن بعد أن قمت بإنشاء جدول 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 أو عمود العدد الصحيح. سننشئ جدولاً مقسّمًا لتاريخ DATE.

يمكنك الاطّلاع على المزيد من المعلومات حول الجداول المقسَّمة هنا.

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

لإنشاء جدول مقسم باستخدام بيانات من جدول أو استعلام موجود، ستقوم بالاستعلام عن مجموعة بيانات 2018 Stack Overflow وكتابة النتائج إلى جدول جديد، أكمل الخطوات التالية:

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. حدد "Run" (تشغيل). ينشئ الطلب جدولاً جديدًا questions_2018_clustered في مجموعة بيانات stackoverflow في مشروعك باستخدام البيانات الناتجة عن تشغيل طلب على جدول BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. يتم تقسيم الجدول الجديد في create_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 (GCP)، اختَر IAM (إدارة الهوية وإمكانية الوصول) المشرف.
  2. في لوحة التنقّل، اختَر الإعدادات.
  3. في لوحة التفاصيل، تأكَّد من أنّ مشروعك الحالي هو المشروع الذي أنشأته لهذا الدرس التطبيقي حول الترميز واختَر إيقاف التشغيل.
  4. في مربّع الحوار إيقاف المشروع، أدخِل رقم تعريف المشروع (وليس اسم المشروع) لمشروعك واختَر إيقاف التشغيل للتأكيد.

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

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

تجدر الإشارة إلى أنّك لم تكن مضطرًا إلى إعداد المجموعات أو إدارتها للعمل مع مجموعات البيانات.