增量 PDT

在 Looker 中,永久性派生表 (PDT) 会写入数据库的底层存储架构。Looker 会根据其持久化策略持久化和重新构建 PDT。触发 PDT 重新构建时,Looker 默认会重新构建整个表。

增量 PDT 是 Looker 通过将最新数据附加到表(而不是完全重新构建表)来构建的 PDT:

一个大型表格,其中突出显示了底部的三行,表示向表格中添加了少量新行。

如果您的方言支持增量 PDT,您可以将以下类型的 PDT 转换为增量 PDT:

首次对增量 PDT 运行查询时,Looker 会构建整个 PDT 以获取初始数据。如果表很大,则初始构建可能需要很长时间,就像构建任何大型表一样。初始表构建完成后,后续构建将是增量构建,如果增量 PDT 设置得当,构建时间会更短。

对于增量 PDT,请注意以下事项:

  • 只有使用基于触发器的持久性策略(datagroup_trigger、sql_trigger_value 或 interval_trigger)的 PDT 才支持增量 PDT。使用 persist_for 持久性策略的 PDT 不支持增量 PDT。
  • 对于基于 SQL 的 PDT,必须使用 sql 参数定义表查询,才能用作增量 PDT。使用 sql_create 参数或 create_process 参数定义的基于 SQL 的 PDT 无法以增量方式构建。如本页中的示例 1 所示,Looker 会使用 INSERT 或 MERGE 命令为增量 PDT 创建增量。派生表不能使用自定义数据定义语言 (DDL) 语句进行定义,因为 Looker 无法确定创建准确增量所需的 DDL 语句。
  • 增量 PDT 的源表必须针对基于时间的查询进行优化。具体而言,用于增量键的基于时间的列必须具有优化策略,例如分区、排序键、索引或您的方言支持的任何优化策略。强烈建议您优化源表,因为每次更新增量表时,Looker 都会查询源表,以确定用于增量键的基于时间的列的最新值。如果源表未针对这些查询进行优化,Looker 查询最新值的速度可能会很慢,且费用高昂。
  • 增量 PDT 不直接支持 SQL 窗口函数。如需将窗口函数与增量 PDT 搭配使用,请将增量 PDT 构建为不含窗口函数的基础表或中间表,然后在应用窗口函数的单独派生表或 PDT 中引用该增量 PDT。

定义增量 PDT

您可以使用以下参数将 PDT 转换为增量 PDT:

  • increment_key(必须提供,才能将 PDT 设为增量 PDT):定义应查询的新记录的时间段。
  • {% incrementcondition %} Liquid 过滤器(需要将基于 SQL 的 PDT 设为增量 PDT;不适用于基于 LookML 的 PDT):将增量键连接到增量键所基于的数据库时间列。如需了解详情,请参阅 increment_key 文档页面。
  • increment_offset(可选):一个整数,用于定义每次增量构建时要重新构建的先前时间段(按增量键的粒度)的数量。increment_offset 参数在数据延迟到达的情况下非常有用,因为之前的时间段可能包含新数据,而这些数据在最初构建相应增量并将其附加到 PDT 时并未包含在内。

如需查看展示如何通过原生永久性派生表、基于 SQL 的永久性派生表和汇总表格创建增量 PDT 的示例,请参阅 increment_key 参数文档页面。

以下是一个视图文件示例,其中定义了一个基于 LookML 的增量 PDT:

view: flights_lookml_incremental_pdt {
  derived_table: {
    indexes: ["id"]
    increment_key: "departure_date"
    increment_offset: 3
    datagroup_trigger: flights_default_datagroup
    distribution_style: all
    explore_source: flights {
      column: id {}
      column: carrier {}
      column: departure_date {}
    }
  }

  dimension: id {
    type: number
  }
  dimension: carrier {
    type: string
  }
   dimension: departure_date {
    type: date
  }
}

首次对该表运行查询时,系统会完整构建该表。之后,PDT 将以一天 (increment_key: departure_date) 为增量重新构建,回溯三天 (increment_offset: 3)。

增量键基于 departure_date 维度,该维度实际上是 departure 维度组中的 date 时间范围。(如需简要了解维度组的工作原理,请参阅 dimension_group 参数文档页面。)维度组和时间范围均在 flights 视图中定义,该视图是此 PDT 的 explore_source。以下是 departure 维度组在 flights 视图文件中的定义方式:

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

增量参数和持久化策略的交互

