In hive, is there a way to specify between which c

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

Ah, here's the explanation for why you listed user_id twice (it's not a type):

// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is:  b int, a2 string, c int.

Answer 2

No, it is not possible.

One solution is to create new table using "CREATE TABLE AS SELECT" approach and drop older one.

Answer 3

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.