Are we able to edit the schema of bigquery table after creation?
Solution 1
It looks that you can not change the schema after creation of the table, but you can rename the columns by looking at the suggestions at this SO post: https://stackoverflow.com/questions/42395612/update-big-query-table-schema
Solution 2
The BigQuery Doesn't allow us to modify the table schema directly. Although we can add new columns using Web UI. There are two way we can modify the Schemas after creation both of these approaches need us to recreate the table.
Method 1 Using SQL:
Write a SQL query in Query editor to Select all the required columns except the ones you want to modify.
Go to query Setting
Set Destination table having same as the Original one
Select write Preference as "Overwrite Table"
Save and run query.
Method 2 Using CLI This is an elegant way to modify the existing Schema.
Run bq show --schema --format=prettyjson project_id:dataset.table > schema_file
where you need to specify project, dataset and table path.
Define "schema_file" having .json format in above command.
Modify the Mode or Name in the Json file
Update the existing table using bq update project_id:dataset.table schema
Related videos on Youtube
Kim
Updated on September 18, 2022Comments
-
Kim almost 2 years
I made a mistake of specifying a field as integer instead of float. I found that I am not able to make correction a field once the table is created. I have to delete and re-create the table again to make things right. Does anyone know of a better way to modify a field after a table is created? Thanks.