Looker 中的派生表

在 Looker 中，“派生表”是一种查询，其结果就像是数据库中的实际表。

例如，您可能有一个名为 orders 的数据库表，其中包含许多列。您需要计算一些客户级别的汇总指标，例如每个客户下的订单数量或每个客户的第一笔订单。您可以使用原生派生表或基于 SQL 的派生表创建名为 customer_order_summary 且包含这些指标的新数据库表。

然后，您可以像处理数据库中的任何其他表一样使用 customer_order_summary 派生表。

原生派生表和基于 SQL 的派生表

如需在 Looker 项目中创建派生表，请使用 view 参数下的 derived_table 参数。在 derived_table 参数内，您可以通过以下两种方式之一定义派生表的查询：

例如，以下视图文件展示了如何使用 LookML 从 customer_order_summary 派生表创建视图。LookML 的两个版本说明了如何使用 LookML 或 SQL 创建对应派生表，从而为派生表定义查询：

  • 原生派生表在 explore_source 参数中使用 LookML 定义查询。在此示例中，查询基于一个现有的 orders 视图，后者在另一个未显示的文件中进行定义。原生派生表中的 explore_source 查询会引入 orders 视图文件中的 customer_id、first_order 和 total_amount 字段。
  • 基于 SQL 的派生表在 sql 参数中使用 SQL 定义查询。在此示例中，SQL 查询是数据库中 orders 表的直接查询。
原生派生表版本 
view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      column: customer_id {
        field: orders.customer_id
      }
      column: first_order {
        field: orders.first_order
      }
      column: total_amount {
        field: orders.total_amount
      }
    }
  }
  dimension: customer_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }
  dimension_group: first_order {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.first_order ;;
  }
  dimension: total_amount {
    type: number
    value_format: "0.00"
    sql: ${TABLE}.total_amount ;;
  }
}
基于 SQL 的派生表版本
view: customer_order_summary {
  derived_table: {
    sql:
      SELECT
        customer_id,
        MIN(DATE(time)) AS first_order,
        SUM(amount) AS total_amount
      FROM
        orders
      GROUP BY
        customer_id ;;
  }
  dimension: customer_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }
  dimension_group: first_order {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.first_order ;;
  }
  dimension: total_amount {
    type: number
    value_format: "0.00"
    sql: ${TABLE}.total_amount ;;
  }
}

这两个版本都会创建基于 orders 表的 customer_order_summary 视图，其中包含 customer_id 列、first_order, 列和 total_amount 列。

除了 derived_table 参数及其子参数以外，此 customer_order_summary 视图的运作方式与任何其他视图文件一样。无论您是使用 LookML 还是 SQL 定义派生表的查询，都可以根据派生表的列创建 LookML 衡量指标和维度。

定义派生表后，即可像数据库中的任何其他表一样使用派生表。

原生派生表

原生派生表根据您使用 LookML 字词定义的查询生成。如需创建原生派生表，请在 view 参数的 derived_table 参数中使用 explore_source 参数。您可以引用模型中的 LookML 维度或测量值，来创建原生派生表的列。请参阅上一个示例中的原生派生表视图文件。

与基于 SQL 的派生表相比，原生派生表在为数据建模时更易于读取和理解。

如需详细了解如何创建原生派生表，请参阅创建原生派生表文档页面。

基于 SQL 的派生表

如需创建基于 SQL 的派生表，请使用 SQL 字词定义查询，并使用 SQL 查询在表中创建列。您不能在基于 SQL 的派生表中引用 LookML 维度和测量值。请参阅上一个示例中基于 SQL 的派生表视图文件。

最常见的情况是，使用 view 参数的 derived_table 参数内的 sql 参数定义 SQL 查询。

在 Looker 中创建基于 SQL 的查询的一种实用方法是，使用 SQL Runner 创建 SQL 查询并将其转换为派生表定义。

某些极端情况下不允许使用 sql 参数。在这种情况下，Looker 支持以下参数，以便针对永久性派生表 (PDT) 定义 SQL 查询：

  • create_process：对 PDT 使用 sql 参数时，Looker 会在后台封装相应查询的方言 CREATE TABLE 数据定义语言 (DDL) 语句，以便通过 SQL 查询创建 PDT。某些方言不支持单步执行 SQL CREATE TABLE 语句。对于这些方言，您无法使用 sql 参数创建 PDT。您可以改用 create_process 参数通过多个步骤创建 PDT。如需了解相关信息和示例，请参阅 create_process 参数文档页面。
  • sql_create：如果您的用例需要自定义 DDL 命令，并且您的方言支持 DDL（例如，Google 预测 BigQuery ML），则您可以使用 sql_create 参数（而不是 sql 参数）来创建 PDT。如需查看相关信息和示例，请参阅 sql_create 文档页面。

无论您是使用 sql、create_process 还是 sql_create 参数，在所有这些情况下，您都可以使用 SQL 查询定义派生表，因此这些表都被视为基于 SQL 的派生表。

定义基于 SQL 的派生表时，请务必使用 AS 为每个列指定简洁的别名。这是因为，您将需要在维度中引用结果集的列名称，例如 ${TABLE}.first_order。因此，上一个示例使用 MIN(DATE(time)) AS first_order 而不是 MIN(DATE(time))。

临时和永久性派生表 (PDT)

除了原生派生表和基于 SQL 的派生表之间的区别之外，临时派生表（不写入数据库）与永久性派生表 (PDT)（写入数据库架构）之间的区别。

原生派生表和基于 SQL 的派生表可以是临时表，也可以是永久性表。

临时派生表

之前显示的派生表是临时表。这只是暂时的，因为 derived_table 参数中未定义任何持久性策略。

临时派生表不会写入数据库。当用户运行涉及一个或多个派生表的“探索”查询时，Looker 会使用派生表的 SQL 方言专用字段以及所请求的字段、联接和过滤条件值来构建 SQL 查询。如果该组合之前已运行，并且结果在缓存中仍然有效，Looker 会使用缓存的结果。如需详细了解 Looker 中的查询缓存，请参阅缓存查询文档页面。

