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

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

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

מגבלות

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

• טבלאות של תווים כלליים לחיפוש זמינות רק ב-GoogleSQL. לפונקציונליות מקבילה ב-SQL מדור קודם, ראו פונקציות של תו כללי לחיפוש בטבלאות.
• הפונקציונליות של טבלת תווים כלליים לחיפוש לא תומכת בתצוגות. אם טבלת התווים הכלליים לחיפוש תואמת לתצוגה כלשהי במערך הנתונים, השאילתה תחזיר שגיאה גם אם היא מכילה סעיף WHERE בעמודה המדומה _TABLE_SUFFIX כדי לסנן את התצוגה.
• אי אפשר להשתמש בתו כללי לחיפוש כדי להריץ שאילתות על כמה טבלאות לגבי תוצאות במטמון, גם אם סימנתם את האפשרות Use Cached Results. אם תפעילו כמה פעמים את אותה שאילתה עם תו כללי לחיפוש, תחויבו על כל שאילתה.
• טבלאות תווים כלליים לחיפוש תומכות רק באחסון מובנה ב-BigQuery. אי אפשר להשתמש בתווים כלליים לחיפוש כדי לשלוח שאילתות לטבלה חיצונית או לתצוגה מפורטת.
• אי אפשר להשתמש בשאילתות עם תווים כלליים לחיפוש בטבלאות עם חלוקה למחיצות שלא תואמת, או בשילוב של טבלאות עם חלוקה למחיצות וטבלאות ללא חלוקה למחיצות. גם הטבלאות ששולחים אליהן שאילתות צריכות להיות עם מפרטי אשכולות זהים.
• אפשר להשתמש בטבלאות של תווים כלליים לחיפוש עם טבלאות מחולקות למחיצות, ויש תמיכה גם בגיזום מחיצות וגם בגיזום אשכולות. עם זאת, טבלאות מקובצות לאשכולות שלא חולקו למחיצות לא נהנות מהיתרון המלא של גיזום אשכולות באמצעות תו כללי לחיפוש.
• בשאילתות שמכילות פקודות של שפת טיפול בנתונים (DML), אי אפשר להשתמש בטבלת תווים כלליים לחיפוש כיעד של השאילתה. לדוגמה, אפשר להשתמש בטבלת תווים כלליים לחיפוש בסעיף FROM של שאילתת UPDATE, אבל אי אפשר להשתמש בטבלת תווים כלליים לחיפוש כיעד של פעולת UPDATE.
• מסננים בעמודות המדומה _TABLE_SUFFIX או _PARTITIONTIME שכוללים פונקציות בהגדרת המשתמש (UDF) ב-JavaScript לא מגבילים את מספר הטבלאות שנסרקות בטבלת תווים כלליים לחיפוש.
• אי אפשר להשתמש בשאילתות עם תווים כלליים לחיפוש בטבלאות שמוגנות באמצעות מפתחות הצפנה בניהול הלקוח (CMEK).
• כל הטבלאות שמוזכרות בשאילתת תו כללי לחיפוש חייבות לכלול בדיוק את אותה קבוצה של מפתחות וערכים של תגים.

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

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

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

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

* (תו כללי לחיפוש)

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

שאילתות עם טבלאות תווים כלליים לחיפוש תומכות בעמודה המדומה _TABLE_SUFFIX בסעיף WHERE. העמודה הזו מכילה את הערכים שתואמים לתו ה-wildcard, כך ששאילתות יכולות לסנן את הטבלאות שאליהן מתבצעת הגישה. לדוגמה, בסעיפי 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, ואחריה השנה בת ארבע הספרות. הטבלאות נקראות 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*`

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

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

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

אם הקידומת ריקה, העמודה המדומה _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.