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

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

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

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

דוגמה ל-UDF

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

חזרה למעלה

מבנה של UDF

function name(row, emit) {
  emit(<output data>);
}

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

‫row: שורת קלט.
‫emit: וו שמשמש את BigQuery לאיסוף נתוני פלט. הפונקציה emit מקבלת פרמטר אחד: אובייקט JavaScript שמייצג שורה אחת של נתוני פלט. אפשר לקרוא לפונקציה emit יותר מפעם אחת, למשל בלולאה, כדי להפיק כמה שורות של נתונים.

בדוגמה הבאה של קוד מוצגת פונקציית UDF בסיסית.

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

רישום של פונקציית UDF

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

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

עמודות קלט

שמות עמודות הקלט צריכים להיות זהים לשמות (או לכינויים, אם יש) של העמודות בטבלת הקלט או בשאילתת המשנה.

עבור עמודות קלט שהן רשומות, צריך לציין ברשימת עמודות הקלט את שדות העלה שרוצים לגשת אליהם מהרשומה.

לדוגמה, אם יש לכם רשומה שבה מאוחסנים השם והגיל של אדם מסוים:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

מפרט הקלט של השם והגיל יהיה:

['person.name', 'person.age']

שימוש בפונקציה ['person'] בלי שם או גיל ייצור שגיאה.

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

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

סכימת פלט

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

בוליאני
מספר ממשי (float)
מספר שלם
הקלטה
מחרוזת
חותמת זמן

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

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

כל שדה יכול להכיל מאפיין אופציונלי mode, שתומך בערכים הבאים:

nullable : זוהי ברירת המחדל ואפשר להשמיט אותה.
required : אם מציינים את המאפיין הזה, צריך להגדיר ערך לשדה הנתון, והוא לא יכול להיות לא מוגדר.
repeated : אם מציינים את האפשרות הזו, השדה הנתון חייב להיות מערך.

השורות שמועברות לפונקציה emit() צריכות להתאים לסוגי הנתונים של סכמת הפלט. שדות שמיוצגים בסכימת הפלט ומושמטים בפונקציית הפלט יופיעו כערכי null.

הגדרה או הפניה של פונקציית UDF

אם אתם מעדיפים, אתם יכולים להגדיר את הפונקציה המוגדרת על ידי המשתמש בשורה ב-bigquery.defineFunction. לדוגמה:

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

אפשרות אחרת היא להגדיר את ה-UDF בנפרד ולהעביר הפניה לפונקציה ב-bigquery.defineFunction. לדוגמה:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

טיפול בשגיאות

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

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

הרצת שאילתה עם UDF

אפשר להשתמש ב-UDF ב-SQL מדור קודם באמצעות כלי שורת הפקודה של BigQuery או BigQuery API. ‫Google Cloud במסוף אין תמיכה בפונקציות UDF ב-SQL מדור קודם.

שימוש בכלי שורת הפקודה של BigQuery

