טבלאות נגזרות ב-Looker

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

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

אחרי זה אפשר לעבוד עם הטבלה הנגזרת customer_order_summary כאילו הייתה כל טבלה אחרת במסד הנתונים.

תרחישי שימוש פופולריים בטבלאות נגזרות מפורטים במאמר ספרי מתכונים של Looker: שימוש מיטבי בטבלאות נגזרות ב-Looker.

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

כדי ליצור טבלה נגזרת בפרויקט Looker, משתמשים בפרמטר derived_table מתחת לפרמטר view. בתוך הפרמטר derived_table, אפשר להגדיר את השאילתה לטבלת הנגזרת באחת משתי דרכים:

• בטבלה נגזרת מבוססת LookML‏, מגדירים את הטבלה הנגזרת באמצעות שאילתה מבוססת LookML.
• במקרה של טבלה נגזרת שמבוססת על SQL, מגדירים את הטבלה הנגזרת באמצעות שאילתת SQL.

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

• הטבלה הנגזרת מבוססת LookML מגדירה את השאילתה באמצעות LookML בפרמטר explore_source. בדוגמה הזו, השאילתה מבוססת על תצוגת orders קיימת, שמוגדרת בקובץ נפרד שלא מוצג בדוגמה הזו. השאילתה explore_source בטבלה הנגזרת מבוססת LookML ‏(NDT) מביאה את השדות customer_id, first_order ו-total_amount מקובץ התצוגה orders.
• הטבלה הנגזרת שמבוססת על SQL מגדירה את השאילתה באמצעות SQL בפרמטר sql. בדוגמה הזו, שאילתת ה-SQL היא שאילתה ישירה של הטבלה orders במסד הנתונים.

view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      column: customer_id {
        field: orders.customer_id
      }
      column: first_order {
        field: orders.first_order
      }
      column: total_amount {
        field: orders.total_amount
      }
    }
  }
  dimension: customer_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }
  dimension_group: first_order {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.first_order ;;
  }
  dimension: total_amount {
    type: number
    value_format: "0.00"
    sql: ${TABLE}.total_amount ;;
  }
}

view: customer_order_summary {
  derived_table: {
    sql:
      SELECT
        customer_id,
        MIN(DATE(time)) AS first_order,
        SUM(amount) AS total_amount
      FROM
        orders
      GROUP BY
        customer_id ;;
  }
  dimension: customer_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }
  dimension_group: first_order {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.first_order ;;
  }
  dimension: total_amount {
    type: number
    value_format: "0.00"
    sql: ${TABLE}.total_amount ;;
  }
}

שתי הגרסאות יוצרות תצוגה בשם customer_order_summary שמבוססת על הטבלה orders, עם העמודות customer_id, first_order, ו-total_amount.

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

אחרי שמגדירים את הטבלה הנגזרת, אפשר להשתמש בה כמו בכל טבלה אחרת במסד הנתונים.

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

טבלאות נגזרות מקוריות מבוססות על שאילתות שאתם מגדירים באמצעות מונחי LookML. כדי ליצור טבלה נגזרת מבוססת LookML ‏(NDT), משתמשים בפרמטר explore_source בתוך הפרמטר derived_table של הפרמטר view. כדי ליצור את העמודות של הטבלה הנגזרת מבוססת LookML ‏(NDT), צריך להפנות למאפייני LookML או למדדים במודל. אפשר לראות את הקובץ של תצוגת הטבלה הנגזרת המקורית בדוגמה הקודמת.

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

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

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

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

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

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

במקרים מסוימים, לא ניתן להשתמש בפרמטר sql. במקרים כאלה, Looker תומך בפרמטרים הבאים להגדרת שאילתת SQL עבור טבלאות נגזרות מתמידות (PDT):

• ‫create_process: כשמשתמשים בפרמטר sql עבור PDT, ‏ Looker עוטף ברקע את CREATE TABLE ההצהרה של שפת הגדרת הנתונים (DDL) של הניב סביב השאילתה כדי ליצור את ה-PDT מהשאילתה ב-SQL. חלק מהניבים לא תומכים בהצהרת SQL‏ CREATE TABLE בשלב אחד. בניבים האלה, אי אפשר ליצור PDT עם הפרמטר sql. במקום זאת, אפשר להשתמש בפרמטר create_process כדי ליצור PDT בכמה שלבים. מידע ודוגמאות מופיעים בדף התיעוד של הפרמטר create_process.
• sql_create: אם תרחיש השימוש שלכם דורש פקודות DDL בהתאמה אישית, והדיאלקט שלכם תומך ב-DDL (לדוגמה, BigQuery ML של Google לחיזוי), אתם יכולים להשתמש בפרמטר sql_create כדי ליצור PDT במקום להשתמש בפרמטר sql. מידע ודוגמאות מופיעים בדף התיעוד בנושא sql_create.

בין אם משתמשים בפרמטר sql, create_process או sql_create, בכל המקרים האלה מגדירים את הטבלה הנגזרת באמצעות שאילתת SQL, ולכן כל אלה נחשבות לטבלאות נגזרות שמבוססות על SQL.

כשמגדירים טבלה נגזרת שמבוססת על SQL, חשוב לתת לכל עמודה כינוי ברור באמצעות AS. הסיבה לכך היא שתצטרכו להפנות לשמות העמודות של קבוצת התוצאות במאפיינים, כמו ${TABLE}.first_order. לכן, בדוגמה הקודמת נעשה שימוש ב-MIN(DATE(time)) AS first_order במקום רק ב-MIN(DATE(time)).

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

בנוסף להבחנה בין טבלאות נגזרות מבוססות LookML לבין טבלאות נגזרות מבוססות SQL, יש גם הבחנה בין טבלה נגזרת זמנית – שלא נכתבת למסד הנתונים – לבין טבלה נגזרת מתמידה (PDT) – שנכתבת לסכימה במסד הנתונים.

טבלאות נגזרות מבוססות LookML וטבלאות נגזרות מבוססות SQL יכולות להיות זמניות או מתמידות.

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

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

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

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

ניבי SQL נתמכים של מסדי נתונים לטבלאות נגזרות זמניות

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

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

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

טבלת PDT יכולה להיות טבלה נגזרת מבוססת LookML או טבלה נגזרת שמבוססת על SQL.

דרישות ל-PDT

כדי להשתמש בטבלאות נגזרות קבועות (PDT) בפרויקט Looker, צריך:

• ניב של מסד נתונים שתומך ב-PDT. בהמשך הדף מופיע הקטע דיאלקטים נתמכים של מסדי נתונים ל-PDT, שבו מפורטות רשימות של דיאלקטים שתומכים בטבלאות נגזרות מתמידות שמבוססות על SQL ובטבלאות נגזרות מתמידות מקוריות.

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

• חיבור Looker שמוגדר עם המתג הפעלת PDT במצב מופעל. ההגדרה Enable PDTs (הפעלת PDT) מוגדרת בדרך כלל כשמגדירים את החיבור ל-Looker בפעם הראשונה (הוראות להגדרת הדיאלקט של מסד הנתונים מופיעות בדף התיעוד בנושא דיאלקטים של Looker), אבל אפשר גם להפעיל PDT לחיבור אחרי ההגדרה הראשונית.

ניבים נתמכים של מסדי נתונים ל-PDT

כדי ש-Looker יתמוך ב-PDT בפרויקט Looker, הניב של מסד הנתונים צריך לתמוך בהם גם כן.

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

בטבלה הבאה מפורטים הניבים שתומכים בטבלאות נגזרות מתמידות שמבוססות על SQL בגרסה האחרונה של Looker: