Looker 中的派生表

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

例如，您可能有一个名为 orders 且包含多个列的数据库表。您想要计算一些客户级汇总指标，例如每位客户已下的订单数，或每位客户首次下单的时间。您可以使用原生派生表或基于 SQL 的派生表创建名为 customer_order_summary 且包含这些指标的新数据库表。

然后，您可以像处理 customer_order_summary 表中的任何其他表一样使用该表。

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

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

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

原生派生表版本 
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 参数来创建 PDT，而不是使用 sql 参数。如需了解相关信息和示例，请参阅 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 中的查询缓存，请参阅使用 datagroups 缓存查询和重建 PDT 文档页面。

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

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

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

永久性派生表 (PDT)

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

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

PDT 的要求

如需在 Looker 项目中使用 PDT，您需要符合以下条件：

  • 支持 PDT 的数据库方言。如需查看支持基于 SQL 的永久性派生表和永久性原生派生表的方言列表，请参阅本页面后面的支持的 PDT 数据库方言部分。
  • 数据库的暂存架构。它可以是数据库中的任何架构，但我们建议创建一个仅用于此目的的新架构。您的数据库管理员必须为 Looker 数据库用户配置写入权限的架构。
  • 在启用了 Persistent Derived Tables 选项的情况下配置的 Looker 连接。通常在首次配置 Looker 连接时进行设置（如需了解数据库方言的说明，请参阅 Looker 方言文档页面），但您也可以在初始设置后为连接启用 PDT。

PDT 支持的数据库方言

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

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

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

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

逐步构建 PDT

如果您的方言支持此功能，您就可以在项目中创建增量 PDT。增量永久性磁盘 (PDT) 是一种永久性派生表 (PDT)，Looker 通过向表附加新鲜数据而不是完整地重新构建表来构建表。如需了解详情，请参阅增量 PDT 文档页面。

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

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

创建永久性派生表 (PDT)

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

持久性策略

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

如需使派生表保持持久性，请将以下参数之一添加到 derived_table 定义中：

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

datagroup_trigger

数据组是最灵活的持久性创建方法。如果您为缓存政策定义了 datagroup，则可以使用 datagroup_trigger 参数按照与缓存政策相同的时间表开始重新构建 PDT。

Looker 会在数据库中维护 PDT，直到触发其数据组。当数据组被触发时，Looker 会重新构建 PDT 以替换之前的版本。这意味着，在大多数情况下，您的用户无需等待构建 PDT。如果用户在构建过程中从 PDT 请求数据，并且在缓存中没有查询结果，Looker 就会返回来自现有 PDT 的数据，直到新的 PDT 构建完毕。如需简要了解数据组，请参阅缓存查询并使用 datagroups 重新构建 PDT 文档页面。

如需详细了解重新生成器如何构建 PDT，请参阅 Looker 再生器部分。

sql_trigger_value

sql_trigger_value 参数会根据您提供的 SQL 语句触发重新生成 PDT。如果 SQL 语句的结果与之前的值不同，系统会重新生成 PDT。否则，现有 PDT 将保留在数据库中。这意味着，在大多数情况下，您的用户无需等待构建 PDT。如果用户在构建过程中从 PDT 请求数据，并且缓存中没有查询结果，Looker 会从现有的 PDT 返回数据，直到构建新的 PDT 为止。

如需详细了解重新生成器如何构建 PDT，请参阅 Looker 再生器部分。

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 的 persist_for 参数中指定的时长内进行保留。如果用户在 persist_for 天内查询 PDT，Looker 会尽可能使用缓存结果，或者对 PDT 运行查询。

persist_for 时间过后，Looker 会从您的数据库中清除 PDT，用户下次查询 PDT 时，系统将重新构建 PDT，这意味着该查询需要等待重新构建。

使用persist_for的 PDT 不会由 Looker 重新生成程序重建，级联 PDT 的情况除外。如果 persist_for 表是基于触发器的 PDT（使用 datagroup_trigger、interval_trigger 或 sql_trigger_value 持久性策略的依赖项级联的一部分），则重新生成器会监控和重新构建 persist_for 表，以便在级联中重新构建其他表。请参阅本页面上的 Looker 如何构建级联派生表部分。

materialized_view: yes

具体化视图允许您利用数据库的功能将派生表保留在 Looker 项目中。如果您的数据库方言支持具体化视图，并且您的 Looker 连接配置了永久性派生表选项，则您可以为派生表指定 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 会先构建 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 权限的用户可见，并且仅在“探索”查询加载后可见。

无论查询采用何种持久策略，重建派生表和运行选项都会重建响应查询所需的所有持久性表（所有 PDT 和汇总表）。这包括当前查询中的所有汇总表和 PDT，以及当前查询中的汇总表和 PDT 引用的任何汇总表和 PDT。

对于增量 PDT，Red Derived Tables &Run 选项会触发新的增量构建。如果使用增量型太平洋夏令时，则增量包括 increment_key 参数中指定的时间段，以及 increment_offset 参数中指定的先前时间段的数量（如果有）。如需了解一些示例场景，请参阅增量 PDT 文档页面，这些示例显示了增量 PDT 的构建方式（具体取决于它们的配置）。

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

如果 table_c 依赖于 table_b，且 table_b 依赖于 table_a，那么重新构建 table_c 会先重新构建 table_a，然后再重新构建 table_b，最后再构建 table_c。

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

  • 对于启动重建派生表和运行操作的用户，查询将等待表进行重建，然后再加载结果。其他用户的查询仍将使用现有表。永久表重新构建后，所有用户都将使用重新构建的表。虽然此过程旨在避免在表重建期间干扰其他用户的查询，但这些用户仍可能会受到数据库的额外负载的影响。如果您发现在工作时间触发重新构建可能会给数据库带来不可接受的负担，您可能需要告知用户，在这些时段内绝不应重新构建某些 PDT 或汇总表。

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

  • 对于 Looker 托管的实例，如果派生的表需要超过一个小时的重新构建时间，则该表将无法成功重建，并且浏览器会话将超时。如需详细了解可能影响 Looker 进程的超时，请参阅查询超时和排队帮助中心文章。

在开发模式下保留表

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

如果您在开发模式下查询持久性表，而没有更改定义，Looker 将查询该表的正式版本。如果您更改了表定义，这会影响表中的数据或表的查询方式，则下次在开发模式下查询表时，系统会创建新的表开发版本。使用这种开发表，您可以测试更改，而不会干扰最终用户。

什么提示 Looker 创建开发表

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

如果您处于开发模式，并且使用包含 if prod 和 if dev 语句的条件 WHERE 子句定义的WHERE基于 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，则可以在做出更改之前，在开发模式下查询这些 PDT，以使开发表可以在生产环境中使用。
  • 如果持久表具有使用 if prod 和 if dev 语句的dev_filters 参数（对于原生派生表）或条件 WHERE 子句（对于基于 SQL 的派生表），开发表无法用作正式版，因为开发版具有简化的数据集。在这种情况下，您可以在开发完表并部署更改之前注释掉 dev_filters 参数或条件 WHERE 子句，然后在开发模式下查询表。然后，Looker 会构建一个完整版表格，供您在部署更改时在生产环境中使用。

否则，如果您在没有可用作生产表的有效开发表的情况下部署更改，Looker 将下次在生产模式下查询该表（针对使用 persist_for 策略的持久性表）或下次运行重新生成工具时（对于使用 datagroup_trigger、interval_trigger 或 ）的该表。

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

如果在将更改部署到 PDT 或汇总表后，开发表仍保留在您的数据库中，那么 Looker 通常可以将开发表用作生产表，这样用户在查询该表时就不必等待该表构建完毕。如需了解详情，请参阅本页面上的 Looker 会将开发表保留多长时间和提示 Looker 如何创建开发表部分。

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

