We can easily add new column for a table in BigQuery:

ALTER TABLE mydataset.mytable
      ADD COLUMN new_col STRING

But when you want to delete or rename an existed column, there is no SQL to implement it. The only way to delete or rename an existed column is to use the bq command:

bq show --format=prettyjson mydataset.mytable > schema.json
# Edit the schema.json to only leave a list of columns
bq mk --table mydataset.new_mytable schema.json
# Export data from `mytable` to `new_mytable`
bq rm --table mydataset.mytable
bq cp --table mydataset.new_mytable mydataset.mytable

And remember to backup your data before operating!

Related Posts