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

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

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

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

יצירת פונקציית SQL UDAF קבועה

אפשר ליצור פונקציית SQL UDAF מתמידה, כלומר אפשר להשתמש בה שוב בכמה שאילתות. אפשר להפעיל פונקציות 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)
);

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

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

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

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

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

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

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

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

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

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

אפשר ליצור פונקציית JavaScript UDAF זמנית, כלומר פונקציית 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

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

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

אפשר להרחיב את פונקציות ה-UDAF ב-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

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

סוג הנתונים של GoogleSQL
 סוג נתונים של JavaScript
 הערות
ARRAY Array אין תמיכה במערך של מערכים. כדי לעקוף את המגבלה הזו, השתמשו בסוגי הנתונים Array<Object<Array>> (JavaScript) ו-ARRAY<STRUCT<ARRAY>> (GoogleSQL).
BIGNUMERIC Number או String בדיוק כמו במכשיר NUMERIC.
BOOL Boolean
BYTES Uint8Array
DATE Date
FLOAT64 Number
INT64 BigInt
JSON סוגים שונים אפשר להמיר את סוג הנתונים JSON של GoogleSQL ל-Object, ל-Array או לסוג נתונים אחר של JavaScript שנתמך ב-GoogleSQL.
NUMERIC Number או String אם אפשר לייצג ערך NUMERIC בדיוק כערך IEEE 754 floating-point (טווח [-253, 253]), ואין לו חלק שברי, הוא מקודד כסוג נתונים Number. אחרת, הוא מקודד כסוג נתונים String.
STRING String
STRUCT Object כל שדה STRUCT הוא מאפיין בעל שם בסוג הנתונים Object. אין תמיכה בשדה STRUCT ללא שם.
TIMESTAMP Date ‫Date מכיל שדה מיקרו-שנייה עם החלק של המיקרו-שנייה של TIMESTAMP.

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

רשימת פונקציות UDAF

פונקציות 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);
}
...

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

מגבלות

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

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

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

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

תמחור

החיוב על פונקציות UDAF מתבצע לפי מודל התמחור הסטנדרטי של BigQuery.

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

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