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

במסמך הזה מפורטות כמה נקודות חשובות לגבי שאילתות של טבלאות מחולקות למחיצות ב-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")
• טבלה מחולקת למחיצות (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

בטבלאות עם חלוקה למחיצות לפי יום, אפשר לבחור את עמודת ה-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()

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

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

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

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

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