PopSQL

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;

Then click Save Results:

bigquery ui drop column

And choose BigQuery table as your desired option:

bigquery ui save new table

It's not a perfect solution, but it may work for you. Use with care.

database icon
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery