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

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

במדריך הזה מוסבר על הנושאים הבאים:

איך Looker יוצר את ה-SQL לטבלאות נגזרות

כשמריצים שאילתות על טבלאות נגזרות ב-LookML, ‏ Looker מתרגם את השאילתות להצהרות SQL כביטויים של טבלאות נפוצות (CTE) או כתצוגות מוטבעות, בהתאם לניב. במילים אחרות, שאילתות ה-SQL שנוצרות יכולות להיות דומות לאחת מהדוגמאות הבאות.

בדוגמה הבאה אפשר לראות איך Looker יכול ליצור שאילתת SQL שמשתמשת ב-CTE כדי לשלוח שאילתה לטבלה נגזרת:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

בדוגמה הבאה אפשר לראות איך Looker יכול ליצור שאילתת SQL שמשתמשת בתצוגה מוטבעת כדי להריץ שאילתה על טבלה נגזרת:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

טבלאות נגזרות שמבוססות על SQL

כדי ליצור טבלת נתונים נגזרת שמבוססת על SQL, מגדירים שאילתת SQL ישירות ב-LookML באמצעות הפרמטר sql בתוך הפרמטר derived_table של תצוגה. כך אפשר להגדיר את העמודות של הטבלה הנגזרת באמצעות SQL.

לדוגמה, קוד LookML לדוגמה הבא מגדיר טבלה נגזרת בשם top_100_users שמזהה את 100 המשתמשים המובילים עם סכום ההוצאות הכולל הגבוה ביותר בכל ההזמנות שלהם:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

כשמפנים אל התצוגה top_100_users בשאילתת Explore, Looker משתמש בהצהרת ה-SQL SELECT הזו ב-SQL שנוצר כ-CTE או כתצוגה מוטבעת, בהתאם לדיאלקט.

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

  • יכול להיות שהקשר בין הטבלאות orders ו-order_items כבר מוגדר במודל LookML. אם שמות הטבלאות הבסיסיות במסד הנתונים משתנים, צריך לעדכן את שמות הטבלאות בכמה מקומות, כולל בהגדרה של טבלה נגזרת שמבוססת על SQL.
  • ה-SQL בהגדרת הטבלה הנגזרת צריך להיות בניב הנכון של מסד הנתונים הבסיסי. אם הנתונים מועברים למסד נתונים אחר, צריך לשנות את ההגדרה של הטבלה הנגזרת שמבוססת על SQL.

לכן, ברוב המקרים עדיף להשתמש בטבלאות נגזרות מקוריות.

טבלאות נגזרות מבוססות LookML ‏(NDT)

ב-Looker, טבלאות נגזרות מבוססות LookML מוגדרות באמצעות LookML. בניגוד לשימוש בטבלאות נגזרות שמבוססות על SQL, כשמגדירים טבלה נגזרת מבוססת LookML ‏(NDT), משתמשים במודל LookML בדרכים הבאות:

  • אם השם של הטבלה משתנה במסד הנתונים, צריך לעדכן אותו רק פעם אחת במודל LookML. מכיוון שהטבלה הנגזרת מבוססת LookML מצביעה על אובייקט של LookML קיים שבו מוגדרת טבלת מסד הנתונים, הטבלה הנגזרת מבוססת LookML תפנה אוטומטית לטבלה המתאימה.
  • באופן דומה, אם עוברים לדיאלקט אחר, קוד ה-LookML של הטבלה הנגזרת מבוססת LookML ‏(NDT) עדיין יהיה תקף כי Looker יוצר את ה-SQL שמתאים לחיבור למסד הנתונים.

אפשר להגדיר את LookML לטבלה נגזרת מבוססת LookML ‏(NDT) באופן ידני. עם זאת, הדרך הקלה ביותר ליצור טבלה נגזרת מבוססת LookML ‏(NDT) היא לבקש מ-Looker ליצור את הטבלה הנגזרת משאילתת Explore. ב-Explore, בוחרים את השדות שרוצים להשתמש בהם בטבלה הנגזרת, ומשתמשים באפשרות קבלת LookML > טבלה נגזרת בתפריט האפשרויות (סמל גלגל השיניים) של Explore כדי לקבל את ה-LookML. ‫Looker יוצר את קוד ה-LookML שדרוש ליצירת טבלה נגזרת משאילתת הניתוח, כולל הגדרות השדות הרלוונטיות ממודל ה-LookML שדרושות ליצירת העמודות של הטבלה הנגזרת. כדי ליצור את הטבלה הנגזרת, אפשר להעתיק את קוד ה-LookML לקובץ תצוגה בפרויקט.

בדוגמה הבאה מוצגת טבלה נגזרת מבוססת LookML ‏(NDT) שמזהה את 100 המשתמשים המובילים עם סך ההוצאות הגבוה ביותר בכל ההזמנות שלהם:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

כשמריצים שאילתה על טבלה נגזרת מבוססת LookML ‏(NDT) ב-Explore, כל הפרטים של שאילתת ה-SQL עבור הטבלה הנגזרת מבוססת LookML ‏(NDT) נוצרים באופן אוטומטי. ההגדרות של העמודות בתצוגת LookML עם טבלה נגזרת מבוססת LookML ‏(NDT) נגזרות מההגדרות של ה-Explore הבסיסי, כך שאין צורך לחזור על הגדרות.

טבלאות נגזרות מתמידות (PDT)

ב-Looker, אפשר ליצור טבלאות נגזרות זמניות וטבלאות נגזרות מתמידות (PDT). אחרי שיוצרים טבלה נגזרת שמבוססת על SQL או טבלה נגזרת מבוססת LookML ‏(NDT), אפשר להוסיף התמדה כדי שמערכת Looker תכתוב את הטבלה לסכימת בסיס במסד הנתונים שלכם ותיצור אותה מחדש לפי התזמון שאתם מציינים. מידע נוסף זמין במאמר בנושא טבלאות נגזרות ב-Looker.