Ignore unknown values on append

Question

I am running into a bit of an issue when appending a JSON blob to an existing table in BigQuery; specifically, the existence of new, but not necessary, leaf elements in the JSON being appended is preventing me from appending the data.

Using the UI, I am able to create a table using a JSON file from Google Cloud Storage, and after describing the schema, I can select to "Ignore Unknown Values". From the definition:

Enable to accept rows that contain values that do not match the schema. Unknown values will be ignored.

This works extremely well for the initial table creation and import from GCS, and is a feature I would like to continue to leverage for appends. However, when I try to append data to the table using the bq command line tool, I am getting errors such that individual leaf elements of the JSON blob do not fall into the schema.

Has anyone experienced similar issues? Have a quick solve? I know that I could pre-process the JSON itself to remove the elements that I do not need, but I feel like the "Ignore Unknown Values" should be a command line flag.

Unless someone has a specific reason why this is not implemented, I may file a feature request.

Thanks!

Answer 1

Use the command line option: --ignore-unknown-values

Use bq load --help to see all your options.