שילוב של 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 של טווח הזמן בקבוצת מאפיינים של 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.