autogenerate_primary_keys

Usage

  autogenerate_primary_keys: yes
Hierarchy
view
autogenerate_primary_keys

- or -

explore
autogenerate_primary_keys
Default Value
No

Special Rules

Definition

When the autogenerate_primary_keys parameter is enabled, Looker generates a temporary, distinct key for a view when one is not explicitly defined in your LookML. The autogenerated primary key allows symmetric aggregates and one-to-many counts to be calculated correctly without any changes to your underlying database table. The primary key is generated at query runtime; it does not persist across queries.

The autogenerate_primary_keys parameter is supported for both views and Explores:

  • For views, if you define the view with autogenerate_primary_keys: yes, Looker generates a primary key for queries on the view where a primary key is needed to avoid duplicating rows. If you add autogenerate_primary_keys: yes to a view that already has a field defined as a primary key, the Looker IDE displays a LookML validation warning.

  • For Explores, if you define the Explore with autogenerate_primary_keys: yes, Looker generates a primary key for each of the Explore's views that are required for an Explore query where a primary key is needed to avoid duplicating rows:

    • Looker generates primary keys only for the views in the Explore that don't have a field defined with primary_key: yes. If a view has a field defined with primary_key: yes, Looker uses the view's primary key.
    • Looker generates a primary key for an Explore's view only when the view is being queried through that Explore. If the same view is being queried through a different Explore that isn't defined with autogenerate_primary_keys: yes, and the view itself isn't defined with autogenerate_primary_keys: yes, Looker won't generate a primary key for the view.

Example

Here is an orders Explore that joins an order_items view. The Explore is defined with autogenerate_primary_keys: yes:

explore: orders {
  autogenerate_primary_keys: yes
  join: order_items {
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }
}

For queries on the orders Explore that require symmetric aggregates or calculations where rows from a view may be duplicated, Looker automatically generates a distinct key for the orders or order_items views.

Things to know

Note the following about using autogenerate_primary_keys: yes:

  • Enabling autogenerate_primary_keys can change the behavior of your existing queries. Measures that Looker previously had to drop from queries because of a missing primary key will now be included in the results, which could be a breaking change for some reports.
  • Autogenerating primary keys may increase query time due to the extra computation required.
  • When Looker generates a primary key for a query, that primary key is a temporary runtime primary key that can't be selected or visualized in an Explore.

Dialect support for autogenerate_primary_keys

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

  • Google BigQuery Standard SQL
  • Amazon Redshift
  • Amazon Redshift 2.1+
  • Amazon Redshift Serverless 2.1+
  • Google Cloud PostgreSQL
  • Greenplum
  • Microsoft Azure PostgreSQL
  • MySQL 8.0.12+
  • PostgreSQL pre-9.5