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

פונקציה בהגדרת המשתמש (UDF) מאפשרת ליצור פונקציה באמצעות ביטוי SQL או קוד JavaScript. הפונקציה UDF מקבלת עמודות קלט, מבצעת פעולות בקלט ומחזירה את התוצאה של הפעולות האלה בתור ערך.

אפשר להגדיר שפונקציות UDF יהיו מתמידות או זמניות. אפשר להשתמש שוב בפונקציות UDF מתמידות בכמה שאילתות, ופונקציית UDF זמנית קיימת רק בהיקף של שאילתה אחת.

כדי ליצור UDF, משתמשים בהצהרה CREATE FUNCTION. כדי למחוק פונקציה מתמידה בהגדרת המשתמש, משתמשים בהצהרה DROP FUNCTION. התוקף של פונקציות UDF זמניות פג כשהשאילתה מסתיימת. ההצהרה DROP FUNCTION נתמכת בפונקציות UDF זמניות רק בשאילתות עם כמה הצהרות ובהליכים.

מידע על פונקציות בהגדרת משתמש ב-SQL מדור קודם זמין במאמר פונקציות בהגדרת משתמש ב-SQL מדור קודם.

פונקציות UDF ב-SQL

בדוגמה הבאה נוצרת פונקציית UDF זמנית של SQL בשם AddFourAndDivide, והפונקציה נקראת מתוך הצהרת SELECT:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

הדוגמה הזו יוצרת את הפלט הבא:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

בדוגמה הבאה נוצרת אותה פונקציה כפונקציה מוגדרת על ידי המשתמש (UDF) מתמידה:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

מכיוון שזו פונקציה מתמידה בהגדרת המשתמש, צריך לציין מערך נתונים לפונקציה (mydataset בדוגמה הזו). אחרי שמריצים את ההצהרה CREATE FUNCTION, אפשר להפעיל את הפונקציה משאילתה:

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

פרמטרים של פונקציות מוגדרות על ידי המשתמש (UDF) של SQL שמבוססות על תבניות

פרמטר עם סוג ששווה ל-ANY TYPE יכול להתאים ליותר מסוג אחד של ארגומנט כשמפעילים את הפונקציה.

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

בדוגמה הבאה מוצגת פונקציית SQL UDF שמשתמשת בפרמטר מבוסס-תבנית.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

הדוגמה הזו יוצרת את הפלט הבא:

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

בדוגמה הבאה נעשה שימוש בפרמטר מבוסס-תבנית כדי להחזיר את הרכיב האחרון במערך מכל סוג:

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE)
AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

הדוגמה הזו יוצרת את הפלט הבא:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

שאילתות משנה סקלריות

פונקציית UDF של SQL יכולה להחזיר את הערך של שאילתת משנה סקלרית. בשאלת משנה סקלרית צריך לבחור עמודה אחת.

בדוגמה הבאה מוצגת פונקציית SQL UDF שמשתמשת בשאילתת משנה סקלרית כדי לספור את מספר המשתמשים בגיל מסוים בטבלת משתמשים:

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

הדוגמה הזו יוצרת את הפלט הבא:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

פרויקט ברירת מחדל בביטויי SQL

בגוף של פונקציית UDF מסוג SQL, כל הפניות לישויות ב-BigQuery, כמו טבלאות או תצוגות, חייבות לכלול את מזהה הפרויקט, אלא אם הישות נמצאת באותו פרויקט שמכיל את פונקציית ה-UDF.

לדוגמה, נניח את ההצהרה הבאה:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

אם מריצים את ההצהרה הזו מ-project1 ו-mydataset.mytable קיים ב-project1, ההצהרה תצליח. אבל אם מריצים את ההצהרה הזו מפרויקט אחר, ההצהרה תיכשל. כדי לתקן את השגיאה, צריך לכלול את מזהה הפרויקט בהפניה לטבלה:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

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

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

פונקציות UDF ב-JavaScript

