שילוב של SQL והפניה לאובייקטים של LookML

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

אופרטור החלפה ($)

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

יש כמה דרכים להשתמש באופרטור ההחלפה:

‫${TABLE}.column_name מפנה לעמודה בטבלה שמקושרת לתצוגה שאתם עובדים עליה. לדוגמה:

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

‫${field_name} מתייחס למאפיין או למדד בתצוגה שאתם עובדים עליה. לדוגמה:

measure: total_population {
  type: sum
  sql: ${population} ;;
}

‫${view_name.field_name} מפנה למאפיין או למדד מתצוגה אחרת. לדוגמה:

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

‫${view_name.SQL_TABLE_NAME} מפנה לתצוגה אחרת או לטבלה נגזרת. הערה: SQL_TABLE_NAME בהפניה הזו היא מחרוזת מילולית, ולא צריך להחליף אותה בשום דבר. לדוגמה:

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}

‫${view_name.SQL_TABLE_NAME} לא פועל עם הפרמטר sql_trigger שמשמש עם datagroups.

היקף ומתן שמות

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

לשדות ולערכות LookML יש שמות מלאים ושמות מקוצרים:

• השמות המלאים הם מהצורה <view>.<field-name | set-name>. בצד ימין מצוין ההיקף, שהוא התצוגה שמכילה את השדה או את הקבוצה. בצד שמאל מצוין השדה או השם של קבוצת השדות.
• שמות מקוצרים מופיעים בתבנית <field-name | set-name>, ללא נקודה להפרדה. ‫Looker מרחיב שמות מקוצרים לשמות מלאים באמצעות ההקשר שבו הם נמצאים.

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

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

בהצהרה dimension: customer_address, שימו לב שהתצוגה הבסיסית של בלוק ה-SQL ‏ (customer) שונה מהיקף התצוגה המקיף (orders). זה יכול להיות שימושי כשצריך להשוות שדות בין שתי תצוגות שונות.

כשצפייה (נקרא לה 'צפייה א') מתייחסת לשדה שמוגדר בצפייה אחרת (נקרא לה 'צפייה ב'), חשוב לזכור כמה דברים:

1. קובץ התצוגה B צריך להיכלל באותו מודל כמו תצוגה A, באמצעות הפרמטר include.
2. צריך לצרף את תצוגה B לתצוגה A ב-Explore אחד או יותר. מידע נוסף על הצטרפות זמין בדף עבודה עם הצטרפויות ב-LookML.

דיאלקט SQL

‫Looker תומך בסוגים רבים של מסדי נתונים, כמו MySQL,‏ Postgres,‏ Redshift ו-BigQuery. כל מסד נתונים תומך בקבוצת תכונות שונה במקצת עם שמות פונקציות שונים, שנקראת דיאלקט SQL.

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

בלוקים של SQL

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

פרמטרים של LookML שמתחילים ב-sql_ מצפים לביטוי SQL כלשהו. דוגמאות: sql_always_where,‏ sql_on ו-sql_table_name. הפרמטר הכי נפוץ ב-LookML לבלוקים של SQL הוא sql, שמשמש בהגדרות של שדות מאפיינים ומדדים כדי לציין את ביטוי ה-SQL שמגדיר את המאפיין או המדד.

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

דוגמאות לבלוקים של SQL למאפיינים ולמדדים

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

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;   # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

כפי שמוצג בשני המאפיינים האחרונים, בלוקים של SQL יכולים להשתמש בפונקציות שנתמכות על ידי מסד הנתונים הבסיסי (כמו הפונקציות CONCAT ו-DATEDIFF של MySQL בדוגמה הזו).

דוגמה לבלוק SQL עם שאילתת משנה מתואמת

אפשר להציב כל הצהרת SQL בבלוק ה-SQL של שדה, כולל הצהרת משנה מתואמת. לדוגמה:

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

דוגמה לבלוק SQL לטבלאות נגזרות

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

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }
  # later, dimension declarations reference the derived column(s)

  dimension: lifetime_orders {
    type: number
  }
}

הפניות לסוגי שדות ב-LookML

