מושגים ב-SQL שקשורים לתצוגות

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

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

מידע נוסף על שימוש ב-SQL להגדרה ולהתאמה אישית של טבלאות נגזרות ב-LookML זמין במאמר מושגי SQL לטבלאות נגזרות.

התצוגה

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

view: users {
  sql_table_name: thelook.users ;;

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

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

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

  measure: count {
    type: count
  }
}

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

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

FROM `thelook`.`users` AS `users`

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

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

כש-Looker יוצר את ה-SQL לשליחה למסד הנתונים, הוא מחליף את הכינוי של התצוגה ב-${TABLE}. למאפיין age מהדוגמה הקודמת, Looker ייצור פסוקית SELECT כמו הבאה:

SELECT `users`.`age` AS `users.age`

מדדים הם לרוב צבירות שמבוצעות על מאפיינים. מציינים את הכינוי של המאפיין בביטוי sql של המדד. לדוגמה, למדד שמחשב את הממוצע של המאפיין age יכול להיות ביטוי sql עם הכינוי ${age}, כמו בדוגמה הבאה:

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

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

אם משנים את השם של מאפיין age, הכינוי החדש שלו מועבר לכל ההפניות לכינוי המאפיין.

התאמה אישית של קובץ תצוגה

אתם יכולים להתאים אישית את ביטויי ה-SQL של קובץ התצוגה או להשתמש בלוגיקה המובנית של LookML ב-Looker כדי לחקות את הלוגיקה של ביטוי SQL.

שימוש בביטוי SQL

נניח שאתם רוצים לחלק את נתוני הגיל לארבע קבוצות, כשהמשתמשים מתחת לגיל 18 מוגדרים כ'נוער', משתמשים בגיל 18 עד 35 מוגדרים כ'מבוגרים צעירים', משתמשים בגיל 36 עד 65 מוגדרים כ'מבוגרים' ומשתמשים בגיל 65 ומעלה מוגדרים כ'קשישים'. כדי לבצע את החלוקה הזו, צריך להגדיר מאפיין חדש – למשל, dimension: age_cohort – עם ביטוי sql שמתעד את הקבוצות האלה. הגדרת המאפיין הבאה ב-LookML משתמשת בהצהרת CASE שמתאימה לחיבור למסד נתונים של MySQL:

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

אחרי שהגדרתם את קבוצת הגיל כמאפיין, אתם יכולים להשתמש שוב בלוגיקת ה-CASE על ידי הכללת מאפיין קבוצת הגיל בשאילתות שלכם ב'חקירה'.

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

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

שימוש בלוגיקת ה-case המובנית של Looker

אפשר להשיג את אותה ההשפעה כמו של משפט SQL CASE באמצעות ביטוי שלא תלוי במסד הנתונים. הפרמטר case של LookML מאפשר להגדיר את קטגוריות הקוהורט שמורכבות מהצהרות when שמשתמשות בביטויי sql כדי לתעד תנאים ומחרוזות ספציפיים לצורך תיוג התוצאות.

בדוגמה הבאה מוצג אותו מאפיין חדש age_cohort שנכתב באמצעות הפרמטר case LookML:

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

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

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

שימוש בלוגיקה מובנית של סלים או רמות ב-Looker

שיטה נוספת לציון האופן שבו ערכים מספריים צריכים להיות מקובצים היא באמצעות סוגי הפרמטרים המובנים של Looker‏ bin או tier. הפרמטר type:bin משמש יחד עם הפרמטר bins, והפרמטר type: tier משמש יחד עם הפרמטר tiers, כדי להפריד מאפיין מספרי לקבוצה של טווחי מספרים. החיסרון הוא שאי אפשר להגדיר תוויות לכל משבצת.

בדוגמה הבאה של LookML נעשה שימוש בפרמטר bins במאפיין כדי להגדיר את הערך המינימלי בכל קבוצה:

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

אפשר להשתמש בפרמטר tiers במאפיין בדיוק באותה הדרך. לדוגמה:

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

‫Looker יוצר הצהרת SQL שנראית בערך כך:

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500