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

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

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

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

שיטות מומלצות לסינון מחיצות

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

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

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

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

עם זאת, השאילתה הבאה לא מצמצמת את המחיצות, כי המסנן, WHERE t1.ts = (SELECT timestamp from table where key = 2), הוא לא ביטוי קבוע. הוא תלוי בערכים הדינמיים של השדות timestamp ו-key:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

בידוד עמודת החלוקה במסנן

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

לדוגמה, המסנן הבא לא יסיר מחיצות כי הוא דורש חישוב שמבוסס על שדה החלוקה למחיצות ts ועל שדה שני ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

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

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

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

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