derived_analytic_model

Usage

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}
היררכיה
view
derived_analytic_model
ערך ברירת המחדל
ללא

כללים מיוחדים
מודלים אנליטיים נתמכים רק בחיבורים ל-BigQuery ול-Snowflake.

הגדרה

בחיבורים ל-BigQuery ול-Snowflake, הפרמטר derived_analytic_model מגדיר מודל ניתוח בתוך מסד הנתונים (גרף BigQuery או תצוגה סמנטית ב-Snowflake) שמנוהל על ידי Looker. בתרחיש הזה, Looker יוצר את המודל האנליטי במסד הנתונים על ידי הפעלת ההצהרות המתאימות של שפת הגדרת נתונים (DDL) ב-SQL שציינתם בהגדרה של פרמטר derived_analytic_model LookML. התחביר של SQL שאתם מגדירים בפרמטר derived_analytic_model צריך להיות נתמך על ידי מסד הנתונים שלכם.

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

כדי להגדיר מודל ניתוח, משתמשים באחד מפרמטרי המשנה הבאים של הפרמטר derived_analytic_model:

בנוסף, אם מגדירים את התצוגה הניתוחית באמצעות תת-הפרמטר sql, אפשר להשתמש בתת-הפרמטר publish_as_db_analytic_model של הפרמטר derived_analytic_model כדי ליצור מודל ניתוחי יציב שאפשר להריץ עליו שאילתות מחוץ ל-Looker.

אחרי שמגדירים את המודל האנליטי בתוך הפרמטר derived_analytic_model, אפשר להגדיר מימדים ומדדים של LookML שממופים למודל האנליטי. דוגמאות מופיעות בקטע דוגמאות.

sql

משתמשים בפרמטר sql אם רוצים לספק את ה-SQL רק להגדרה של המודל האנליטי, ולתת ל-Looker לנהל את היצירה של המודל האנליטי. כשמשתמשים בתת-פרמטר sql, לא צריך לכלול הצהרה של CREATE או CREATE OR REPLACE, כי Looker ייצור אוטומטית את הצהרת ה-DDL כדי ליצור את המודל האנליטי בצד מסד הנתונים.

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

sql_create

משתמשים בפרמטר sql_create כדי להגדיר הצהרת SQL מלאה ליצירת מודל ניתוח. כשמשתמשים בפרמטר sql_create, צריך לכלול הצהרת CREATE OR REPLACE (או הצהרת CREATE, אם הניב שלכם לא תומך ב-CREATE OR REPLACE).

כשמשתמשים בפרמטר המשנה sql_create, חשוב לשים לב לנקודות הבאות:

  • בחיבורים ל-BigQuery, משתמשים בהצהרת CREATE OR REPLACE כדי ליצור את המודל האנליטי.
  • משתמשים ב-${SQL_TABLE_NAME} כדי להחליף את השם המחושב של המודל האנליטי שנוצר. כך אפשר לוודא שהצהרת ה-SQL תכלול בצורה נכונה את שם המודל האנליטי שציינתם בפרמטר view של LookML.

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

create_process

משתמשים בפרמטר create_process כשצריך להגדיר כמה הצהרות SQL רציפות כדי להגדיר את המודל האנליטי. בפרמטר create_process, משתמשים בפרמטר המשנה sql_step כדי לציין את משפטי ה-SQL הבודדים. מסד הנתונים יבצע את ההצהרות של sql_step אחת בכל פעם, לפי הסדר שציינתם. ‫Looker מנפיק את משפטי ה-SQL בתת-פרמטרים sql_step כמו שהגדרתם אותם, ללא wrapper, מה שאומר שאתם צריכים לכלול שלב עם משפט CREATE OR REPLACE (או משפט CREATE, אם הדיאלקט שלכם לא תומך ב-CREATE OR REPLACE).

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

publish_as_db_analytic_model

