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

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

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

יצירת פונקציית UDAF של SQL

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

יצירת פונקציית UDAF מתמשכת של SQL

אפשר ליצור פונקציית UDAF ב-SQL שהיא מתמידה, כלומר אפשר להשתמש בה שוב בכמה שאילתות. בטוח להפעיל פונקציות UDAF קבועות כשמשתפים אותן בין בעלים. פונקציות UDAF לא יכולות לשנות נתונים, לתקשר עם מערכות חיצוניות או לשלוח יומנים ל-Google Cloud Observability או לאפליקציות דומות.

כדי ליצור UDAF קבוע, משתמשים בהצהרה CREATE AGGREGATE FUNCTION בלי מילות המפתח TEMP או TEMPORARY. חובה לכלול את קבוצת הנתונים בנתיב הפונקציה.

לדוגמה, השאילתה הבאה יוצרת UDAF מתמשכת בשם ScaledAverage:

CREATE AGGREGATE FUNCTION myproject.mydataset.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

יצירת פונקציית UDAF זמנית ב-SQL

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

כדי ליצור פונקציית UDAF זמנית, משתמשים בהצהרה CREATE AGGREGATE FUNCTION עם מילת המפתח TEMP או TEMPORARY.

לדוגמה, השאילתה הבאה יוצרת UDAF זמני בשם ScaledAverage:

CREATE TEMP AGGREGATE FUNCTION ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

שימוש בפרמטרים מצטברים ולא מצטברים

אפשר ליצור פונקציית UDAF של SQL עם פרמטרים מצטברים ופרמטרים לא מצטברים.

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

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

לדוגמה, הפונקציה הבאה מכילה פרמטר מצטבר שנקרא dividend ופרמטר לא מצטבר שנקרא divisor:

-- Create the function.
CREATE TEMP AGGREGATE FUNCTION ScaledSum(
  dividend FLOAT64,
  divisor FLOAT64 NOT AGGREGATE)
RETURNS FLOAT64
AS (
  SUM(dividend) / divisor
);

שימוש בפרויקט שמוגדר כברירת מחדל בגוף הפונקציה

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

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

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM dataset_a.my_table )
);

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

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project1.dataset_a.my_table )
);

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

CREATE AGGREGATE FUNCTION project1.dataset_a.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  ( SELECT AVG(dividend / divisor) FROM project2.dataset_c.my_table )
);

יצירת פונקציית UDAF ב-JavaScript

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

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

• מותר להשתמש רק בקידודים מסוגים מסוימים. מידע נוסף זמין במאמר בנושא קידודים מותרים של סוגי SQL בפונקציית UDAF של JavaScript.

• גוף הפונקציה של JavaScript חייב לכלול ארבע פונקציות JavaScript שמאתחלות, מצברות, ממזגות ומסיימות את התוצאות של פונקציית הצבירה המוגדרת על ידי המשתמש (UDAF) ב-JavaScript‏ (initialState,‏ aggregate,‏ merge ו-finalize). מידע נוסף זמין במאמר בנושא פונקציות צבירה נדרשות ב-JavaScript.

• כל ערך שמוחזר על ידי הפונקציה initialState או שנותר בארגומנט state אחרי הקריאה לפונקציה aggregate או merge, חייב להיות ניתן לסריאליזציה. אם רוצים לעבוד עם נתוני צבירה שלא ניתן לסדר בסדרות, כמו פונקציות או שדות סמלים, צריך להשתמש בפונקציות serialize ו-deserialize שכלולות. מידע נוסף על סדרת נתונים וביטול הסדרה שלהם ב-UDAF של JavaScript

יצירת פונקציית UDAF של JavaScript לאחסון מתמיד

אפשר ליצור פונקציית UDAF מתמידה ב-JavaScript, כלומר אפשר להשתמש בה שוב בכמה שאילתות. בטוח להפעיל פונקציות UDAF קבועות כשמשתפים אותן בין בעלים. פונקציות UDAF לא יכולות לשנות נתונים, לתקשר עם מערכות חיצוניות או לשלוח יומנים ל-Google Cloud Observability או לאפליקציות דומות.

