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?

Answer 1

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).