מבוא לטבלאות מחולקות למחיצות (Partitions)

טבלה מחולקת למחיצות מחולקת לפלחים, שנקראים מחיצות, כדי שיהיה קל יותר לנהל את הנתונים ולבצע בהם שאילתות. חלוקה של טבלה גדולה למחיצות קטנות יותר משפרת את ביצועי השאילתות ומאפשרת לשלוט בעלויות, כי היא מצמצמת את מספר הבייטים שהשאילתה קוראת. כדי לחלק טבלה למחיצות, מציינים עמודת חלוקה למחיצות שבה BigQuery משתמש כדי לפלח את הטבלה.

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

איך פועלת חלוקה למחיצות

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

מתי כדאי להשתמש בחלוקה למחיצות

כדאי לחלק טבלה למחיצות בתרחישים הבאים:

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

במקרים הבאים כדאי לשקול יצירת אשכולות בטבלה במקום חלוקה למחיצות:

  • אתם צריכים פירוט רב יותר ממה שמאפשרת החלוקה למחיצות.
  • השאילתות שלכם משתמשות בדרך כלל במסננים או בצבירה בכמה עמודות.
  • העוצמה (cardinality) של מספר הערכים בעמודה או בקבוצת עמודות היא גדולה.
  • אתם לא צריכים הערכות עלות מדויקות לפני הרצת השאילתה.
  • החלוקה למחיצות יוצרת כמות קטנה של נתונים בכל מחיצה (בערך פחות מ-10GB). יצירה של הרבה מחיצות קטנות מגדילה את המטא-נתונים של הטבלה, ויכולה להשפיע על זמני הגישה למטא-נתונים כששולחים שאילתות לטבלה.
  • החלוקה למחיצות יוצרת מספר גדול של מחיצות, שחורג מהמגבלות על טבלאות מחולקות למחיצות.
  • פעולות ה-DML משנות לעיתים קרובות (לדוגמה, כל כמה דקות) את רוב המחיצות בטבלה.

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

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

סוגי חלוקה למחיצות

בקטע הזה מתוארות הדרכים השונות לחלוקה של טבלה למחיצות.

חלוקה למחיצות לפי טווח של מספרים שלמים

אפשר לחלק טבלה למחיצות על סמך טווחי ערכים בעמודה ספציפית מסוג INTEGER. כדי ליצור טבלה שמחולקת לפי טווח של מספרים שלמים, צריך לספק את הפרטים הבאים:

  • עמודת החלוקה למחיצות.
  • ערך ההתחלה של חלוקה למחיצות לפי טווח (כולל).
  • ערך הסיום של חלוקה למחיצות לפי טווח (לא כולל).
  • המרווח של כל טווח בתוך המחיצה.

לדוגמה, נניח שאתם יוצרים חלוקה למחיצות לפי טווח של מספרים שלמים עם המפרט הבא:

ארגומנט ערך
שם העמודה customer_id
התחלה 0
מקש End 100
מרווח זמן 10

הטבלה מחולקת למחיצות בעמודה customer_id לפי טווחים של מרווח 10. הערכים 0 עד 9 נכנסים למחיצה אחת, הערכים 10 עד 19 נכנסים למחיצה הבאה וכן הלאה, עד 99. ערכים מחוץ לטווח הזה נכנסים למחיצה בשם __UNPARTITIONED__. כל השורות שבהן הערך של customer_id הוא NULL נכנסות למחיצה בשם __NULL__.

מידע על טבלאות שמחולקות למחיצות לפי טווח של מספרים שלמים זמין במאמר יצירת טבלה שמחולקת למחיצות לפי טווח של מספרים שלמים.

חלוקה למחיצות לפי עמודת יחידת זמן

אפשר לחלק טבלה לפי עמודה מסוג DATE,‏ TIMESTAMP או DATETIME. כשכותבים נתונים בטבלה, BigQuery ממקם את הנתונים באופן אוטומטי במחיצה הנכונה, על סמך הערכים בעמודה.

