Knowledge Drop

Easy Primary_Keys, no Derived Table necessary

  • 15 June 2021
  • 0 replies
  • 23 views

Userlevel 3

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.

 

This content is subject to limited support.                

 

 

 


0 replies

Be the first to reply!

Reply