כשמפנים לשדה LookML קיים בתוך שדה אחר, אפשר להנחות את Looker להתייחס לשדה שאליו מפנים כאל סוג נתונים ספציפי. לשם כך, משתמשים בנקודתיים כפולות (::) ואחריהן בסוג הנתונים הרצוי. לדוגמה, אם מפנים למאפיין orders.created_date בשדה אחר, אפשר להשתמש בתחביר ${orders.created_date::date} כדי לוודא שהשדה created_date יטופל כשדה תאריך ב-SQL שנוצר על ידי Looker, ולא יומר למחרוזת.

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

• בהפניה לשדה מחרוזת, אפשר להשתמש ב-::string.
• בהפניה לשדה מספרים, אפשר להשתמש ב-::string וב-::number.
• כשמפנים לשדה של תאריך או שעה, אפשר להשתמש ב-::string, ב-::date וב-::datetime.
  הפניות באמצעות ::string ו-::date מחזירות נתונים באזור הזמן של השאילתה, בעוד שהפניות באמצעות ::datetime מחזירות נתונים באזור הזמן של מסד הנתונים.
• כשמפנים לשדה yesno, אפשר להשתמש ב-::string, ב-::number וב-::boolean.
  הפניות לשדות באמצעות הסוג ::boolean לא זמינות בניבים של מסדי נתונים שלא תומכים בסוג הנתונים הבוליאני.
• בהפניה לשדה מיקום, אפשר להשתמש ב-::latitude וב-::longitude.

שימוש בהפניות לסוגי שדות LookML עם שדות תאריך

לדוגמה, נניח שיש לכם מאפיין enrollment_month ומאפיין graduation_month, שניהם נוצרו בקבוצות מאפיינים של type: time. בדוגמה הזו, המאפיין enrollment_month נוצר על ידי קבוצת המאפיינים הבאה של type: time:

dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

באופן דומה, המאפיין graduation_month נוצר על ידי קבוצת המאפיינים הבאה של type: time:

dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

באמצעות המאפיינים enrollment_month ו-graduation_month, אפשר לחשב כמה חודשים או שנים עברו בין ההרשמה של התלמיד לבין סיום הלימודים שלו. לשם כך, יוצרים קבוצת מאפיינים של type: duration. עם זאת, מכיוון שחלק משדות התאריך מוגדרים כמחרוזות ב-SQL שנוצר על ידי Looker, הגדרת המאפיינים enrollment_month ו-graduation_month כערכים של sql_start ו-sql_end עלולה לגרום לשגיאה.

כדי למנוע שגיאה כתוצאה מהמרת שדות הזמן האלה למחרוזות, אפשרות אחת היא ליצור קבוצת מאפיינים של type: duration, עם הפניות לפרקי הזמן raw מקבוצות המאפיינים enrollment ו-graduation בפרמטרים sql_start ו-sql_end:

dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

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

חלופה פשוטה יותר לשימוש בפרמטר raw timeframe בקבוצת מאפיינים של type: duration היא לציין את סוג ההפניה ::date או ::datetime בשדות שאליהם מתבצעת הפניה בפרמטרים sql_start ו-sql_end.

dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

ה-LookML בדוגמה הזו גם יוצר קבוצת מאפיינים של משך ההרשמה, אבל השימוש בהפניה ::date מאפשר להשתמש במאפיינים enrollment_month ו-graduation_month בלי להשתמש בטווח זמן raw או להמיר אותם למחרוזות באמצעות SQL.

דוגמה נוספת לשימוש בהפניות לסוגי שדות ב-LookML כדי ליצור קבוצות של מאפיינים מותאמים אישית של type: duration מופיעה בדף התיעוד של הפרמטר dimension_group.

התחביר הזה לא זמין למדדים של type: list, שאי אפשר להפנות אליהם החל מ-Looker 6.8.

קבועים ב-LookML

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

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

constant: city {
  value: "Okayama"
}

אחר כך אפשר להפנות לקבוע city בכל מקום בפרויקט באמצעות התחביר @{city}. לדוגמה, אפשר להשתמש בקבוע city עם הפרמטר label בפונקציה users Explore:

explore: users {
  label: "@{city} Users"
}

‫Looker מציג את Okayama Users בתפריט Explore ובכותרת של ה-Explore, במקום Users שמוצג כברירת מחדל.

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