כדי להריץ שאילתה שמכילה פונקציות UDF אחת או יותר, מציינים את הדגל --udf_resource בכלי שורת הפקודה של BigQuery מתוך Google Cloud CLI. הערך של הדגל יכול להיות URI של Cloud Storage‏ (gs://...) או הנתיב לקובץ מקומי. כדי לציין כמה קובצי משאבים של UDF, חוזרים על השימוש בדגל הזה.

כדי להריץ שאילתה עם UDF, משתמשים בתחביר הבא:

bq query --udf_resource=<file_path_or_URI> <sql_query>

בדוגמה הבאה מריצים שאילתה שמשתמשת ב-UDF שמאוחסן בקובץ מקומי ובשאילתת SQL שמאוחסנת גם היא בקובץ מקומי.

יצירת פונקציית UDF

אפשר לאחסן את ה-UDF ב-Cloud Storage או כקובץ טקסט מקומי. לדוגמה, כדי לאחסן את הפונקציה הבאה urlDecode UDF, יוצרים קובץ בשם urldecode.js ומדביקים את קוד ה-JavaScript הבא בקובץ לפני ששומרים אותו.

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

יצירת השאילתה

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

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [my-project:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

אחרי ששומרים את הקובץ, אפשר להפנות אליו בשורת הפקודה.

אני מריץ את השאילתה

אחרי שמגדירים את ה-UDF ואת השאילתה בקבצים נפרדים, אפשר להפנות אליהם בשורת הפקודה. לדוגמה, הפקודה הבאה מריצה את השאילתה ששמרתם בקובץ בשם query.sql ומתייחסת ל-UDF שיצרתם.

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

שימוש ב-BigQuery API

configuration.query

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

מקורות מידע על קוד

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

בלובים של קוד JavaScript בתוך השורה מאוכלסים בקטע inlineCode של רכיב userDefinedFunctionResource. עם זאת, קוד שיושמש מחדש או שיהיה הפניה אליו בכמה שאילתות צריך לשמור ב-Cloud Storage ולהפנות אליו כאל משאב חיצוני.

כדי להפנות לקובץ מקור של JavaScript מ-Cloud Storage, מגדירים את הקטע resourceURI של רכיב userDefinedFunctionResource ל-URI של קובץ gs://.

הגדרת השאילתה יכולה להכיל כמה רכיבי userDefinedFunctionResource. כל רכיב יכול להכיל קטע inlineCode או קטע resourceUri.

דוגמה

בדוגמה הבאה של JSON מוצגת בקשת שאילתה שמפנה לשני משאבי UDF: BLOB אחד של קוד מוטבע וקובץ אחד lib.js לקריאה מ-Cloud Storage. בדוגמה הזו, myFunc והפעלת הרישום של myFunc מסופקים על ידי lib.js.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

חזרה למעלה

שיטות מומלצות

פיתוח פונקציית UDF

אתם יכולים להשתמש בכלי הבדיקה של פונקציות UDF כדי לבדוק ולנפות באגים בפונקציות UDF בלי להגדיל את החשבון שלכם ב-BigQuery.

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

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

בדוגמה של הרצת שאילתה, שאילתת משנה מועברת כקלט ל-urlDecode, במקום טבלה מלאה. טבלה יכולה לכלול מיליארדי שורות, ואם נריץ את ה-UDF על הטבלה כולה, ה-JavaScript framework יצטרך לעבד הרבה יותר שורות מאשר אם נריץ אותה על שאילתת המשנה המסוננת.

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

אין לאחסן או לגשת למצב שניתן לשינוי בשיחות של UDF. בדוגמה הבאה של קוד מתואר התרחיש הזה:

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

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

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

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

הרחבת שאילתות נבחרות

חובה לציין במפורש את העמודות שנבחרות מ-UDF. אין תמיכה ב-SELECT * FROM <UDF name>(...).

כדי לבדוק את המבנה של נתוני השורה של הקלט, אפשר להשתמש ב-JSON.stringify() כדי להפיק עמודת פלט של מחרוזת:

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

חזרה למעלה

מגבלות

כמות הנתונים שפונקציית ה-UDF מוציאה כשמעבדים שורה אחת צריכה להיות בערך 5MB או פחות.
כל משתמש מוגבל להרצת כ-6 שאילתות UDF בפרויקט מסוים בו-זמנית. אם קיבלתם הודעת שגיאה על כך שחרגתם ממגבלת השאילתות המקבילות, כדאי להמתין כמה דקות ולנסות שוב.
יכול להיות שפונקציית UDF תגיע לזמן קצוב לתפוגה ותמנע את השלמת השאילתה. הזמן הקצוב לתפוגה יכול להיות קצר כמו 5 דקות, אבל הוא משתנה בהתאם לכמה גורמים, כולל כמות הזמן שפונקציית המשתמש צורכת במעבד, וגודל הקלט והפלט של פונקציית ה-JS.
בכל עבודת שאילתה יכולים להיות עד 50 משאבי UDF (בלובי קוד מוטבעים או קבצים חיצוניים).
כל blob של קוד מוטבע מוגבל לגודל מקסימלי של 32KB. כדי להשתמש במשאבי קוד גדולים יותר, מאחסנים את הקוד ב-Cloud Storage ומפנים אליו כמשאב חיצוני.
כל משאב קוד חיצוני מוגבל לגודל מקסימלי של 1MB.
הגודל המצטבר של כל משאבי הקוד החיצוניים מוגבל ל-5MB לכל היותר.

חזרה למעלה

מגבלות

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

חזרה למעלה