You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by infa elance <in...@gmail.com> on 2017/01/13 19:50:28 UTC
Insert into Hive avro table
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.