Redshift spectrum - Updating external spectrum table column type

Question

I've created an external table having 4 columns. One of the column is of custom datatype.

create EXTERNAL table public.test_table_1(
uuid varchar(36),
event_id varchar(36),
last_updated_timestamp bigint,
user_app struct<starttime : int, endtime : int, id_1 : struct<value : float>>

I want to update the column user_app to a new datatype of formar:

struct<starttime : int, endtime : int, id_1 : struct<value : float>, id_2 : struct<value : float>>

Answer 1

How about dropping the table, and creating it anew with the correct column type?

Since it's an external table, you will not loose any date upon dropping it.

DROP TABLE public.test_table_1;

create EXTERNAL table public.test_table_1(
    uuid varchar(36),
    event_id varchar(36),
    last_updated_timestamp bigint,
    user_app struct<
        starttime : int, 
        endtime : int, 
        id_1 : struct<value : float>, 
        id_2 : struct<value : float>
     >
);

Answer 2

You can do it using Glue data catalog.

Navigate to Glue data catalog --> Tables --> Edit Schema

Click on struct data type for column user_map and update the definition.

As shown in screenshot