partition_keys

Usage

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

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 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.