עבודה עם הצטרפויות ב-LookML

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

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

הצטרפות מתחילה ב-Explore

ההצטרפויות מוגדרות בקובץ המודל כדי ליצור את הקשר בין Explore לבין view. הצטרפות מחברת תצוגה מפורטת אחת או יותר ל-Explore יחיד, באופן ישיר או דרך תצוגה מפורטת אחרת שהצטרפה.

נניח שיש שתי טבלאות במסד נתונים: order_items ו-orders. אחרי שיוצרים תצוגות לשתי הטבלאות, צריך להצהיר על אחת או יותר מהן בפרמטר explore בקובץ המודל:

explore: order_items { ... }

כשמריצים שאילתה מתוך order_items Explore, ‏order_items מופיע בסעיף FROM של ה-SQL שנוצר:

SELECT ...
FROM order_items

אפשר להוסיף מידע נוסף לניתוח order_items. לדוגמה, אפשר להשתמש בדוגמת ה-LookML הבאה כדי לצרף את התצוגה orders ל-Explore‏ order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

קוד ה-LookML שמוצג למעלה מבצע שני דברים. קודם כל, אפשר לראות שדות מ-orders ומ-order_items בבורר השדות של כלי הניתוחים:

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

שנית, קובץ ה-LookML מתאר איך לבצע הצטרפות בין orders לבין order_items. קוד LookML הזה יתורגם ל-SQL הבא:

SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id

בקטעים הבאים מוסבר על הפרמטרים האלה של LookML בפירוט רב יותר.

פרמטרים להצטרפות

יש ארבעה פרמטרים עיקריים שמשמשים להצטרפות: join,‏ type,‏ relationship ו-sql_on.

שלב 1: הפעלת הניתוח

קודם כול, יוצרים את order_items Explore:

explore: order_items { ... }

שלב 2: join

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

לאחר מכן, משתמשים בפרמטר join כדי להצהיר שרוצים לצרף את התצוגה orders לניתוח order_items:

explore: order_items {
  join: orders { ... }
}

שלב 3: type

כדאי לחשוב איזה סוג של הצטרפות רוצים לבצע. ‫Looker תומך ב-LEFT JOIN,‏ INNER JOIN,‏ FULL OUTER JOIN ו-CROSS JOIN. הערכים האלה תואמים לערכי הפרמטר type‏ left_outer,‏ inner,‏ full_outer ו-cross.

explore: order_items {
  join: orders {
    type: left_outer
  }
}

ערך ברירת המחדל של type הוא left_outer.

שלב 4: relationship

מגדירים קשר בין ה-Explore‏ order_items לבין התצוגה orders. חשוב להגדיר בצורה נכונה את הקשר בין הטבלאות בצירוף כדי ש-Looker יוכל לחשב מדדים מדויקים. קשר הגומלין מוגדר מ התצוגה order_items Explore אל התצוגה orders. האפשרויות האפשריות הן one_to_one,‏ many_to_one,‏ one_to_many ו-many_to_many.

בדוגמה הזו, יכולים להיות הרבה פריטים בהזמנה אחת. הקשר בין ה-Explore‏ order_items לבין התצוגה orders הוא many_to_one:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
  }
}

אם לא כוללים פרמטר relationship בצירוף, Looker משתמש בברירת המחדל many_to_one.

טיפים נוספים להגדרת הפרמטר relationship בצורה נכונה לצירוף מופיעים במאמר הגדרת הפרמטר relationship בצורה נכונה.

שלב 5: sql_on

מצהירים איך לצרף את הטבלה order_items ואת הטבלה orders באמצעות הפרמטר sql_on או הפרמטר foreign_key.

הפרמטר sql_on מקביל לתנאי ON בשאילתת ה-SQL שנוצרת. בעזרת הפרמטר הזה, אפשר להצהיר אילו שדות צריכים להיות זהים כדי לבצע את הצירוף:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

