Usage
view: my_view {
derived_table: {
increment_key: ["created_date"]
increment_offset: 1
...
}
}
|
Hierarchy
increment_offset- or - increment_offset |
Default Value
0
Accepts
An integer
Special Rules
|
Definition
You can create incremental PDTs in your project if your dialect supports them. An incremental PDT is a persistent derived table (PDT) that Looker builds by appending fresh data to the table, instead of rebuilding the table in its entirety. See the Incremental PDTs documentation page for more information.
The increment_key parameter specifies the time period for which fresh data should be queried and appended to the aggregate table. The increment_offset parameter is an optional parameter you can use if you want to rebuild the table for previous time periods at the same time that fresh data is appended to the table. The increment_offset parameter defines the number of previous time periods that will be rebuilt when appending data to an incremental PDT.
The increment_offset parameter is useful in the case of late-arriving data, where previous time periods may have new data that wasn't included when the corresponding increment was originally built and appended to the PDT.
The default increment_offset value is 0, which means that only the new data from the current increment is appended to the table. If you set the increment_offset to 1, late-arriving data from the previous increment will be added to the table in addition to the new data from the current time increment.
See the Incremental PDTs documentation page for some example scenarios that illustrate how incremental PDTs work and that show the interaction of
increment_key,increment_offset, and persistence strategy.
See the Supported database dialects for incremental PDTs section on this page for the list of dialects that support incremental PDTs.
Example
For example, this PDT is rebuilt in increments of one day (increment_key: event_day), going back three days (increment_offset: 3):
derived_table: {
datagroup_trigger: usagetable_etl
increment_key: "event_day"
increment_offset: 3
explore_source: events {
column: name { field: account.name }
column: account_id { field: account.id }
column: database_dialect {}
column: count_events {}
column: event_day {}
}
}
Supported database dialects for incremental PDTs
For Looker to support incremental PDTs in your Looker project, your database dialect must support Data Definition Language (DDL) commands that enable deleting and inserting rows.
The following table shows which dialects support incremental PDTs in the latest release of Looker:
| Dialect | Supported? |
|---|---|
| Actian Avalanche | No |
| Amazon Athena | No |
| Amazon Aurora MySQL | No |
| Amazon Redshift | Yes |
| Apache Druid | No |
| Apache Druid 0.13+ | No |
| Apache Druid 0.18+ | No |
| Apache Hive 2.3+ | No |
| Apache Hive 3.1.2+ | No |
| Apache Spark 3+ | No |
| ClickHouse | No |
| Cloudera Impala 3.1+ | No |
| Cloudera Impala 3.1+ with Native Driver | No |
| Cloudera Impala with Native Driver | No |
| DataVirtuality | No |
| Databricks | Yes |
| Denodo 7 | No |
| Denodo 8 | No |
| Dremio | No |
| Dremio 11+ | No |
| Exasol | No |
| Firebolt | No |
| Google BigQuery Legacy SQL | No |
| Google BigQuery Standard SQL | Yes |
| Google Cloud PostgreSQL | Yes |
| Google Cloud SQL | No |
| Google Spanner | No |
| Greenplum | Yes |
| HyperSQL | No |
| IBM Netezza | No |
| MariaDB | No |
| Microsoft Azure PostgreSQL | Yes |
| Microsoft Azure SQL Database | No |
| Microsoft Azure Synapse Analytics | Yes |
| Microsoft SQL Server 2008+ | No |
| Microsoft SQL Server 2012+ | No |
| Microsoft SQL Server 2016 | No |
| Microsoft SQL Server 2017+ | No |
| MongoBI | No |
| MySQL | Yes |
| MySQL 8.0.12+ | Yes |
| Oracle | No |
| Oracle ADWC | No |
| PostgreSQL 9.5+ | Yes |
| PostgreSQL pre-9.5 | Yes |
| PrestoDB | No |
| PrestoSQL | No |
| SAP HANA | No |
| SAP HANA 2+ | No |
| SingleStore | No |
| SingleStore 7+ | No |
| Snowflake | Yes |
| Teradata | No |
| Trino | No |
| Vector | No |
| Vertica | Yes |