Change the datatype of the field in a table in Bigquery?

Question

In a Bigquery table, I am having data. I want to change/upgrade the datatype of one the fields in the table.

Current Table structure: Name:String , FlatNumber:Integer,Address:String, Amount:Integer

Required Updated Table Structure: Name:String , FlatNumber:String, Address:String, Amount:Float

Note: I am having data in the table

Answer 1

You would need to transform the data and write it to a new table. You can then copy it back. For example, run this query:

#standardSQL
SELECT
  Name,
  CAST(FlatNumber AS STRING) AS FlatNumber,
  Address,
  CAST(Amount AS FLOAT64) AS Amount
FROM YourTable;

Then use e.g. bq cp to copy the table and overwrite the original one. If you have more columns, you can use SELECT * with EXCEPT or REPLACE to avoid listing all of them:

#standardSQL
SELECT * EXCEPT(FlatNumber, Amount),
  CAST(FlatNumber AS STRING) AS FlatNumber,
  CAST(Amount AS FLOAT64) AS Amount
FROM YourTable;