How to Drop a Column in BigQuery
In most databases, it's easy and common to drop a column. In BigQuery data warehouse? Not so much.
You can't use a command like
ALTER TABLE TABLE_NAME DROP COLUMN_NAME in BigQuery, nor is there a flow in the BigQuery web UI.
Before you get all 😡 at BigQuery, it's important to realize why this common command is missing.
According to Elliott Brossard who helped build BigQuery:
"Dropping a column would mean removing the data from all of the Capacitor files that make up the table, which is an expensive operation. If BigQuery were simply to remove metadata related to the column, you would be charged storage for a phantom column that you can't actually query, which wouldn't be ideal. When you add a column, conversely, BigQuery treats the missing column in past files as having all NULL values, which doesn't require modifying them."
Workaround to Dropping a Column in BigQuery
Select from the original table, but use the
except command to exclude the column you don't want. You can then save the results as a new BigQuery table. Here's an example:
select * except(author, ranking) from `bigquery-public-data.hacker_news.comments` limit 10;
BigQuery table as your desired option:
It's not a perfect solution, but it may work for you. Use with care.