פונקציה בהגדרת המשתמש (UDF) ב-JavaScript מאפשרת לקרוא לקוד שנכתב ב-JavaScript מתוך שאילתת SQL. פונקציות UDF ב-JavaScript צורכות בדרך כלל יותר משאבי משבצת בהשוואה לשאילתות SQL סטנדרטי, ולכן הן מפחיתות את ביצועי העבודה. אם אפשר לבטא את הפונקציה ב-SQL, לרוב עדיף להריץ את הקוד כמשימת שאילתת SQL רגילה.

בדוגמה הבאה מוצגת פונקציית UDF ב-JavaScript. קוד ה-JavaScript מצוטט בתוך מחרוזת גולמית.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) AS product
FROM numbers;

הדוגמה הזו יוצרת את הפלט הבא:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

בדוגמה הבאה מסכמים את הערכים של כל השדות שנקראים foo במחרוזת ה-JSON שצוינה.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) && obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
""";

WITH Input AS (
  SELECT
    STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s,
    10 AS foo
  UNION ALL
  SELECT
    NULL,
    4 AS foo
  UNION ALL
  SELECT
    STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s,
    NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

הדוגמה הזו יוצרת את הפלט הבא:

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

סוגי הנתונים הנתמכים ב-UDF של JavaScript

חלק מסוגי ה-SQL ממופים ישירות לסוגי JavaScript, אבל אחרים לא. ב-BigQuery, הסוגים מיוצגים באופן הבא:

סוג נתונים ב-BigQuery	סוג נתונים ב-JavaScript
ARRAY	ARRAY
BOOL	בוליאני
BYTES	מחרוזת בקידוד Base64
FLOAT64	מספר
NUMERIC, BIGNUMERIC	אם אפשר לייצג ערך מסוג NUMERIC או BIGNUMERIC בדיוק כערך IEEE 754 floating-point ואין לו חלק שברי, הערך מקודד כמספר. הערכים האלה הם בטווח [‎-2⁵³, 2⁵³]. אחרת, הערך מקודד כמחרוזת.
מחרוזת	מחרוזת
STRUCT	אובייקט שבו כל שדה STRUCT הוא שדה עם שם
TIMESTAMP	‫DATE עם שדה מיקרו-שנייה שמכיל את `microsecond` החלק של חותמת הזמן
תאריך	תאריך
JSON	אובייקטים, מערכים וערכים של JSON מומרים לאובייקטים, למערכים ולערכים מקבילים של JavaScript. ‫JavaScript לא תומך בערכים מסוג INT64. רק מספרים בפורמט JSON בטווח [‎-2⁵³, 2⁵³] מומרים בדיוק. אחרת, הערך המספרי יעוגל, מה שעלול לגרום לאובדן דיוק.

מכיוון ש-JavaScript לא תומך בסוג מספר שלם עם 64 ביט, אין תמיכה ב-INT64 כסוג קלט לפונקציות מוגדרות על ידי המשתמש (UDF) ב-JavaScript. במקום זאת, משתמשים ב-FLOAT64 כדי לייצג ערכים של מספרים שלמים כמספר, או ב-STRING כדי לייצג ערכים של מספרים שלמים כמחרוזת.

‫BigQuery תומך ב-INT64 כסוג החזרה בפונקציות מוגדרות על ידי המשתמש (UDF) ב-JavaScript. במקרה הזה, גוף הפונקציה ב-JavaScript יכול להחזיר מספר או מחרוזת ב-JavaScript. לאחר מכן, BigQuery ממיר את אחד מהסוגים האלה ל-INT64.

אם ערך ההחזרה של פונקציית ה-UDF ב-JavaScript הוא Promise, מערכת BigQuery מחכה ל-Promise עד ש-Promise מסתיים. אם השאילתה Promise עוברת למצב 'הושלמה', BigQuery מחזיר את התוצאה שלה. אם Promise עובר למצב דחייה, מערכת BigQuery מחזירה שגיאה.

כללי הצעות מחיר

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

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

הדוגמה הזו יוצרת את הפלט הבא:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

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

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js
AS r"""
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
""";

SELECT customGreeting(names) AS everyone
FROM UNNEST(['Hannah', 'Max', 'Jakob']) AS names;

הדוגמה הזו יוצרת את הפלט הבא:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

הכללה של ספריות JavaScript

אפשר להרחיב את הפונקציות המוגדרות על ידי המשתמש ב-JavaScript באמצעות הקטע OPTIONS. בקטע הזה אפשר לציין ספריות קוד חיצוניות ל-UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
  OPTIONS (
    library=['gs://my-bucket/path/to/lib1.js', 'gs://my-bucket/path/to/lib2.js'])
AS r"""
  // Assumes 'doInterestingStuff' is defined in one of the library files.
  return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