אפשר גם לכתוב הצטרפויות מורכבות יותר. לדוגמה, יכול להיות שתרצו לצרף רק הזמנות עם id גדול מ-1,000:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
  }
}

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

שלב 6: בדיקה

כדי לבדוק שההצטרפות הזו פועלת כמו שצריך, עוברים ל-Explore Order Items (פריטי הזמנה). אמורים להופיע שדות מ-order_items ומ-orders.

במאמר בדיקת השדות בכלי הניתוח מוסבר איך לבדוק שינויים ב-LookML בכלי הניתוח.

הצטרפות דרך תצוגה אחרת

אפשר להצטרף לתצוגה מפורטת בניתוח באמצעות תצוגה מפורטת אחרת. בדוגמה join parameters example, הצטרפתם מ-orders אל order_items באמצעות השדה order_id. יכול להיות שנרצה גם לצרף את הנתונים מתצוגה שנקראת users לכרטיסייה order_items ניתוח, גם אם אין להם שדה משותף. כדי לעשות זאת, אפשר להצטרף דרך התצוגה של orders.

כדי להצטרף לתצוגה מפורטת users לתצוגה מפורטת orders, במקום ל-Explore order_items, צריך להשתמש בפרמטר sql_on או בפרמטר foreign_key. כדי לעשות את זה, צריך להגדיר את ההיקף של השדה מ-orders ל-orders.user_id.

דוגמה לשימוש בפרמטר sql_on:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

הצטרפות לתצוגה יותר מפעם אחת

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

הפרמטר from מאפשר לכם לציין איזו תצוגה להשתמש בה בצירוף, וגם לתת לצירוף שם ייחודי. לדוגמה:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: buyers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.buyer_id} = ${buyers.id} ;;
  }
  join: sellers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.seller_id} = ${sellers.id} ;;
  }
}

במקרה הזה, רק נתוני הקונה מצורפים כ-buyers, ורק נתוני המוכר מצורפים כ-sellers.

הערה: עכשיו צריך להתייחס לתצוגה המפורטת users באמצעות השמות שמוגדרים לה ככינויים buyers ו-sellers בפעולת הצירוף.

הגבלת שדות מחיבור

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

לדוגמה, כשמצטרפים לטבלה orders לטבלה order_items, יכול להיות שתרצו להעביר רק את השדות shipping ו-tax דרך ההצטרפות:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [shipping, tax]
  }
}

אפשר גם להפנות לקבוצה של שדות, כמו [set_a*]. כל קבוצה מוגדרת בתצוגה מפורטת באמצעות הפרמטר set. נניח שהגדרתם את קבוצת ההגדרות הבאה בתצוגה המפורטת orders:

set: orders_set {
  fields: [created_date, shipping, tax]
}

אתם יכולים לבחור להעביר רק את שלושת השדות האלה כשמצטרפים מ-orders אל order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [orders_set*]
  }
}

צבירה סימטרית

‫Looker משתמש בתכונה שנקראת 'צבירות סימטריות' כדי לחשב צבירות (כמו סכומים וממוצעים) בצורה נכונה, גם כשצירופים מובילים לפיצול. מידע נוסף על צבירה סימטרית זמין במאמר הסבר על צבירה סימטרית. הבעיה של פיצול נתונים (fanout) שפונקציות צבירה סימטריות פותרות מוסברת בפוסט לקהילה The problem of SQL fanouts (הבעיה של פיצול נתונים ב-SQL).

חובה להשתמש במקשים במקלדת הראשית

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

כדי לעשות זאת, מוסיפים את הפרמטר primary_key להגדרת השדה של המפתח הראשי בכל תצוגה:

dimension: id {
  type: number
  primary_key: yes
}

דיאלקטים נתמכים של SQL

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

דיאלקט האם יש תמיכה?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica

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

מידע נוסף על צירופים

מידע נוסף על פרמטרים של צירוף ב-LookML זמין במאמר Join reference.