בעמודות TIMESTAMP ו-DATETIME, המחיצות יכולות להיות ברמת פירוט שעתית, יומית, חודשית או שנתית. בעמודות DATE, המחיצות יכולות להיות ברמת פירוט יומית, חודשית או שנתית. גבולות המחיצות מבוססים על זמן UTC.

לדוגמה, נניח שאתם מחלקים למחיצות טבלה בעמודה DATETIME עם חלוקה חודשית למחיצות. אם מוסיפים לטבלה את הערכים הבאים, השורות נכתבות במחיצות הבאות:

ערך עמודה מחיצה (חודשי)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

בנוסף, נוצרות שתי מחיצות מיוחדות:

  • ‫__NULL__: מכיל שורות עם ערכי NULL בעמודת החלוקה למחיצות.
  • ‫__UNPARTITIONED__: מכיל שורות שבהן הערך של עמודת החלוקה למחיצות הוא לפני 1960-01-01 או אחרי 2159-12-31.

למידע על טבלאות שמחולקות לפי עמודת יחידת זמן, קראו את המאמר יצירת טבלה שמחולקת לפי עמודת יחידת זמן.

חלוקה למחיצות לפי זמן הטמעה

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

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

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

זמן הטמעה _PARTITIONTIME מחיצה (שעתית)
2021-05-07 17:22:00 2021-05-07 17:00:00 2021050717
2021-05-07 17:40:00 2021-05-07 17:00:00 2021050717
2021-05-07 18:31:00 2021-05-07 18:00:00 2021050718

מכיוון שהטבלה בדוגמה הזו מחולקת למחיצות לפי שעה, הערך של _PARTITIONTIME נחתך לגבול של שעה. ‫BigQuery משתמש בערך הזה כדי לקבוע את המחיצה הנכונה לנתונים.

אפשר גם לכתוב נתונים למחיצה ספציפית. לדוגמה, יכול להיות שתרצו לטעון נתונים היסטוריים או להתאים את אזורי הזמן. אפשר להשתמש בכל תאריך תקין בין 0001-01-01 ל-9999-12-31. עם זאת, הצהרות DML לא יכולות להפנות לתאריכים שלפני 1970-01-01 או אחרי 2159-12-31. מידע נוסף זמין במאמר כתיבת נתונים למחיצה ספציפית.

במקום להשתמש ב-_PARTITIONTIME, אתם יכולים להשתמש גם ב-_PARTITIONDATE. העמודה המדומה _PARTITIONDATE מכילה את התאריך ב-UTC שתואם לערך בעמודה המדומה _PARTITIONTIME.

בוחרים חלוקה למחיצות לפי יום, שעה, חודש או שנה.

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

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

  • בחרו באפשרות חלוקה למחיצות לפי שעה אם הטבלאות שלכם מכילות נפח גדול של נתונים שמתפרסים על פני טווח תאריכים קצר – בדרך כלל פחות משישה חודשים של ערכי חותמות זמן. אם בוחרים בחלוקה למחיצות לפי שעה, חשוב לוודא שמספר המחיצות לא חורג ממגבלות המחיצות.

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

שילוב של טבלאות מקובצות ומחולקות למחיצות

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

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

כשיוצרים טבלה עם אשכולות ומחיצות, אפשר למיין בצורה מפורטת יותר, כמו שמוצג בתרשים הבא:

השוואה בין טבלאות שלא מקובצות לאשכולות או מחולקות למחיצות לבין טבלאות שכן מקובצות לאשכולות ומחולקות למחיצות.

חלוקה למחיצות לעומת חלוקה לפלחים (sharding)

פיצול טבלאות הוא שיטה לאחסון נתונים בכמה טבלאות, באמצעות תחילית לשמות כמו [PREFIX]_YYYYMMDD.

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

אם יצרתם בעבר טבלאות עם חלוקה לפי תאריכים, אתם יכולים להמיר אותן לטבלה מחולקת למחיצות (Partitions) לפי זמני כתיבת הנתונים. למידע נוסף, קראו את המאמר המרת טבלאות עם חלוקה לפי תאריכים לטבלאות מחולקות למחיצות (Partitions) לפי זמני הטמעת הנתונים.

