How to export AVRO files from a BigQuery table with a DATE column and load it again to BigQuery

1
votes

For moving data from a BigQuery (BQ) table that resides in the US, I want to export the table to a Cloud Storage (GCS) bucket in the US, copy it to an EU bucket, and from there import it again.

The problem is that AVRO does not support DATE types, but it is crucial to us as we are using the new partitioning feature that is not relying on ingestion time, but a column in the table itself.

The AVRO files contain the DATE column as a STRING and therefore a Field date has changed type from DATE to STRING error is thrown, when trying to load the files via bq load.

There has been a similar question, but it is about timestamps - in my case it absolutely needs to be a DATE as dates don't carry timezone information and timestamps are always interpreted in UTC by BQ.

It works when using NEWLINE_DELIMITED_JSON, but is it possible to make this work with AVRO files?

1
google-bigqueryavro
Why do you have to use AVRO? If it works with NJSON, then why not just do it that way? Or CSV. - Graham Polley
AVRO includes a schema, JSON and CSV do not - Tobi
Please see the feature request for logical types. You can also sign up for the whitelist. - Elliott Brossard
Awesome @ElliotBrossard ! I signed up to have the project whitelisted - Tobi
Just specify the schema when you load it back in though. Is that a problem for you? - Graham Polley

1 Answers

1
votes

As @ElliottBrossard pointed out in the comments, there's a public feature request regarding this where it's possible to sign up for the whitelist.