למודלים אנליטיים נגזרים שנוצרו באמצעות הפרמטר sql, אפשר להגדיר את המודל האנליטי הנגזר באמצעות publish_as_db_analytic_model: yes כדי להנחות את Looker ליצור מודל אנליטי יציב שאפשר להריץ עליו שאילתות מחוץ ל-Looker.

המודל האנליטי היציב יפורסם (ייווצר) במחזור הבא של הגנרטור של Looker אחרי שקוד ה-LookML של המודל האנליטי הנגזר ייפרס לפרודקשן עם publish_as_db_analytic_model: yes.

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

יצירת מאפיינים ומדדים של LookML על סמך התצוגה האנליטית

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

במסמכי התיעוד של הניב שלכם מוסבר מה התחביר הנכון להגדרת המודל האנליטי ולהתייחסות לרכיבים במודל האנליטי. לדוגמה, כדי ליצור מאפיין LookML מישות של תרשים BigQuery, צריך להשתמש בקו תחתון כדי להפריד בין רכיבים כשמגדירים את ההיקף. לדוגמה, ב-BigQuery Graph, המאפיין הזה של LookML מבוסס על המאפיין location_id בטבלת הצמתים Stores:

  dimension: location_id {
    type: number
    sql: Stores_location_id ;;
  }

עם זאת, כדי ליצור מאפיין LookML שמבוסס על תצוגה סמנטית של Snowflake, צריך להשתמש בשם לא מוסמך של מדד או מאפיין.

דוגמאות

בקטעים הבאים מופיעות דוגמאות ליצירת תצוגה אנליטית באמצעות פרמטרים משניים שונים של derived_analytic_model:

יצירת מודל אנליטי נגזר באמצעות sql

בהמשך מופיע קובץ תצוגה מסוג LookML שמגדיר מודל ניתוח נתונים מבוסס-SQL למסד נתונים של BigQuery באמצעות תת-הפרמטר sql של derived_analytic_model. ‫Looker ייצור את המודל האנליטי במסד הנתונים על ידי הפעלת פקודות SQL DDL שמופיעות בפרמטר sql.

בדוגמה הזו:

  • תת-הפרמטר sql מכיל רק את ההגדרה של המודל האנליטי עצמו. אין הצהרה של CREATE, כי ב-sql Looker מטפל באופן אוטומטי בפקודות CREATE של המודל האנליטי.
  • המודל האנליטי מוגדר באמצעות publish_as_db_analytic_model: yes, ולכן Looker ייצור מודל אנליטי יציב שאפשר להריץ עליו שאילתות מחוץ ל-Looker.