您可以在项目健康状况面板中检查项目中是否有未构建的 PDT。点击 Looker IDE 中的项目健康状况图标以打开项目健康状况面板。然后点击验证 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（或 CTAS）语句并执行该语句。例如，如需重新构建名为 customer_orders_facts 的 PDT，请使用以下代码：CREATE TABLE tmp.customer_orders_facts AS SELECT ... FROM ... WHERE ...
  2. 在构建表时发出语句以创建索引
  3. 将表格从“LC$.”（“Looker 创建”）重命名为 LR$.（"Looker Read"），以表明表格可以使用了
  4. 删除不应再使用的任何旧版表

有一些重要的影响：

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

Looker 再生器

Looker 重新生成程序会检查状态，并为触发器持久表启动重新构建。触发器保留表是一种使用触发器作为持久性策略的 PDT：

  • 对于使用 sql_trigger_value 的表，触发器是在表的 sql_trigger_value 参数中指定的查询。如果最近一次触发查询检查的结果不同于之前触发查询检查的结果，Looker 重新生成程序会触发对表的重建。例如，如果派生表使用 SQL 查询 SELECT CURDATE() 保留，则 Looker 重新生成程序将在日期变更后下一次检查触发器时重新构建该表。默认情况下，Looker' 的重新生成程序会每 5 分钟触发一次查询，看看是否会触发持久表，是否需要进行重建。但是，其他因素可能会影响重建表所需的时间，如本页实现持久性表的重要注意事项部分所述。
  • 对于使用 interval_trigger 的表，触发器是表的 interval_trigger 参数中指定的时长。在指定的时间过后，Looker 重新生成程序会触发表的重建。
  • 对于使用 datagroup_trigger 的表，触发器可以是在关联的数据组的 sql_trigger 参数中指定的查询，也可以是在数据组的 interval_trigger 参数中指定的时长。

只有在 persist_for 表是触发器保留表的依赖项级联时，Looker 重新生成工具才会为使用 persist_for 参数的持久性表启动重建。在这种情况下，Looker 重新生成程序将为 persist_for 表启动重建，因为在级联中重建其他表需要用到该表。否则，重新生成工具不会监控使用 persist_for 策略的持久性表。

如果无法根据 PDT 进行构建，重新生成器可能会尝试在下一个重新生成器周期内重建表：

  • 如果数据库连接启用了一律重试失败的 PDT build 设置，即使不符合表的触发条件，Looker 重新生成程序也会在下一个重新生成周期内尝试重建表。
  • 如果停用了一律重试失败的 PDT build 设置，那么在满足 PDT 的触发条件之前，Looker 重新生成程序不会尝试重新构建表。

如果用户在构建持久化表时从该表中请求数据，而查询结果不在缓存中，Looker 便会检查现有表是否仍然有效。（如果旧表与新版本不兼容，则可能会发生以下情况：新表具有不同的定义、新表使用其他数据库连接或创建了使用不同版本的 Looker。）如果现有表仍然有效，则 Looker 将从现有表返回数据，直到构建新表。否则，如果现有表无效，Looker 将在重新构建新表后提供查询结果。

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

考虑到持久性表（PDT 和汇总表）的实用性，您可以轻松地在 Looker 实例上积累很多这样的表。可能会出现这样的情景：Looker 重新生成工具需要同时构建多个表。特别是在级联表或长时间运行的表的情况下，您可以创建一个表，以便该表在重新构建之前有很长的延迟，或者用户在数据库正在努力生成表时，从表中获取查询结果时出现延迟。

默认情况下，Looker' 的重新生成工具会每 5 分钟检查一次 PDT 触发器，看看是否应重新构建使用 datagroup_trigger、interval_trigger 或 sql_trigger_value 保留策略的触发器保留表（PDT 和汇总表）。

