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

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

לדוגמה, יכול להיות שהמסד נתונים שלכם כולל את הטבלאות order_items, orders ו-users. אפשר להשתמש בשאילתות איחוד (join) כדי לחקור נתונים מכל הטבלאות בו-זמנית. בדף הזה מוסבר על הצטרפות ב-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: 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. חשוב להצהיר בצורה נכונה על הקשר בין טבלאות ב-join כדי ש-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 בניתוח.

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

אפשר להצטרף לתצוגה ב-Explore דרך תצוגה אחרת. בדוגמה join parameters example, הצטרפתם מ-orders ל-order_items באמצעות השדה order_id. יכול להיות שנרצה גם לאחד את הנתונים מתצוגה שנקראת users עם הנתונים ב-order_items Explore, גם אם אין להם שדה משותף. כדי לעשות זאת, אפשר להצטרף דרך התצוגה של 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
ClickHouse 26+
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+
Dremio 26+ with Arrow Flight SQL
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
MongoSQL
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.