Using CLI for data load into column type partitioning: Incompatible table partitioning specification

Question

At a simple BQ load some CSVs to a new schema using this cmd we get the below error:

bq load --time_partitioning_field saved_timestamp
--skip_leading_rows=1 --max_bad_records=100 --allow_jagged_rows 
--replace --source_format=CSV --ignore_unknown_values  TABLE gs://.../export*.gz schema.json

Incompatible table partitioning specification. Expects partitioning specification none, but input partitioning specification is interval(type:day,field:saved_timestamp)

My expectation would be to create a column type partitioning column. What's wrong?

Also can we use the same syntax to specify Clustering?

Does the table exist already? It sounds like the problem is that the table exists but isn't partitioned, so the new schema is incompatible. Can you delete the existing table first? — Elliott Brossard
@ElliottBrossard good catch, assuming --replace would work, that was the issue. Anyway you could improve de error message, it's really misleading, or setup a blog page with all the possible errors, submit this as answer — Pentium10
I added an answer with a workaround using the CLI, and I requested on the issue tracker to improve the error message. — Elliott Brossard

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-08-29T16:04:53

At the time of this writing, there is a limitation that you cannot replace a table and change the partitioning specification at the same time, which is listed as a limitation of CREATE TABLE statements as well. When using the BigQuery CLI, you can:

Load the data into a new table with the desired partitioning/clustering spec,
Drop the table intended to be replaced (bq rm dataset.table),
Copy the new table to the name of the old one (bq cp dataset.new_table dataset.table), then
Drop the new table (bq rm dataset.new_table).

Using CLI for data load into column type partitioning: Incompatible table partitioning specification

1 Answers