Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

partition_keys

Avatar
Looker Support
  • Updated
Follow

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 below 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 Looker 22.6, 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.