Hive table field is delimited by '¬' hive does not accept this character

Question

The data i have is delimited by '¬' when i try using it in create table i get error as invalid character. i tried using it's unicode version '\U00AC' but no use all record gets ingested in first field. in unix this character '¬' is displayed as ï¿½. Could anyone guide me on this as to what to use in the field terminated by section.

CREATE TABLE IF NOT EXISTS test
(
CONTACT_RECORD_ID    string,
ACCOUNT    string,
CUST    string,
NUMBER    string,
NUMBER1    string,
NUMBER2    string,
NUMBER3    string,
NUMBER4    string,
NUMBER5    string,
NUMBER6    string,
NUMBER7    string,
LIST    string
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '¬';

In whatever displays `ï¿½` you need to change the text encoding settings. — OneCricketeer, Mar 18 '16 at 07:24
On Linux, what does the `file xyz.txt` command show? I.e. which encoding? Hadoop expects UTF-8 by default. — Samson Scharfrichter, Mar 18 '16 at 07:34
yes bang on its encoded as ISO-8859 text any solution to this buddy :) — Chetan Pulate, Mar 18 '16 at 11:38

score 0 · Answer 1 · answered Mar 18 '16 at 12:25

0

Can you please try with this DDL.

CREATE TABLE IF NOT EXISTS test
(
CONTACT_RECORD_ID    string,
ACCOUNT    string,
CUST    string,
NUMBER    string,
NUMBER1    string,
NUMBER2    string,
NUMBER3    string,
NUMBER4    string,
NUMBER5    string,
NUMBER6    string,
NUMBER7    string,
LIST    string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('empty.string.null'='true','field.delim'='\0AC','serialization.format'='\0AC','serialization.null.format'='')
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

answered Mar 18 '16 at 12:25

donut

790
5
11

ty tried it but didn't solve the problem. all data in first column – Chetan Pulate Mar 21 '16 at 05:36
output is like this 1ï¿½0000000000000010792ï¿½0000000000000549223ï¿½HFBlHgUUmGrCdï¿½HaNlzg0tEï¿½4Gï¿½HFNï¿½Hï¿½Xï¿½ï¿½ï¿½93aï¿½HFNBd2htï¿½QB3aï¿½HFNBughjkL4ï¿½u NULL NULLNULL NULL NULL NULL NULL NULL NULL NULL NULL – Chetan Pulate Mar 21 '16 at 05:37
Can you verify the file contains the ¬ as delimiter.Please use "cat | grep ¬ " – donut Mar 21 '16 at 05:47

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

if you have data in csv format, you can use this tool as in Hadoop/Hive : Loading data from .csv on a local machine to easily export data into Hadoop.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 20 '16 at 11:31

Vijay Kumar

124
1
10

Hive table field is delimited by '¬' hive does not accept this character

2 Answers2