否则，如果 Looker 无法使用缓存的结果，那么每当用户从临时派生表请求数据时，Looker 都必须对您的数据库运行一个新查询。因此，您应该确保临时派生表的性能良好，并且不会给数据库带来过多压力。如果查询需要一些时间才能运行，则 PDT 通常是更好的选择。

临时派生表支持的数据库方言

为了让 Looker 支持 Looker 项目中的派生表，您的数据库方言也必须支持这些表。下表显示了最新版 Looker 中的哪些方言支持派生表：

永久性派生表 (PDT)

永久性派生表 (PDT) 是一种派生表，会写入数据库的暂存架构中，并按照使用永久性策略指定的时间表重新生成。

PDT 可以是原生派生表或基于 SQL 的派生表。

PDT 要求

如需在 Looker 项目中使用 PDT，您需要以下各项：

  • 支持 PDT 的数据库方言。请参阅本页面下文支持的 PDT 数据库方言部分，了解支持基于 SQL 的永久性派生表和永久性原生派生表的方言列表。
  • 数据库的临时架构。它可以是数据库的任何架构，但我们建议您创建一个仅用于此目的的新架构。您的数据库管理员必须为 Looker 数据库用户配置具有写入权限的架构。
  • 配置了 启用 PDT 切换开关的 Looker 连接。这通常是在首次配置 Looker 连接时设置的（有关 Looker 方言文档页面如需了解您的数据库方言），但您也可以在初始设置后为您的连接启用 PDT。

PDT 支持的数据库方言

为了让 Looker 在您的 Looker 项目中支持 PDT，您的数据库方言也必须支持它们。

如需支持任何类型的 PDT（基于 LookML 或 SQL），方言必须支持对数据库进行写入，并满足其他要求。有一些只读数据库配置不允许保留持久性功能（最常见的是 Postgres 热交换副本数据库）。在这些情况下，您可以改用临时派生表。

下表显示了最新版 Looker 中支持基于 SQL 的永久性派生表的方言：

如需支持永久性原生派生表（具有基于 LookML 的查询），方言还必须支持 CREATE TABLE DDL 函数。下面列出了最新版 Looker 中支持持久原生（基于 LookML）派生表的方言：

逐步构建 PDT

增量 PDT 是 Looker 构建的永久性派生表 (PDT)，通过将新数据附加到表，而不是完整地重建表。

如果您的方言支持增量 PDT，并且您的 PDT 使用基于触发器的持久性策略（datagroup_trigger、sql_trigger_value 或 interval_trigger），您可以将 PDT 定义为增量 PDT。

如需了解详情，请参阅增量 PDT 文档页面。

适用于增量 PDT 的数据库方言

为了让 Looker 在您的 Looker 项目中支持增量 PDT，您的数据库方言也必须支持它们。下表显示了最新版 Looker 中支持增量 PDT 的方言：

创建永久性派生表 (PDT)

如需将派生表转变为永久性派生表 (PDT)，您需要为该表定义永久性策略。为了优化广告效果，您还应添加优化策略。

持久性策略

派生表的持久化可以通过 Looker 进行管理，或者对于支持具体化视图的方言，可由数据库使用具体化视图进行管理。

如需使派生表持久化，请在 derived_table 定义中添加以下参数之一：

使用基于触发器的持久化策略（datagroup_trigger、sql_trigger_value 和 interval_trigger）时，Looker 会在数据库中维护 PDT，直到触发 PDT 以进行重建。当 PDT 被触发后，Looker 会重新构建 PDT 以取代之前的版本。这意味着，对于基于触发器的 PDT，您的用户无需等待构建 PDT，即可从 PDT 获得“探索”查询的答案。

datagroup_trigger

数据组是最灵活的持久化方法。如果您已使用 sql_trigger 或 interval_trigger 定义数据组，则可以使用 datagroup_trigger 参数来开始重新构建 PDT。

Looker 会维护数据库中的 PDT，直到触发其数据组为止。当数据组被触发时，Looker 会重新构建 PDT 以替换之前的版本。这意味着，在大多数情况下，您的用户不必等待 PDT 构建完毕。如果用户在构建 PDT 时请求数据，但查询结果不在缓存中，Looker 就会返回现有 PDT 中的数据，直到构建新的 PDT 为止。如需简要了解数据组，请参阅缓存查询。

请参阅Looker 再生程序部分，详细了解该生成器如何构建 PDT。

sql_trigger_value

sql_trigger_value 参数会根据您提供的 SQL 语句触发 PDT 的重新生成。如果 SQL 语句的结果与之前的值不同，PDT 将重新生成。否则，现有 PDT 将在数据库中维护。这意味着，在大多数情况下，您的用户不必等待 PDT 构建完毕。如果用户在构建 PDT 时从 PDT 请求数据，但查询结果不在缓存中，Looker 就会返回来自现有 PDT 的数据，直到构建新的 PDT 为止。

请参阅Looker 再生程序部分，详细了解该生成器如何构建 PDT。

interval_trigger

interval_trigger 参数会根据您提供的时间间隔（例如 "24 hours" 或 "60 minutes"）触发重新生成 PDT。与 sql_trigger 参数类似，这意味着在用户查询 PDT 时，系统通常会进行预构建。如果用户在构建 PDT 时从 PDT 请求数据，但查询结果不在缓存中，Looker 就会返回来自现有 PDT 的数据，直到构建新的 PDT 为止。

persist_for

另一种方法是使用 persist_for 参数设置在将派生表标记为已过期之前应存储多长时间，使其不再用于查询，并会从数据库中删除。

persist_for PDT 会在用户首次运行查询时构建。然后，Looker 会将 PDT 中的数据库保持在 PDT 的 persist_for 参数中指定的时长内。如果用户在 persist_for 天内查询 PDT，Looker 会尽可能使用缓存的结果，否则就会对 PDT 运行查询。

