BigQuery Drop or Change Column

Mark Scannell
Sep 6 · 1 min read

BigQuery DDL doesn’t support altering tables, but it does support re-writing tables. You can use this to achieve the same effect.

Create a Table

For example, if you create a table as follows:

CREATE OR REPLACE TABLE `transactions.test_table` AS
SELECT
'' AS a,
1 AS b,
TRUE AS c
LIMIT
0;

You will have three columns (a, b, c) with respective types (STRING, INTEGER, BOOLEAN).

Change column b to an FLOAT

CREATE OR REPLACE TABLE `transactions.test_table` AS
SELECT
* EXCEPT (b),
CAST(b AS FLOAT64) AS b
FROM
`transactions.test_table`;

Drop column c

CREATE OR REPLACE TABLE `transactions.test_table` AS
SELECT
* EXCEPT (c)
FROM
`transactions.test_table`;

Be careful!

As of writing this will remove any table history so be very careful of what you are doing. Please be sure that you backup your critical data and test your SQL before running on production.

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Mark Scannell

Written by

Strategic Cloud Engineer at Google Cloud

Google Cloud Platform - Community

A collection of technical articles published or curated by Google Cloud Platform Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade