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

פונקציית טבלה, שנקראת גם פונקציה עם ערך טבלה (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
);

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

אפשר להגדיר פרמטרים של 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
);

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

-- Simple pass-through (accepts any table, as used in your devstack)
CREATE OR REPLACE TABLE FUNCTION dataset_test.MyTVF(input_table ANY TABLE)
AS
SELECT * FROM input_table;

-- Generic filter (accepts any table, but implicitly requires a 'status' column)
CREATE TEMP TABLE FUNCTION FilterByStatus(input_table ANY TABLE, status_val STRING)
AS
SELECT * FROM input_table
WHERE status = status_val;

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

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

אישור תרחישים

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

מגבלות

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

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

מכסות

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