0

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.

Fabian Bosler
  • 2,310
  • 2
  • 29
  • 49
  • Does this answer your question? [Inserting new columns in the middle of a table?](https://stackoverflow.com/questions/1740838/inserting-new-columns-in-the-middle-of-a-table) – Fernando C. Feb 26 '20 at 16:53
  • @FernandoCid No, it does not – Fabian Bosler Feb 26 '20 at 17:44
  • 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 Feb 26 '20 at 20:25
  • @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 Feb 26 '20 at 20:27
  • @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 Feb 26 '20 at 21:39

1 Answers1

1

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

  1. Create a view after adding your column.
  2. 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.

Enrique Zetina
  • 825
  • 5
  • 16
  • 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 Feb 26 '20 at 21:40
  • SELECT * EXCEPT (col) is a good approach after creates a table from a query result, especially if you want to get the [schema information](https://cloud.google.com/bigquery/docs/information-schema-intro) for your users. – Enrique Zetina Feb 26 '20 at 22:30