question Re: how to load double quotes data of fields in hive table? in Archives of Support Questions (Read Only)

how to load double quotes data of fields in hive table?

Former Member — Mon, 18 Sep 2017 18:31:26 GMT

am having csv file data like this as shown below

example

1,"Air Transport International, LLC",example,city

i have to load this data in hive like this as shown below

1,Air Transport InternationalLLC,example,city

but actually am getting like below??

1,Air Transport International, LLC,example,city

how to solve this problem

please give me solution

thanks with regards,

swathi.T

Re: how to load double quotes data of fields in hive table?

anarasimham — Mon, 18 Sep 2017 20:18:11 GMT

You can use the CSV SerDe: https://cwiki.apache.org/confluence/display/Hive/CSV+Serde

This has support for quoted cells.

Please refer to the general SerDe documentation if you have questions on how to use SerDe's:

https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe

Re: how to load double quotes data of fields in hive table?

Shu_ashu — Mon, 18 Sep 2017 21:12:33 GMT

@swathi thukkaraju,
You can use the below serde properties to read your data correctly

CREATE TABLE test(a string, b string,..)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "\""
)  
STORED AS TEXTFILE 
location 'location of csv file';

Re: how to load double quotes data of fields in hive table?

zfanswer — Mon, 08 Jan 2018 13:39:04 GMT

Does it support load gzipped csv file? I got `FAILED: SemanticException Unable to load data to destination table. Error: The file that you are trying to load does not match the file format of the destination table.`