After removing a column from a table by:
ALTER TABLE MyTable
DROP COLUMN IF EXISTS MyColumn
In BigQuery UI I Can see that the column was deleted successfully & I can't query the specific column but when I query DDL I can see that the column still exists in the scheme:
SELECT DDL FROM MyDataSet.INFORMATION_SCHEMA.TABLES
WHERE DDL LIKE '%MyTable%'
What am I doing wrong?
This is a nasty, undocumented side effect of Bigquery's Time Travel. Time Travel makes it unsafe to use ALTER TABLE statements in bigquery.
Demonstration of problem:
create table apu.time_travel_problem
( id int64
, name string
);
select column_name, data_type
FROM apu.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'time_travel_problem';
| column_name | data_type |
|---|---|
| id | INT64 |
| name | STRING |
This is all normal so far, but after an ALTER TABLE everything goes odd:
alter table apu.time_travel_problem drop column name;
select column_name, data_type
FROM apu.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'time_travel_problem';
| column_name | data_type |
|---|---|
| id | INT64 |
| name | STRING |
The column we just dropped is still there!
Now try this:
alter table apu.time_travel_problem add column name string;
Column `name` was recently deleted in the table `time_travel_problem`. Deleted column name is reserved for up to the time travel duration, use a different column name instead.
Solution:
Do not use ALTER TABLE in bigquery. Instead DROP and reCREATE using a temporary table.
This is a jinja template which I use:
/* {{TABLE}} */
CREATE TABLE IF NOT EXISTS {{DATASET}}.{{TABLE}}_migration
OPTIONS (expiration_timestamp = timestamp_add(CURRENT_TIMESTAMP(), INTERVAL 8 HOUR))
AS SELECT * FROM {{DATASET}}.{{TABLE}};
DROP TABLE {{DATASET}}.{{TABLE}};
CREATE TABLE {{DATASET}}.{{TABLE}}
(
{{COLUMN_DDL}}
);
INSERT INTO {{DATASET}}.{{TABLE}}
(
{{COLUMN_LIST}}
)
SELECT
{{COLUMN_LIST}}
FROM {{DATASET}}.{{TABLE}}_migration;