In hive, is there a way to specify between which columns to add a new column to?

Question

I can do

ALTER TABLE table_name ADD COLUMNS (user_id BIGINT)

to add a new column to the end of my non-partition columns and before my partition columns.

Is there any way to add a new column to anywhere among my non-partition columns? For example, I would like to put this new column user_id as the first column of my table

Answer 1

Yes it is possible to change the location of columns but only after adding it in the table using CHANGE COLUMN

In your case, first add the column user_id to the table with below command:

ALTER TABLE table_name ADD COLUMNS (user_id BIGINT);

Now to make user_id column as the first column in your table use change column with FIRST clause:

 ALTER TABLE table_name CHANGE COLUMN user_id user_id BIGINT first;

This will move the user_id column to the first position.

Similarly you can use After instead of first if you want to move the specified column after any other column. Like say, I want to move dob column after user_id column. Then my command would be:

ALTER TABLE table_name CHANGE COLUMN dob dob date AFTER user_id;

Please note that this commands changes metadata only. If you are moving columns, the data must already match the new schema or you must change it to match by some other means.