persist_for 之后，Looker 会清除数据库中的 PDT，并在用户下次查询 PDT 时重新构建该 PDT，这意味着查询需要等待重新构建。

Looker 的再生程序不会自动重建使用 persist_for 的 PDT，除非 PDT 具有依赖项级联。如果 persist_for 表属于基于触发器的 PDT（使用 datagroup_trigger、interval_trigger 或 sql_trigger_value 持久策略的 PDT）级联的一部分，则生成器会监控并重建 persist_for 表，以便重新构建该级联中的其他表。请参阅本页中的 Looker 如何构建级联派生表部分。

materialized_view: yes

具体化视图使您能够利用数据库的功能在 Looker 项目中保留派生表。如果您的数据库方言支持具体化视图，并且您的 Looker 连接配置了启用 PDT 切换开关，那么您可以通过为派生表指定 materialized_view: yes 来创建具体化视图。原生派生表和基于 SQL 的派生表均支持具体化视图。

与永久性派生表 (PDT) 类似，具体化视图是一种以表形式存储在数据库的暂存架构中的查询结果。PDT 与具体化视图之间的主要区别在于表的刷新方式：

  • 对于 PDT，持久性策略在 Looker 中定义，而持久性则由 Looker 管理。
  • 对于具体化视图，数据库负责维护和刷新表中的数据。

因此，具体化视图功能需要您对方言及其功能的高级知识。在大多数情况下，每当数据库检测到具体化视图查询表中的新数据时，您的数据库就会刷新具体化视图。具体化视图最适合需要实时数据的场景。

如需了解方言支持、要求和重要注意事项，请参阅 materialized_view 参数文档页面。

优化策略

由于 PDT 存储在您的数据库中，因此您应按照您的方言支持的策略优化 PDT：

例如，如需为派生表示例添加持久性参数，您可以将它设置为在数据组 orders_datagroup 触发时重新构建，并在 customer_id 和 first_order 上添加索引，如下所示：

view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      ...
    }
    datagroup_trigger: orders_datagroup
    indexes: ["customer_id", "first_order"]
  }
}

如果您未添加索引（或方言的对等项），Looker 将发出警告，提醒您这样做以提高查询性能。

PDT 用例

PDT 非常有用，因为它可以通过将查询结果保存在表中来提升查询的性能。

通常，开发者应尽可能在不使用 PDT 的情况下对数据进行建模，直到绝对必要为止。

在某些情况下，可以通过其他方式优化数据。例如，添加索引或更改列的数据类型可能可以解决问题，而无需创建 PDT。请务必使用 Explain from SQL Runner 工具来分析慢速查询的执行计划。

除了减少经常运行的查询的查询时间和数据库负载外，PDT 还有一些其他用例，包括：

如果没有合理地将表中的唯一行标识为主键，您还可以使用 PDT 定义主键。

使用 PDT 测试优化

您可以使用 PDT 测试不同的索引编制、分发和其他优化选项，而无需您的 DBA 或 ETL 开发者提供大量支持。

假设您有一个表，但想要测试不同的索引。视图的初始 LookML 可能如下所示：

view: customer {
  sql_table_name: warehouse.customer ;;
}

如需测试优化策略，您可以使用 indexes 参数向 LookML 添加索引，如下所示：

view: customer {
  # sql_table_name: warehouse.customer
  derived_table: {
    sql: SELECT * FROM warehouse.customer ;;
    persist_for: "8 hours"
    indexes: [customer_id, customer_name, salesperson_id]
  }
}

查询一次视图即可生成 PDT。然后运行测试查询并比较结果。如果结果不错，您可以让 DBA 或 ETL 团队将索引添加到原始表中。

请务必将您的数据视图代码改回之前以移除 PDT。

使用 PDT 预联接或汇总数据

预联接或预汇总数据有助于针对大量或多种数据调整查询优化。

例如，假设您希望根据客户首次下订单的同类群组生成报告。每当需要实时数据时，运行此查询的开销可能会非常高昂；但是，您只能计算一次查询，然后使用 PDT 重复使用结果：

view: customer_order_facts {
  derived_table: {
    sql: SELECT
    c.customer_id,
    MIN(o.order_date) OVER (PARTITION BY c.customer_id) AS first_order_date,
    MAX(o.order_date) OVER (PARTITION BY c.customer_id) AS most_recent_order_date,
    COUNT(o.order_id) OVER (PARTITION BY c.customer_id) AS lifetime_orders,
    SUM(o.order_value) OVER (PARTITION BY c.customer_id) AS lifetime_value,
    RANK() OVER (PARTITION BY c.customer_id ORDER BY o.order_date ASC) AS order_sequence,
    o.order_id
    FROM warehouse.customer c LEFT JOIN warehouse.order o ON c.customer_id = o.customer_id
    ;;
    sql_trigger_value: SELECT CURRENT_DATE ;;
    indexes: [customer_id&, order_id, order_sequence, first_order_date]
  }
}

级联派生表

可以在另一个实例的定义中引用一个派生表，以创建级联派生表或级联 PDT 的链，视情况而定。级联派生表的示例包括表 TABLE_D，它依赖于另一个表 TABLE_C，而 TABLE_C 依赖于 TABLE_B，而 TABLE_B 依赖于 TABLE_A。

引用派生表的语法

如需引用另一个派生表中的派生表，请使用以下语法：

`${derived_table_or_view_name.SQL_TABLE_NAME}`

在此格式中，SQL_TABLE_NAME 是字面量字符串。例如，您可以使用以下语法引用 clean_events 派生表：

`${clean_events.SQL_TABLE_NAME}`

您可以使用相同的语法来引用 LookML 视图。同样，在本例中，SQL_TABLE_NAME 是字面量字符串。

在下一个示例中，clean_events PDT 基于数据库中的 events 表创建。clean_events PDT 会从 events 数据库表中遗漏不需要的行。然后，显示了第二个 PDT；event_summary PDT 是 clean_events PDT 的摘要。每当有新行添加到 clean_events 时，event_summary 表都会重新生成。

