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 שצוינו בהגדרה של פרמטר LookML‏ derived_analytic_model. התחביר של 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 Graph, צריך להשתמש בקו תחתון כדי להפריד בין רכיבים כשמגדירים את ההיקף. לדוגמה, ב-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.

אחרי שמפרסמים את המודל האנליטי היציב, אפשר לשלוח לו שאילתות ישירות באמצעות השם היציב שלו. אפשר לקבוע את השם היציב מהמידע שמופיע בכרטיסייה 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.

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