Why BigQuery doesn't have an option to remove column?

Question

I'm looking for an option to remove a column from my BigQuery table like ALTER TABLE TABLE_NAME DROP COLUMN_NAME - but all I found in online was, drop the old table and create a new one.

I'm just wondering, Is there any logical reason not having this option in BigQuery?

Answer 1

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.

There are a couple of different options to remove a column:

• Select from the original table, excluding the column that you don't want to keep. Then copy the resulting table and overwrite the original one.
• Create a logical view over the table with the columns that you want. Now query the logical view instead of the table--you can make other "modifications" as well such as casting or filtering without having to touch the underlying table. The downside is that you will be charged for the old column that you don't need to query any more, however.

Answer 2

You could remove column via re-writing table.

CREATE OR REPLACE TABLE
  temp.table_name AS
SELECT
  * EXCEPT (column_name)
FROM
  temp.table_name