בפוסט זה נמשיך במסע שלנו לחקר המחוזות הפחות מוכרים של Big Query בתקווה שיעזור לקצר, לייעל וליפות את הקוד שלכם. אם פספסתם את חלק א מומלץ להציץ לפני.
נציין שוב- קיים שוני בהתאם לסביבת ה-SQL בה האנליסט משתמש. המאמר יעסוק ב Big Query של גוגל שזו המערכת בה אני עושה שימוש. אם אתם משתמשים בסוג אחר של SQL סיכוי סביר שהפונקציות שאציין פה קיימות גם במערכות שלכם אבל בשמות אחרים (אם כי לא בטוח).
Safe
מניח שלרבים ממכם יצא לכתוב שאילתה שעובדת תקין, לייצר ממנה Schedule Query , רק כדי לקבל ביום בהיר אחד התראת שגיאה שנגרמת בגלל חילוק ב-0, Cast שפתאום לא עובד או שימוש ברשימה עם ערך שלא קיים. על מנת לפתור את כל אלו קיימות פונקציות Safe שנראה לי מסבירות את עצמן.
חילוק באפס:
-- good (return 2)
select 4/2
-- good (return 4)
select safe_divide(12,3)
-- query failed: return error
select 5/0
-- return null
select safe_divide(5,0)
Cast:
-- good casting
select cast('2029-10-07' as date)
-- query failed: return error
select cast('2029-13-07' as date)
-- return null
select safe_cast('2029-13-07' as date)
Array:
WITH Items AS
(
SELECT ["coffee", "tea", "milk"] AS item_array
)
-- good: return "coffee"
SELECT item_array[0] AS item_index
FROM Items
-- query failed: return error
SELECT item_array[6] AS item_index
FROM Items
-- return null
SELECT item_array[SAFE_OFFSET(6)] AS item_safe_offset
FROM Items
חשוב לציין כי על אף שהפונקציות הנ״ל בהחלט מאפשרות שקט תעשייתי, השימוש בהן כן יכול להיות חרב פפיות ולמנוע איתור באגים בדאטה. אז ראו הוזהרתם.
לרוב התוצאות של שאילתה מועברות ישר לתוכנות גרפיות או סטטיסטיות להמשך ניתוח. אבל לפעמים אנחנו שולפים דאטה כדי לקבל תשובות זריזות ישר מה- UX של ביג קוורי. במקרים האלו אני אוהב להשתמש בפונקציית Format כדי להצליח לקרוא יותר טוב את הדאטה- הדבר נכון בעיקר שהפלט שלנו הוא מספרים גבוהים (להם ארצה להוסיף פסיק) או Float אותם נמיר לאחוזים (במידה ויש הגיון עסקי לעשות זאת).
צריך להודות שהסינטקסט לא הכי אינטואטיבי , אבל אחרי 4-5 פעמים הוא נחקק בזיכרון. הערה חשובה נוספת היא שהפלט המעובד הוא String על כל המשמעויות שנגזרות מכך.
אם Max/Min משמשים אותנו כדי לחשב את הערך המקסימלי/מינימלי עבור עמודה ספציפית על פני הרבה שורות (ולכן תמיד יופיעו כחלק משאילתה אגרגטיבית או פונקציית חלון), Greatest/Least ישמשו אותנו כדי לחשב את הערך המקסימלי/מינימלי פר שורה על פני מספר עמודות (כמה שתבחרו להכניס לפונקציה). הפונקציה, לצערי, רגישה לnulls, כך שאם אחד מהערכים שתכניסו יהיה null התוצאה של הפונקציה תהיה null.
אני משתמש בפונקציה הזו לרוב שאני רוצה לעשות capping לדאטה שלי (נפוץ במבחני השערות). אם נרצה, לשם הדוגמא, לטפל ביוזרים קיצוניים במדד של ״זמן באפליקציה״ (בלי להוציא אותם מהמדגם)- נחשב מהו אחוזון 99 (או כל מדד אחר שתראו לנכון) ובעזרת Least נוודא שהערך המקסימלי שיוזר יכול לקבל הוא אחוזון 99.
הייתה לי התלבטות אם להתייחס ליכולות (הדי מפותחות) של ביג קוורי עם ML. מצד אחד זו אכן יכולת פחות מוכרת שעולה בקנה אחד עם מטרת הפוסט, אך מצד שני אני כשלעצמי פחות משתמש בה. נכון להיום, כאשר אני צריך לבנות מודל פרדיקציה אני פשוט פותח מחברת Jupyter ומחבר אותה לדאטה בייס. עם זאת, אני בהחלט רואה את הערך בלבנות מודל ״ישר״ מהדאטה, מה שיכול לחסוך את הטרחה של לייצר תהליכי ETL הלוך ושוב.
בכל אופן, מוזמנים להציץ פה ולבחון מה יש לBQ להציע- אבל בגדול זה כולל את כל הכיף של פיצול דאטה, אימון מודל, הערכה שלו ויצירת פרדיקציות. כאשר המערכת כבר יודעת לתמוך בלא מעט מודלים. אם יש בקהל מישהו שכן עושה בזה שימוש ורוצה להרחיב אני אשמח לשמוע.
גם לגבי הסינטקסט הזה הייתה לי התלבטות קלה, לא בגלל שאני לא משתמש בו (להפך!) אלא כי מרגיש לי שהוא כבר ״סוד״ ידוע. אבל לטובות הנשמות האבודות שטרם גילו, Qualify מאשר לנו לפלטר Windows functions ללא צורך ביצירת sub query (שכן, כפי שיודע כל מי שהתנסה עם פונקציות חלון, לא ניתן להשתמש באילו בשילוב עם פקודת where).
-- filter windows function with "where"
select *
from(
select age,
rank() over(order by age desc) age_analyst_rank
from others.analyst_pool
)
where age_analyst_rank=1
;
-- filter windows function with "qualify"
select age,
rank() over(order by age desc) age_analyst_rank
from others.analyst_pool
qualify age_analyst_rank=1
אני מהמר שאם הייתה לי גישה לדשבורד פרודקט של ביג קוורי, הייתי רואה כי פונקצית החלון הכי פחות פופולארית היא Ntiles, וחבל שכך. בגדול, Ntiles מאפשרת לנו לחלק את הדאטה ל-N קבוצות שוות בגודלן. כדי להסביר יותר טוב את הערך שהפונקציה הזו נותנת לנו- בוא נקח את הדאטה של סקר האנליסטים עליו כבר בצעתי את האנליזה הזו והזו.
אחת העמודות בדאטה היא גיל המשיבים, ופרקטיקה נפוצה בחקירת הדאטה (בטח שבונים דאטה אגרגטיבי) תהיה לחלק את המשתנה הזה לקבוצות שונות לפי טווח הגילאים של המשיבים לסקר. אם ברצוננו לחלק את משתנה הגיל לקבוצות שוות (שזה לעתים קרובות המקרה, אך לא תמיד), Ntiles יכול לעזור לנו בקביעת הטווחים הנכונים.
לצערי, בניגוד למקבילה שלה בפייתון (qcut) הפונקציה לא יודעת להחזיר טווחים אלא רק ״קבוצות״ מספריות (1/2/3), אבל עם קצת מניפולציה בקוד אפשר לייצר בקלות את השאילתה הבאה שמחלקת את הדאטה לחמש קבוצות של טווחי גיל, ואשר מספקת לנו את האינפורמציה לאיך החלוקה נעשתה:
לבטח שמתם לב שסיווג המשיבים לסקר מתחלק לגדלי קבוצות זהים לחלוטין. ״הקסם״ הזה מתאפשר רק כי יש חפיפה בין הקבוצות. בדוגמא שלנו, חלק מהמשיבים בני ה-28 שובצו בקבוצה ״1״ בעוד שחלק שובצו בקבוצה ״2״. אנחנו נקח את האינפורמציה הזו ועל בסיס נחלק את היוזרים לקבוצות תוך כדי שמוש בסינטקסט המוכר של Case When:
אמנם התוצאה היא לא חלוקה לחמש קבוצות שוות, אבל דאטה הוא לא תוכנית לבקשתך והשימוש בntiles אפשר לנו לחלק את הדאטה לחמש קבוצות שהן בקירוב שוות בגודלן בצורה יחסית מדעית ואוטומטית.
אז בוא נדבר שניה על השאילתה Case When שהצגתי למעלה- מניח שהיא לא זרה לרובכם וגם אתם יצרתם משתני טווחים באופן הזה בעבר. מה אם הייתי אומר לכם שאת כל הסינטקסט הארוך הזה אפשר לכווץ לשורה אחת? אז בשביל זה נולדה פונקציית- Range Bucket, *שכמעט* עושה את זה.
במקום לקשקש יותר קל להראות:
אני מניח שניתן להבין לבד את הערך בפונקציה הנ״ל, לצד הבעיתיות בפלט שמונע ממנה להיות פונקציה שימושית עבורי (הפלט המספרי הוא חסר קונטציה עסקית). אני אנצל את המעמד לקרוא לחברה של הפרודקט בגוגל (שכידוע עוקבים אחרי הבלוג האזוטרי הזה באדיקות) – !Do something about it.
אך עד שהחברה שם יתעשתו על עצמם, אני אפתור את זה עבורכם בהמשך הפוסט.
User-defined functions
״אם אין אני לי מי״ – בסופו של יום, לא נרצה לסמוך או לחכות לגוגל או כל חברה אחרת כדי שייצרו את הפונקציה שאנחנו צריכים, מה גם שסביר להניח שבהרבה מקרים נרצה דברים מאוד ספציפיים. לשם כך BQ מאפשרת למשתמש לייצר פונקציות מותאמות אישית. כאן צריך לציין שהיכולת הזו דורשת הרשאות מה- DBA.
מהניסיון שלי (שמורכב ממדגם לא מייצג של חברות) האופציה הזו, מסיבה שלא לגמרי ברורה לי, חסומה לאנליסטים. אם זה המצב אצלכם מוזמנים לנסות להרים קול זעקה. לחלופין, אנשי הBI בקהל אולי יוכלו להסביר לי מה הסכנה באפשרות הזו.
לענייננו, להלן קוד שהופך את שאילתת הcase when שייצרנו קודם לפונקציה מובנית. הקוד שומר את הפונקציה בתוך הדאטה סט של functions שיצרתי ובו אני נוהג לאחסן פונקציות.
CREATE FUNCTION functions.age_buckets(age INT64)
RETURNS String
AS (
case when age<28 then '1) <=27'
when age<31 then '2) 28-30'
when age<34 then '3) 31-33'
when age<38 then '4) 34-37'
when age>=38 then '5) >=38' end
)
לאחר מימוש הפונקציה, קל להכניס אותה לשאילתות שלכם ולהשתמש בה באופן שוטף (ראו דוגמא). שימוש בפונקציות כזה בקוד יותר אלגנטי, חוסך זמן, שמור על אחידות הגדרות הטווחים ובעיקר- מאפשר שינוי מהיר וגורף במידה ונרצה לעשות שינוי בכל השאילתות שלנו (בתנאי כמובן שהשאילתות הן מסוג Overwrite ולא Append).
אבל פונקציות מותאמות אישית מאפשרות לנו יותר מזה. בוא נחזור לפונקציה המובנית והכמעט מוצלחת שהצגנו קודם- range_bucket, עם קצת מאמץ (שכבר חסכתי לכם) ניתן לייצר את הפונקציה הבאה שנקרא לה daniel_data_buckets ואשר תפעל בדיוק כמו range_bucket רק עם פלט שנותן הקשר עסקי נכון:
CREATE FUNCTION functions.daniel_data_buckets(value INT64,list ARRAY)
RETURNS String
AS (
case when range_bucket(value,list)=ARRAY_LENGTH(list) then concat(cast(range_bucket(value,list)+1 as string),') ','>=',cast(list[range_bucket(value,list)-1] as string))
when range_bucket(value,list)=0 then concat('1) <=',cast(list[range_bucket(value,list)]-1 as string))
else concat(cast(range_bucket(value,list)+1 as string),') ',cast(list[range_bucket(value,list)-1] as string),'-',cast(list[range_bucket(value,list)]-1 as string))
end
)
ואחריי שיישמנו את הקוד, נוכל להשתמש בפונקצייה בכל שאילתה ולסווג את הדאטה שלנו לאיזה מספר של קבוצות וטווחים שנמצא לנכון, לדוגמא:
