No schema auto-detect when querying External Tables in Bigquery and new data arrive

1
votes

This is the currently situation:

I've created an external table in Bigquery against json in Cloud Storage.

I'm testing how it works regarding to the schema auto-detect.

When I create the table, there were 2 json files with different schemas, and Bigquery does it well.

When I load a new file with a new schema (adding a new attribute to a record field), Bigquery recognizes the new record, but this new field doesn't appear. So the schema auto-detect doesn't work as I expected.

How can I get schema auto-detect when new files arrives to my cloud storage folder?

Any help?

1
google-bigquerygoogle-cloud-platformgoogle-cloud-storage

1 Answers

1
votes

Culprit: AFAIK the auto-schema detection happens when you create a table, and not updated as you add new files.

Possible solution:

  • Re-create the tables when new files arrive.

Straightforward implementation:

  • Add a pub/sub notification on GCS for new arriving files, have a Google Cloud Function that re-creates the table trigger on this.