Why do I see `_1` appended to field name aliases in generated SQL

Knowledge Drop

Last tested; Mar 24, 2020
 

This happens when a field name has upper-case letters. Because LookML is case sensitive, we append a _1 to the field name alias in the generated SQL to avoid any potential collision with a field that has the same name with different case.

Generated SQL looks something like:

 

SELECT

view.field_name AS view_field_name_1

FROM `bigquery-table-stuff`

For a field that is simply:

 

dimension: Field_Name {

type: string

sql: ${TABLE}.field_name ;;

}

Example:

 

### Capital Letters ###

explore: NEW_EXPLORE {

from: breeds

join: not_bites {

from: bites

type: left_outer

sql_on: ${not_bites.breed} = ${NEW_EXPLORE.breed_name} ;;

relationship: one_to_one

}

}

### GENERATED SQL ###

SELECT

NEW_EXPLORE.breed_name AS new_explore_breed_name_1,

1=1 AS new_explore_dummy_1

FROM dogs_of_new_york.breeds AS NEW_EXPLORE

GROUP BY 1

ORDER BY 1

### Lower case ###

explore: new_explore {

from: breeds

join: not_bites {

from: bites

type: left_outer

sql_on: ${not_bites.breed} = ${new_explore.breed_name} ;;

relationship: one_to_one

}

}

### Generated SQL ###

SELECT

new_explore.breed_name AS new_explore_breed_name,

1=1 AS new_explore_dummy

FROM dogs_of_new_york.breeds AS new_explore

GROUP BY 1

ORDER BY 1

This content is subject to limited support.                

Version history
Last update:
‎07-07-2021 01:14 PM
Updated by: