Styjun

I am trying to load information from a view into an Hive table stored as textfile configured like this:

CREATE TABLE table1 (
field1 BIGINT
,field2 STRING
,email STRING
,field3 BIGINT
,field4 STRING
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY 'u0001' 
LINES TERMINATED BY 'n'
STORED AS TEXTFILE ;

The thing is that after i created the table i insert the info from the view like this:

insert into table1 as 
select 
field1 
,field2 
,email 
,field3 
,field4 
from v_table2 ;

The process returns success but when i check the data on table1 the select statement returns the following output:

Error converting column: 4 to BIGINT

The information on that field returns Null when there is info on that field on the view but the insert statement doesn't return any error.

Besides, i can load the first BIGINT field correctly but the email fields displays a "square" and the BIGINT field after that gives that error.
Checking the data on the file it seems that the information suffers a shift after the email field.

Sounds like some encoding problem.

Already tried to force the encoding like this but the no luck:

ALTER TABLE table1 SET SERDEPROPERTIES('serialization.format'='u0001', 'serialization.encoding'='windows-1252');

I should also mention that the fields that are being loaded into table1 from the view are already BIGINT in the table that the view loads information from.

Anyone can help ?

Solution

After some research i manage to found a solution by creating a the destination table with the following definition:

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar'='u0001', 'quoteChar'="'", 'escapeChar' = '\')
STORED AS TEXTFILE 

And by treating the data on the field "email" like that:

case 
 when ascii(email_fe) <> 1 
 then email_fe 
 else NULL 
end email_fe

for the "email" fields where the strange data appears i checked that the ascii code was always 1 and by checking that on the select statement i could treat the information accordingly and get the data on the table for the other fields inserted the right way.

Does anyone have a better alternative ?

Can you give a try this please? This error is thrown since the table created has a column of bigint and we are trying to insert an int value

insert into table1 as
select
field1
,field2
,email
,cast(field3 as bigint)
,field4
from v_table2 ;