autogenerate_primary_keys

Usage

 autogenerate_primary_keys 
: yes 

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