event_summary PDT 和 clean_events PDT 是级联 PDT，其中 event_summary 依赖于 clean_events（因为 event_summary 是使用 clean_events PDT 定义的）。这个示例可以在单个 PDT 中更高效地工作，但对于演示派生表引用非常有用。

view: clean_events {
  derived_table: {
    sql:
      SELECT *
      FROM events
      WHERE type NOT IN ('test', 'staff') ;;
    datagroup_trigger: events_datagroup
  }
}

view: events_summary {
  derived_table: {
    sql:
      SELECT
        type,
        date,
        COUNT(*) AS num_events
      FROM
        ${clean_events.SQL_TABLE_NAME} AS clean_events
      GROUP BY
        type,
        date ;;
    datagroup_trigger: events_datagroup
  }
}

以这种方式引用派生表时，并非总是必需，但使用以下格式为表创建别名通常很有用：

${derived_table_or_view_name.SQL_TABLE_NAME} AS derived_table_or_view_name

上面的示例执行此操作：

${clean_events.SQL_TABLE_NAME} AS clean_events

使用别名会很有帮助，因为在后台，PDT 使用数据库中冗长的代码命名。在某些情况下（尤其是在 ON 子句时），您很容易忘记需要使用 ${derived_table_or_view_name.SQL_TABLE_NAME} 语法来检索这个冗长的名称。别名有助于防止出现此类错误。

Looker 如何构建级联派生表

对于级联临时表，如果用户的查询结果不在缓存中，Looker 就会构建查询所需的所有派生表。如果您的 TABLE_D 的定义包含对 TABLE_C 的引用，那么 TABLE_D 依赖于 TABLE_C。这意味着，如果您查询 TABLE_D 并且查询不在 Looker 的缓存中，Looker 将重新构建 TABLE_D。但首先，它必须重新构建 TABLE_C。

现在我们来看这样一种情况：级联临时派生表，其中 TABLE_D 依赖于 TABLE_C，而依赖于 TABLE_B，而依赖于 TABLE_A。如果 Looker 在缓存中对 TABLE_C 的查询没有有效结果，Looker 将会构建查询所需的所有表。因此，Looker 会依次构建 TABLE_A、TABLE_B 和 TABLE_C：

在这种情况下，TABLE_A 必须先完成生成，然后 Looker 才能开始生成 TABLE_B，依此类推，直到 TABLE_C 完成且 Looker 可以提供查询结果。（由于回答此查询不需要 TABLE_D，因此 Looker 目前不会重新构建 TABLE_D。）

如需查看级联同一数据组的 PDT 的示例，请参阅 datagroup 参数文档页面。

相同的基本逻辑适用于 PDT：Looker 将构建回答查询所需的任何表，一直到依赖项链。但在 PDT 中，表通常就已存在，无需重新构建。使用级联 PDT 的标准用户查询时，只有当数据库中没有有效的 PDT 版本时，Looker 才会重新构建级联 PDT。如果要在级联中为所有 PDT 强制重新构建，则可以通过“探索”功能手动重新构建查询表。

一个重要的逻辑点是，在 PDT 级联中，依赖 PDT 实际上是在查询其所依赖的 PDT。这对于使用 persist_for 策略的 PDT 尤为重要。通常，persist_for PDT 会在用户查询时构建，在数据库的 persist_for 间隔时间之前一直保留在数据库中，并且在用户下次查询之前不会重新构建。但是，如果 persist_for PDT 与基于触发器的 PDT（使用 datagroup_trigger、interval_trigger 或 sql_trigger_value 持久化策略的级联）的一部分，则在重新构建依赖 PDT 时，实际上会查询 persist_for PDT。因此，在这种情况下，persist_for PDT 将根据其依赖的 PDT 重新构建。这意味着，persist_for PDT 可能会受到依赖者的持久策略的影响。

手动为查询重新构建永久性表

用户可以从“探索”菜单中选择重新构建派生表和运行选项，以替换持久性设置，并重建“探索”中当前查询所需的全部 PDT 和汇总表：

点击“探索操作”按钮可打开“探索”菜单，然后从中选择“重新构建派生表和运行”。

此选项仅对具有 develop 权限的用户可见，并且仅在“探索”查询加载完毕后才会显示。

无论构建何种持久策略，Rebuild Durived Tables & Run 选项都会重新构建回答查询所需的所有永久性表（所有 PDT 和汇总表）。这包括当前查询中的所有汇总表和 PDT，其中包括当前查询中的汇总表和 PDT 引用的所有汇总表和 PDT。

对于增量 PDT，Rebuild Durived Tables & Run 选项会触发新的增量的构建。使用 PDT 时，增量包括 increment_key 参数中指定的时间段，以及 increment_offset 参数中指定的过往时间段的数量（如果有）。如需查看一些示例场景，请参阅增量 PDT 的构建方式（具体取决于其配置），请参阅增量 PDT 文档页面。

对于级联 PDT，这意味着需要重新构建级联中的所有派生表，从顶部开始。这与您在临时派生表的级联中查询表的行为相同：

如果 table_c 依赖于 table_b，而 table_b 依赖于 table_a，则重新构建 table_c 会先重新构建 table_a，然后重新构建 table_b，最后成为 table_c。

对于手动重新构建派生表，请注意以下事项：

  • 对于启动重建 build 派生表和运行操作的用户，查询会等待表重新构建，之后再加载结果。其他用户的查询仍将使用现有表。重新构建永久性表后，所有用户都将使用重新构建的表。虽然此过程经过设计，可避免在表重新构建时干扰其他用户的查询，但这些用户仍可能会受到数据库的额外负载的影响。如果您遇到了在工作时间触发重新构建可能会让您的数据库出现不可接受的压力的情况，您可能需要告知用户他们绝不应在这些小时内重新构建某些 PDT 或汇总表。

  • 如果用户处于开发模式，并且“探索”基于开发表，重新构建派生表和运行操作将为“探索”重新构建开发表，而不是正式版表。但是，如果处于开发模式的“探索”模式使用的是派生版本的正式版本，则系统会重新构建正式表格。如需了解开发表和生产表，请参阅开发模式下的持久表。

  • 对于 Looker 托管的实例，如果派生的表需要超过一小时才能重新构建，则表将无法成功重建，并且浏览器会话会超时。如需详细了解可能影响 Looker 进程的超时，请参阅管理设置 - 查询文档页面上的查询超时和队列。

