Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

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

פונקציה בהגדרת המשתמש (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 |
+-----+-----+

בדוגמה הבאה נוצרת אותה פונקציה כפונקציה מתמידה:

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

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

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) של SQL

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

פונקציות 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

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

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.

שיטות מומלצות לשימוש בפונקציות מוגדרות על ידי המשתמש (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 (שימוש במפתח גולמי לא אפשרי)
- פונקציית הגיבוב (hash) ‏FARM_FINGERPRINT
- FROM_BASE32 פונקציית מחרוזת
- FROM_BASE64 פונקציית מחרוזת
- FROM_HEX פונקציית מחרוזת
- פונקציה בסיסית של GENERATE_UUID
- KEYS.KEYSET_CHAIN פונקציית הצפנה של AEAD
- פונקציית המחרוזת LENGTH
- LOWER פונקציית מחרוזת
- LPAD פונקציית מחרוזת
- LTRIM פונקציית מחרוזת
- פונקציית הגיבוב (hash) ‏MD5
- REGEXP_REPLACE פונקציית מחרוזת
- REGEXP_EXTRACT פונקציית מחרוזת
- REPLACE פונקציית מחרוזת
- RPAD פונקציית מחרוזת
- RTRIM פונקציית מחרוזת
- פונקציית המרה אחת (SAFE_CAST)
- פונקציית הגיבוב (hash) ‏SHA1
- פונקציית הגיבוב (hash) ‏SHA256
- פונקציית הגיבוב (hash) ‏SHA512
- פונקציית המחרוזת 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. כדי לראות את כל פונקציות ה-UDF של הקהילה במסוף 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 בשאילתה. גם אם הטבלאות זהות, צריך לשמור שתי גרסאות של הפונקציה. לדוגמה, אם אתם מאחסנים את נתוני המכירות שלכם במספר אזורים EU ו-US, אתם צריכים לשמור גרסה של הפונקציה בכל אזור. לדוגמה:

שאילתה באזור EU multi-region:

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

שאילתה באזור US multi-region:

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

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

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

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