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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

הטבלה מחולקת למחיצות (Partitions) בעמודה 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.

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

חלוקה למחיצות (partitioning) לפי זמני כתיבת הנתונים

כשיוצרים טבלה עם חלוקה למחיצות לפי זמן ההטמעה, 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 משתמשת בערך הזה כדי לקבוע את המחיצה הנכונה לנתונים.

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

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

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

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

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

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

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

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

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

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

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

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

חלוקה למחיצות לעומת חלוקה לרסיסים

שמירת נתונים בכמה טבלאות באמצעות קידומת לשם כמו [PREFIX]_YYYYMMDD.

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

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

Partition decorators

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

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

כדי לראות את הערך של המגבלה Number of partition modifications per column-partitioned table per day, אפשר לעיין במאמר בנושא Partitioned tables.

הודעת השגיאה

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

רזולוציה

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

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

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

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

תמחור לפי טבלה

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

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

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

אבטחת טבלאות

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

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