开发模式下保留的表

Looker 具有一些特殊行为，可在开发模式下管理持久性表。

如果您在开发模式下查询已保留的表，但未对其定义进行任何更改，Looker 会查询该表的正式版。如果您对表定义做出了更改，这会影响表中的数据或表的查询方式，那么当您下次在开发模式下查询表时，系统会创建新的表的开发版本。有了这样的开发表格，您就可以在不干扰最终用户的情况下测试更改。

是什么提示 Looker 创建开发表

无论您是否处于开发模式，Looker 都会使用现有生产表来解答查询。但在某些情况下，Looker 在开发模式下无法使用生产表进行查询：

如果您处于开发模式，并且查询基于条件 WHERE 子句并包含 if prod 和 if dev 语句的 SQL 派生表，Looker 会构建开发表。

对于没有用于缩小开发模式的数据集范围的永久性表，Looker 会使用开发版表来回答开发模式下的查询，除非您更改表的定义，然后在开发模式下查询该表。这会影响对表中的数据或表的查询方式产生影响的任何表更改。

下面列举了一些更改类型，会提示 Looker 创建开发版永久性表格（Looker 只有在做出这些更改后随后查询该表时，才会创建该表）：

对于不会修改表数据或影响 Looker 查询表的方式的变更，Looker 不会创建开发表。publish_as_db_view 形参就是一个很好的例子：在开发模式下，如果您仅更改派生表的 publish_as_db_view 设置，Looker 无需重新构建派生表，因此不会创建开发表。

Looker 会将开发表保留多长时间

无论表的实际保留策略如何，Looker 都会将开发持久性表视为持久性策略为 persist_for: "24 hours"。这是为了确保 Looker 开发者在开发过程中可以查询表的多次迭代，并且每当构建新的开发表时，Looker 都会确保开发表的保留期不超过一天。为了防止开发表使数据库变得杂乱，Looker 会应用 persist_for: "24 hours" 策略，确保频繁地从数据库中清理表。

否则，Looker 在开发模式下构建 PDT 和汇总表的方式与在生产模式下构建持久性表的方式相同。

如果您在部署对 PDT 或汇总表的更改时保留了开发表，则 Looker 通常可以将开发表用作生产表，这样用户就不必在查询表时等待构建表。

请注意，部署更改时，可能仍需重新构建表，才能在生产环境中查询查询：

  • 如果自您在开发模式中查询表的时间已超过 24 小时，则该表的开发版本会被标记为“已过期”，并且不会用于查询。您可以使用 Looker IDE 或永久性派生表页面的开发标签页检查是否存在未构建的 PDT。如果您有未构建的 PDT，您可以先在“开发模式”中进行查询，然后再进行更改，以使开发表可用于生产环境。
  • 如果某个永久性表具有 dev_filters 参数（对于原生派生表）或使用 if prod 语句和 if dev 语句的条件 WHERE 子句（针对基于 SQL 的派生表），开发表不能用作生产版本，因为开发版本具有简化的数据集。如果是这种情况，在完成表格的开发以及部署更改之前，您可以注释掉 dev_filters 参数或条件 WHERE 子句，然后在开发模式下查询表。之后，Looker 会构建出完整的表格，供您在部署更改时将其用于生产环境。

否则，如果在没有可用作生产表的有效开发表时部署更改，Looker 会在下次在生产模式下查询该表时（对于使用 persist_for 策略的永久性表）或者在下次运行生成器时（对于使用 datagroup_trigger、interval_trigger 或 sql_trigger_value} 的永久性表）重新构建该表。

在开发模式下检查未构建的 PDT

如果您在部署对 PDT 或汇总表的更改时保留了开发表，则 Looker 通常可以将开发表用作生产表，这样用户就不必在查询表时等待构建表。如需了解详情，请参阅本页面上的 Looker 可以将开发表保留多长时间以及提示 Looker 如何创建开发表部分。

因此，最好在部署到生产环境时构建所有 PDT，以便表可以立即用作正式版。

您可以在 Project Health 面板中检查您的项目是否存在未构建的 PDT。点击 Looker IDE 中的 Project Health 图标，以打开 Project Health 面板。然后点击验证 PDT 状态按钮。


如果存在未构建的 PDT，Project Health 面板会列出它们：

“Project Health”面板会显示项目的未构建 PDT 列表和“前往 PDT 管理”按钮。

如果您拥有 see_pdts 权限，则可以点击转到 PDT 管理按钮。Looker 将打开永久性派生表页面的开发标签页，并将结果过滤到您的特定 LookML 项目。在该页面中，您可以看到哪些开发 PDT 已构建和未构建，还可以访问其他问题排查信息。如需了解详情，请参阅管理设置 - 永久性派生表文档页面。

当您在项目中发现未构建的 PDT 后，就可以打开开发版本来查询表，然后使用“探索”菜单中的重新构建派生表和运行选项，构建开发版本。请参阅本页面上的手动为查询重新构建永久性表部分。

表共享和清理

在任何给定的 Looker 实例中，如果表具有相同的定义和保留方法设置，则 Looker 将在用户之间共享持久表。此外，如果表的定义不再存在，Looker 会将该表标记为已过期。

