How to change the column type of a BigQuery table by querying and exporting to itself?

Question

I have an integer column in my BigQuery table, and now I need to convert it to a float column. I also have to keep all the records. I want to do a column type change . Not casting.

I read that this can be done by simply exporting the query results to a table for myself.

How to do it?

Answer 1

Using SELECT with writing the result back to the table

 SELECT CAST(int_field AS FLOAT) AS float_field, <all_other_fields> FROM YourTable 

This approach will check the whole table

To accomplish this, you must use the Show Parameter in the BQ Web UI and configure the parameters correctly, as shown below. After you run this, your table will have this column with the float and the original integer data type as you like. Note. You must use the correct / same field name for int_field and float_field

if you just need to add a new column, I would point you to Tables: patch GBQ API .
I'm not sure if this API allows you to change the type of a column - I doubt it, but it's easy to check

Using Jobs: insert EXTRACT and then LOAD

Here you can extract the table into GCS and then load it back into GBQ using the adjusted scheme

The above approach will allow you to: a) eliminate the cost of querying (scanning) tables and b) can help with restrictions that may arise if you have difficult humility (with records / repetition / nested, etc. type / mode)