Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Adding Column at partition level in hive

Highlighted

Adding Column at partition level in hive

New Contributor

Am new to hive, We had requirement to add columns to existing hive table. I did that with the help of below command.

alter table tableName add columns (colName datatype) cascade;

But in hive documentation, we have alter command to add columns at partition level. I tried below commands.

hive> SET hive.exec.dynamic.partition = true;
hive> alter table test_alter_col partition(c=1) add columns (d1 int);
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Duplicate column name: d1
hive> select d1 from test_alter_col where c=1;
FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'd1': (possible column names are: a1, b1, d, c)
hive> alter table test_alter_col partition(c=1) add columns (d2 int);
OK
Time taken: 0.178 seconds
hive> select d2 from test_alter_col where c=1;
FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'd2': (possible column names are: a1, b1, d, c)
hive>

Have tried below commands as well, but still neither i am able to query newly added column nor able to insert the data.

create table test_partn (a int, b int, c int) partitioned by (d int) row format delimited fields terminated by '\t' stored as textfile;

insert into table test_partn partition(d) values (1, 11, 111, 1111), (2, 22, 222, 2222), (3, 33, 333, 3333);

SET hive.exec.dynamic.partition = true;

alter table test_partn partition(d=1111) add columns (e int);
insert into test_partn partition(d=1111) values (1, 12, 13, 14);
FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different '1111': Table insclause-0 has 3 columns, but query has 4 columns.

What does above alter command do when we add columns at partition level?

Hive Version - Hive 2.1.1