5

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.

YABADABADOU
  • 1,238
  • 1
  • 16
  • 38

1 Answers1

8

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.

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • 1
    It'd be perfect If you'd give some more details about 'how' exactly it can be done or give some reference links etc. – scaryguy Mar 17 '16 at 20:08
  • Would you mind to answer this question? http://stackoverflow.com/questions/36071216/how-to-change-a-column-type-of-a-bigquery-table-by-querying-and-exporting-to-its – scaryguy Mar 17 '16 at 21:02
  • I need the same use case, but I have `_PARTITIONTIME` (My table is partitioned one), so how to copy to the destination table with field `_PARTITIONTIME`? – Jaya Ananthram Jan 31 '18 at 16:17