hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From infa elance <infa.ela...@gmail.com>
Subject Insert into Hive avro table
Date Fri, 13 Jan 2017 19:50:28 GMT
Im trying to insert into hive avro table from a hive text table and getting
the following error.

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)


Here is what im trying to do

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","Fay","010119677","123
Street","AL","78678");
insert into member_poc values ("Cory","Locke","01011987","123
Street","MN","78678");

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

The avro table looks like below

+------------+----------------------------------------------
----------------------+----------+--+
|  col_name  |                             data_type
       | comment  |
+------------+----------------------------------------------
----------------------+----------+--+
| lastname   | string
      |          |
| firstname  | string
      |          |
| dob        | string
      |          |
| addresses  | struct<address:struct<street:string,state:string,zipcode:string>>
 |          |
+------------+----------------------------------------------
----------------------+----------+--+


then i ran the below command to insert in to avro table which gave me an
error.

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


Is there a way to insert from member_poc table to memer_poc_avro table or
do i have to conver the text files to avro ?


Thanks and Regards,
Ajay.

Mime
View raw message