חלוקה למחיצות (partitioning) ואשכולות ב-BigQuery

1. מבוא

BigQuery הוא מחסן נתונים (data warehouse) מנוהל בקנה מידה של פטה-בייט שמיועד לניתוח נתונים ובעלות נמוכה. BigQuery הוא ללא שרת (serverless). לא צריך להגדיר ולנהל אשכולות.

מערך נתונים ב-BigQuery נמצא בפרויקט GCP ומכיל טבלה אחת או יותר. תוכלו להריץ שאילתות במערכי הנתונים האלה באמצעות SQL.

בשיעור ה-Codelab הזה תשתמשו בממשק המשתמש של BigQuery באינטרנט במסוף GCP כדי להבין את חלוקה למחיצות (partitioning) וקיבוץ לאשכולות ב-BigQuery. החלוקה למחיצות (partitioning) וקיבוץ בטבלה ב-BigQuery עוזרת לארגן את הנתונים כך שיתאימו לדפוסי גישה נפוצים לנתונים. חלוקה וקיבוץ לאשכולות הם המפתח למיקסום הביצועים והעלות של BigQuery כשאתם שולחים שאילתות על טווח נתונים ספציפי. התוצאה היא פחות נתונים בסריקה של כל שאילתה, והחיתוך נקבע לפני שעת ההתחלה של השאילתה.

מידע נוסף על BigQuery זמין במסמכי התיעוד של BigQuery.

מה תלמדו

  • איך ליצור טבלאות מחולקות למחיצות ומקובצות לאשכולות ולשלוח שאילתות
  • השוואה בין ביצועי שאילתות לבין טבלאות מחולקות למחיצות ומקובצות לאשכולות

מה צריך להכין

כדי להשלים את שיעור ה-Lab תצטרכו:

  • הגרסה האחרונה של Google Chrome
  • חשבון לחיוב ב-Google Cloud Platform

2. בתהליך ההגדרה

כדי לעבוד עם BigQuery, צריך ליצור פרויקט ב-GCP או לבחור פרויקט קיים.

יצירת פרויקט

כדי ליצור פרויקט חדש, פועלים לפי השלבים הבאים:

  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. ב-codelab הזה תעבוד עם מערך הנתונים הציבורי Stack Overflow.

עיון במערך הנתונים Stack Overflow

מערך הנתונים Stack Overflow מכיל מידע על פוסטים, תגים, תגים, תגובות, משתמשים ועוד. כדי לעיין במערך הנתונים Stack Overflow בממשק המשתמש של BigQuery באינטרנט:

  1. פותחים את מערך הנתונים Stack Overflow. ממשק המשתמש של BigQuery באינטרנט ייפתח במסוף GCP ויוצג בו מידע על מערך הנתונים של Stackoverflow.
  2. בחלונית הניווט בוחרים באפשרות bigquery-public-data. התפריט מתרחב ומציג מערכי נתונים ציבוריים. כל מערך נתונים מורכב מטבלה אחת או יותר.
  3. גוללים למטה ובוחרים באפשרות stackoverflow. התפריט יורחב ויופיעו הטבלאות שבמערך הנתונים Stack Overflow.
  4. כדי לראות את הסכימה של טבלת התגים, לוחצים על תגים. בודקים את שמות השדות בטבלה.
  5. מעל שמות השדות, לוחצים על Preview (תצוגה מקדימה) כדי לראות נתונים לדוגמה של טבלת התגים.

למידע נוסף על כל מערכי הנתונים הציבוריים הזמינים ב-BigQuery, ראו מערכי נתונים ציבוריים של Google BigQuery.

שאילתות על מערך הנתונים של Stackoverflow

דפדוף במערך נתונים הוא דרך טובה להבין את הנתונים שאתם עובדים איתם, אבל שליחת שאילתות על מערכי נתונים היא המקום הבולט ביותר ב-BigQuery. בקטע הזה מוסבר איך להריץ שאילתות של BigQuery. בשלב הזה, אתם לא צריכים לדעת SQL. אפשר להעתיק ולהדביק את השאילתות שבהמשך.

כדי להריץ שאילתה, מבצעים את השלבים הבאים:

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.
  2. באזור הטקסט Query Editor, מעתיקים ומדביקים את שאילתת ה-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. בחלונית הניווט במסוף GCP, בוחרים את שם הפרויקט שנוצר כחלק מההגדרה.
  2. בחלונית הפרטים בצד שמאל, בוחרים באפשרות יצירת מערך נתונים.

