Support Questions
Find answers, ask questions, and share your expertise

Can I add a subcolumn to a hive struct column using alter table?

Solved Go to solution

Can I add a subcolumn to a hive struct column using alter table?

New Contributor

I've a very complex table structure in hive, let's say that it's like the following table:

create table dirceu ( a struct<b:string,c:string>); 

Now I do need to add another subcolumn to the a column, and it should have the structure b,c and d, I'm trying to do it with the following alter table: alter table dirceu change column a a struct<b:string,c:string, d:string>;

But this throw the following error:

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions :
a (state=08S01,code=1) 

Is there a way to do this using alter table? I know that I can do it using create table and copy the data, but I would like to know if there is another way to do it.

I'm using hive: 2.1.0.2.6.1.0-129 HortonWorks: HDP-2.6.1.0

This question is also in StackOverflow

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Can I add a subcolumn to a hive struct column using alter table?

Expert Contributor

@Dirceu Semighini Filho

Well you can force hive to allow you to change it by using:

SET hive.metastore.disallow.invalid.col.type.changes=true;

But the exception you are getting after doing that I'm gonna guess is going to be:

ClassCastException

This helpful exception you are getting now is telling you your data doesn't match up to the definition and you should not make the change or you'll feel the pain later. The real issue here is: there isn't an implicit conversion from struct<b:string,c:string> to struct<b:string,c:string,d:string>.

If you really felt strongly about the matter you could expand hive functionality to allow this to work. You could create an implicit conversion for a struct<b:string,c:string> to struct<b:string,c:string,d:string>. Doesn't seem too bad. But would take some doing.

You could just add a new column(struct<b:string,c:string,d:string>)to the table and write a UDF to populate (it from the old column struct<b:string,c:string>). This would avoid creating a new table if that's your goal.

If this answer was helpful please mark it as an answer.

View solution in original post

2 REPLIES 2

Re: Can I add a subcolumn to a hive struct column using alter table?

Expert Contributor

@Dirceu Semighini Filho

Well you can force hive to allow you to change it by using:

SET hive.metastore.disallow.invalid.col.type.changes=true;

But the exception you are getting after doing that I'm gonna guess is going to be:

ClassCastException

This helpful exception you are getting now is telling you your data doesn't match up to the definition and you should not make the change or you'll feel the pain later. The real issue here is: there isn't an implicit conversion from struct<b:string,c:string> to struct<b:string,c:string,d:string>.

If you really felt strongly about the matter you could expand hive functionality to allow this to work. You could create an implicit conversion for a struct<b:string,c:string> to struct<b:string,c:string,d:string>. Doesn't seem too bad. But would take some doing.

You could just add a new column(struct<b:string,c:string,d:string>)to the table and write a UDF to populate (it from the old column struct<b:string,c:string>). This would avoid creating a new table if that's your goal.

If this answer was helpful please mark it as an answer.

View solution in original post

Re: Can I add a subcolumn to a hive struct column using alter table?

New Contributor

Thanks for answering this question. I'll try to create an udf to populate it. There was no problem in creating another table, i tried to do that and to convert the data via spark, but it didn't worked (ClassCastException)