indexes

LookML
Version

On this Page
Docs Menu

Go to View Parameter List

Usage

indexes is a child of
derived_table is a child of
view
view: my_view
derived_table:
...
1st
Tab
2nd
Tab
indexes: [column_name]
one or more columns from
the derived table sql

Definition

indexes allows you to specify one or more columns of a persistent derived table on which to apply an index. When you add more than one column, Looker will create one index for each individual column that you specify; it does not create a single, multi-column index.

If you use indexes with Redshift, you will create an interleaved sort key, which is overviewed in this blog post. You can also create regular sort keys using sortkeys, but you cannot use both at the same time. Distribution keys can be created with distribution.

Generally speaking, indexes should be applied to primary keys and date or time columns.

Examples

Create a customer_order_facts persistent derived table with an index on customer_id, assuming you’re using a traditional database (MySQL, Postgres, etc):

- view: customer_order_facts derived_table: sql: | SELECT customer_id, COUNT(*) AS lifetime_orders FROM order GROUP BY customer_id persist_for: 24 hours indexes: [customer_id]

Create a customer_day_facts derived table with indexes on both customer_id and date, assuming you’re using a traditional database (MySQL, Postgres, etc):

- 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 indexes: [customer_id, date]

Create a customer_day_facts derived table with an interleaved sort key built from customer_id and date, assuming you’re using Redshift:

- 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 indexes: [customer_id, date]

Common Challenges

indexes Only Works With Derived Tables That Are Persisted

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

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