מעצבי מחיצות

בעזרת מעצבי מחיצות אפשר להפנות למחיצה בטבלה. לדוגמה, אפשר להשתמש בהם כדי לכתוב נתונים למחיצה ספציפית.

למזהה של מחיצה יש את הצורה table_name$partition_id, כאשר הפורמט של הקטע partition_id תלוי בסוג החלוקה למחיצות:

סוג החלוקה למחיצות פורמט דוגמה
מדי שעה yyyymmddhh my_table$2021071205
יומי yyyymmdd my_table$20210712
כל חודש yyyymm my_table$202107
כל שנה yyyy my_table$2021
טווח של מספרים שלמים range_start my_table$40

עיון בנתונים במחיצה

כדי לעיין בנתונים במחיצה מסוימת, משתמשים בפקודה bq head עם מעטר מחיצה.

לדוגמה, הפקודה הבאה מציגה את כל השדות ב-10 השורות הראשונות של my_dataset.my_table במחיצה 2018-02-24:

    bq head --max_rows=10 'my_dataset.my_table$20180224'

ייצוא נתוני הטבלה

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

כדי לייצא נתונים ממחיצה ספציפית, משתמשים בפקודה bq extract ומוסיפים את מעטר המחיצה לשם הטבלה. לדוגמה, my_table$20160201. אפשר גם לייצא נתונים מהמחיצות __NULL__ ו-__UNPARTITIONED__ על ידי הוספת שמות המחיצות לשם הטבלה. לדוגמה, my_table$__NULL__ או my_table$__UNPARTITIONED__.

מגבלות

לטבלאות מחולקות למחיצות יש את המגבלות הבאות:

  • אי אפשר להשתמש ב-SQL מדור קודם כדי לשלוח שאילתות לטבלאות מחולקות למחיצות, או כדי לכתוב תוצאות של שאילתות בטבלאות מחולקות למחיצות.

  • ב-BigQuery אין תמיכה בחלוקה למחיצות לפי כמה עמודות. אפשר להשתמש רק בעמודה אחת כדי לחלק טבלה למחיצות.

  • אי אפשר להמיר ישירות טבלה קיימת ללא מחיצות לטבלה עם מחיצות. אסטרטגיית החלוקה למחיצות מוגדרת כשיוצרים את הטבלה. במקום זאת, צריך להשתמש בהצהרה CREATE TABLE כדי ליצור טבלה חדשה מחולקת למחיצות, על ידי שליחת שאילתה לנתונים בטבלה הקיימת.

  • טבלאות שמחולקות לפי עמודות של יחידות זמן כפופות למגבלות הבאות:

    • עמודת החלוקה למחיצות חייבת להיות עמודה סקלרית מסוג DATE, TIMESTAMP או DATETIME. מצב העמודה יכול להיות REQUIRED או NULLABLE, אבל לא REPEATED (מבוסס-מערך).
    • עמודת החלוקה למחיצות חייבת להיות שדה ברמה העליונה. אי אפשר להשתמש בשדה עלה מ-RECORD (STRUCT) כעמודת החלוקה למחיצות.

    למידע על טבלאות שמחולקות לפי עמודת יחידת זמן, קראו את המאמר יצירת טבלה שמחולקת לפי עמודת יחידת זמן.

  • טבלאות שמחולקות לפי טווח של מספרים שלמים כפופות למגבלות הבאות:

    • עמודת החלוקה למחיצות חייבת להיות עמודת INTEGER. מצב העמודה יכול להיות REQUIRED או NULLABLE, אבל לא REPEATED (מבוסס-מערך).
    • עמודת החלוקה למחיצות חייבת להיות שדה ברמה העליונה. אי אפשר להשתמש בשדה עלה מ-RECORD (STRUCT) כעמודת החלוקה למחיצות.

    מידע על טבלאות שמחולקות למחיצות לפי טווח של מספרים שלמים זמין במאמר יצירת טבלה שמחולקת למחיצות לפי טווח של מספרים שלמים.

מכסות ומגבלות