这样做有几个好处：

  • 如果您尚未在“开发模式”中对表进行任何更改，则查询将使用现有的生产表。除非您的表格是基于 SQL 的派生表格（使用包含 if prod 和 if dev 语句的条件 WHERE 子句定义），否则就会出现这种情况。如果使用条件 WHERE 子句定义表，则如果您在开发模式下查询表，Looker 会构建开发表。（对于带有 dev_filters 参数的原生派生表，Looker 具有使用生产表来回答开发模式下查询的逻辑，除非您更改表的定义，然后在开发模式下查询该表。）
  • 如果两个开发者在开发模式下对表格做了相同的更改，那么他们会共用同一个开发表格。
  • 将更改从开发模式推送到生产模式后，旧的生产定义不再存在，因此旧生产表会被标记为已过期并将被丢弃。
  • 如果您决定舍弃开发模式更改，则该表格定义不再存在，因此不需要的开发表会被标记为已过期并将被丢弃。

在开发模式下更快地工作

在某些情况下，您正在创建的 PDT 需要很长时间才能生成，如果要在开发模式中测试大量更改，这可能会很耗时。对于这些情况，您可以在开发模式下提示 Looker 创建较小版本的派生表。

对于原生派生表，您可以使用 explore_source 的 dev_filters 子参数来指定仅应用于派生表的开发版过滤条件：

view: e_faa_pdt {
  derived_table: {
  ...
    datagroup_trigger: e_faa_shared_datagroup
    explore_source: flights {
      dev_filters: [flights.event_date: "90 days"]
      filters: [flights.event_date: "2 years", flights.airport_name: "Yucca Valley Airport"]
      column: id {}
      column: airport_name {}
      column: event_date {}
    }
  }
...
}

此示例包含一个 dev_filters 参数（用于过滤过去 90 天的数据）和一个 filters 参数（用于过滤过去 2 年的数据以及尤卡谷机场的数据）。

dev_filters 参数与 filters 参数结合使用，以便所有过滤条件都应用于表的开发版本。如果 dev_filters 和 filters 均为同一列指定了过滤条件，则 dev_filters 将优先于表的开发版本。在本示例中，表格的开发版会过滤出尤卡谷机场最近 90 天的数据。

对于基于 SQL 的派生表，Looker 支持条件 WHERE 子句，其中包含针对表 (if prod) 和开发 (if dev) 版本的不同选项：

view: my_view {
  derived_table: {
    sql:
      SELECT
        columns
      FROM
        my_table
      WHERE
        -- if prod -- date > '2000-01-01'
        -- if dev -- date > '2020-01-01'
      ;;
  }
}

在此示例中，查询将包括 2000 年以后的状态（采用生产模式），但仅包含 2020 年之后的数据（处于开发模式）。有策略地使用此功能来限制结果集并提高查询速度，可以使开发模式的更改更容易验证。

Looker 如何构建 PDT

在 PDT 被设定并首次运行后，或再现程序会根据其持久性策略进行重建时，Looker 会执行以下步骤：

  1. 使用派生表 SQL 创建 CREATE TABLE AS SELECT (or CTAS) 语句并执行它。例如，如需重新构建名为 customer_orders_facts 的 PDT，请使用以下代码：CREATE TABLE tmp.customer_orders_facts AS SELECT ... FROM ... WHERE ...
  2. 在构建表时发出语句以创建索引
  3. 将表从 LC$..（“Looker 创建”）重命名为 LR$..（“Looker 读取”），以表明表已可供使用
  4. 删除不应被使用的任何旧版表

有一些重要影响：

  • 构成派生表的 SQL 必须在 CTAS 语句中有效。
  • SELECT 语句结果集中的列别名必须是有效的列名称。
  • 指定分布、排序键和索引时使用的名称必须是派生表的 SQL 定义中列出的列名称，而不是 LookML 中定义的字段名称。

Looker 再生器

Looker 再生程序会检查状态并启动对触发器持久表的重新构建。触发器持久性表是 PDT 或将触发器用作持久性策略的汇总表：

  • 对于使用 sql_trigger_value 的表，触发器是在表的 sql_trigger_value 参数中指定的查询。当最新触发器查询检查的结果与上一个触发器查询检查的结果不同时，Looker 重新生成器会触发对表格的重新构建。例如，如果您的派生表持久保留为 SQL 查询 SELECT CURDATE()，则 Looker 重新生成器会在日期发生更改后再次检查触发器时重建表。
  • 对于使用 interval_trigger 的表，触发器是在表的 interval_trigger 参数中指定的时长。在指定时间过后，Looker 重新生成器会触发对表的重新构建。
  • 对于使用 datagroup_trigger 的表，触发器可以是关联数据组的 sql_trigger 参数中指定的查询，也可以是触发器在数据组的 interval_trigger 参数中指定的时长。

Looker 重新生成器还会为使用 persist_for 参数的持久性表发起重新构建，但前提是 persist_for 表是触发器持久表的依赖项级联。在这种情况下，Looker 重新生成器将为 persist_for 表启动重新构建，这是因为在重新级联时需要其他表进行重新构建。否则，生成器不会监控使用 persist_for 策略的永久性表。

Looker 再生周期的起始周期为 Looker 管理员在数据库连接的 Datagroup 和 PDT 维护时间表设置中配置的（默认为 5 分钟间隔）。不过，Looker Regenizer 不会启动新周期，直至完成上一周期的所有检查和 PDT 重建。这意味着，如果您有长时间运行的 PDT 构建，Looker 再生周期的运行频率可能会低于数据组和 PDT 维护时间表设置中指定的频率。其他因素可能影响重新构建表所需的时间，如本页实现永久性表的重要注意事项部分所述。

如果 PDT 无法构建，重新生成工具可能会尝试在下一个再生周期中重建表：

  • 如果您的数据库连接启用了重试失败的 PDT 构建设置，那么 Looker 重新生成器将在下一重新生成周期内尝试重新构建此表格，即使不符合该表格的触发器条件也是如此。
  • 如果重试失败的 PDT 构建设置处于停用状态，那么在 PDT 的触发条件满足之前，Looker 重新生成器将不会尝试重新构建表。

