Support Questions

Find answers, ask questions, and share your expertise

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

avatar

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

1 ACCEPTED SOLUTION

avatar
Master Guru

@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';

View solution in original post

3 REPLIES 3

avatar
Super Collaborator

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

avatar
Master Guru

@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';

avatar
New Contributor

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.`