但是，其他因素可能会影响重新构建表所需的时间：

  • 您的 Looker 管理员可能已在数据库连接上使用 PDT 和数据组维护时间表设置，更改了重新生成触发器的时间间隔。
  • 默认情况下，重新生成程序可以通过连接一次开始重建一个 PDT 或汇总表。Looker 管理员可以使用连接设置中的 Max PDT Builder Connections（最大 PDT 构建器连接）字段调整重新生成程序的并发构建次数。
  • 由同一 datagroup 触发的所有 PDT 和汇总表都将在同一重新生成过程中重新构建。如果您有许多使用数据组的表（无论是直接加载还是由于级联依赖项而加载），这可能会造成沉重负载。

除了前面的注意事项外，在某些情况下，您还应避免为派生表添加持久性：

  • 派生的表将进行扩展 - 每个 PDT 扩展都会在您的数据库中新建一个表副本。
  • 派生表使用模板化过滤条件或流式参数时 - 使用模板化过滤条件或流式参数的派生表不支持持久性。
  • 如果原生衍生表格是通过将用户属性与 access_filters 或 sql_always_where 结合使用，在数据库中为指定的每个可能的属性值生成的表格副本。
  • 当底层数据频繁更改，并且您的数据库方言不支持增量 PDT 时。
  • 当创建 PDT 涉及的费用和时间太高时。

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

使用 API 大规模管理 PDT

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

PDT 监控和问题排查

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

尝试排查 PDT 问题时：

  • 在调查 PDT 事件日志时，请特别注意开发表和生产表之间的区别。
  • 验证对 Looker 存储持久性派生表的暂存架构没有进行任何更改。如果进行了更改，您可能需要在 Looker 的管理部分更新连接设置，然后可能需要重启 Looker 以恢复正常的 PDT 功能。
  • 确定是所有 PDT 存在问题，还是只有一个。如果存在问题，那么问题可能是由 LookML 或 SQL 错误导致的。
  • 确定 PDT 存在的问题是否与安排重新构建的时间相符。
  • 确保所有 sql_trigger_value 查询都会成功求值，并且仅返回一行和一列。对于基于 SQL 的 PDT，您可以通过在 SQL Runner 中运行它们来实现。（应用 LIMIT 可防止失控查询。）如需详细了解如何使用 SQL Runner 调试派生表，请参阅此社区主题。
  • 对于基于 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 成功构建 PDT，需要对表获取独占锁以进行更新。这会与表中当前存在的其他共享锁定或独占锁定冲突。在所有其他锁定被清除之前，Looker 将无法更新 PDT。表上的任何排斥锁也在建立 PDT；如果表具有独占锁，那么在独占锁被清除之前，Looker 将无法获取共享锁来运行查询。
  • 使用 SQL Runner 中的 Show Processes 按钮。如果有大量进程处于活动状态，这可能会降低查询时间。
  • 监控查询中的注释。请参阅本页面上的为 PDT 查询评论部分。

查询 PDT 的备注

数据库管理员可以轻松地将普通查询与生成 PDT 的查询区分开来。Looker 向 CREATE TABLE ... AS SELECT ... 语句添加注解，其中包括 PDT 的 LookML 模型和视图，以及 Looker 实例的唯一标识符 (lulu)。如果系统在开发模式下代表用户生成了 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，则“探索”的 SQL 标签页中会显示 PDT 生成注释。该注释会显示在 SQL 语句的顶部。

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

在发生故障后重新构建 PDT

如果 PDT 失败，则查询该 PDT 时发生的情况：

  • 如果之前运行了同一查询，Looker 会使用缓存中的结果。（请参阅使用 datagroups 缓存查询和重建 PDT 文档页面，了解其工作原理）。
  • 如果结果不在缓存中，Looker 会从数据库中的 PDT 提取结果（如果存在有效的 PDT 版本）。
  • 如果数据库中没有有效的 PDT，Looker 将尝试重新构建 PDT。
  • 如果无法重建 PDT，Looker 将针对查询返回错误。下次查询 PDT 或下次 PDT 的持久性策略触发重建时，Looker 重新生成程序会尝试重建 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.)