如果用户在持久化表构建过程中请求数据，并且查询结果不在缓存中，Looker 就会检查现有的表是否仍然有效。（如果新表与新表具有不同定义、新表使用不同的数据库连接或新表是使用不同版本的 Looker 创建的，则上述表可能无效）。如果现有表仍然有效，则 Looker 将返回现有表中的数据，直到新表构建完毕。否则，如果现有表无效，则 Looker 将在重新构建新表后提供查询结果。

实现永久性表的重要注意事项

鉴于持久性表（PDT 和汇总表）的实用性，您可以轻松地在 Looker 实例上累积许多表。可能会出现一种情况，即 Looker 重新生成器需要同时构建许多表。特别是对于级联表或长时间运行的表，您可以创建以下场景：表在重新构建之前存在较长时间的延迟，或者当数据库正在努力生成表时，用户在从表获取查询结果时出现延迟。

Looker 的重新生成程序会检查 PDT 触发器，以确定是否应重新构建触发器持久表。再生周期由您的 Looker 管理员在数据库连接的数据组和 PDT 维护时间表设置中配置的定期间隔设置（默认为 5 分钟的间隔）。

以下几个因素可能会影响重新构建表所需的时间：

  • 您的 Looker 管理员可能针对数据库连接使用了数据组和 PDT 维护时间表设置，更改了重新生成程序触发器检查的间隔时间。
  • 在完成上一周期的所有检查和 PDT 重建之前，Looker 重新生成器不会开始新的周期。因此，如果您有长时间运行的 PDT 构建，Looker 再生周期的频率可能会低于数据组和 PDT 维护时间表设置。
  • 默认情况下，Regener 可以通过连接一次开始重新构建一个 PDT 或汇总表。Looker 管理员可以使用连接设置中的 Maxit of PDT builder connections（PDT 构建器连接数上限）字段，调整重新生成器允许重新构建的次数。
  • 同一 datagroup 触发的所有 PDT 和汇总表都将在同一重新生成过程中重建。如果您有许多表使用数据组，则直接加载或因级联依赖项而会产生大量负载。

除了上述注意事项之外，在某些情况下，您还应避免为派生表添加保留：

  • 派生表将进行扩展时 - PDT 的每个扩展程序都会在数据库中创建表的新副本。
  • 派生表使用模板化过滤条件或液体参数 - 使用模板化过滤条件或液体参数的派生表不支持持久化。
  • 原生派生表格是使用 access_filters 或 sql_always_where 使用用户属性的“探索”功能构建而成的，系统会在数据库中针对指定的每个可能的用户属性值生成表格副本。
  • 当底层数据频繁变化并且数据库方言不支持增量 PDT 时。
  • 当创建 PDT 的费用和时间过高。

根据 Looker 连接上永久性表的数量和复杂程度，队列可能包含许多需要在每个周期检查和重建的持久性表，因此在 Looker 实例上实现派生表时请务必注意这些因素。

使用 API 大规模管理 PDT

随着您在实例上创建更多 PDT，监控和管理按不同时间表刷新的 PDT 将变得越来越复杂。还可以考虑使用 Looker 的 Apache Airflow 集成来管理其他 ETL 和 ELT 进程的 PDT 时间表。

监控和 PDT 问题排查

如果您使用 PDT，尤其是级联 PDT，那么查看 PDT 的状态会很有帮助。您可以在 Looker 的永久性派生表管理页面中查看 PDT 的状态。有关信息，请参阅管理设置 - 永久性派生表文档页面。

在尝试排查 PDT 问题时：

  • 在调查 PDT 事件日志时，请特别注意开发表和生产表之间的区别。
  • 验证 Looker 在其中存储永久性派生表的暂存架构是否未进行任何更改。如果做出了更改，您可能需要在 Looker 的管理部分中更新连接设置，然后可能重启 Looker 才能恢复正常的 PDT 功能。
  • 确定所有 PDT 是否存在问题，还是仅有一个问题。如果某个问题出现问题，可能是因为 LookML 或 SQL 错误所致。
  • 确定 PDT 问题是否与安排重新构建的时间相对应。
  • 确保所有 sql_trigger_value 查询都能够成功求值，并且仅返回一行和一列。为此，您可以在 SQL Runner 中运行基于 SQL 的 PDT。（应用 LIMIT 可防止失控查询。）如需详细了解如何使用 SQL Runner 调试派生表，请参阅使用 SQL 运行程序测试派生表 社区帖子。
  • 对于基于 SQL 的 PDT，请使用 SQL Runner 来验证 PDT 的 SQL 是否执行正确。（请务必在 SQL Runner 中应用 LIMIT 以确保查询时间合理。）
  • 对于基于 SQL 的派生表，请避免使用通用表表达式 (CTE)。将 DT 与 DT 搭配使用可创建嵌套的 WITH 语句，这可能导致 PDT 在没有警告的情况下失败。相反，您可以使用 CTE 的 SQL 创建辅助 DT，并使用 ${derived_table_or_view_name.SQL_TABLE_NAME} 语法从第一个 DT 引用该 DT。
  • 检查 PDT 所依赖的所有表（无论是普通表还是 PDT 本身）是否存在，以及是否可进行查询。
  • 确保问题 PDT 所依赖的任何表都没有共享或独占锁。Looker 需要获得对表的独占锁才能更新，以便 Looker 成功构建 PDT。这会与表中当前存在的其他共享或独占锁冲突。在所有其他锁定解除之前，Looker 将无法更新 PDT。对于表中的所有排除锁，Looker 也会基于此建立 PDT；如果表上有独占锁，Looker 将无法获取共享锁来运行查询，直到独占锁被清除为止。
  • 在 SQL Runner 中使用 Show Processes 按钮。如果有大量进程处于活动状态，这可能会降低查询时间。
  • 监控查询中的注释。请参阅本页面上的查询 PDT 的评论部分。

查询 PDT 的注释

数据库管理员可以轻松区分正常查询与生成 PDT 的查询。Looker 向 CREATE TABLE ... AS SELECT ... 语句添加注释，其中包含 PDT 的 LookML 模型和视图，以及 Looker 实例的唯一标识符（Slug）。如果是在“开发模式”下代表用户生成 PDT，则注释会指明该用户的 ID。PDT 生成注释遵循以下模式：

