partition_keys

LookML
Version

On this Page
Docs Menu

Go to View Parameter List

Usage

partition_keys is a child of
derived_table is a child of
view
view: my_view
derived_table:
...
1st
Tab
2nd
Tab
partition_keys: [column_name]
a column from
the derived table

Definition

partition_keys 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 only scan 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 PDTs when the appropriate partition and filter is specified.

When creating a persistent derived table (PDT), if your underlying Presto or BigQuery database table uses partitioning, Looker can use that partitioning. In BigQuery, partitioning can only be used on one table column which must be a date/time column — so a Looker PDT based on a BigQuery table can only use partitioning on one date/time column.

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

Examples

Create a customer_day_facts native derived table 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, state, DATE(order_time) AS date, COUNT(*) AS num_orders FROM order GROUP BY customer_id partition_keys: [ date, state ] datagroup_trigger: daily_datagroup

Common Challenges

partition_keys Only Works With Derived Tables That Are Persisted

Derived tables can be calculated at query time, or they can be made persistent using datagroup_trigger, persist_for, or sql_trigger_value. partition_keys only works with persistent derived tables.

Still have questions?
Go to Discourse - or - Email Support
Top