בדוגמה הקודמת, הקוד ב-lib1.js וב-lib2.js זמין לכל קוד בקטע [external_code] של הפונקציה המוגדרת על ידי המשתמש.

שיטות מומלצות לשימוש בפונקציות מוגדרות על ידי המשתמש (UDF) ב-JavaScript

סינון מראש של הקלט

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

הימנעות ממצב משתנה מתמשך

לא לשמור או לגשת למצב שניתן לשינוי בין קריאות ל-UDF של JavaScript. לדוגמה, אל תשתמשו בתבנית הבאה:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

שימוש יעיל בזיכרון

בסביבת העיבוד של JavaScript יש זיכרון מוגבל לכל שאילתה. שאילתות של פונקציות UDF ב-JavaScript שצוברות יותר מדי נתונים מקומיים עלולות להיכשל בגלל חוסר זיכרון.

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

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

הוספת תיאורים לפונקציות מוגדרות על ידי המשתמש

כדי להוסיף תיאור ל-UDF, מבצעים את השלבים הבאים:

המסוף

נכנסים לדף BigQuery במסוף Google Cloud .

כניסה ל-BigQuery
בחלונית הימנית, לוחצים על כלי הניתוחים:

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

SQL

כדי לעדכן את התיאור של פונקציה, צריך ליצור מחדש את הפונקציה באמצעות הצהרת ה-DDL‏ CREATE FUNCTION ולהגדיר את השדה description ברשימה OPTIONS:

במסוף Google Cloud , עוברים לדף BigQuery.

כניסה ל-BigQuery

מזינים את ההצהרה הבאה בעורך השאילתות:

CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

לוחצים על הפעלה.

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

יצירת שגרות מיסוך בהתאמה אישית

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