ב-BigQuery יש מגבלות מוגדרות לטבלאות מחולקות למחיצות. למידע נוסף על כל המכסות והמגבלות, ראו מכסות ומגבלות.

מכסות ומגבלות על משימות בטבלאות מחולקות למחיצות

המכסות והמגבלות חלות גם על סוגי המשימות השונים שאפשר להריץ על טבלאות מחולקות למחיצות, כולל:

שגיאות מכסה של מספר השינויים במחיצות בטבלאות עם מחיצות לפי עמודות

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

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

הודעת שגיאה

Quota exceeded: Your table exceeded quota for
Number of partition modifications to a column partitioned table

רזולוציה

אי אפשר להגדיל את המכסה הזו. כדי לפתור את שגיאת המכסה:

  • לשנות את החלוקה למחיצות בטבלה כך שיהיו יותר נתונים בכל מחיצה, כדי להקטין את המספר הכולל של המחיצות. לדוגמה, אפשר לשנות את החלוקה למחיצות לפי יום לחלוקה למחיצות לפי חודש או לשנות את אופן החלוקה של הטבלה למחיצות.
  • להשתמש באשכולות במקום בחלוקה למחיצות.
  • אם אתם טוענים לעיתים קרובות נתונים מכמה קבצים קטנים שמאוחסנים ב-Cloud Storage, ומשתמשים במשימה לכל קובץ, כדאי לשלב כמה משימות טעינה למשימה אחת. אפשר לטעון מכמה מזהי URI של Cloud Storage באמצעות רשימה מופרדת בפסיקים (לדוגמה, gs://my_path/file_1,gs://my_path/file_2) או באמצעות תווים כלליים לחיפוש (לדוגמה, gs://my_path/*).

    מידע נוסף זמין במאמר טעינת נתונים באצווה.

  • לדוגמה, אם אתם משתמשים במשימות טעינה, בחירה או העתקה כדי להוסיף שורות נתונים בודדות לטבלה, כדאי לשקול לאחד כמה משימות למשימה אחת. ‫BigQuery לא פועל היטב כשמשתמשים בו כמסד נתונים רלציוני. מומלץ להימנע מהפעלה תכופה של פעולות הוספה של שורה אחת.
  • כדי להוסיף נתונים בקצב גבוה, כדאי להשתמש ב-BigQuery Storage Write API (gRPC). זהו פתרון מומלץ להטמעת נתונים עם ביצועים גבוהים. ל-BigQuery Storage Write API‏ (gRPC) יש תכונות מתקדמות, כולל סמנטיקה של מסירה חד-פעמית. במאמר Storage Write API מפורט מידע על המגבלות והמכסות, ובמאמר מחירי הטמעת הנתונים ב-BigQuery מפורט מידע על עלויות השימוש ב-API הזה.
  • כדי לעקוב אחרי מספר המחיצות שהשתנו בטבלה, משתמשים בתצוגה INFORMATION_SCHEMA.
  • במאמר אופטימיזציה של משימות טעינה מוסבר איך לבצע אופטימיזציה של משימות טעינה של טבלאות כדי לא לחרוג מהמכסות.

תמחור טבלאות

כשיוצרים טבלאות מחולקות למחיצות ב-BigQuery ומשתמשים בהן, החיובים מבוססים על כמות הנתונים שמאוחסנים במחיצות ועל השאילתות שמריצים על הנתונים:

פעולות רבות בטבלה המחולקת למחיצות (Partitions) הן בחינם, כולל טעינת נתונים למחיצות, העתקת מחיצות וייצוא נתונים ממחיצות. על אף שהן בחינם, הפעולות האלו כפופות למכסות ולמגבלות של BigQuery. למידע על כל הפעולות שניתן לבצע בחינם, ראו פעולות בחינם בדף התמחור.

במאמר ניהול העלויות ב-BigQuery מפורטות שיטות מומלצות לניהול העלויות ב-BigQuery.

אבטחת טבלאות

בקרת הגישה לטבלאות מחולקות זהה לבקרת הגישה לטבלאות רגילות. למידע נוסף, קראו את מבוא לבקרות גישה לטבלאות.

המאמרים הבאים