Support expression-aggregations for BigQuery

[VikramTiwari]

Feature requests and proposals

Enable bigquery to use expression-aggregations by slugging the auto-generated column names

• Does this mean we have to slug the sql query here? https://github.com/metabase/metabase/blob/master/src/metabase/driver/bigquery.clj#L272

I am trying to make this work? Could anyone help?

[camsaul]

It's not currently enabled because:

Expression aggregations would work, but BigQuery doesn't support the auto-generated column names. BQ column names
can only be alphanumeric or underscores. If we slugified the auto-generated column names, we could enable this feature.

Just need to add some logic to BQ query processor to make the column names BQ-friendly

[camsaul]

Related: #4087 and #4089

[VikramTiwari]

@camsaul So this works fine. Except for one edge case:

If I create two custom expressions which are either same or similar in formula

cost per click = sum(cost)/sum(clicks)
cost per impression = sum(cost)/sum(impressions)

The resultant columns are same : sum___sum This results in an error from bigquery itself which results in metabase throwing following error:

2.101 - 2.195: Ambiguous column name sum___sum.

My suggestion is to re-open the issue and fix it using following strategies:

• similar formula: pass column name along to create the query to BigQuery's AS statement as sum_column_name__sum_column_name
• same formula: Catch it in the UI and ask the user to either give them different names or different formula

@salsakran Thoughts?

[camsaul]

@VikramTiwari I just fixed that this morning, see #4089/#4753 😉

[VikramTiwari]

@camsaul Sigh! I pulled it yesterday and did test just now. haha Kudos to you! 👍

[VikramTiwari]

Since I could not find any existing issue:
Are there any plans to change these sum__sum to something better on UI side? Preferably without underscores. Even if I give a name to an expression, the name remains underscored.

@camsaul @tlrobinson