Appending data to a table created from an Avro file in BigQuery

2
votes

Every morning, an automatic job creates a new table from an Avro file. In the afternoon, I would need to append some data to this table from a Query.

When trying to do so, I get the following error:

Error: Invalid schema update. Field chn has changed mode from REQUIRED to NULLABLE

I noticed that I can change the property of the field chn from REQUIRED to NULLABLE in the BigQuery Web UI and then it works fine, but I would have to do it manually everyday which is not what I am looking for.

Is there a way to "cast" the field as REQUIRED during the append query ?

Or during the first import from the Avro file, force the field to be NULLABLE and not REQUIRED ?

Thanks !

1
google-bigqueryavro

1 Answers

3
votes

The feature that allows relaxing a field as part of a query or a load job will be available in production shortly. I will update this answer when it goes live (likely within a week).

Update: 08/25/2016

You can supply schemaUpdateOptions in load or query job configuration. Multiple options can be provided. It allows the schema of the destination table to be updated as a side effect of the load or query job. Schema update options are supported in two cases:

  • When writeDisposition is WRITE_APPEND
  • When writeDisposition is WRITE_TRUNCATE and the destination table is a partition of a table, specified by partition decorators For non-partitioned tables, WRITE_TRUNCATE will always overwrite the schema.

    • The following values are supported:

  • ALLOW_FIELD_ADDITION: allow adding a nullable field to the schema
  • ALLOW_FIELD_RELAXATION: allow relaxing a required field in the original schema to nullable

    • NOTE: This doesn't currently work with schema auto-detection. We plan to support that soon.