איך Looker יוצר SQL

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

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

צפייה בשאילתה

בLook שנשמר או בניתוח, אפשר להשתמש בכרטיסייה SQL בחלונית Data כדי לראות מה Looker שולח למסד הנתונים כדי לקבל את הנתונים. אפשר גם להשתמש בקישורים Open in SQL Runner (פתיחה ב-SQL Runner) ו-Explain in SQL Runner (הסבר ב-SQL Runner) בתחתית הכרטיסייה SQL כדי לראות את השאילתה ב-SQL Runner או כדי לראות את תוכנית ההסבר של מסד הנתונים לגבי השאילתה.

מידע נוסף על SQL Runner מופיע במאמר העקרונות הבסיסיים של SQL Runner. מידע נוסף על אופטימיזציה של שאילתה באמצעות SQL Runner זמין בפוסט איך לבצע אופטימיזציה של SQL באמצעות EXPLAIN בקהילה.

הצורה הקנונית של שאילתת Looker

שאילתות SQL ב-Looker תמיד נראות כך.

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

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

רכיבים בסיסיים של שאילתת Looker

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

‫Looker משתמש בפרמטרים הבאים כדי ליצור שאילתת SQL מלאה:

  • ‫model: השם של מודל LookML שאליו מכוונים, שמציין את מסד הנתונים שאליו מכוונים
  • ‫explore: השם של הניתוח ב-Explore שרוצים לשלוח אליו שאילתה, שמאכלס את סעיף ה-SQL‏ FROM
  • Fields: הפרמטרים dimension ו-measure שרוצים לכלול בשאילתה, שמאכלסים את פסוקית ה-SQL‏ SELECT
  • ‫filter: ביטויי סינון של Looker להחלה על אפס שדות או יותר, שמאכלסים את סעיפי ה-SQL‏ WHERE ו-HAVING
  • סדר המיון: השדה שלפיו יתבצע המיון וסדר המיון, שמאכלס את פסוקית ה-SQL‏ ORDER BY

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

מה לגבי התצוגות שצוינו בסעיפי LEFT JOIN? סעיפי JOIN מאוכלסים על סמך המבנה של מודל LookML, שמציין איך תצוגות מצטרפות ל-Explores. כשיוצרים שאילתות SQL, ‏ Looker כולל סעיפי JOIN רק כשצריך. כשמשתמשים יוצרים שאילתה ב-Looker, הם לא צריכים לציין איך הטבלאות מצטרפות זו לזו, כי המידע הזה מוצפן במודל – אחד מהיתרונות הכי משמעותיים של Looker למשתמשים עסקיים.

דוגמה לשאילתה ול-SQL שנוצר בעקבותיה

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

orders
id INT
created_at DATETIME
users_id INT
status VARCHAR(255)
traffic_source VARCHAR(15)
users
id INT
email VARCHAR(255)
first_name VARCHAR(255)
last_name VARCHAR(255)
created_at DATETIME
zip INT
country VARCHAR(255)
state VARCHAR(255)
city VARCHAR(255)
age INT
traffic_source VARCHAR(15)

נניח שאנחנו רוצים למצוא את מספר ההזמנות (ORDERS Count) שמקובצות לפי מדינה (USERS State) ומסוננות לפי תאריך יצירת ההזמנה (ORDERS Created Date) ב-Looker Explore.

בטבלת הנתונים של &#39;ניתוח נתונים&#39; מוצג מספר ההזמנות שקובצו לפי מצב המשתמש, עבור הזמנות שבוצעו ב-30 הימים האחרונים.

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

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

שימו לב לדמיון לנוסחת השאילתה הקנונית. קוד ה-SQL ב-Looker מציג מאפיינים מסוימים של קוד שנוצר על ידי מכונה (לדוגמה, COALESCE(users.state,'') AS "_g1"), אבל הוא תמיד מתאים לנוסחה.

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

הרצת SQL גולמי ב-SQL Runner של Looker

‫Looker כולל תכונה בשם SQL Runner שמאפשרת להריץ כל שאילתת SQL שרוצים על חיבורי מסד הנתונים שהגדרתם ב-Looker.

מכיוון שכל שאילתה שנוצרת על ידי Looker מובילה לפקודת SQL מלאה ופונקציונלית, אפשר להשתמש ב-SQL Runner כדי לבדוק את השאילתה או להתנסות בה.

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

בדיקת רכיבי השאילתה בכתובת ה-URL המלאה

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

כתובת ה-URL המלאה מספקת מספיק מידע כדי ליצור מחדש את השאילתה. לדוגמה, כתובת ה-URL המלאה הבאה מספקת את המידע הבא:

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
מודל e_thelook
לחקור events
שדות לשליחת שאילתה ולהצגה fields=users.state,users.count
שדה וסדר המיון sorts=users.count+desc
שדות וערכים של מסננים f[users.created_year]=2020

איך Looker יוצרת הצטרפויות

בשאילתת הדוגמה הקודמת, שימו לב שהתצוגה המשותפת orders Explore מופיעה בסעיף הראשי FROM והתצוגות המצורפות מופיעות בסעיפים LEFT JOIN. אפשר לכתוב צירופים ב-Looker בדרכים רבות ושונות, והסבר מפורט יותר מופיע בדף עבודה עם צירופים ב-LookML.

בלוקים של SQL מציינים משפטי SQL בהתאמה אישית

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

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

דוגמאות לבלוקים של 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: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

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

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

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

view: user_order_facts {
  derived_table: {
    sql:
      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
  }
}

דוגמה לבלוק SQL לסינון של ניתוח

הפרמטרים sql_always_where ו-sql_always_having LookML מאפשרים להגביל את הנתונים שזמינים לשאילתה על ידי הזרקת בלוק SQL לתנאי SQL WHERE או HAVING. בדוגמה הזו, אופרטור ההחלפה של LookML ${view_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});;
}