ניתוח נתונים על שדות השמורים בפורמט JSON

הקדמה

JSON הוא סוג של מבנה נתונים המשמש לשמירת נתונים באופן היררכי. ישנם מערכות מידע השומרות את הנתונים במבנה נתונים זה ועל כן דאטה אנליסט צריך לדעת איך לחלץ את המידע מתוך מבנה נתונים זה כדי שיוכל לבצע אנליזות.

לרוב JSON יופיע בתור שדה בתוך דאטה בייס. למשל שדה בשם Button_tap שיכיל את כל המידע על האירועים של לחיצת כפתור באפליקציה.

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

במאמר זה אראה מספר שיטות לעבודה עם שדות JSON, כיצד ניתן לחלץ מידע מ- JSON באמצעות שאילתות SQL וכיצד ניתן להתגבר על החסרונות בעבודה עם מבנה נתונים זה. 

**** לפודקאסט של הבלוג לחצו כאן ****

חילוץ מידע משדות JSON – הגישה המיידית

בעזרת פקודת SELECT פשוטה ניתן לחלץ מידע משדה JSON וליצור באמצעותו שדה כאילו היה זה שדה רגיל בדאטה בייס. האופרטור של החילוץ משתנה בין הסוגים של הדאטה-בייסים. ב- Snowflake לדוגמה, יש שימוש באופרטור ":".

Select
   Button_tap:event_date as event_date
From
   Button_tap_events

לפי הגישה המיידית, בכל פעם שנצטרך לחלץ מידע, ניצור שדה חדש מתוך ה- JSON.

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

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

גישת שאילתות CTE

שאילתות CTE מאפשרות יצירת תת שאילתה חיצונית לשאילתה המרכזית. בתוך התת שאילתה ניתן ליצור בעזרת SELECT פשוט את השדות שאנו רוצים מתוך ה- JSON, ובשאילתה המרכזית לקרוא לשדות שיצרנו.

יתרונות – הקוד פחות מסורבל ומאפשרת ליצור קטעי קוד ברורים ומובנים יותר כיוון שאפשר בעזרת שאילתת CTE לחלץ את הנתונים מתוך ה- JSON ולהשתמש בהם בשאילתה הראשית כאילו עבדנו על טבלה רגילה.

חסרונות – השיטה הזאת אומנם פותרת את הבעיה של כתיבה מסורבלת אך היא אינה פותרת את המאמץ שעל הדאטה-בייס לבצע, בחילוץ הנתונים מתוך השדות של ה- JSON ולכן גם כאן היעילות של השאילתה תהיה נמוכה.

גישת טבלאות זמניות

לפי גישה זו, במקום להשתמש בשאילתת CTE היוצרת את השדות שאנחנו רוצים, נכתוב שאילתה שמייצרת טבלה פיסית זמנית בדאטה-בייס ונייצר לה index (או Partition).

יתרונות –  הטבלה הזמנית ממנה נשלוף את השדות שיצרנו היא כבר טבלה רלציונית לכל דבר והדאטה-בייס יכול לשלוף ממנה נתונים בצורה יעילה ומהירה.

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

גישת טבלאות קבועות ב- Sandbox

Sandbox הוא כינוי לאזור בדאטה-בייס שמוקצה ע"י מנהלי הדאטה-בייס לדאטה אנליסט ובו הוא יכול לייצר ולמחוק בו טבלאות ללא בקשה של הרשאות מיוחדות. אפשר לראות אזור זה כמגרש משחקים שבו דאטה אנליסט יכול לשחק בטבלאות כאוות נפשו מבלי לבקש הרשאות מיוחדות עבור כל טבלה שהוא נוגע בה.

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

יתרונות – בגישה זו אין צורך ליצור את הטבלה שמייצרת את השדות מתוך ה JSON בתחילת יום העבודה והטבלה לא נמחקת אם יש בעיה בסשן החיבור לדאטה-בייס.

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

* הפוסט מנוסח בלשון זכר אך מתאים לכל המינים.

המאמר נכתב על ידי יובל מרנין.
לחברות המעונינות בנהפקת תובנות מנתונים או יצירת דוחות BI ניתן לפנות אליי ב- לינקדאין, פייסבוק או במייל: [email protected]

לקריאה נוספת

היתרונות בהעסקת דאטה אנליסט חיצוני (פרילנסר).
מדריך לשפת SQL בגישה אנליטית
התפקידים של דאטה אנליסט – חלק א.