השגרה המותאמת אישית להסתרת נתונים חייבת להיות פונקציה מוגדרת על ידי המשתמש (UDF) של SQL.
בפונקציה OPTIONS, האפשרות data_governance_type צריכה להיות מוגדרת כ-DATA_MASKING.
שגרות מיסוך בהתאמה אישית תומכות בפונקציות הבאות:
- AEAD.DECRYPT_BYTES פונקציית הצפנה של AEAD עם KEYS.KEYSET_CHAIN (שימוש במפתח גולמי לא אפשרי)
- AEAD.DECRYPT_STRING, פונקציית הצפנה של AEAD עם KEYS.KEYSET_CHAIN (שימוש במפתח גולמי לא אפשרי)
- AEAD.ENCRYPT פונקציית הצפנה AEAD עם keyset_chain (שימוש במפתח גולמי לא נתמך)
- CAST פונקציית המרה
- CONCAT פונקציית מחרוזת
- CURRENT_DATETIME פונקציית datetime
- CURRENT_DATE פונקציית תאריך
- ‫CURRENT_TIMESTAMP פונקציית חותמת זמן
- פונקציית הזמן CURRENT_TIME
- DETERMINISTIC_DECRYPT_BYTES פונקציית הצפנה של AEAD עם KEYS.KEYSET_CHAIN (שימוש במפתח גולמי לא אפשרי)
- DETERMINISTIC_DECRYPT_STRING פונקציית הצפנה של AEAD עם KEYS.KEYSET_CHAIN (שימוש במפתח גולמי לא אפשרי)
- DETERMINISTIC_ENCRYPT פונקציית הצפנה של AEAD עם KEYS.KEYSET_CHAIN (שימוש במפתח גולמי לא אפשרי)
- FARM_FINGERPRINT פונקציית גיבוב (hash)
- FROM_BASE32 פונקציית מחרוזת
- FROM_BASE64 פונקציית מחרוזת
- FROM_HEX פונקציית מחרוזת
- פונקציה בסיסית של GENERATE_UUID
- KEYS.KEYSET_CHAIN פונקציית הצפנה של AEAD
- פונקציית המחרוזת LENGTH
- LOWER פונקציית מחרוזת
- LPAD פונקציית מחרוזת
- LTRIM פונקציית מחרוזת
- MD5 פונקציית גיבוב (hash)
- REGEXP_REPLACE פונקציית מחרוזת
- REGEX_EXTRACT פונקציית מחרוזת
- REPLACE פונקציית מחרוזת
- RPAD פונקציית מחרוזת
- RTRIM פונקציית מחרוזת
- פונקציית המרה אחת (SAFE_CAST)
- SHA1 פונקציית גיבוב (hash)
- SHA256 פונקציית גיבוב (hash)
- SHA512 פונקציית גיבוב (hash)
- פונקציית המחרוזת STARTS_WITH
- SUBSTRING פונקציית מחרוזת
- פונקציית המחרוזת SUBSTR
- TO_BASE32 פונקציית מחרוזת
- TO_BASE64 פונקציית מחרוזת
- TO_HEX פונקציית מחרוזת
- TRIM פונקציית מחרוזת
- UPPER פונקציית מחרוזת
שגרות מותאמות אישית של מיסוך יכולות לקבל אפס קלטים או קלט אחד בסוגי נתונים של BigQuery, למעט GEOGRAPHY ו-STRUCT. אין תמיכה ב-GEOGRAPHY וב-STRUCT בשגרות מיסוך מותאמות אישית.
אין תמיכה בפרמטרים של פונקציות מוגדרות על ידי המשתמש (UDF) של SQL שמבוססות על תבניות.
כשמספקים קלט, סוגי הנתונים של הקלט והפלט צריכים להיות זהים.
צריך לציין סוג פלט.
אי אפשר להפנות לפונקציות UDF אחרות, לשאילתות משנה, לטבלאות או לתצוגות בגוף ההגדרה.
אחרי שיוצרים שגרת מיסוך, אי אפשר לשנות אותה לפונקציה רגילה. כלומר, אם האפשרות data_governance_type מוגדרת לערך DATA_MASKING, אי אפשר לשנות את data_governance_type באמצעות הצהרות DDL או קריאות ל-API.
שגרות מיסוך בהתאמה אישית תומכות בהצהרות CASE ו-CASE expr. אפשר להשתמש באופרטורים הבאים עם הצהרות CASE ו-CASE expr:
- ‫Comparison Operators - <, <=, >, >=, =, !=, IN
- Logical Operators - AND, OR, NOT
- IS Operator

לדוגמה, שגרת מיסוך שמחליפה את מספר הביטוח הלאומי של משתמש ב-XXX-XX-XXXX עשויה להיראות כך:

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

בדוגמה הבאה, מתבצע גיבוב עם מלח שסופק על ידי המשתמש, באמצעות הפונקציה SHA256:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

בדוגמה הבאה מוסתרת עמודה DATETIME באמצעות ערך קבוע:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

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

אחרי שיוצרים את שגרת המיסוך בהתאמה אישית, היא זמינה ככלל מיסוך ביצירת כללי מדיניות לנתונים.

פונקציות שנוספו על ידי הקהילה

פונקציות UDF שנוצרו על ידי הקהילה זמינות במערך הנתונים הציבורי bigquery-public-data.persistent_udfs ובמאגר הקוד הפתוח bigquery-utils ב-GitHub. כדי לראות את כל הפונקציות המוגדרות על ידי המשתמשים בקהילה במסוף Google Cloud , מסמנים בכוכב את הפרויקט bigquery-public-data בחלונית Explorer, ואז מרחיבים את מערך הנתונים persistent_udfs שמוטמע בפרויקט הזה.

אישור גישה לפונקציות שנוספו על ידי הקהילה בתוך היקף VPC Service Controls

בפרויקטים שבהם מופעל VPC Service Controls ו-BigQuery הוא שירות מוגן, צריך להגדיר כלל לתעבורת נתונים יוצאת (egress) לפרויקט bigquery-public-data (מזהה הפרויקט: 1057666841514).

הכלל הזה צריך לאפשר את הפעולות הבאות:

bigquery.routines.get (לשימוש בתרחישים)
‫bigquery.tables.getData (לשאילתות בטבלאות BigQuery)

בדוגמה הבאה מוצגת הגדרת YAML:

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

אם אתם רוצים לתרום לפונקציות UDF במאגר הזה, תוכלו לקרוא את ההוראות במאמר תרומה של פונקציות UDF.

גישה מאוחדת לשגרה בכמה אזורים

כדי להשתמש בפונקציות UDF בשאילתות בכמה אזורים, הפונקציה צריכה להיות זמינה בכל אזור שבו מופעלת שאילתה שמכילה את הפונקציה. לכן, כדאי ליצור ולתחזק פונקציות UDF בכל אזור שבו אתם עשויים להשתמש בפונקציית UDF בשאילתה. גם אם הטבלאות זהות, צריך לשמור 2 גרסאות של הפונקציה. לדוגמה, אם אתם מאחסנים את נתוני המכירות שלכם במספר אזורים EU ו-US, אתם צריכים לתחזק גרסה של הפונקציה בכל אזור. לדוגמה:

שאילתה באזור EU במספר אזורים:

  SELECT
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

שאילתה באזור US במספר אזורים:

  SELECT
    id,
    us_dataset.my_function(value)
  FROM
    sales;

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

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

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

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

  SELECT
    id,
    my_utils.my_function(value)
  FROM
    sales;

מגבלות

ההגבלות הבאות חלות על פונקציות זמניות ומתמידות בהגדרת המשתמש:

אין תמיכה באובייקטים של DOM‏ Window, Document ו-Node, ובפונקציות שדורשות אותם.
פונקציות JavaScript פועלות בסביבת ארגז חול, ויכול להיות שהפונקציות שמסתמכות על קוד מערכת בסיסי ייכשלו בגלל קריאות מערכת מוגבלות.
יכול להיות שפונקציית UDF ב-JavaScript תגיע לזמן קצוב לתפוגה ותמנע את השלמת השאילתה. הזמן הקצוב לתפוגה יכול להיות קצר כמו 5 דקות, אבל הוא משתנה בהתאם לכמה גורמים, כולל כמות הזמן שפונקציית ה-CPU של המשתמש צורכת וגודל הקלט והפלט של פונקציית ה-JavaScript.
פעולות Bitwise ב-JavaScript מטפלות רק ב-32 הביטים הכי משמעותיים.
פונקציות UDF כפופות למגבלות מסוימות על קצב הבקשות ולמגבלות על מכסות. מידע נוסף זמין במאמר בנושא מגבלות על פונקציות מוגדרות על ידי המשתמש.

המגבלות הבאות חלות על פונקציות מתמידות בהגדרת המשתמש:

כל מערך נתונים יכול להכיל רק פונקציה מתמידה אחת עם אותו שם. עם זאת, אפשר ליצור פונקציה מוגדרת על ידי המשתמש (UDF) שהשם שלה זהה לשם של טבלה באותו מערך נתונים.
כשמפנים לפונקציה מתמידה מפונקציה מתמידה אחרת או מתצוגה לוגית, צריך לציין את מערך הנתונים בשם. לדוגמה:
CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

המגבלות הבאות חלות על פונקציות זמניות בהגדרת המשתמש.

כשיוצרים פונקציית UDF זמנית, function_name לא יכול להכיל נקודות.
צפיות ופונקציות UDF מתמידות לא יכולות להתייחס לפונקציות UDF זמניות.