PDT 的 increment_key 和 increment_offset 设置与 PDT 的持久性策略无关:

  • 增量 PDT 的持久性策略仅决定 PDT 的增量时间。除非触发了表的持久化策略,或者在“探索”中使用重新构建派生表并运行选项手动触发了 PDT,否则 PDT 构建器不会修改增量 PDT。
  • 当 PDT 递增时,PDT 构建器将根据最新的时间增量(由 increment_key 参数定义的时间段)确定之前向表中添加最新数据的时间。基于此,PDT 构建器会将数据截断到表中最近一次时间增量的开头,然后从该位置构建最新增量。
  • 如果 PDT 具有 increment_offset 参数,PDT 构建器还会重新构建 increment_offset 参数中指定的先前时间段的数量。之前的时间段从当前时间增量(由 increment_key 参数定义的时间段)的开始时间算起。

以下示例场景通过展示 increment_key、increment_offset 和持久化策略的交互,说明了增量 PDT 的更新方式。

示例 1

此示例使用具有以下属性的 PDT:

  • 递增键:日期
  • 递增偏移:3
  • 持久性策略:每月触发一次,在每月的第一天触发

此表格将按以下方式更新:

  • 按月持久化策略意味着系统每月会自动构建一次表。这意味着,例如在 6 月 1 日,表格中的最后一行将是 5 月 1 日添加的数据。
  • 由于此 PDT 具有基于日期的增量键,因此 PDT 构建器会将 5 月 1 日截断回当天的开始时间,并重新构建 5 月 1 日至当前日期(6 月 1 日)的数据。
  • 此外,此 PDT 的增量偏移量为 3。因此,PDT 构建器还会重新构建 5 月 1 日之前三个时间段(天)的数据。结果是,系统会重新构建 4 月 28 日、29 日、30 日以及截至 6 月 1 日当天的数据。

在 SQL 术语中,以下是 PDT 构建器将于 6 月 1 日运行的命令,用于确定应重新构建的现有 PDT 中的行:

## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))

## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)

以下是 PDT 构建器将于 6 月 1 日运行的 SQL 命令,用于构建最新的增量:

## Example SQL for BigQuery:

MERGE INTO [pdt_name] USING (SELECT [columns]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
   AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
   THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]

## Example SQL for other dialects:

START TRANSACTION;
DELETE FROM [pdt_name]
   WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
   SELECT [columns]
   FROM [source_table]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;

示例 2

此示例使用具有以下属性的 PDT:

  • 持久化策略:每天触发一次
  • 递增键:月
  • 递增偏移:0

此表格将于 6 月 1 日更新,更新后的内容如下:

  • 每日持久化策略意味着该表每天会自动构建一次。在 6 月 1 日,表格中的最后一行将是 5 月 31 日添加的数据。
  • 由于增量键基于月份,因此 PDT 构建器将从 5 月 31 日截断回月初,并重新构建整个 5 月以及截至当前日期(包括 6 月 1 日)的数据。
  • 由于此 PDT 没有增量偏移量,因此不会重新构建之前的时间段。

此表格将于 6 月 2 日更新,更新后的内容如下:

  • 在 6 月 2 日,表格中的最后一行将是 6 月 1 日添加的数据。
  • 由于 PDT 构建器会截断到 6 月初,然后从 6 月 1 日开始重新构建数据,直到当天,因此系统只会重新构建 6 月 1 日和 6 月 2 日的数据。
  • 由于此 PDT 没有增量偏移量,因此不会重新构建之前的时间段。

示例 3

此示例使用具有以下属性的 PDT:

  • 递增键:月
  • 递增偏移:3
  • 持久化策略:每天触发一次

此场景说明了增量 PDT 的不良设置,因为它是每日触发的 PDT,偏移量为三个月。这意味着每天至少会重建三个月的数据,这会非常低效地使用增量 PDT。不过,这是一个有趣的场景,可以帮助您了解增量 PDT 的工作原理。

此表格将于 6 月 1 日更新,更新后的内容如下:

  • 每日持久化策略意味着该表每天会自动构建一次。例如,在 6 月 1 日,表格中的最后一行将是 5 月 31 日添加的数据。
  • 由于增量键基于月份,因此 PDT 构建器会将 5 月 31 日截断回月初,并重新构建整个 5 月以及截至当前日期(包括 6 月 1 日)的数据。
  • 此外,此 PDT 的增量偏移量为 3。这意味着 PDT 构建器还会重新构建 5 月之前三个时间段(月)的数据。结果是,系统会重新构建 2 月、3 月、4 月以及截至当前日期(6 月 1 日)的数据。

此表格将于 6 月 2 日更新,更新后的内容如下:

  • 在 6 月 2 日,表中的最后一行将是在 6 月 1 日添加的。
  • PDT 构建器会将月份截断回 6 月 1 日,并重新构建 6 月份(包括 6 月 2 日)的数据。
  • 此外,由于增量偏移量,PDT 构建器会重新构建 6 月之前三个月的数据。结果是,系统会重新构建 3 月、4 月、5 月以及截至当天(6 月 2 日)的数据。