-- Building `<view_name>` in dev mode for user `<user_id>` on instance `<instance_slug>`
CREATE TABLE `<table_name>` SELECT ...
-- finished `<view_name>` => `<table_name>`

如果 Looker 必须为探索的查询生成 PDT，则 PDT 生成评论将显示在探索的 SQL 标签页中。注释将显示在 SQL 语句的顶部。

最后，PDT 生成注释会显示在查询弹出式窗口中查询详细信息弹出式窗口信息标签页中的消息字段中。

在失败后重新构建 PDT

当 PDT 失败时，查询 PDT 时发生的情况如下：

  • 如果之前运行了相同的查询，Looker 会使用缓存中的结果。（如需了解工作原理，请参阅缓存查询文档页面）。
  • 如果结果不在缓存中，如果存在有效的 PDT 版本，Looker 将提取数据库中 PDT 的结果。
  • 如果数据库中没有有效的 PDT，Looker 会尝试重新构建 PDT。
  • 如果无法重新构建 PDT，Looker 将针对查询返回错误。Looker Regenerator 会在下次查询 PDT 时或在 PDT 的持久性策略下次触发重新构建时尝试重建 PDT。

对于级联 PDT，系统会采用相同的逻辑，但级联 PDT 除外：

  • 如果未能针对一个表进行构建，将无法在依赖项链中构建 PDT。
  • 依赖 PDT 实质上是查询它所依赖的 PDT，因此一个表的持久策略可以触发重新构建上游的 PDT。

回顾前面的级联表示例，其中 TABLE_D 依赖于 TABLE_C，后者依赖于 TABLE_B，而依赖于 TABLE_A：

如果 TABLE_B 出现故障，则所有标准（非级联）行为都适用于 TABLE_B：如果查询 TABLE_B，Looker 会先尝试使用缓存来返回结果，然后在可能的情况下尝试使用之前版本的表格，再尝试重新构建表格，最后如果 TABLE_B 无法重新构建，就会返回错误。Looker 会在下一次查询表或表的永久性策略下次触发重新构建时尝试重新构建 TABLE_B。

这同样适用于 TABLE_B 的从属项。因此，如果无法构建 TABLE_B，并且 TABLE_C 存在查询：

  • Looker 会尝试在 TABLE_C 上对查询使用缓存。
  • 如果结果不在缓存中，Looker 将尝试从数据库中 TABLE_C 提取结果。
  • 如果没有有效的 TABLE_C 版本，Looker 将尝试重新构建 TABLE_C，从而在 TABLE_B 上创建查询。
  • 然后，Looker 会尝试重新构建 TABLE_B（如果 TABLE_B 尚未修复，它会失败）。
  • 如果无法重新构建 TABLE_B，则 TABLE_C 无法重新构建，因此 Looker 会针对 TABLE_C 返回查询错误。
  • 然后，Looker 将根据其常规的持久策略或 PDT 下次查询（包括 TABLE_D 下次尝试构建时，因为 TABLE_D 依赖于 TABLE_C）来尝试重新构建 TABLE_C。

使用 TABLE_B 解决问题后，TABLE_B 和每个依赖表都将尝试根据其持久策略或下次查询（包括依赖 PDT 的时间尝试重新构建）进行重建。或者，如果级联中的开发版本是在开发模式下构建的，则开发版本可能会用作新的正式版 PDT。（请参阅本页上的开发模式下的持久性表部分，了解工作原理。）或者，您也可以使用“探索”针对 TABLE_D 运行查询，然后手动为查询重新构建 PDT，这会强制重新构建所有依存于级联的 PDT。

提升 PDT 性能

创建 PDT 时，可能会担心性能。特别是在表非常大的情况下，对表的查询可能比较慢，就像处理数据库中任何大型表一样。

如需提高性能，您可以过滤数据或控制 PDT 中的数据的排序和编入索引方式。

添加过滤条件以限制数据集

对于特别大的数据集，拥有许多行会减慢针对 PDT 的查询速度。如果您通常仅查询近期的数据，请考虑为 PDT 的 WHERE 子句添加一个过滤条件，以将表的数据限制为不超过 90 天。这样，每次重新构建时，表只会添加相关数据，以大幅提高运行查询的速度。然后，您可以创建一个单独的更大、PDT 来进行历史分析，从而实现快速查询近期数据和查询旧数据。

使用 indexes 或 sortkeys 和 distribution

创建大型 PDT 时，将表（针对 MySQL 或 Postgres 等方言）编入索引或添加排序键和分布（针对 Redshift）有助于提升性能。

通常，最好在 ID 或日期字段中添加 indexes 参数。

对于 Redshift，通常最好在 ID 或日期字段中添加 sortkeys 参数，以及在用于联接的字段中添加 distribution 参数。

以下设置用于控制 PDT 中的数据的排序和编入索引方式。这些设置是可选的，但强烈建议您使用：

  • 对于 Redshift 和 Aster，请使用 distribution 参数指定列名称，其值用于围绕集群分布数据。当两个表通过 distribution 参数中指定的列联接时，数据库可以找到同一节点上的联接数据，因此节点间 I/O 降至最低。
  • 对于 Redshift，将 distribution_style 参数设置为 all，以指示数据库在每个节点上保留数据的完整副本。这通常用于在联接实际的小表时最大限度地减少节点间 I/O。将此值设为 even 可指示数据库通过集群均匀分布数据，而不使用分布列。仅当未指定 distribution 时，才能指定此值。
  • 对于 Redshift，请使用 sortkeys 参数。这些值会指定 PDT 的哪些列用于对磁盘中的数据进行排序，以便更轻松地进行搜索。在 Redshift 上，您可以使用 sortkeys 或 indexes，但不能同时使用这两者。例如：
* On most databases, use the [`indexes`](/reference/view-params/indexes) parameter. The values specify which columns of the PDT are indexed. (On Redshift, indexes are used to generate interleaved sort keys.)