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