1
Change the schema of BigQuery tables
source link: http://www.donghao.org/2021/03/11/change-the-schema-of-bigquery-tables/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Change the schema of BigQuery tables
We can easily add new column for a table in BigQuery:
ALTER TABLE mydataset.mytable
ADD COLUMN new_col STRINGPython
xxxxxxxxxx
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
Python
xxxxxxxxxx
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!
Like this:
Loading...
BigQuery
Leave a comment
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK