Loading Dynamic Schema Json Files Into A Bigquery Table Using Autodetect

This question is very similar to this one - but using the python API, and a couple of years later - and as the solution there is not complete, I thought it's best to open a new que

Solution 1:

In autodetect, BigQuery read a bunch of first line and try to detect the schema. After this first run, the schema is set and can't be changed during the whole ingestion. You can use the "ignore_unknown_values" option to skip the value that doesn't match the autodetected schema.

But it won't work as you wish. I know that BigQuery team work on a better JSON integration. For now, I can only recommend you to ingest the JSON as a string and to use the BigQuery JSON function to browse the documents.

Solution 2:

BigQuery natively supports JSON data using the new JSON data type, with this we can ingest semi-structured JSON into BigQuery without providing a schema for the JSON data upfront. Hope this will solve your problem.

https://cloud.google.com/bigquery/docs/reference/standard-sql/json-data