Dynamic table name setting by using parameter for table with hypen

Looker Q&A Looker
1

Hi team,

In a SQL template, I am trying to dynamically set the table name using a parameter. Since it is a table name, when I use the parameter type string, it includes single quotes at each end and results in a BigQuery error. So, I tried to use an unquoted type parameter, but this time it seems that hyphens - are not allowed.

Can anyone help with this?

A huge thanks in advance…

view: daniel_song_test {
  
  parameter: table_name {
    type: unquoted
    default_value: "`a-1-c.x-1.table`"
    allowed_value: { label: "1" value: "`a-1-c.x-prod.table`" }
    allowed_value: { label: "2" value: "`a-2-c.y-prod.table`" }
    allowed_value: { label: "3" value: "`a-3-c.z-prod.table`" }
  }

  filter: filter_date {
    type: date_time
  }

  derived_table: {
    sql:
      SELECT
        timestamp_trunc(timestamp, day) ts_dt,
        count(1) as count
      FROM
        {% parameter table_name %}
      WHERE
        {% condition filter_date %} timestamp {% endcondition %}
      GROUP BY 1
      ;;
  }

  dimension: ts_dt {
    type: date
    sql: ${TABLE}.ts_dt ;;
  }

  measure: count {
    type: sum
    sql: ${TABLE}.count ;;
  }
}
2

Hi Daniel - when you say ‘hyphens are not allowed’, are you getting a LookML error, or an error from your database? Some info about the error you are seeing would be helpful in figuring out the problem.

3

Hi Gavin!

I meant a LookML error! I guess hyphen is not allowed for unquoted type!

https://cloud.google.com/looker/docs/reference/param-dimension-filter-parameter-types#unquoted

4

Hmm…I can’t think of a clever workaround for this - the limited characters allowed in unquoted parameters is a security feature. So the solution to this might be on the BigQuery side - renaming the tables to have underscores rather than hyphens, or perhaps creating views of those tables in BigQuery that do the same?

5

That is true… But the problem is our company is using hyphen in the gcp project name..

Thanks. Does anyone have clever idea on this issue?!