Knowledge Drop
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.
LinkedIn
Twitter