sortkeys

Usage

view: my_view {
  derived_table: {
    sortkeys: ["date"]
    ...
  }
}
Hierarchy
view
derived_table
sortkeys

- or -

explore
aggregate_table
materialization
sortkeys
Default Value
None

Accepts
The names of one or more columns in a PDT or an aggregate table

Special Rules
sortkeys is supported only on specific dialects

Definition

The sortkeys parameter lets you specify one or more columns of a persistent derived table (PDT) or an aggregate table on which to apply a regular sort key. Use a sort key to specify the columns of a PDT that will be most frequently sorted by to make querying the data faster.

See the Dialect support for sortkeys section below for the list of dialects that support sortkeys.

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.

The sortkeys parameter works only with tables that are persistent, such as PDTs and aggregate tables. sortkeys is not supported for derived tables without a persistence strategy.

In addition, the sortkeys parameter is not supported for derived tables that are defined using create_process or sql_create.

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

Examples

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 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"]
  }
}

Dialect support for sortkeys

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

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

Dialect Supported?
Actian Avalanche
No
Amazon Athena
No
Amazon Aurora MySQL
No
Amazon Redshift
Yes
Apache Druid
No
Apache Druid 0.13+
No
Apache Druid 0.18+
No
Apache Hive 2.3+
Yes
Apache Hive 3.1.2+
Yes
Apache Spark 3+
Yes
ClickHouse
No
Cloudera Impala 3.1+
No
Cloudera Impala 3.1+ with Native Driver
No
Cloudera Impala with Native Driver
No
DataVirtuality
No
Databricks
No
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11+
No
Exasol
No
Firebolt
No
Google BigQuery Legacy SQL
No
Google BigQuery Standard SQL
No
Google Cloud PostgreSQL
No
Google Cloud SQL
No
Google Spanner
No
Greenplum
No
HyperSQL
No
IBM Netezza
No
MariaDB
No
Microsoft Azure PostgreSQL
No
Microsoft Azure SQL Database
No
Microsoft Azure Synapse Analytics
No
Microsoft SQL Server 2008+
No
Microsoft SQL Server 2012+
No
Microsoft SQL Server 2016
No
Microsoft SQL Server 2017+
No
MongoBI
No
MySQL
No
MySQL 8.0.12+
No
Oracle
No
Oracle ADWC
No
PostgreSQL 9.5+
No
PostgreSQL pre-9.5
No
PrestoDB
No
PrestoSQL
No
SAP HANA 2+
No
SingleStore
No
SingleStore 7+
No
Snowflake
No
Teradata
No
Trino
No
Vector
No
Vertica
No