Unable to alter column data type in Big Query

user_2736817 0
Viewed 1369

We imported database into BigQuery but a lot of columns are not in correct data types, many of them are stored as STRING. I want to fix them by change the data type in BigQuery

ALTER TABLE my.data_set.my_table ALTER COLUMN create_date SET DATA TYPE DATE;

But I got

ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRING) is assignable to the new type (DATE)

How to solve it?

google-bigquery sqldatatypes
2 Answers

Unfortunately, as far as I know there is no way to convert data type from STRING to DATE using ALTER TABLE but to create it again with the schema you want.

CREATE OR REPLACE TABLE testset.tbl AS 
SELECT 'a' AS col1, '2022-05-16' AS create_date
 UNION ALL
SELECT 'a' AS col1, '2022-05-14' AS create_date
;

-- ALTER TABLE testset.tbl ALTER COLUMN create_date SET DATA TYPE DATE;

-- Query error: ALTER TABLE ALTER COLUMN SET DATA TYPE requires that
-- the existing column type (STRING) is assignable to the new type (DATE) at [7:25]


-- Create it again. 
CREATE OR REPLACE TABLE testset.tbl AS
SELECT * REPLACE(SAFE_CAST(create_date AS DATE) AS create_date) 
  FROM testset.tbl;
user_190399200

Follow the steps below:

  1. Rename the existing column
ALTER TABLE mydataset.mytable
RENAME COLUMN create_date TO create_date_to_drop;
  1. Add the column with the required data type.
ALTER TABLE mydataset.mytable
ADD COLUMN create_date DATE;
  1. Drop the renamed column.
ALTER TABLE mydataset.mytable
DROP COLUMN create_date_to_drop;

Note: As of now, DROP COLUMN won't work on the tables protected by customer-managed encryption keys.

Alternatively, you can drop the existing column in the beta version and add it with the new data type, but you need to wait until the time travel window (7 days by default) to get over for adding the column with the same name.

user_101685840
Related