BigQuery: How can I change the type of one of my column from INTEGER to STRING?

5
votes

In BigQuery, let say I have an existing table with X fields. Field 1 is currently an INTEGER but I would like to change it to a STRING.

I need to keep data that is currently in Field 1, while being able to insert string data into that field.

I believe that it is currently impossible in Google BigQuery to modify the type of a column. I think that the only modification that we can do to a table is add columns with the Table.Update command

So what would be the best way do to that?

I was thinking of doing this, but I would hope that there is a better solution:

  1. Select STRING(field1) as field1, field2, field3, fieldX from MyTable
  2. Export the result in a TempTable
  3. Delete MyTable
  4. Copy TempTable to MyTable

I would then be able to insert strings in field1.

1
google-bigquery

1 Answers

8
votes

Steps 1-4 can all be done in a single atomic step. Just set a destination table on your query and use allow_large_results and use write_truncate to write your results back to the original table. This will update your table in place.