How do we handle quoted field names?

[lloydtabb]

Most SQL dialects allow field names contain just about any character so long as they are quoted. The exception here is BigQuery which still only allows r/[A-z_][A-z0-9_]*/ even when a backquote is applied.

Spaces in field names are really useful in that you can use the display name of a field to show in the result. When reading a spreadsheet, the column heading is usable as a field name, regardless of what it contains. Data at rest needs lest conversion to be useful.

Malloy already supports backquote as a mechanism to escape keywords but it would be really nice to support arbitrary strings.

For example this model can be programmed directly against a CSV provided by data.gov.

sql: ||
  SELECT * FROM read_csv('https://data.transportation.gov/api/views/6axg-epim/rows.csv')
;; on "duckdb"

source: recall is table('duckdb:/usr/local/google/home/ltabb/tmp/recall.csv') {
  declare:
    recall_count is count()

  query: by_manufacturer is {
    group_by: `Manufacturer`
    aggregate: recall_count
  }

  query: by_type is {
    group_by: `Recall Type`
    aggregate: recall_count
  }

  query: by_year is {
    group_by: recall_year is `Report Received Date`.year
    aggregate: recall_count
  }

  query: `By Action` is {
    group_by: `Corrective Action`
    aggregate: recall_count
  }

  query: detail is {
    project: 
      `NHTSA ID`, 
      `Report Received Date`
      Manufacturer,
      Subject, 
      `Recall Type`
      `Recall Description`
  }

  query: `dashboard` is by_manufacturer + {
    nest: by_year + {limit: 10}
    nest: by_type
  }
}

And the result can look like

The problem here is how do we degrade in BigQuery? On schema read, we could easily convert illegal characters to underbar. The problem is Malloy models that have output identifiers that would be illegal in BigQuery, how would we map them? Could we store a map with the result? Could we make something that would fail gracefully?

[colin-recurve]

There could also be collisions when converting illegal characters to underbar. For example, with fields like `Savings$` and `Savings%`. Perhaps, if conversion is necessary, there could be a mapping that includes a prefix or postfix with the integer number of backquoted fields in the model. The field order doesn't change, so perhaps a suffix or postfix like _1_, _2_, _3_ could be used for a bidirectional mapping. No mapping would need to be stored, ideally - the reverse mapping could be reconstructed from the model.

[lloydtabb]

Spaces in fieldnames in BigQuery is landing today. It turns out they implemented them and there were some subtle changes to the language to get it to all work properly.

#1153

[lloydtabb]

Please leave further comments in the pull request if you have them #1153