Google BigQuery - Insert All with Table Suffix failing

Question

I have a project where I was previously creating tables on Insert. I am attempting to instead perform an insertAll with a templateSuffix. It seems to work great with new tables, but I have this odd case.

The following URL (https://gist.github.com/dovy/b5b5b25e660ac037aaa130294ab42e3a) provides an example insert. I have some data from a source, the desired table (table_schema.txt) and a template schema (table_template_schema.txt). The only difference between the two schemas is the order of the last 2 columns:

|- cache_file: string
|- deduped: integer

The error I get is HttpError:

https://www.googleapis.com/bigquery/v2/projects/flash-student-96619/datasets/log_data_v7/tables/day/insertAll?alt=json returned "Provided Schema does not match Table flash-student-96619:log_data_v7.day20160423. Template and generated table schemas are incompatible"

Is insertAll really that picky? There's no way to re-order columns unless I do a query and replace on the same table. That seems incredibly painful.

Any clues from anyone out there?

Answer 1

I ended up doing a standard insert without tableSuffix and if it failed (try/catch) I did a insert with tableSuffix. That bypasses this insane requirement of perfect order and all works for me.

Just wish I didn't have to get around this.