Easy Primary_Keys, no Derived Table necessary (Community)

View the original community article here

Last tested: Sep 3, 2020
 

Well hello good Looker! Ever wonder if you could easily generate a unique PK to solve those pesky non-unique primary-key errors? Don't want to add another derived_table to your impressive list of view files to do so? Then this post is for you.

While not best practice in most contexts, you can write a subquery in the sql_table_name LookML parameter.

# For BigQuery and other dialects with a uuid function:

sql_table_name: ( select generate_uuid() as new_id, * FROM schema.table ) ;;

# For other dialects, use row number:

sql_table_name: ( select ROW_NUMBER () OVER (order by some_field) as new_id, * from schema.table ) ;;

# Create primary_key dimension from our new field

dimension: new_id {

type: string

primary_key: yes

}

Note: On MySQL, derived_tables requires CREATE TABLE permissions on the temp schema. The only alternative for users who don't want to grant this access, but still need to use a window function to generate a PK, is this workaround.