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

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

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

סקירה כללית

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

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

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

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

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

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

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

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

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

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

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

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

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

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

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

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

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

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

מידע על יצירת תצוגות זמין במאמר יצירת תצוגות.

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

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

בדוגמה הבאה, נניח ש-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()

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

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

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

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

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

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