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

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

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

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

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

מגבלות

ההגבלות הבאות חלות על שאילתות של טבלאות תווים כלליים לחיפוש.

הפונקציונליות של טבלת התווים הכלליים לחיפוש לא תומכת בתצוגות. אם טבלת תווים כלליים לחיפוש תואמת לתצוגה כלשהי במערך הנתונים, השאילתה תחזיר שגיאה גם אם היא מכילה פסקה WHERE בעמודה הווירטואלית _TABLE_SUFFIX כדי לסנן את התצוגה.
תוצאות שנשמרו במטמון אינן נתמכות עבור שאילתות מול טבלאות מרובות באמצעות תו כללי לחיפוש, גם אם האפשרות שימוש בתוצאות במטמון מסומנת. אם תפעילו כמה פעמים את אותה שאילתה עם תו כללי לחיפוש, תחויבו על כל שאילתה.
טבלאות עם תווים כלליים תומכות רק באחסון מובנה ב-BigQuery. אי אפשר להשתמש בתווים כלליים לחיפוש כדי לשלוח שאילתה לטבלה חיצונית או לתצוגה מפורטת.
אי אפשר להשתמש בשאילתות עם תווים כלליים בטבלאות עם חלוקה למחיצות לא תואמת או בטבלאות שהן גם מחולקות למחיצות וגם לא מחולקות למחיצות. גם הטבלאות שנכללות בשאילתה צריכות להיות בעלות מפרטי אשכול זהים.
אפשר להשתמש בטבלאות עם תו כללי לחיפוש עם טבלאות מחולקות למחיצות, ונתמכות גם טכניקות של סינון מחיצות וסינון אשכולות. עם זאת, טבלאות שמקובצות לאשכולות אבל לא מחולקות למחיצות לא נהנות מהיתרון המלא של גיזום אשכולות משימוש בתו כללי.
בשאילתות שמכילות הצהרות של שפת טיפול בנתונים (DML) אי אפשר להשתמש בטבלת תווים כלליים לחיפוש כיעד של השאילתה. לדוגמה, אפשר להשתמש בטבלת תו כללי לחיפוש בסעיף FROM של שאילתת UPDATE, אבל אי אפשר להשתמש בטבלת תו כללי לחיפוש כיעד של פעולת UPDATE.
מסננים בעמודות פסאודו _TABLE_SUFFIX או _PARTITIONTIME שכוללים פונקציות בהגדרת המשתמש ב-JavaScript לא מגבילים את מספר הטבלאות שנסרקות בטבלת תווים כלליים לחיפוש.
אין תמיכה בשאילתות עם תווים כלליים לחיפוש בטבלאות שמוגנות באמצעות מפתחות הצפנה בניהול הלקוח (CMEK).
לכל הטבלאות שאליהן מתייחסת שאילתת wildcard צריכה להיות בדיוק אותה קבוצה של מפתחות וערכים של תגים.
אם בטבלה סרוקה אחת יש אי התאמה בסכימה (כלומר, עמודה עם אותו שם היא מסוג אחר), השאילתה תיכשל עם השגיאה Cannot read field of type X as Y Field: column_name. כל הטבלאות מותאמות, גם אם משתמשים באופרטור השוויון =. לדוגמה, בשאילתה הבאה, מתבצע סריקה גם של הטבלה my_dataset.my_table_03_backup. לכן, יכול להיות שהשאילתה תיכשל בגלל חוסר התאמה בסכימה. עם זאת, אם אין אי התאמה בסכימה, התוצאות מגיעות רק מהטבלה my_dataset.my_table_03, כמו שצפוי.
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```
‫BI Engine לא תומך בטבלאות עם תווים כלליים לחיפוש.
אין תמיכה בטבלאות עם תווים כלליים לחיפוש בטבלאות עם שמות עמודות גמישים או עם עמודות ששמן שונה. כדי לעקוף את ההגבלה הזו, אפשר להחריג את העמודות המושפעות מהשאילתה או לסנן את הטבלה באמצעות פסאודו-העמודה _TABLE_SUFFIX.

לפני שמתחילים

חשוב לוודא שאתם משתמשים ב-GoogleSQL. מידע נוסף זמין במאמר בנושא מעבר בין ניבי SQL.
אם אתם משתמשים ב-SQL מדור קודם, תוכלו לעיין במאמר בנושא פונקציות של תו כללי לחיפוש בטבלאות.
רבות מהדוגמאות בדף הזה מבוססות על מערך נתונים ציבורי של National Oceanic and Atmospheric Administration ‏ (NOAA, הרשות הלאומית של ארה"ב לאוקיינוסים ולאטמוספרה). מידע נוסף על הנתונים זמין במאמר בנושא נתוני מזג האוויר של NOAA Global Surface Summary of the Day.

מתי כדאי להשתמש בטבלאות עם תווים כלליים

טבלאות עם תווים כלליים שימושיות כשמערך נתונים מכיל כמה טבלאות עם שמות דומים וסכימות תואמות. בדרך כלל, מערכי נתונים כאלה מכילים טבלאות שכל אחת מהן מייצגת נתונים מיום, חודש או שנה מסוימים. לדוגמה, מערך נתונים ציבורי שמארח BigQuery, ‏ NOAA Global Surface Summary of the Day Weather Data, מכיל טבלה לכל שנה מ-1929 ועד היום.

שאילתה שסורקת את כל מזהי הטבלאות מ-1929 עד 1940 תהיה ארוכה מאוד אם תצטרכו לציין את כל 12 הטבלאות בקטע FROM (רוב הטבלאות לא מופיעות בדוגמה הזו):

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

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

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC

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

תחביר של טבלת תווים כלליים לחיפוש

תחביר של טבלת תווים כלליים לחיפוש:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>: מזהה הפרויקט ב-Cloud Platform. אופציונלי אם משתמשים במזהה פרויקט ברירת המחדל.
<dataset-id>: מזהה קבוצת נתונים ב-BigQuery.
<table-prefix>: מחרוזת שמשותפת לכל הטבלאות שתואמות לתו כל ה-Wildcard. הקידומת של הטבלה היא אופציונלית. אם לא מציינים את הקידומת של הטבלה, המערכת מתאימה את כל הטבלאות במערך הנתונים.
‫* (תו כללי לחיפוש): התו הכללי '*' מייצג תו אחד או יותר בשם של טבלה. התו הכללי לחיפוש יכול להופיע רק כתו האחרון בשם של טבלת תווים כלליים לחיפוש.

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

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

מידע נוסף על פסאודו-העמודה _TABLE_SUFFIX זמין במאמר סינון טבלאות נבחרות באמצעות ‎_TABLE_SUFFIX.

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

שם טבלת התווים הכלליים לחיפוש מכיל את התו המיוחד (*), ולכן צריך להקיף את שם טבלת התווים הכלליים לחיפוש בתווים (`) של גרש הפוך. לדוגמה, השאילתה הבאה תקינה כי נעשה בה שימוש בגרשיים הפוכים:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

