BigQuery insert (not append) a new column into schema

Question

Is there a convenient way (Python, web UI, or CLI) for inserting a new column into an existing BigQuery table (that already has 100 columns or so) and update the schema accordingly?

Say I want to insert it after column 49. If I do this via a query, I will have to type every single column name, will I not?

Update: the suggested answer does not make it clear how this applies to BigQuery. Furthermore, the documentation does not seem to cover

ALTER TABLE `tablename` ADD `column_name1` TEXT NOT NULL AFTER `column_name2`;

Syntax. A test confirmed that the AFTER identifier does not work for BigQuery.

Does this answer your question? Inserting new columns in the middle of a table? — Fernando C.
BigQuery doesn't allow you to insert a column in the middle right now. You'll have to recreate the table and migrate the data over. `CREATE OR REPLACE TABLE ... AS SELECT ..." allows you to do this in one operation. — Yun Zhang
@FabianBosler, do you mind explaining a little why you need to insert a column after a specific column? Is this to support SELECT * FROM ... without manually adjusting order of column or you have more complex scenario to support? — Yun Zhang
@YunZhang It's really only for cosmetic reasons. I value descriptions and structure. The table in question is a base table and I actually do expect people to look at the Schema and read the column description and I want to keep columns together that should logically be close to each other. I guess a SELECT * EXCEPT (cols_after), new col, cols after at least reduces the number of col names to type. — Fabian Bosler

Enrique Zetina Enrique Zetina · Accepted Answer · 2020-02-26T20:38:40

I think that is not possible to perform this action in a simple way, I thought in some workarounds to reach this such as:

Create a view after adding your column.
Creating a table from a query result after adding your column.

On the other hand, I can't catch how this is useful, the only scenario I can think for this requirement is if you are using SELECT * which is not recommended when using BigQuery according with the Bigquery best practices. If is not the case share your case of use to get a better understanding of it.

Since this is not a current feature of BigQuery you can file a feature request asking for this feature.

BigQuery insert (not append) a new column into schema

1 Answers