שיטות מומלצות לפונקציות

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

אופטימיזציה של השוואת מחרוזות

שיטה מומלצת: כשאפשר, השתמשו ב-LIKE במקום ב-REGEXP_CONTAINS.

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

נבחן את השימוש הבא בפונקציה REGEXP_CONTAINS:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

כדי לייעל את השאילתה הזו:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

אופטימיזציה של פונקציות צבירה

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

אם לפונקציית הצבירה ב-SQL שבה אתם משתמשים יש פונקציית קירוב מקבילה, פונקציית הקירוב תניב ביצועי שאילתה מהירים יותר. לדוגמה, במקום להשתמש ב-COUNT(DISTINCT), השתמשו ב-APPROX_COUNT_DISTINCT. מידע נוסף זמין במאמר פונקציות צבירה משוערות.

אפשר גם להשתמש בפונקציות HyperLogLog++ כדי לבצע קירובים (כולל צבירות מקורבות בהתאמה אישית). מידע נוסף זמין בקטע פונקציות HyperLogLog++‎ בחומר העזר בנושא GoogleSQL.

נבחן את השימוש הבא בפונקציה COUNT:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

כדי לייעל את השאילתה הזו:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

אופטימיזציה של פונקציות קוונטיל

שיטה מומלצת: כשאפשר, השתמשו ב-APPROX_QUANTILE במקום ב-NTILE.

הרצת שאילתה שמכילה את הפונקציה NTILE עלולה להיכשל עם השגיאה Resources exceeded אם יש יותר מדי רכיבים ל-ORDER BY במחיצה אחת, מה שגורם לנפח הנתונים לגדול. חלון הניתוח לא מחולק למחיצות, ולכן החישוב של NTILE דורש ORDER BY גלובלי לכל השורות בטבלה, כדי שיוכלו לעבור עיבוד על ידי משבצת או עובד אחד.

במקום זאת, אפשר להשתמש ב-APPROX_QUANTILES. הפונקציה הזו מאפשרת להריץ את השאילתה בצורה יעילה יותר, כי היא לא דורשת ORDER BY גלובלי לכל השורות בטבלה.

נבחן את השימוש הבא בפונקציה NTILE:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

כדי לייעל את השאילתה הזו:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

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

  1. מספק צבירת נתונים משוערת.
  2. ממקמת את ערכי השאריות (השארית של מספר השורות חלקי הקטגוריות) בצורה אחרת.

אופטימיזציה של פונקציות UDF

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

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

פונקציות מתמידות

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

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

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

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

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;

אופטימיזציה של עלויות ה-AI

שיטה מומלצת: אם תרחיש השימוש שלכם תומך בכך, השתמשו במצב האופטימיזציה כשאתם מריצים את AI.IF או את AI.CLASSIFY על מערכי נתונים גדולים.

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