Usage

view: view_name {  derived_table: {    cluster_keys:["customer_city","customer_state"]    ...  }}

Definition

Clustering apartitioned table sorts the data in a partition based on the values in the clustered columns and organizes the clustered columns in optimally sized storage blocks. Clustering can improve the performance and reduce the cost of queries that filter on or aggregate by the clustered columns.

See theDialect support forcluster_keys section for the list of dialects that supportcluster_keys.

• Thecluster_keys parameter works only with tables that arepersistent, such as PDTs and aggregate tables.cluster_keys is not supported forderived tables without a persistence strategy.
• In addition, thecluster_keys parameter is not supported for derived tables that are defined usingcreate_process orsql_create.

To add a clustered column to apersistent derived table (PDT) or anaggregate table, use thecluster_keys parameter and supply the names of the columns you want clustered in the database table.

Examples

Create acustomer_order_factsnative derived table on a BigQuery database, partitioned on thedate column and clustered on thecity,age_tier, andgender columns to optimize queries that are filtered or aggregated on those columns:

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

Dialect support forcluster_keys

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