Extensions: firestore-bigquery-export: Cannot generate schema view where field name is SQL keyword

[Polarisation]

• Extension name: firestore-bigquery-export
• Extension version: 0.1.7

Steps to reproduce:

After Importing a Firestore Collection to BigQuery I wanted to generate a schema view.

The collection contained a field with name order which is an SQL keyword, used to order queries.

I created a schema saved as docs_schema.json:

{
  "fields": [
    {
      "name": "order",
      "type": "number"
    }
  ]
}

(I included only the problematic field here, in practice there were others).

Then I ran the script to generate a view:

npx @firebaseextensions/fs-bq-schema-views \
  --non-interactive \
  --project="example-project" \
  --dataset="firestore_export" \
  --table-name-prefix="docs" \
  --schema-files=./docs_schema.json

Actual result

This results in an error:

Query:
SELECT
  document_name,
  document_id,
  timestamp,
  operation,
  `example-project.firestore_export.firestoreNumber`(JSON_EXTRACT_SCALAR(data, '$.order')) AS order,
FROM
  `example-project.firestore_export.docs_raw_changelog`
{"code":400,"errors":[{"message":"Syntax error: Unexpected keyword ORDER at [5:92]","domain":"global","reason":"invalidQuery","location":"q","locationType":"parameter"}],"response":{"headers":{"alt-svc":"h3-29=\":443\"; ma=2592000,h3-27=\":443\"; ma=2592000,h3-T050=\":443\"; ma=2592000,h3-Q050=\":443\"; ma=2592000,h3-Q046=\":443\"; ma=2592000,h3-Q043=\":443\"; ma=2592000,quic=\":443\"; ma=2592000; v=\"46,43\"","cache-control":"private","content-encoding":"gzip","content-type":"application/json; charset=UTF-8","date":"Wed, 26 Aug 2020 09:48:31 GMT","server":"ESF","transfer-encoding":"chunked","vary":"Origin, X-Origin, Referer","x-content-type-options":"nosniff","x-frame-options":"SAMEORIGIN","x-xss-protection":"0"}},"message":"Syntax error: Unexpected keyword ORDER at [5:92]"}
Syntax error: Unexpected keyword ORDER at [5:92]

Expected result

I expected a view would be created. In practice, it might not be possible to do so for a field with the keyword "order". In which case, it would be best to give a specific error message recommending a way to rectify the error.

The solution could be to add an additional name to the JSON schema for use in the generated view. Something like this:

{
  "fields": [
    {
      "name": "order",
      "type": "number",
      "view-name": "ordering_index",
    }
  ]
}

The field in the view would then be created as ordering_index.

[russellwheatley]

Hey @Polarisation, thanks for bringing this to our attention. We'll consider what course of action to take after assessing our options.

[cosminc03]

Also interested into this because I found the same bug few days ago

[i14h]

@jhuleatt this is waiting for a testing plan from you

[DanSchick]

Thank you for this, I just tried the code from your pull review as I had a field named timestamp in my collection that was a duplication of the timestamp filed the script includes as a column which threw the same error. It worked for that as well as SQL keywords. 👍