כדי ליצור UDAF קבוע, משתמשים בהצהרה CREATE AGGREGATE FUNCTION בלי מילות המפתח TEMP או TEMPORARY. חובה לכלול את קבוצת הנתונים בנתיב הפונקציה.

השאילתה הבאה יוצרת UDAF מתמשך של JavaScript שנקרא SumPositive:

CREATE OR REPLACE AGGREGATE FUNCTION my_project.my_dataset.SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT my_project.my_dataset.SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

יצירת פונקציית UDAF זמנית ב-JavaScript

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

כדי ליצור פונקציית UDAF זמנית, משתמשים בהצהרה CREATE AGGREGATE FUNCTION עם מילת המפתח TEMP או TEMPORARY.

השאילתה הבאה יוצרת פונקציית UDAF זמנית ב-JavaScript שנקראת SumPositive:

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, -1.0, 3.0, -3.0, 5.0, -5.0]) AS x)
SELECT SumPositive(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 9.0 |
 *-----*/

הכללת פרמטרים לא מצטברים ב-UDAF של JavaScript

אפשר ליצור פונקציית UDAF ב-JavaScript עם פרמטרים מצטברים ופרמטרים לא מצטברים.

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

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

בדוגמה הבאה, פונקציית ה-UDAF של JavaScript מכילה פרמטר מצטבר בשם s ופרמטר לא מצטבר בשם delimiter:

CREATE TEMP AGGREGATE FUNCTION JsStringAgg(
  s STRING,
  delimiter STRING NOT AGGREGATE)
RETURNS STRING
LANGUAGE js
AS r'''

  export function initialState() {
    return {strings: []}
  }
  export function aggregate(state, s) {
    state.strings.push(s);
  }
  export function merge(state, partialState) {
    state.strings = state.strings.concat(partialState.strings);
  }
  export function finalize(state, delimiter) {
    return state.strings.join(delimiter);
  }

''';

-- Call the JavaScript UDAF.
WITH strings AS (
  SELECT * FROM UNNEST(["aaa", "bbb", "ccc", "ddd"]) AS values)
SELECT JsStringAgg(values, '.') AS result FROM strings;

/*-----------------*
 | result          |
 +-----------------+
 | aaa.bbb.ccc.ddd |
 *-----------------*/

סדרת נתונים וביטול הסדרה שלהם ב-UDAF של JavaScript

מערכת BigQuery צריכה לבצע סריאליזציה של כל אובייקט שמוחזר על ידי הפונקציה initialState או שנשאר בארגומנט state אחרי הקריאה לפונקציה aggregate או merge. ‫BigQuery תומך בסריאליזציה של אובייקט אם כל השדות הם אחד מהסוגים הבאים:

• ערך פרימיטיבי של JavaScript (לדוגמה: 2, "abc", null, undefined).
• אובייקט JavaScript ש-BigQuery תומך בסריאליזציה של כל ערכי השדות שלו.
• מערך JavaScript ש-BigQuery תומך בסריאליזציה של כל הרכיבים שלו.

אפשר לבצע סריאליזציה של ערכי ההחזרה הבאים:

export function initialState() {
  return {a: "", b: 3, c: null, d: {x: 23} }
}

export function initialState() {
  return {value: 2.3};
}

אי אפשר לבצע סריאליזציה של ערכי ההחזרה הבאים:

export function initialState() {
  return {
    value: function() {return 6;}
  }
}

export function initialState() {
  return 2.3;
}

אם רוצים לעבוד עם מצבי צבירה שלא ניתן לסדר, פונקציית ה-UDAF של JavaScript צריכה לכלול את הפונקציות serialize ו-deserialize. הפונקציה serialize ממירה את מצב הצבירה לאובייקט שניתן לסדר אותו, והפונקציה deserialize ממירה את האובייקט שניתן לסדר בחזרה למצב צבירה.

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

export class SumAggregator {
 constructor() {
   this.sum = 0;
 }
 update(value) {
   this.sum += value;
 }
 getSum() {
   return this.sum;
 }
}

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

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

''';

--Error: getSum is not a function
SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

אם מוסיפים את הפונקציות serialize ו-deserialize לשאילתה הקודמת, השאילתה מופעלת כי אובייקט המחלקה SumAggregator מומר לאובייקט שניתן לסריאליזציה ב-BigQuery, ואז בחזרה לאובייקט המחלקה SumAggregator.

CREATE TEMP AGGREGATE FUNCTION F(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  class SumAggregator {
   constructor() {
     this.sum = 0;
   }

   update(value) {
     this.sum += value;
   }

   getSum() {
     return this.sum;
   }
  }

  export function initialState() {
   return new SumAggregator();
  }

  export function aggregate(agg, value) {
   agg.update(value);
  }

  export function merge(agg1, agg2) {
   agg1.update(agg2.getSum());
  }

  export function finalize(agg) {
   return agg.getSum();
  }

  export function serialize(agg) {
   return {sum: agg.getSum()};
  }

  export function deserialize(serialized) {
   var agg = new SumAggregator();
   agg.update(serialized.sum);
   return agg;
  }

''';

SELECT F(x) AS results FROM UNNEST([1,2,3,4]) AS x;

/*-----------------*
 | results         |
 +-----------------+
 | 10.0            |
 *-----------------*/

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

הכללת משתנים גלובליים ופונקציות בהתאמה אישית ב-UDAF של JavaScript

גוף הפונקציה של JavaScript יכול לכלול קוד JavaScript בהתאמה אישית, כמו משתנים גלובליים של JavaScript ופונקציות בהתאמה אישית.

משתנים גלובליים מופעלים כשקוד ה-JavaScript נטען ל-BigQuery ולפני שהפונקציה initialState מופעלת. משתנים גלובליים יכולים להיות שימושיים אם צריך לבצע עבודת אתחול חד-פעמית שלא צריכה לחזור על עצמה עבור כל קבוצת צבירה, כמו במקרה של הפונקציות initialState,‏ aggregate,‏ merge ו-finalize.

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

בשאילתה הבאה, הפונקציה SumOfPrimes מחשבת סכום, אבל רק מספרים ראשוניים נכללים בחישוב. בגוף הפונקציה של JavaScript יש שני משתנים גלובליים, primes ו-maxTested, שמופעלים קודם. בנוסף, יש פונקציה מותאמת אישית בשם isPrime שבודקת אם מספר הוא ראשוני.

CREATE TEMP AGGREGATE FUNCTION SumOfPrimes(x INT64)
RETURNS INT64
LANGUAGE js
AS r'''

  var primes = new Set([2]);
  var maxTested = 2;

  function isPrime(n) {
    if (primes.has(n)) {
      return true;
    }
    if (n <= maxTested) {
      return false;
    }
    for (var k = 2; k < n; ++k) {
      if (!isPrime(k)) {
        continue;
      }
      if ((n % k) == 0) {
        maxTested = n;
        return false;
      }
    }
    maxTested = n;
    primes.add(n);
    return true;
  }

  export function initialState() {
    return {sum: 0};
  }

  export function aggregate(state, x) {
    x = Number(x);
    if (isPrime(x)) {
      state.sum += x;
    }
  }

  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }

  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([10, 11, 13, 17, 19, 20]) AS x)
SELECT SumOfPrimes(x) AS sum FROM numbers;

/*-----*
 | sum |
 +-----+
 | 60  |
 *-----*/

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

אפשר להרחיב את הפונקציות המוגדרות על ידי המשתמש (UDAFs) ב-JavaScript באמצעות האפשרות library בסעיף OPTIONS. האפשרות הזו מאפשרת לכם לציין ספריות קוד חיצוניות עבור פונקציית ה-UDAF של JavaScript, ואז לייבא את הספריות האלה באמצעות הצהרת import.

בדוגמה הבאה, קוד ב-bar.js זמין לכל קוד בגוף הפונקציה של UDAF ב-JavaScript:

CREATE TEMP AGGREGATE FUNCTION JsAggFn(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
OPTIONS (library = ['gs://foo/bar.js'])
AS r'''

  import doInterestingStuff from 'bar.js';

  export function initialState() {
    return ...
  }
  export function aggregate(state, x) {
    var result = doInterestingStuff(x);
    ...
  }
  export function merge(state, partial_state) {
    ...
  }
  export function finalize(state) {
    return ...;
  }

''';

מבנה JavaScript נדרש

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

פונקציות צבירה נדרשות של JavaScript

אפשר לכלול את פונקציות ה-JavaScript, אבל גוף הפונקציה של ה-JavaScript חייב לכלול את פונקציות ה-JavaScript הבאות שאפשר לייצא:

• ‫initialState([nonAggregateParam]): מחזירה אובייקט JavaScript שמייצג מצב צבירה שבו עדיין לא בוצעה צבירה של שורות.

• ‫aggregate(state, aggregateParam[, ...][, nonAggregateParam]): מצטבר בשורה אחת של נתונים, ומעדכן את המצב כדי לאחסן את תוצאת הצבירה. הפונקציה לא מחזירה ערך.

• ‫merge(state, partialState, [nonAggregateParam]): מיזוג של מצב הצבירה partialState עם מצב הצבירה state. הפונקציה הזו משמשת כשהמנוע צובר נתונים מקטעים שונים במקביל וצריך לשלב את התוצאות. הפונקציה לא מחזירה ערך.

• ‫finalize(finalState, [nonAggregateParam]): מחזירה את התוצאה הסופית של פונקציית הצבירה, בהינתן מצב צבירה סופי finalState.

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

פונקציות אופציונליות של סריאליזציה ב-JavaScript

אם רוצים לעבוד עם מצבי צבירה שלא ניתן לסדר, פונקציית ה-UDAF ב-JavaScript צריכה לספק את הפונקציות serialize ו-deserialize. הפונקציה serialize ממירה את מצב הצבירה לאובייקט שאפשר להעביר ב-BigQuery, והפונקציה deserialize ממירה את האובייקט שאפשר להעביר ב-BigQuery בחזרה למצב צבירה.

• ‫serialize(state): מחזירה אובייקט שניתן לסדר אותו, שמכיל את המידע במצב אגרגציה, כדי לבטל את הסדר שלו באמצעות הפונקציה deserialize.

• ‫deserialize(serializedState): מבצעת דה-סריאליזציה של serializedState (שסוריילה קודם על ידי הפונקציה serialize) למצב צבירה שאפשר להעביר לפונקציות serialize, aggregate, merge או finalize.

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

כדי ללמוד איך לבצע סריאליזציה ודה-סריאליזציה של נתונים באמצעות UDAF של JavaScript, אפשר לעיין במאמר בנושא סריאליזציה ודה-סריאליזציה של נתונים ב-UDAF של JavaScript.

קידודים מותרים של סוגי SQL בפונקציית UDAF ב-JavaScript

ב-UDAFs של JavaScript, סוגי הנתונים של GoogleSQL שנתמכים מייצגים סוגי נתונים של JavaScript באופן הבא:

התקשרות ל-UDAF

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

התקשרות לפונקציית UDAF מתמידה

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

בדוגמה הבאה, השאילתה קוראת ל-UDAF מתמשך שנקרא WeightedAverage:

SELECT my_project.my_dataset.WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

נוצרת טבלה עם התוצאות הבאות:

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

התקשרות לפונקציית UDAF זמנית

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

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

בדוגמה הבאה, השאילתה קוראת ל-UDAF זמני שנקרא WeightedAverage:

CREATE TEMP AGGREGATE FUNCTION WeightedAverage(...)

-- Temporary UDAF function call
SELECT WeightedAverage(item, weight, 2) AS weighted_average
FROM (
  SELECT 1 AS item, 2.45 AS weight UNION ALL
  SELECT 3 AS item, 0.11 AS weight UNION ALL
  SELECT 5 AS item, 7.02 AS weight
);

נוצרת טבלה עם התוצאות הבאות:

/*------------------*
 | weighted_average |
 +------------------+
 | 4.5              |
 *------------------*/

התעלמות משורות עם ערכים של NULL או הכללה שלהן

כשקוראים לפונקציית UDAF ב-JavaScript עם הארגומנט IGNORE NULLS,‏ BigQuery מדלג אוטומטית על שורות שבהן כל ארגומנט מצטבר מקבל את הערך NULL. השורות האלה מוחרגות לחלוטין מהצבירה ולא מועברות לפונקציית ה-JavaScript‏ aggregate. כשמספקים את הארגומנט RESPECT NULLS, הסינון NULL מושבת וכל שורה מועברת ל-UDAF של JavaScript, ללא קשר לערכים של NULL.

אם לא מציינים את הארגומנטים IGNORE NULLS ו-RESPECT NULLS, הארגומנט שמוגדר כברירת מחדל הוא IGNORE NULLS.

בדוגמה הבאה אפשר לראות את התנהגות ברירת המחדל של NULL, את ההתנהגות של IGNORE NULLS ואת ההתנהגות של RESPECT NULLS:

CREATE TEMP AGGREGATE FUNCTION SumPositive(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''

  export function initialState() {
    return {sum: 0}
  }
  export function aggregate(state, x) {
    if (x == null) {
      // Use 1000 instead of 0 as placeholder for null so
      // that NULL values passed are visible in the result.
      state.sum += 1000;
      return;
    }
    if (x > 0) {
      state.sum += x;
    }
  }
  export function merge(state, partialState) {
    state.sum += partialState.sum;
  }
  export function finalize(state) {
    return state.sum;
  }

''';

-- Call the JavaScript UDAF.
WITH numbers AS (
  SELECT * FROM UNNEST([1.0, 2.0, NULL]) AS x)
SELECT
  SumPositive(x) AS sum,
  SumPositive(x IGNORE NULLS) AS sum_ignore_nulls,
  SumPositive(x RESPECT NULLS) AS sum_respect_nulls
FROM numbers;

/*-----+------------------+-------------------*
 | sum | sum_ignore_nulls | sum_respect_nulls |
 +-----+------------------+-------------------+
 | 3.0 | 3.0              | 1003.0            |
 *-----+------------------+-------------------*/

מחיקת פונקציית UDAF

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

מחיקה של פונקציית UDAF מתמידה

כדי למחוק פונקציית UDAF מתמידה, משתמשים בהצהרה DROP FUNCTION. צריך לכלול את קבוצת הנתונים בנתיב הפונקציה.

בדוגמה הבאה, השאילתה מוחקת פונקציית UDAF מתמשכת שנקראת WeightedAverage:

DROP FUNCTION IF EXISTS my_project.my_dataset.WeightedAverage;

מחיקה של פונקציית UDAF זמנית

כדי למחוק UDAF זמנית, משתמשים בהצהרה DROP FUNCTION.

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

DROP FUNCTION IF EXISTS WeightedAverage;

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

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

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

טיפים לשיפור הביצועים

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

• מסננים מראש את הקלט. עיבוד נתונים ב-JavaScript יקר יותר מאשר ב-SQL, ולכן מומלץ לסנן את הקלט כמה שיותר ב-SQL קודם.

  השאילתה הבאה פחות יעילה כי היא מסננת את הקלט באמצעות x > 0 בקריאה ל-UDAF:

  SELECT JsFunc(x) FROM t;
  

  השאילתה הבאה יעילה יותר כי היא מסננת מראש את הקלט באמצעות WHERE x > 0 לפני הקריאה ל-UDAF:

  SELECT JsFunc(x) FROM t WHERE x > 0;
  

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

  השאילתה הבאה פחות יעילה כי היא מטמיעה UDAF:

  SELECT SumSquare(x) FROM t;
  

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

  SELECT SUM(x*x) FROM t;
  

• פונקציות UDAF ב-JavaScript מתאימות לפעולות צבירה מורכבות יותר, שלא ניתן לבטא באמצעות פונקציות מובנות.

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

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

  export function initialState() {
    return {rows: []};
  }
  export function aggregate(state, x) {
    state.rows.push(x);
  }
  ...
  

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

מגבלות

• ההגבלות שחלות על UDF חלות גם על UDAF. פרטים נוספים זמינים במאמר בנושא מגבלות של UDF.

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

• השימוש בפסקה ORDER BY בקריאה לפונקציית UDAF של JavaScript לא נתמך.

  SELECT MyUdaf(x ORDER BY y) FROM t; -- Error: ORDER BY is unsupported.
  

תמחור

החיוב על UDAFs מתבצע לפי מודל התמחור הרגיל של BigQuery.

מכסות ומגבלות

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