Is there a way to alter column type in hive table?

Question

The current schema is:

hive> describe tableA;
OK
id      int
ts      timestamp

I want to change ts column to be BIGINT without dropping table and recreate again. Is it possible?

Additionally if you are using partitions you must `alter table {table_name} partition column ({column_name} {column_type})`. Hive can create lots of inconsistency so easily. Maybe it's me but Hive is very frustrating :-( — Kenji Noguchi, Jan 07 '16 at 20:35

score 70 · Accepted Answer · edited Mar 05 '18 at 22:02

70

Found the solution:

ALTER TABLE tableA CHANGE ts ts BIGINT AFTER id;

See this for complete details: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterColumn

edited Mar 05 '18 at 22:02

Oskar Austegard

4,599
4
36
50

answered Jul 05 '13 at 22:38

interskh

2,511
4
20
20

2

@interskh is there a way to apply this to multiple columns in one statement? such as `ALTER TABLE tableA CHANGE a,b,c a,b,c BIGINT;` I tried separating columns using a comma and it did not work. Reason I ask is my table was created using the Hue interface for hive and `DECIMAL` defaults to `(10,0)` however I require `(38,0)` – AM_Hawk Apr 14 '16 at 15:10
Do you know if this would also work in case the data is stored as ORC or Parquet? – Stefan Papp Nov 30 '16 at 07:50
1

DO NOT USE if data is in parquet and you are not at Hive version 1.2. Supposedly this is fixed in Hive 1.2, but my company uses 1.1. I had to delete my table and start over. – Climbs_lika_Spyder Nov 27 '17 at 18:15

score 34 · Answer 2 · edited Sep 04 '14 at 20:11

34

ALTER TABLE table_name CHANGE col_name col_name newType

edited Sep 04 '14 at 20:11

qdot

6,195
5
44
95

answered Jul 17 '13 at 21:47

Animesh Raj Jha

2,704
1
21
25

score 15 · Answer 3 · edited Apr 18 '17 at 09:17

It's simple usually to change/modify the exesting table use this syntax in Hive.

ALTER TABLE table_name CHANGE old_col_name new_col_name new_data_type

Here you can change your column name and data type at a time. If you don't want to change col_name simply makes old_col_name and new_col_name are same. Ok.

Come to your problem. If you want to change ts column to be BIGINT.It means column-type you are changing. so simply run this query.

ALTER TABLE tableA CHANGE ts ts BIGINT;

Here ts and ts are same, means you are not changing column name, but changing column-type; if you wish to change column name also simply run it.

ALTER TABLE tableA CHANGE ts new_col BIGINT;

Now run

hive> describe tableA;
OK
id      int
new_col      bigint

Is there a way to alter column type in hive table?

3 Answers3

Linked