How to change the datatype of a field in a struct column

Luis Novo 0
Viewed 54

I have a struct column in BigQuery called meta. Inside that field, I have a field called join_at which is currently in a FLOAT datatype and I'd like to change it to a TIMESTAMP datatype.

I tried running this query:

ALTER TABLE `my-table` 
ALTER COLUMN meta.join_at SET DATA TYPE TIMESTAMP

That doesn't work. It throws an error at the "." character. So, apparently I can't just change the struct field like that.

What would be the correct approach in this case?

google-bigquery
1 Answers

If you were wanting to alter a field in a struct you would do something like this:

CREATE OR REPLACE TABLE so_test.alter_struct(s1 STRUCT<a FLOAT64, b STRING>);

ALTER TABLE so_test.alter_struct ALTER COLUMN s1
SET DATA TYPE STRUCT<a TIMESTAMP, b STRING>;

However a FLOAT is not coercible to a TIMESTAMP as listed in this table here: https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules#comparison_chart

Instead you can take an approach similar to this: How to delete a column in BigQuery that is part of a nested column

And just define the new structure while overwriting the table.

Daniel Zagales0
Related