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

Insert into Hive Avro Table


Insert into Hive Avro Table

New Contributor

Is there a way to insert data from Hive table stored as text to avro with struct datatype?


Below are my two tables, the avro table is defined using an avro schema.


CREATE TABLE member_poc (firstname string,lastname string,dob string, street string, state string,zipcode string);


insert into member_poc values ("John","Smith","01011987","123 Street","NY","78678"); insert into member_poc values ("Shaun","Anderson","010119677","123 Street","AL","78678");


insert into member_poc values ("Cory","Law","01011987","123 Street","MN","78678");


CREATE EXTERNAL TABLE member_poc_avro STORED AS AVRO LOCATION '/staging/avro/member/data' TBLPROPERTIES ('avro.schema.url'='hdfs:////staging/avro/member/schema/member.avsc');


Im not sure if this is the right way but i tried running below statement without luck.


insert overwrite table member_poc_avro select lastname,firstname,dob,NAMED_STRUCT('street',street,'state',state,'zipcode',zipcode) from member_poc;


The error i get is Error: Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different 'member_poc_avro': Cannot convert column 3 from struct<street:string,state:string,zipcode:string> to struct<address:struct<street:string,state:string,zipcode:string>>. (state=42000,code=10044)


Re: Insert into Hive Avro Table

Super Collaborator

From the error message it seems like the result of the SELECT do not match the avro schema of the target table.


You create a record of type "struct<street:string,state:string,zipcode:string>"

But your avro table has a record of type "struct<address:struct<street:string,state:string,zipcode:string>>"


I have not use avro table so I don't know if it will work. But the query should looks like something like this :


insert overwrite table member_poc_avro select lastname,firstname,dob,NAMED_STRUCT('address',NAMED_STRUCT('street',street,'state',state,'zipcode',zipcode)) from member_poc;

I don't know if the function NAMED_STRUCT can be nested.

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