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

Question

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

I've read that it's possible to do it just by exporting results of a query on a table to itself.

How to do it?

Answer 1

Using SELECT with writing result back to table

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

This approach will co$t you scan of whole table

To execute this - you should use Show Option button in BQ Web UI and properly set options as in below example. After you run this - your table will have that column with float vs. original integer data type as you wanted. Note: You should use proper/same field name for both int_field and float_field

if you would just needed to add new column I would point you to Tables: patch GBQ API.
I am not sure if this API allows to change type of column - I doubt - but it is easy to check

Using Jobs: insert EXTRACT and then LOAD

Here you can extract table to GCS and then load it back to GBQ with adjusted schema

Above approach will a) eliminate cost cost of querying (scan) tables and b) can help with limitations that you can come up if you have complex schame (with records/repeated/nested, etc. type /mode)