acc6378c49622323.png

  1. בתיבת הדו-שיח Create dataset מקלידים stackoverflow בשדה Dataset ID. משאירים את כל שאר הגדרות ברירת המחדל במקומן ולוחצים על אישור.

7a2dfd8bcb8f259a.png

יצירת טבלה חדשה עם פוסטים של StackOverflow מ-2018

אחרי שיוצרים מערך נתונים ב-BigQuery, אפשר ליצור טבלה חדשה ב-BigQuery. כדי ליצור טבלה עם נתונים מטבלה קיימת, שולחים שאילתה למערך הנתונים של פוסטים ב-Stack Overflow משנת 2018 ולכתוב את התוצאות בטבלה חדשה. לשם כך, מבצעים את השלבים הבאים:

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.

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-public-data.stackoverflow.posts_questions של BigQuery Stack Overflow.

שאילתות על הטבלה החדשה עם פוסטים ב-Stack Overflow מ-2018

עכשיו, לאחר שיצרתם טבלה ב-BigQuery, נריץ שאילתה כדי להחזיר פוסטים ב-Stack Overflow עם שאלות וכותרות, יחד עם נתונים סטטיסטיים אחרים כמו מספר התשובות, תגובות, צפיות ומועדפים. כך עושים את זה:

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.
  2. באזור הטקסט Query Editor, מעתיקים ומדביקים את שאילתת ה-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 מושבתת. נשבית את המטמון בשאר שיעור ה-Lab כדי לשמור על הוגנות בהשוואת ביצועים מול טבלאות מחולקות למחיצות ומקובצות לאשכולות, שיופעלו בחלקים הבאים. בעורך השאילתות, לוחצים על עוד ובוחרים באפשרות הגדרות השאילתה. הגדרות השאילתה
  4. בקטע העדפה לגבי המטמון, מבטלים את הסימון של שימוש בתוצאות שנשמרו במטמון. אפשרות של תוצאות במטמון
  5. אתם אמורים לראות בתוצאות השאילתה כמה זמן עבר עד להשלמת השאילתה ואת נפח הנתונים שעובדו כדי לקבל את התוצאות.

f197b022b4276338.png

5. יצירת טבלה מחולקת למחיצות ושליחת שאילתות אליה

בקטע הקודם, יצרתם טבלה חדשה ב-BigQuery עם נתונים מהטבלה posts_questions באמצעות מערך הנתונים הציבורי Stack Overflow. בדקנו את מערך הנתונים הזה כשהשמירה במטמון מושבתת, ובחנו את ביצועי השאילתה. בקטע הזה, יוצרים טבלה חדשה עם חלוקה למחיצות מהטבלה posts_questions של מערך הנתונים הציבורי ב-Stack Overflow, ותבדקו את ביצועי השאילתה.

טבלה מחולקת למחיצות היא טבלה מיוחדת שמחולקת לפלחים, שנקראים מחיצות, כדי להקל עליכם לנהל את הנתונים שלכם ולשלוח שאילתות לגביהם. בדרך כלל ניתן לפצל טבלאות גדולות למחיצות קטנות רבות באמצעות זמן הטמעת הנתונים, או העמודה TIMESTAMP/DATE או עמודה INTEGER. ניצור טבלה מחולקת למחיצות (DATE).

כאן אפשר לקרוא מידע נוסף על טבלאות שמחולקות למחיצות.

יצירת טבלה מחולקת למחיצות (Partitions) חדשה עם פוסטים מסוג StackOverflow מ-2018

כדי ליצור טבלה מחולקת למחיצות עם נתונים מטבלה או משאילתה קיימת, מריצים שאילתה על מערך הנתונים של Stack Overflow של פוסטים משנת 2018, ולכתוב את התוצאות בטבלה חדשה. כך עושים את זה:

b9d0ca4df0881f58.png

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.

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-public-data.stackoverflow.posts_questions של BigQuery Stack Overflow

שאילתות על הטבלה המחולקת למחיצות (partitioning) עם פוסטים ב-Stack Overflow מ-2018

עכשיו, לאחר שיצרתם טבלה שמחולקת למחיצות ב-BigQuery, נריץ את אותה השאילתה, הפעם על הטבלה המחולקת למחיצות כדי להחזיר פוסטים ב-Stack Overflow עם שאלות וכותרות, יחד עם נתונים סטטיסטיים אחרים כמו מספר התשובות, התגובות, הצפיות והמועדפים. כך עושים את זה:

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.
  2. באזור הטקסט Query Editor, מעתיקים ומדביקים את שאילתת ה-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. בוחרים באפשרות Run (הפעלה) כאשר השמירה במטמון ב-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

