CODEWITHSUNDEEP
google-bigquery
Pentium10
Pentium10

Reputation: 207912

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

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?

Upvotes: 4

Views: 3757

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

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

Upvotes: 7

Related Questions