CODEWITHSUNDEEP
sqlhadoophivehiveddl
user2102237
user2102237

Reputation:

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

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.

Upvotes: 3

Views: 6865

Answers (2)

leftjoin
leftjoin

Reputation: 38335

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

Upvotes: 3

OneCricketeer
OneCricketeer

Reputation: 191963

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

Upvotes: 3

Related Questions