cluster_keys

Usage

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

Definition

Clustering a partitioned 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 the Dialect support for cluster_keys section for the list of dialects that support cluster_keys.

To add a clustered column to a persistent derived table (PDT) or an aggregate table, use the cluster_keys parameter and supply the names of the columns you want clustered in the database table.

Examples

Create a customer_order_facts native derived table on a BigQuery database, partitioned on the date column and clustered on the city, age_tier, and gender 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 for cluster_keys

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