您正在查看 Looker 26.8 的文档。点击此链接可查看最新文档。

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

derived_analytic_model

用法

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}

层次结构

查看文件

view

derived_analytic_model

默认值

无

特殊规则

分析模型仅支持 BigQuery 和 Snowflake 连接。

定义

对于 BigQuery 和 Snowflake 连接，derived_analytic_model 参数用于定义由 Looker 管理的数据库内分析模型（BigQuery 图或 Snowflake 中的语义视图）。在这种情况下，Looker 会通过执行您在 derived_analytic_model LookML 参数的定义中指定的相应 SQL 数据定义语言 (DDL) 语句，在数据库中生成分析模型。您在 derived_analytic_model 参数中定义的 SQL 语法必须受数据库支持。

与派生表不同，Looker 管理的分析模型对象不会在数据库中保留任何数据，也不会以增量方式刷新。相反，它们表示直接在数据库中定义关系和度量的语义模型。

如需定义分析模型，请使用 derived_analytic_model 参数的以下子参数之一：

sql
sql_create
create_process

此外，如果您使用 sql 子参数定义分析视图，则可以使用 derived_analytic_model 参数的 publish_as_db_analytic_model 子参数创建一个稳定的分析模型，该模型可在 Looker 之外进行查询。

在 derived_analytic_model 参数中定义分析模型后，您可以定义映射到分析模型的 LookML 维度和度量。如需查看示例，请参阅示例部分。

`sql`

如果您只想提供分析模型的定义 SQL，并让 Looker 管理分析模型的创建，请使用 sql 参数。使用 sql 子参数时，请勿添加 CREATE 或 CREATE OR REPLACE 语句，因为 Looker 会自动生成 DDL 语句，以在数据库端创建分析模型。

如需查看使用 sql 参数在数据库中创建分析模型的示例，请参阅使用 sql 创建派生分析模型。

`sql_create`

使用 sql_create 参数定义用于创建分析模型的完整 SQL 语句。使用 sql_create 参数时，您需要添加 CREATE OR REPLACE 语句（如果您的方言不支持 CREATE OR REPLACE，则添加 CREATE 语句）。

使用 sql_create 子参数时，请注意以下事项：

对于 BigQuery 连接，请使用 CREATE OR REPLACE 语句创建分析模型。
使用 ${SQL_TABLE_NAME} 替换正在创建的分析模型的计算名称。这样可确保 SQL 语句正确包含您在 LookML view 参数中提供的分析模型名称。

如需查看使用 sql_create 参数在数据库中创建分析模型的示例，请参阅使用 sql_create 创建派生分析模型。

`create_process`

当您需要定义多个连续的 SQL 语句来定义分析模型时，请使用 create_process 参数。在 create_process 参数下，使用 sql_step 子参数指定各个 SQL 语句。数据库将按照您指定的顺序逐个执行 sql_step 语句。Looker 会按您定义的顺序发出 sql_step 子参数中的 SQL 语句，且不添加任何封装，这意味着您必须添加一个包含 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 外部查询的稳定分析模型。

在派生分析模型的 LookML 部署到生产环境并带有 publish_as_db_analytic_model: yes 后，稳定分析模型将在下一个 Looker 再生器周期中发布（创建）。

如需了解如何获取稳定分析模型的名称，以便在 Looker 之外使用该名称查询稳定分析模型，请参阅访问稳定分析模型部分。

根据分析视图创建 LookML 维度和测量

定义分析模型后，您可以在同一视图文件中定义基于该分析模型的 LookML 维度和度量。

如需了解用于定义分析模型和引用分析模型中元素的正确语法，请参阅相应方言的文档。例如，如需从 BigQuery 图实体创建 LookML 维度，您必须在确定范围时使用下划线分隔元素。例如，对于 BigQuery Graph，此 LookML 维度基于 Stores 节点表中的 location_id 属性：

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

不过，如需创建基于 Snowflake 语义视图的 LookML 维度，您必须使用指标或维度的非限定名称。

示例

以下各部分提供了使用 derived_analytic_model 的不同子参数创建分析视图的示例：

使用 sql 创建派生分析模型
使用 create_process 创建派生分析模型
使用 sql_create 创建派生分析模型

使用 `sql` 创建派生分析模型

以下是一个 LookML 视图文件示例，该文件使用 derived_analytic_model 的 sql 子参数为 BigQuery 数据库定义了一个基于 SQL 的分析模型。Looker 将通过执行 sql 参数中提供的 SQL DDL 命令在数据库中创建分析模型。

请注意示例中的以下事项：

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 视图文件示例，该文件使用 derived_analytic_model 的 create_process 子形参为 BigQuery 数据库定义了一个基于 SQL 的分析模型。在此示例中，您需要定义多个连续的 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 视图文件示例，该文件使用 derived_analytic_model 的 sql_create 子形参为 BigQuery 数据库定义了基于 SQL 的分析模型。在此示例中，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 子参数创建了派生分析模型，并在 derived_analytic_model 参数下添加了 publish_as_db_analytic_model: yes 语句，那么在派生分析模型的 LookML 部署到生产环境并使用 publish_as_db_analytic_model: yes 后，Looker 将在下一个 Looker 重新生成器周期中发布（创建）稳定的分析模型。

发布稳定的分析模型后，您可以使用其稳定名称直接查询该模型。您可以通过分析模型探索查询的数据部分中SQL标签页上包含的信息来确定稳定名称。请按照以下步骤获取分析模型的稳定名称：

打开分析模型视图的探索。

提示：如果您在 Looker IDE 中，可以使用查看文件操作下拉菜单直接从分析模型的视图文件或从为分析模型的视图定义了探索的模型文件打开探索。
在探索中，从字段选择器中选择任意维度或指标。
点击数据部分的 SQL 标签页。
在 SQL 标签页中，找到以下 SQL 语句之一：
- 对于 BigQuery 图：
  - CREATE PROPERTY GRAPH
  - SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
- 对于 Snowflake 语义视图：
  - CREATE SEMANTIC VIEW
  - SELECT ... FROM SEMANTIC_VIEW_NAME
稳定名称是 Looker 在临时架构中创建的视图，指向 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：定义分析模型的视图的名称。

例如，以下是 BigQuery 连接的探索查询的 SQL 标签页中的文本。分析模型在名为 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）来定义新的基本度量。
- 支持基于其他度量的度量：您可以使用 LookML 度量的 sql 参数来执行非聚合计算，该计算使用分析模型中预定义的基本度量。创建基于其他度量的度量时，您无法将新度量定义为汇总度量类型，例如 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 定义的字段。
分析模型不支持以下功能：

derived_analytic_model 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

用法

定义

sql

sql_create

create_process

publish_as_db_analytic_model