השאילתה הבאה לא תקינה כי היא לא מצוטטת כמו שצריך באמצעות גרשיים הפוכים:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

מירכאות לא עובדות:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

שליחת שאילתות לטבלאות באמצעות טבלאות עם תווים כלליים לחיפוש

טבלאות עם תווים כלליים לחיפוש מאפשרות להריץ שאילתות על כמה טבלאות בצורה תמציתית. לדוגמה, מערך נתונים ציבורי שמארח BigQuery,‏ NOAA Global Surface Summary of the Day Weather Data, מכיל טבלה לכל שנה מ-1929 ועד היום, ולכולן יש את הקידומת המשותפת gsod ואחריה השנה בת 4 ספרות. הטבלאות נקראות gsod1929, gsod1930, gsod1931 וכו'.

כדי לשלוח שאילתה לקבוצת טבלאות עם קידומת משותפת, משתמשים בסמל התו הכללי לחיפוש בטבלאות (*) אחרי קידומת הטבלה בהצהרת FROM. לדוגמה, השאילתה הבאה מוצאת את הטמפרטורה המקסימלית שדווחה במהלך שנות ה-40:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

סינון הטבלאות שנבחרו באמצעות ‎_TABLE_SUFFIX

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

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

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

עמודת ה-pseudocolumn התואמת _TABLE_SUFFIX מכילה ערכים בטווח 0 עד 9, שמייצגים את הטבלאות gsod1940 עד gsod1949. אפשר להשתמש בערכים האלה _TABLE_SUFFIX בסעיף WHERE כדי לסנן טבלאות ספציפיות.

לדוגמה, כדי לסנן לפי הטמפרטורה המקסימלית בשנים 1940 ו-1944, משתמשים בערכים 0 ו-4 בשביל _TABLE_SUFFIX:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

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

עם זאת, מסננים ב-_TABLE_SUFFIX שכוללים תנאים בלי ביטויים קבועים לא מגבילים את מספר הטבלאות שנסרקות בטבלת תווים כלליים לחיפוש. לדוגמה, השאילתה הבאה לא מגבילה את הטבלאות שנסרקות עבור טבלת ה-wildcard‏ bigquery-public-data.noaa_gsod.gsod19* כי המסנן משתמש בערך הדינמי של העמודה table_id:

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

דוגמה נוספת: השאילתה הבאה מגבילה את הסריקה על סמך תנאי המסנן הראשון, _TABLE_SUFFIX BETWEEN '40' and '60', כי זהו ביטוי קבוע. עם זאת, השאילתה הבאה לא מגבילה את הסריקה על סמך תנאי הסינון השני, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), כי זהו ביטוי דינמי:

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

כפתרון עקיף, אפשר להריץ שתי שאילתות נפרדות. לדוגמה:

השאילתה הראשונה:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

השאילתה השנייה:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

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

סריקת טווח של טבלאות באמצעות ‎_TABLE_SUFFIX

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

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

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

כדי לסרוק טווח של טבלאות מחולקות למחיצות (Partitions) לפי זמני כתיבת הנתונים, משתמשים בעמודה הווירטואלית _PARTITIONTIME עם העמודה הווירטואלית _TABLE_SUFFIX. לדוגמה, השאילתה הבאה סורקת את המחיצה של 1 בינואר 2017 בטבלה my_dataset.mytable_id1.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

שליחת שאילתות לכל הטבלאות במערך נתונים

כדי לסרוק את כל הטבלאות במערך נתונים, אפשר להשתמש בקידומת ריקה ובתו כללי של טבלה, כלומר פסאודו-העמודה _TABLE_SUFFIX מכילה שמות מלאים של טבלאות. לדוגמה, סעיף FROM הבא סורק את כל הטבלאות במערך הנתונים GSOD:

FROM
  `bigquery-public-data.noaa_gsod.*`

אם התחילית ריקה, עמודת ה-pseudocolumn‏ _TABLE_SUFFIX מכילה את השמות המלאים של הטבלאות. לדוגמה, השאילתה הבאה שקולה לדוגמה הקודמת שמוצאת את הטמפרטורה המקסימלית בין השנים 1929 ל-1935, אבל היא משתמשת בשמות מלאים של טבלאות בסעיף WHERE:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

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

פרטי הביצוע של השאילתה

סכימה שמשמשת להערכת שאילתות

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

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

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

שיטות מומלצות

בדרך כלל, קידומות ארוכות מניבות ביצועים טובים יותר מקידומות קצרות. לדוגמה, השאילתה הבאה משתמשת בקידומת ארוכה (gsod200):

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.gsod200*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '0' AND '1'
ORDER BY
max DESC

השאילתה הבאה בדרך כלל מניבה ביצועים גרועים יותר כי היא משתמשת בקידומת ריקה:

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
ORDER BY
max DESC

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

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

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

מידע נוסף על GoogleSQL זמין בחומר העזר בנושא שאילתות GoogleSQL.