Naming fields - BigQuery Custom SQL

Data Studio Data Studio Q&A
, ,
1

Hi there,

I’m new at testing custom SQL from BQ source and struggle at finding a way to add proper naming conventions.

I feel like Looker Studio is more limited than BigQuery in standalone.

I would like to have clean fields to ensure readability, but the only version with snake_case__version works and is unpleasant to work with in LS.

SELECT
  -- ... Other things

  -- --- METRICS - CURRENT ---
  CASE WHEN period_type = 'ACTUEL' THEN visits ELSE 0 END AS "visits (actuel)", -- Does not work
  CASE WHEN period_type = 'ACTUEL' THEN visits ELSE 0 END AS `visits (actuel)`, -- Does not work but would work with BQ standalone
  CASE WHEN period_type = 'ACTUEL' THEN visits ELSE 0 END AS visits__actuel -- WORKS

  -- --- METRICS - COMPARED ---
  CASE WHEN period_type = 'COMPARÉ' THEN visits ELSE 0 END AS "visits (comparé)", -- Does not work
  CASE WHEN period_type = 'COMPARÉ' THEN visits ELSE 0 END AS `visits (comparé)`, -- Does not work but would work with BQ standalone
  CASE WHEN period_type = 'COMPARÉ' THEN visits ELSE 0 END AS visits__compare -- WORKS

FROM blblblbl

I would appreciate to have the “visits (actuel|comparé)” working if possible.

Ty in advance ! :slight_smile:

2

What about sending the field as is, but rename directly in your Looker Studio data source? You can do so by clicking on the fields directly

changing field names
changing field names456×367 16.1 KB

3

Terrible idea since the objective is to have the naming clear at the moment you plug the datasource in.

Objective is not to have :

  • Naming convention 1 : In the SQL
  • Naming convertion 2 : By manually renaming 500+ fields across all datasources.

It would 10x lower the productivity to have this approach.

I know you can choose the naming you want when you build a custom Google Apps Script connector, but can’t figure out how to make it work with the Custom SQL one.

4

I also tried to follow the flexible column name from BQ (I know we talk about Looker Studio, but if i respect this convention, I would lower the chances to meet an error in LS).

Doc : Spécifier un schéma  |  BigQuery  |  Google Cloud Documentation

Format :

{snake_case_dimension_or_metric} - {suffix_no_unicode_no_accents}

Test :

SELECT
  -- ... Other things

  -- --- METRICS - CURRENT ---
  CASE WHEN period_type = 'ACTUEL' THEN visits ELSE 0 END AS `visits - actuel` -- NOT WORKING

  -- --- METRICS - COMPARED ---
  CASE WHEN period_type = 'COMPARÉ' THEN visits ELSE 0 END AS `visits - compare` -- NOT WORKING

FROM blblblbl

Can’t make it work :confused:

5

Ah, the 500+ fields wasn’t specified in the original question. Not the same scale then ^^

So, unfortunately I guess you just can’t, the only workaround that I can think of (that is not re-building a custom connector, far from being productive either) would be to use a BQ to GSheet data connector and then use the GSheet Extract as a data source, would accept “ “ in name, but again, 500 fields + potential large amount of data + adding a bridge in between, nogo either…