How to delete one of the two partition columns in Hive?

Question

I've created a hive table with two partition columns say col 1 and col2, now for some analytical purpose I wish to delete the col2 partition.

Basically I want the column - col2 to be removed from the partitioned column list, but I should not lose the data in col2.

Clearly, the final table should remain partitioned by col1 only while col2 should also exist in the final partitioned table but as a normal column and not a partition column.

Kindly suggest a way forward.

Answer 1

Below is one of the best way to do it. Simply update the hive partition:

ALTER TABLE <table_anme> PARTITION(year = 2018, month = 05) 
SET LOCATION 'hdfs://some/temp/location/tale_name/2018/05';

It will set the partition to the new location, and it's not going to move or delete your data file.

Now drop the partition,

ALTER TABLE <table_name> DROP IF EXISTS PARTITION(year = 2018, month = 05);

All the best!!!

Answer 2

There is no explicit command to DELETE a column in hive. However you can do it by using REPLACE command as below.

ALTER TABLE <table_name> REPLACE COLUMNS(column1 <type>, column2 <type>);

Suppose you have a table TEST

CREATE TABLE TEST(a int, b int, c int);

Now you want to delete column c, then you can do it as follows:

ALTER TABLE TEST REPLACE COLUMNS (a int, b int);