Cannot add a column after deleting another column in BigQuery

Stan 0
Viewed 70

I cannot imagine there is such issue in BigQuery:
le's say if I drop a column using below command in BQ console for User table:
Alter table User drop column name -> successful

I am aware this column is preserved for 7 day(for time travel duration purpose).

But I cannot add any column anymore by running below command in BQ console:
ALTER TABLE User add column first_name STRING

Cause it will give an error like below even though the two columns have totally different naming:
Column name was recently deleted in the table User. Deleted column name is reserved for up to the time travel duration, use a different column name instead.

The above error is same as when I try to drop the same column again even with IF EXISTS: Alter table User drop IF EXISTS column name

My question:

  • Why is this issue will happen? After 7 days, Can I add new columns as usual?
google-bigquery
2 Answers

I have recreated your issue wherein I dropped a column named employee_like_2 and then tried to add a new column named new_column.

enter image description here

There is already a created bug for this issue. You may click on +1 to bring more attention on the issue and STAR the issue so that you can be notified for updates.

For the meantime, a possible workaround is to manually add columns through BigQuery UI.

enter image description here enter image description here

userMTlc10

Apart from the solution using UI suggested @Scott B, we can also do it using bq command:

Basically bq query --use_legacy_sql=false 'ALTER TABLE User add column first_name STRING' will fail to add a column. But I found a workaround I can run bq update command instead like below:

  1. bq show --schema --format=prettyjson DATASET.User > user_schema.json
  2. Add a new column I want into file user_schema.json
  3. bp update DATASET.User user_schema.json

So this basically means it is a 100% bug in BigQuery SQL command

Stan0
Related