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

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

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

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

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

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

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

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

DROP TABLE FUNCTION mydataset.names_by_year

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

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

מגבלות

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

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

מכסות

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