Drop column of hive table stored as orc

Question

I have hive table created as below:

create table alpha001(id int, name string) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true')

Now i want to drop one of the columns, say 'name'. I tried the following:

ALTER TABLE alpha001 REPLACE COLUMNS (id int);

which results in below

Exception thrown: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replace columns is not supported for table default.alpha001. SerDe may be incompatible.

and following

ALTER TABLE alpha001 DROP name;

Exception thrown : FAILED: ParseException line 1:26 mismatched input 'name' expecting PARTITION near 'DROP' in drop partition statement

Answer 1

Unfortunately, you can't! The only way you can delete column from existing table is by using REPLACE COLUMNS keyword. But this can be done only for tables with a native SerDe (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe).

Your best bet is recreating the schema. Follows the steps.

1. Check if the table is external. If it isn't, use the following statement to make it external.

   alter table alpha001 set tblproperties('EXTERNAL'='TRUE');
   

2. Drop the table. Since the table is an external table, you can drop it without dropping the actual table.

3. Recreate the table with the new schema. You should be able to access the table with new schema.

Follows a quick sample.

create table alpha001(id int, name string) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true');

--assuming your table is not EXTERNAL already
alter table alpha001 set tblproperties('EXTERNAL'='TRUE');

insert into alpha001 values(1,"A");

select * from alpha001;

OK
1       A

drop table alpha001;

create table alpha001(id int) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true');

select * from alpha001;

OK
1
Time tak

Hope that helps!