פונקציות טבלה

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

יצירת פונקציות של טבלאות

כדי ליצור פונקציית טבלה, משתמשים בהצהרה CREATE TABLE FUNCTION. פונקציית טבלה מכילה שאילתה שמפיקה טבלה. הפונקציה מחזירה את תוצאת השאילתה. פונקציית הטבלה הבאה מקבלת INT64פרמטר ומשתמשת בערך הזה בתוך תנאי WHERE בשאילתה על מערך נתונים ציבורי בשם bigquery-public-data.usa_names.usa_1910_current:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name
);

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

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  GROUP BY year, name
);

פרמטרים של טבלה

הערה: כדי לבקש תמיכה או לשלוח משוב על התכונה הזו, אפשר לשלוח אימייל לכתובת bq-dcr-eng@google.com.

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

פונקציית הטבלה הבאה מחזירה טבלה שמכילה את סך המכירות של item_name מהטבלה orders:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

שמות הפרמטרים

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

שימוש בפונקציות של טבלאות

אפשר לקרוא לפונקציית טבלה בכל הקשר שבו טבלה היא תקפה. בדוגמה הבאה מופעלת הפונקציה mydataset.names_by_year בסעיף FROM של הצהרת SELECT:

SELECT * FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5

התוצאות אמורות להיראות כך:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

אפשר לצרף את הפלט מפונקציית טבלה לטבלה אחרת:

SELECT *
  FROM `bigquery-public-data.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

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

SELECT ARRAY(
  SELECT name FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5)

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

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

שימוש במשתני מערכת עם TVF

יש תמיכה במשתני המערכת @@session_id ו-@@location בפונקציות TVF. אפשר לכלול את משתני המערכת האלה בכל מקום בהצהרה ליצירת פונקציה כדי להחזיר את מזהה הסשן או את המיקום של השאילתה הנוכחית. אין תמיכה בכל שאר משתני המערכת.

הצגת רשימה של פונקציות טבלה

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

מחיקת פונקציות של טבלה

כדי למחוק פונקציית טבלה, משתמשים בהצהרה DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

מתן הרשאה לתרחישים

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

מגבלות

  • גוף השאילתה חייב להיות הצהרה של SELECT ואי אפשר לשנות בו שום דבר. לדוגמה, אי אפשר להשתמש בפונקציות של טבלאות בהצהרות של שפת הגדרת נתונים (DDL) ושפת טיפול בנתונים (DML). אם אתם צריכים תופעות לוואי, כדאי לכתוב פרוצדורה במקום זאת.

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

מכסות

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