Special characters support in column names

kyotsu · 10-26-2023 02:42 AM

Hi,

We have been querying external data sources using temporary tables, with JSON schema files (which include special characters in their "name" field) for a few months. Everything worked fine until a specific day when an error came up saying "Invalid field name \"iphone6+\". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 300 characters long.".

Since then, temporary tables creation process has failed over and over again. We've noticed that in BigQuery document, it is stated that flexible column names does not applied to external tables. However, as we mentioned above, it literally worked fine before.

Is it some kind of bugs or is it just that flexible column names do support external tables before, and stop supporting recently? We would appreciate any help!

ms4446

Flexible column names are not currently supported for external tables in BigQuery. If you observed a different behavior in the past, it might have been an anomaly or a temporary change that was not officially documented.

To address the issue with external tables:

Rename the Fields: Update the JSON schema files to use valid column names. Replace special characters with underscores or other valid characters.
Use Legacy SQL: While Legacy SQL might offer different behaviors in some cases, it's important to note that Google recommends using Standard SQL for new projects. If you choose to use Legacy SQL, ensure that you set the queryJobConfig.useLegacySqlproperty to true in your request.

For example:

{
 "queryJobConfig": {
  "useLegacySql": true
 }
}

Always refer to the official Google Cloud BigQuery documentation: https://cloud.google.com/bigquery/docs for the most up-to-date and accurate information.