在开发模式下测试增量 PDT

在将新的增量 PDT 部署到生产环境之前,您可以测试该 PDT,以确保其能够构建和递增。如需在开发模式下测试增量 PDT,请执行以下操作:

  1. 为 PDT 创建探索:

    • 在关联的模型文件中,使用 include 参数将 PDT 的视图文件包含在模型文件中。
    • 在同一模型文件中,使用 explore 参数为增量 PDT 的视图创建探索。
     include: "/views/e_faa_pdt.view"
     explore: e_faa_pdt {}
    
  2. 打开 PDT 的“探索”。为此,请选择查看文件操作按钮,然后选择一个探索名称。

  1. 在“探索”中,选择一些维度或测量,然后点击运行。然后,Looker 将构建整个 PDT。如果这是您在增量 PDT 上运行的第一个查询,PDT 构建器将构建整个 PDT 以获取初始数据。如果表很大,则初始构建可能需要很长时间,就像构建任何大型表一样。

  2. 您可以通过以下方式验证初始 PDT 是否已构建:

    • 如果您拥有 see_logs 权限,则可以查看 PDT 事件日志,验证表是否已构建。如果您在 PDT 事件日志中没有看到 PDT 创建事件,请查看 PDT 事件日志探索顶部的状态信息。如果显示“来自缓存”,您可以选择清除缓存并刷新,以获取最新信息。
    • 否则,您可以在“探索”的数据栏中的 SQL 标签页中查看注释。SQL 标签页会显示查询以及在“探索”中运行查询时将执行的操作。例如,如果 SQL 标签页中的注释显示 -- generate derived table e_incremental_pdt,则点击运行后将执行该操作。
  3. 创建 PDT 的初始 build 后,请使用“探索”中的重新构建派生表并运行选项,提示对 PDT 进行增量构建。

  4. 您可以使用与之前相同的方法来验证 PDT 是否以增量方式构建:

    • 如果您拥有 see_logs 权限,则可以使用 PDT 事件日志查看增量 PDT 的 create increment complete 事件。如果您在 PDT 事件日志中没有看到此事件,并且查询状态显示“来自缓存”,请选择清除缓存并刷新,以获取最新信息。
    • 查看“探索”的数据栏中 SQL 标签页内的注释。在这种情况下,注释会指明 PDT 已递增。例如:-- increment persistent derived table e_incremental_pdt to generation 2
  5. 验证 PDT 已构建并正确递增后,如果您不想保留 PDT 的专用探索,可以从模型文件中移除或注释掉 PDT 的 explore 和 include 参数。

在开发模式下构建 PDT 后,一旦您部署更改,系统就会将该表用于生产环境,除非您进一步更改该表的定义。如需了解详情,请参阅 Looker 中的派生表文档页面中的开发模式下的持久化表部分。

排查增量 PDT 问题

本部分介绍了您在使用增量 PDT 时可能会遇到的一些常见问题,以及排查和解决这些问题的步骤。

架构更改后,增量 PDT 无法构建

如果您的增量 PDT 是基于 SQL 的派生表,并且 sql 参数包含通配符(例如 SELECT *),则对底层数据库架构的更改(例如添加列、移除列或更改列数据类型)可能会导致 PDT 失败,并显示以下错误:

SQL Error in incremental PDT: Query execution failed

如需解决此问题,请修改 sql 参数中的 SELECT 语句,改为选择各个列。例如,如果您的 select 子句是 SELECT *,请将其更改为 SELECT column1, column2, ...。

如果您的架构发生变化,并且您想从头开始重新构建增量 PDT,请使用 API 调用 start_pdt_build,并添加 full_force_incremental 参数。

窗口函数在增量 PDT 中返回意外结果

不支持在增量 PDT 中直接使用 SQL 窗口函数。 由于增量构建仅处理最新时间增量和任何已配置的 increment_offset 的行,因此直接在增量 PDT 中(例如在 derived_column 或 sql 参数中)定义窗口函数可能会导致在表递增时跨分区边界出现缺失值或意外计算。

如需将窗口函数与增量 PDT 搭配使用,请将增量 PDT 定义为不含窗口函数的基础或中间 PDT,然后在应用窗口函数的单独派生表或常规 PDT 中引用该增量 PDT。

增量 PDT 支持的数据库方言

为了让 Looker 在 Looker 项目中支持增量 PDT,您的数据库方言必须支持数据定义语言 (DDL) 命令,以便删除和插入行。

下表显示了最新版 Looker 中哪些方言支持增量 PDT(对于 Databricks,仅 Databricks 12.1 及更高版本支持增量 PDT):

方言 是否支持?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica