הרצת שאילתות על טבלאות מחולקות למחיצות (Partitions)

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

מידע כללי על הרצת שאילתות זמין במאמר הרצת שאילתה.

סקירה כללית

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

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

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

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

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

בדוגמה הבאה, נניח שהטבלה dataset.table מחולקת למחיצות לפי העמודה transaction_date. השאילתה לדוגמה מסירה תאריכים שלפני 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

הרצת שאילתות על טבלה מחולקת למחיצות (Partitions) לפי זמני כתיבת הנתונים

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

לדוגמה, אם מוסיפים נתונים ב-15 באפריל 2021 בשעה 08:15:00 (שעון UTC), העמודה _PARTITIONTIME בשורות האלה מכילה את הערכים הבאים:

• טבלה מחולקת למחיצות לפי שעה: TIMESTAMP("2021-04-15 08:00:00")
• טבלה מחולקת למחיצות (Partitions) לפי יום: TIMESTAMP("2021-04-15")
• טבלה מחולקת למחיצות לפי חודש: TIMESTAMP("2021-04-01")
• טבלה מחולקת למחיצות (Partitions) לפי שנה: TIMESTAMP("2021-01-01")

אם רמת הפירוט של המחיצה היא יומית, הטבלה מכילה גם עמודה מדומה בשם _PARTITIONDATE. הערך שווה ל-_PARTITIONTIME, מקוצר לערך DATE.

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

כדי לצמצם את המחיצות, מסננים לפי אחת מהעמודות האלה. לדוגמה, השאילתה הבאה סורקת רק את המחיצות שבין התאריכים 1 בינואר 2016 ו-2 בינואר 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

כדי לבחור את העמודה המדומה _PARTITIONTIME, צריך להשתמש בכינוי. לדוגמה, השאילתה הבאה בוחרת את _PARTITIONTIME על ידי הקצאת הכינוי pt לעמודה המדומה:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

בטבלאות שמחולקות לפי יום, אפשר לבחור את העמודה המדומה _PARTITIONDATE באותו אופן:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

העמודות המדומות _PARTITIONTIME ו-_PARTITIONDATE לא מוחזרות על ידי הצהרת SELECT *. צריך לבחור אותם באופן מפורש:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

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

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

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

ביצועים משופרים עם עמודות פסאודו

כדי לשפר את ביצועי השאילתה, השתמשו בעמודה המדומה _PARTITIONTIME לבדה בצד שמאל של ההשוואה.

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

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

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

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

כדי להגביל את המחיצות שנסרקות, אל תכללו עמודות אחרות במסנן _PARTITIONTIME. לדוגמה, השאילתה הבאה לא מגבילה את המחיצות שנסרקות, כי field1 היא עמודה בטבלה.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

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

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

למידע על יצירת תצוגות, קראו את המאמר יצירת תצוגות.

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

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

בדוגמה הבאה, נניח ש-dataset.table היא טבלה שמחולקת למחיצות לפי טווח של מספרים שלמים, עם מפרט חלוקה למחיצות של customer_id:0:100:10. השאילתה לדוגמה סורקת את שלוש המחיצות שמתחילות ב-30, 40 ו-50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

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

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

שליחת שאילתות על נתונים באחסון שמותאם לכתיבה

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

לנתונים באחסון שמותאם לכתיבה יש ערכי NULL בעמודות _PARTITIONTIME ו-_PARTITIONDATE.

כדי לשלוח שאילתה על נתונים במחיצה __UNPARTITIONED__, משתמשים בעמודה המדומה _PARTITIONTIME עם הערך NULL. לדוגמה:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

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

שיטות מומלצות לגיזום מחיצות

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

שימוש בביטוי סינון קבוע

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

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

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

לעומת זאת, השאילתה הבאה לא מצמצמת מחיצות, כי הפרדיקט WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2) הוא לא ביטוי קבוע. השאילתה הזו משווה בין עמודת החלוקה למחיצות לבין ערך דינמי, מה שמונע קיצוץ מחיצות.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

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

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

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

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

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

• ‫DATE_ADD, DATE_DIFF, DATE_SUB, DATE_TRUNC, EXTRACT עם חלק אחד של YEAR,
• DATETIME_DIFF,
• ‫TIMESTAMP_ADD, ‫TIMESTAMP_DIFF, ‫TIMESTAMP_SUB, ‫TIMESTAMP_TRUNC, ‫EXTRACT עם DATE או YEAR חלקים,
• FORMAT_TIMESTAMP עם מפרטי הפורמט הבאים: %F, ‏ %Y-%m-%d ו-%Y%m%d.

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

דוגמאות

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

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

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

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

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

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

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

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

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

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

כדי להפעיל חיתוך מחיצות, צריך לשכתב את הביטוי על ידי בידוד עמודת החלוקה למחיצות ts מהפונקציות או מהפעולות האריתמטיות שלא נתמכות. בטווחי תאריכים, משתמשים ב->= וב-< כדי לתעד את הטווח המדויק. בפעולות חשבון, מעבירים את הפעולה לצד השני של ההשוואה.

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

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

השאילתה הבאה מאפשרת חיתוך מחיצות על ידי בידוד עמודת החלוקה למחיצות מהפעולה האריתמטית:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

סינון לפי כמה עמודות

פרדיקט בעמודת החלוקה למחיצות בשאילתה לא מגביל את מה שאפשר לסנן. אפשר לכלול פרדיקטים בעמודות אחרות באותו משפט WHERE, ועדיין יתבצע גיזום מחיצות, כל עוד התנאי שמעריך את עמודת החלוקה למחיצות פועל לפי השיטות המומלצות. שימו לב ש-AND חשוב בדוגמה הבאה. אם AND משתנה ל-OR, חיתוך המחיצות לא יעבוד, כי גם אם מחיצה לא תואמת לפרדיקט בעמודת החלוקה למחיצות, עדיין לא ניתן לחתוך אותה. הנתונים במחיצות האלה עם meter_id = 1234 עדיין עומדים בתנאים של השאילתה.

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

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

דרישה למסנן מחיצות בשאילתות

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

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

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

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

WHERE partition_id = "20221231"

WHERE partition_id = "20221231" AND f = "20221130"

עם זאת, הדוגמה הבאה לא מספיקה ותגרום לשגיאה:

WHERE partition_id = "20221231" OR f = "20221130"

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

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

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

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