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.

Not able to combine two column as Struct Type in hive

Highlighted

Not able to combine two column as Struct Type in hive

Contributor

Dear All.

i having some issue with combining two columns fields into single fields as STruct type.

 

below code i tried but still getting the same issue?

 

 

 

create table dummy_TBL2 (id int,fname string ,lname string) ;
insert into dummy_TBL2 (id,fname,lname) values(1,'bhau','anna') ; //dummy side
now below show the struct table:-
create table data_TBL2 (id int, name struct<fname:string,lname:string>) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','  
STORED AS TEXTFILE;//in this table i took name as struct type
___inserting the data from dummy into struct type-----
insert into table data_TBL2 
select id,
      name(fname,lname) 
from dummy_TBL2 limit 1;

 

 

struct.PNG

 

 

 

Thanks

HadoopHelp

2 REPLIES 2
Highlighted

Re: Not able to combine two column as Struct Type in hive

Guru

@HadoopHelp ,

 

You should use named_struct function to turn those strings into struct, please refer to Hive's UDF documentation:
Complex Type Constructors

 

So your query should be :

 

insert into table data_TBL2 
select id,
named_struct('fname', fname, 'lname', lname) 
from dummy_TBL2 limit 1;

 

Cheers

Eric

Highlighted

Re: Not able to combine two column as Struct Type in hive

Contributor

Hi @EricL .

 

Thank you very much...

 

but the same use case is here:-

this is my output data structure from a JSON we required:-

{
    "name": [
        {
            "use": "official", //here "tab1.use" is column and value
            "family": "family",//here "tab1.family" is column and value
            "given": [    //this column we need to create and add value from "tab1.fn&ln"
                "first1",   //here "first1" is coming from tab1.fname
                "last1"      //here "last1"is coming from tab1.lname
            ]
        },
        {
            "use": "usual",  //here "tab2.use" is column and value
            "given": [ //here we need to create column with fn&ln
                "first1 last1"   //here "first1 last1" is coming from tab1.fname &tab1.lname
            ]
        }
    ]
}

here we want to create a column(name) from above columns :-
above data is JSON structure but i want in Hive with table columns.
then further we can convert the same into JSON in my use cases.

Note :- structure is matter here.


Thanks
HadoopHelp

 

Don't have an account?
Coming from Hortonworks? Activate your account here