בקטע הזה, יוצרים טבלה חדשה שמחולקת למחיצות ב-creation_date ומקובצת בעמודה tags בהתאם לדפוס הגישה לשאילתה. כדי ליצור טבלה מקובצת עם נתונים מטבלה או משאילתה קיימת, שולחים שאילתה על טבלת הפוסטים ב-Stack Overflow משנת 2018 – ולכתוב את התוצאות בטבלה חדשה. לשם כך:

e7d9acc0dc3b9d79.png

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.

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. הטבלה החדשה מחולקת למחיצות ב-create_date ומקובצת באשכולות בעמודת התגים.

שאילתות על הטבלה המקובצת באשכולות עם פוסטים ב-Stack Overflow

עכשיו, לאחר שיצרתם טבלה מקובצת ב-BigQuery, נריץ שוב את אותה השאילתה, הפעם על הטבלה המחולקת למחיצות או לאשכולות, כדי להחזיר פוסטים ב-Stack Overflow עם שאלות וכותרות, יחד עם נתונים סטטיסטיים אחרים כמו מספר התשובות, התגובות, הצפיות והמועדפים. כך עושים את זה:

  1. ליד הפינה השמאלית העליונה של מסוף GCP, בוחרים באפשרות כתיבת שאילתה חדשה.
  2. באזור הטקסט Query Editor, מעתיקים ומדביקים את שאילתת ה-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. בוחרים באפשרות Run (הפעלה) כאשר השמירה במטמון ב-BigQuery מושבתת (עיינו בקטע הקודם כדי להשבית את המטמון של BigQuery). השאילתה מחזירה שאלות Stack Overflow שנוצרו בחודש ינואר 2018 שתויגו כ-android, לצד השאלה ונתונים סטטיסטיים נוספים.
  2. אתם אמורים לראות בתוצאות השאילתה כמה זמן עבר עד להשלמת השאילתה ואת נפח הנתונים שעובדו כדי לקבל את התוצאות.

85e3c30d6fb3d547.png

טבלה מחולקת למחיצות ומקובצות לאשכולות, כך שהשאילתה סרקה פחות נתונים מאשר טבלה מחולקת למחיצות או טבלה ללא מחיצות. האופן שבו הנתונים מאורגנים על ידי חלוקה למחיצות (partitioning) וקיבוץ לאשכולות (clustering) מצמצם את כמות הנתונים שנסרקים על ידי עובדי מכונות (Slot), ובכך משפר את ביצועי השאילתות ומבצע אופטימיזציה של העלויות.

7. מנקה

אם אתם לא מתכננים להמשיך לעבוד עם מערך הנתונים שלStackoverflow, עליכם למחוק אותו ולמחוק את הפרויקט שיצרתם בשביל ה-Codelab הזה.

מחיקת מערך הנתונים ב-BigQuery

כדי למחוק את מערך הנתונים ב-BigQuery:

  1. בוחרים את מערך הנתונים stackoverflow מחלונית הניווט הימנית ב-BigQuery .
  2. בחלונית הפרטים, לוחצים על מחיקת מערך הנתונים. 67b0f5cb740cb2ec.png
  3. בתיבת הדו-שיח Delete dataset מזינים את הטקסט stackoverflow, ובוחרים באפשרות Delete כדי לאשר שרוצים למחוק את מערך הנתונים.

מחיקת הפרויקט

כדי למחוק את פרויקט GCP שיצרתם עבור Codelab הזה, מבצעים את השלבים הבאים:

  1. בתפריט הניווט של GCP, בוחרים באפשרות IAM & Admin (אדמין).
  2. בחלונית הניווט, לוחצים על הגדרות.
  3. בחלונית הפרטים, מאשרים שהפרויקט הנוכחי הוא הפרויקט שיצרתם בשביל ה-Codelab הזה ובוחרים באפשרות Shut down.
  4. בתיבת הדו-שיח Shut down project, מזינים את מזהה הפרויקט (לא שם הפרויקט) של הפרויקט ובוחרים באפשרות Shut down כדי לאשר.

מעולה! עכשיו למדתם

  • איך משתמשים בממשק המשתמש של BigQuery באינטרנט כדי ליצור טבלה חדשה מטבלאות קיימות
  • איך ליצור טבלאות מחולקות למחיצות ומקובצות לאשכולות ולשלוח שאילתות
  • איך חלוקה למחיצות (partitioning) וקיבוץ לאשכולות (clustering) מייעלת את הביצועים והעלויות של השאילתות

שימו לב שלא היה צורך להגדיר או לנהל אשכולות כדי לעבוד עם מערכי נתונים.