您正在查看 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 子参数定义分析视图，则可以使用 publish_as_db_analytic_model 子参数的 derived_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 OR REPLACE，则添加 CREATE 语句）。

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

`publish_as_db_analytic_model`

对于使用 sql 形参创建的派生分析模型，您可以使用 publish_as_db_analytic_model: yes 定义派生分析模型，以提示 Looker 创建可在 Looker 外部查询的稳定分析模型。

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

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

基于分析视图创建 LookML 维度和测量

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

如需了解定义分析模型和引用分析模型中的元素的正确语法，请参阅方言的文档。例如，如需从 BigQuery 图实体创建 LookML 维度，您必须在确定范围时使用下划线分隔元素。例如，对于 BigQuery 图，此 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 部署到生产环境后，Looker 重新生成器的下一个周期将发布（创建）稳定分析模型，并使用 publish_as_db_analytic_model: yes。

发布稳定分析模型后，您可以使用其稳定名称直接查询该模型。您可以从分析模型的探索查询的数据部分的 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