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

במאמר הזה מוסבר איך לייעל שאילתות שמשתמשות בפונקציות SQL.

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

שיטה מומלצת: כשזה אפשרי, כדאי להשתמש ב-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.

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

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

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

בדוגמה הבאה אפשר לראות איך מפעילים שאילתה של פונקציית 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;