User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
sortkeys

Usage

view: my_view {
  derived_table: {
    sortkeys: ["date"]
    …
  }
}

Hierarchy

sortkeys

Default Value

None

Accepts

The names of one or more columns in a derived table

Definition

sortkeys enables you to specify one or more columns of a persistent derived table on which to apply a regular sort key. Currently, sortkeys only works with Redshift databases. You can also create an interleaved sort key by using indexes instead. You cannot use both at the same time, but at least one is required.

Traditional SQL dialects (such as MySQL and Postgres) should use indexes; sortkeys will not work with these databases.

Generally speaking, a sort key should be applied to date or time columns in the derived table, and possibly to columns that will be used frequently as filters. More information can be found in the Amazon Redshift documentation.

Examples

All of these examples assume that you are working with a Redshift database so that you can use the sortkeys parameter.

Create a customer_day_facts persistent native derived table (NDT) that has a sortkey on date and rebuilds when the datagroup order_datagroup is triggered:

view: customer_day_facts { derived_table: { explore_source: order { column: customer_id { field: order.customer_id } column: date { field: order.order_date } column: num_orders { field: order.customer_order_count } } datagroup_trigger: order_datagroup sortkeys: ["date"] } }

Create a customer_day_facts derived table that is based on a SQL query and has a sortkey on date:

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 ;; persist_for: "24 hours" sortkeys: ["date"] } }

Create a customer_day_facts derived table that is based on a SQL query with a sortkey on date and customer_id:

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 ;; persist_for: "24 hours" sortkeys: ["date", "customer_id"] } }

Common Challenges

sortkeys 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. The sortkeys parameter works only with persistent derived tables.

Top