view: MyWarehouseOrdersView {
  derived_analytic_model: {
    publish_as_db_analytic_model: yes

    # Defining the analytic model
    
    sql:
      NODE TABLES (
        Customers
        KEY(customer_id)
        PROPERTIES(
          country_code,
          concat(first_name, ' ', last_name) AS name,
          age,
          MEASURE(AVG(age)) AS AvgAge 
        ),
      Orders
        KEY(order_id)
        PROPERTIES (
          customer_id,
          employee_id,
          date,
          discount,
          MEASURE(AVG(discount)) AS AvgDiscount
        )

      EDGE TABLES (
        -- Relationship: Orders -> Customers
        looker_test.orders AS orders_to_users
          KEY(id)
          SOURCE KEY (order_id) REFERENCES orders (order_id)
          DESTINATION KEY (customer_id) REFERENCES Customers (customer_id)
          NO PROPERTIES
      ) ;;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: customer_id {
    type: number
    sql: Customers_customer_id ;;
  }

  dimension: customer_age {
    type: number
    sql: Customers_age ;;
  }

  measure: orders_avg_discount {
    type: number
    sql: Orders_AvgDiscount ;; 
  }
}

יצירת מודל אנליטי נגזר באמצעות create_process

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

view: university_statistics {
  derived_analytic_model: {
    create_process: {
      sql_step: 
        DROP PROPERTY GRAPH IF EXISTS ${SQL_TABLE_NAME} ;;
      sql_step: 
        CREATE PROPERTY GRAPH ${SQL_TABLE_NAME}
         NODE TABLES (
          university.College
            KEY(college_id)
            PROPERTIES(college_id, college_name),
          university.Department
            KEY(dept_id)
            PROPERTIES(dept_id, dept_name, college_id,
              budget OPTIONS(description="Department budget in USD"),
              MEASURE(SUM(budget)) AS total_budget),
          university.Course
            KEY(course_id)
            PROPERTIES(
              course_id,
              course_name,
              credits,
              dept_id,
              MEASURE(AVG(credits)) AS avg_credits,
              MEASURE(SUM(credits)) AS total_credits,
              MEASURE(COUNT(course_id)) AS course_count)
        )
        EDGE TABLES (
          university.Department AS CollegeDept
            SOURCE KEY (college_id) REFERENCES College (college_id)
            DESTINATION KEY (dept_id) REFERENCES Department (dept_id),
          university.Course AS DeptCourse
            SOURCE KEY (dept_id) REFERENCES Department (dept_id)
            DESTINATION KEY (course_id) REFERENCES Course (course_id)
        );;
    }
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: college_id {
    type: number
    sql: College_college_id ;;
  }

  dimension: course_name {
    type: string
    sql: Course_course_name ;;
  }
  
  ...
}

יצירת מודל אנליטי נגזר באמצעות sql_create

בהמשך מופיע קובץ תצוגה מסוג LookML שמגדיר מודל ניתוח נתונים מבוסס-SQL למסד נתונים של BigQuery באמצעות תת-הפרמטר sql_create של derived_analytic_model. בדוגמה הזו, הפרמטר sql_create מגדיר את ההצהרה המלאה CREATE OR REPLACE שצריך להריץ כדי ליצור את המודל האנליטי בשלב אחד.

view: MyWarehouseOrdersView {
  derived_analytic_model: {
    sql_create:
      CREATE OR REPLACE PROPERTY GRAPH ${SQL_TABLE_NAME}
        NODE TABLES(
          accounting.Loan AS Loan
            KEY(loanId)
            LABEL Loan PROPERTIES(
              loanId,
              loanAmount,
              balance,
              createTime,
              interestRate,
              accountId,
              balance + 100 AS derived_balance,
              CASE WHEN balance > 1000 THEN "High" ELSE "Low" END AS risk_level,
              CONCAT("ID-", CAST(loanId AS STRING)) AS full_id,
              DATE(2024, 1, 1) AS fixed_date,
              MEASURE(AVG(interestRate)) AS avg_interest_rate
            ),
          accounting.AccountView AS Account
            KEY(accountId)
            LABEL Account PROPERTIES(
              accountId,
              createTime,
              isBlocked,
              accountType,
              amount,
              ownerId,
              MEASURE(MIN(createTime)) AS oldest_account_create_time,
              MEASURE(MAX(createTime)) AS newest_account_create_time,
              MEASURE(AVG(amount)) AS avg_account_amount,
              MEASURE(SUM(amount)) AS total_account_amount,
              MEASURE(COUNT(DISTINCT accountType)) AS account_type_count
            ),
          accounting.PersonMV AS Person
            KEY(personId)
            LABEL Person PROPERTIES(
              personId,
              personName,
              age,
              age_tier,
              MEASURE(AVG(age)) AS avg_age,
              MEASURE(COUNT(DISTINCT age_tier)) AS age_tier_count
            )
        )
        EDGE TABLES(
          accounting.Loan AS Account_Repay_Loan
            KEY(loanId)
            SOURCE KEY(loanId) REFERENCES Loan(loanId)
            DESTINATION KEY(accountId) REFERENCES Account(accountId)
            LABEL Repay NO PROPERTIES,
          accounting.Account AS Person_Own_Account
            KEY(accountId)
            SOURCE KEY(accountId) REFERENCES Account(accountId)
            DESTINATION KEY(ownerId) REFERENCES Person(personId)
            LABEL Own NO PROPERTIES
        );;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: loan_id {
    type: number
    sql: Loan_loanId ;;
  }

  dimension: account_ID {
    type: number
    sql: Account_accountID ;;
  }

  ...

}

גישה למודל האנליטי היציב

אם יצרתם את המודל האנליטי הנגזר באמצעות פרמטר המשנה sql והוספתם את ההצהרה publish_as_db_analytic_model: yes מתחת לפרמטר derived_analytic_model, מערכת Looker תפרסם (תצור) את המודל האנליטי היציב במחזור הבא של Looker regenerator אחרי שקובץ ה-LookML של המודל האנליטי הנגזר ייפרסם בייצור עם publish_as_db_analytic_model: yes.

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

חלון העזר עם פרטי PDT

אם אתם אדמינים או משתמשים עם הרשאה see_pdts, אתם יכולים לבצע את השלבים הבאים כדי להשתמש בדף Persistent Derived Tables בקטע Admin ב-Looker כדי לקבל את השם של מודל אנליטי יציב:

  1. לוחצים על סמל התפריט הראשי של Looker ובוחרים באפשרות אדמין, אם התפריט אדמין לא מוצג. (אם אתם נמצאים בקטע Explore או Develop בתפריט הראשי של Looker, יכול להיות שתצטרכו ללחוץ על החץ 'חזרה' כדי לראות את התפריט Admin).
  2. בתפריט Admin (ניהול), בוחרים באפשרות Persistent Derived Tables (טבלאות נגזרות קבועות).
  3. בדף Persistent Derived Tables (טבלאות נגזרות קבועות), מחפשים את השם של המודל האנליטי.
  4. לוחצים על תפריט שלוש הנקודות של המודל האנליטי ובוחרים באפשרות פרטי PDT.
  5. בתיבת הדו-שיח PDT Details, מחפשים את השדה Stable Name.

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

SELECT * from tmp.PA_bq_analytic_model_sales_semantic_view

הכרטיסייה SQL בדף 'אפשרויות נוספות'

אם אין לכם גישה לדף האדמין Persistent Derived Tables (טבלאות נגזרות קבועות), תוכלו לקבוע את השם היציב מהמידע שמופיע בכרטיסייה SQL בקטע Data (נתונים) של שאילתת ניתוח בכלי הניתוחים. כדי לקבל את השם היציב של מודל ניתוח:

  1. פותחים את הניתוח של התצוגה של המודל האנליטי.

  2. בניתוח, בוחרים מאפיינים או מדדים מבוחר השדות.

  3. לוחצים על הכרטיסייה SQL בקטע נתונים.

  4. בכרטיסייה SQL, מאתרים אחת מהצהרות ה-SQL הבאות:

    • ל-BigQuery Graph:
      • CREATE PROPERTY GRAPH
      • SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
    • לתצוגה סמנטית של Snowflake:
      • CREATE SEMANTIC VIEW
      • SELECT ... FROM SEMANTIC_VIEW_NAME

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

    SCRATCH_SCHEMA_NAME.CONNECTION_REGISTRATION_KEY_MODEL_NAME_VIEW_NAME
    • ‫SCRATCH_SCHEMA_NAME: השם של סכימת בסיס הוא תחילת המחרוזת אחרי ההצהרה CREATE או SELECT, לפני '.'
    • ‫CONNECTION_REGISTRATION_KEY: מפתח הרישום של החיבור הוא מחרוזת של שני תווים. בהתאם לדיאלקט של מסד הנתונים, הוא יופיע אחרי סימן הדולר או אחרי הקו התחתון הראשון בשם הטבלה בהצהרת CREATE או SELECT.
    • ‫MODEL_NAME: השם של מודל LookML.
    • VIEW_NAME: השם של התצוגה שבה מוגדר המודל האנליטי.

לדוגמה, הנה הטקסט מהכרטיסייה SQL של שאילתת ניתוח לחיבור BigQuery. המודל האנליטי מוגדר בתצוגה שנקראת sales_analytic_model, והשם של מודל LookML הוא thelook. במקרה הזה, Looker כבר יצר את המודל האנליטי, ולכן אין הצהרה של CREATE. אבל ההצהרה SELECT ... FROM GRAPH_EXPAND מכילה את פרטי שם הטבלה:

-- use existing sales_analytic_model in `looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model`
SELECT
    sales_analytic_model.orders_id  AS sales_analytic_model_orders_id,
    AGG(sales_analytic_model.orders_count_orders ) AS sales_analytic_model_count_orders
FROM GRAPH_EXPAND("looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model") AS sales_analytic_model
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

אלה הערכים שצריך להשתמש בהם כדי לגזור את השם היציב של המודל האנליטי:

  • הערך של SCRATCH_SCHEMA_NAME הוא looker-test-db.looker_scratch
  • הערך של CONNECTION_REGISTRATION_KEY הוא J7
  • הערך של MODEL_NAME הוא thelook
  • הערך של VIEW_NAME הוא sales_analytic_model

לכן, השם היציב של המודל האנליטי הוא:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

אחרי שמקבלים את השם היציב של המודל האנליטי, אפשר לשלוח שאילתה ישירות למודל האנליטי.

דברים שכדאי לקחת בחשבון

כשמשתמשים במודלים אנליטיים בתוך מסד נתונים, חשוב לזכור את הנקודות הבאות:

  • סוגי נתונים: יש תמיכה רק בסוגי הנתונים הבאים של מאפיינים ומדדים במודלים אנליטיים:

    • נתמך עבור מאפיינים ומדדים:
      • string
      • number
      • date
      • yesno
    • נתמך רק במאפיינים:
      • time
      • date_time

  • מדדים:

    • צריך להגדיר מראש את המדדים הבסיסיים: צריך להגדיר מראש את המדדים הבסיסיים במודל הניתוח של מסד הנתונים הבסיסי. ‫Looker לא יכול להגדיר מדד בסיסי חדש על ידי ביצוע צבירה (כמו type: sum או type: count) על מאפיין ממודל ניתוח.

    • יש תמיכה במדדים שמבוססים על מדדים אחרים: אפשר להשתמש בפרמטר sql של מדד LookML כדי לבצע חישובים לא מצטברים שמשתמשים במדדי בסיס מוגדרים מראש מהמודל האנליטי. כשיוצרים מדד שמבוסס על מדדים אחרים, אי אפשר להגדיר את המדד החדש כסוג מדד מצטבר כמו sum או count. צריך להגדיר את המדד החדש כסוג מדד לא מצטבר, כמו string,‏ number,‏ date או yesno. מקרה לדוגמה:

      measure: average_order_amount {
  type: number
  sql: ROUND(${total_order_amount} / NULLIF(${count_orders}, 0), 2) ;;
}

  • צירופים: בניתוח שהתצוגה הבסיסית שלו מבוססת על מודל אנליטי, אי אפשר לכלול צירופים. באופן דומה, אי אפשר לצרף תצוגה שמבוססת על מודל ניתוח לניתוח ב'הצגה כניתוח' שמבוסס על תצוגת בסיס רגילה של LookML.

  • צירופים מרומזים: תכונות שמסתמכות על צירופים מרומזים לא נתמכות במודלים אנליטיים. דוגמאות לתכונות שמסתמכות על הצטרפות מרומזת הן יומנים מותאמים אישית ושדות שמוגדרים עם type: location,‏ type: distance או type: zipcode.

  • התכונות הבאות לא נתמכות במודלים אנליטיים: