Apache Hive: How to Add Column at Specific Location in Table

Question

I want to add a new column to a specific location in hive table. when i add new column it goes to the last position.

Answer 1

How frequently are people running SELECT *?? Typically, people list out each column in the select statement. Just add the column to the end, and adjust like SELECT last_col, first_col, second_col ...

---

Alternatively, create a VIEW that runs a select statement with the column ordering you want.

Rename the table to something else, and name the view to the table, and no one would know any different

Answer 2

You need to recreate table. If the table is external and data already contains new column, then issue drop and create table statements. General solution is to:

1. create <new_table>...;
2. insert overwrite <new_table> select from <old_table>;
3. drop <old_table>;
4. alter table <new_table> rename to <old_table>; 

Also if datafiles already contain new column in some position you can

1. Alter table add column

Change column position using this example:

2. ALTER TABLE test_change CHANGE old_name new_name STRING AFTER other_col CASCADE;

See docs here: Change Column Name/Type/Position/Comment