partition_keys

Usage

view: view_name {
  derived_table: {
    partition_keys: [ "created_date" ]
    ...
  }
}
Hierarchy
view
derived_table
partition_keys

- or -

explore
aggregate_table
materialization
partition_keys
Default Value
None

Accepts
One or more partitioned column names

Special Rules
partition_keys is supported only on specific dialects

Definition

The partition_keys parameter supports database dialects that have the ability to partition columns. When a query is run that is filtered on a partitioned column, the database will scan only those partitions that include the filtered data, rather than scanning the entire table. Because a smaller subsection of the table is being scanned, this can significantly reduce the time and cost of querying large tables when the appropriate partition and filter are specified.

The partition_keys parameter works only with tables that are persistent, such as PDTs and aggregate tables. partition_keys is not supported for derived tables without a persistence strategy.

In addition, the partition_keys parameter is not supported for derived tables that are defined using create_process or sql_create.

When you create a persistent derived table (PDT) or an aggregate table, if your underlying database table uses partitioning, Looker can use that partitioning.

See the Dialect support for partition_keys section for the list of dialects that support partition_keys.

To add a partitioned column to a PDT or an aggregate table, use partition_keys and supply the names of the corresponding columns that are partitioned in the database table.

Examples

Create a customer_day_facts PDT on a BigQuery database with a partition key on the date column:

view: customer_order_facts {
  derived_table: {
    explore_source: order {
      column: customer_id { field: order.customer_id }
      column: date { field: order.order_time }
      derived_column: num_orders {
        sql: COUNT(order.customer_id) ;;
      }
    }
    partition_keys: [ "date" ]
    datagroup_trigger: daily_datagroup
  }
}

Create a customer_day_facts SQL-based derived table on a Presto database with partition keys on the date and state columns:

view: customer_day_facts {
  derived_table: {
    sql:
      SELECT
        customer_id,
        DATE(order_time) AS date,
        COUNT(*) AS num_orders
      FROM
        order
      GROUP BY
        customer_id ;;
    partition_keys: [ "date", "state" ]
    datagroup_trigger: daily_datagroup
  }
}

Dialect support for partition_keys

The ability to use partition_keys depends on the database dialect your Looker connection is using. In the latest release of Looker, the following dialects support partition_keys:

In BigQuery, partitioning can be used on only one table column, which must be a date/time column — so a Looker PDT based on a BigQuery table can use partitioning on only one date/time column.

Dialect Supported?
Actian Avalanche
No
Amazon Athena
Yes
Amazon Aurora MySQL
No
Amazon Redshift
No
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
Yes
Google BigQuery Standard SQL
Yes
Google Cloud PostgreSQL
No
Google Cloud SQL
No
Google Spanner
No
Greenplum
No
HyperSQL
No
IBM Netezza
No
MariaDB
No
Microsoft Azure PostgreSQL
No
Microsoft Azure SQL Database
No
Microsoft Azure Synapse Analytics
No
Microsoft SQL Server 2008+
No
Microsoft SQL Server 2012+
No
Microsoft SQL Server 2016
No
Microsoft SQL Server 2017+
No
MongoBI
No
MySQL
No
MySQL 8.0.12+
No
Oracle
No
Oracle ADWC
No
PostgreSQL 9.5+
No
PostgreSQL pre-9.5
No
PrestoDB
Yes
PrestoSQL
Yes
SAP HANA 2+
No
SingleStore
No
SingleStore 7+
No
Snowflake
No
Teradata
No
Trino
Yes
Vector
No
Vertica
No