Created 02-18-2018 02:50 PM
Use csv serde to escape quote characters in csv file,
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "\"")
Example:-
input data:-
a, quick,"brown,fox jumps",over,"the, lazy"
Crete table statement:-
create table hcc(field1 string, field2 string, field3 string, field4 string, field5 string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "\"");
Select the data in table
hive> select * from hcc; +---------+---------+------------------+---------+------------+--+ | field1 | field2 | field3 | field4 | field5 | +---------+---------+------------------+---------+------------+--+ | a | quick | brown,fox jumps | over | the, lazy | +---------+---------+------------------+---------+------------+--+ 1 row selected (0.055 seconds)
So in our create table statement we have mentioned quote character as " and seperator as ,. When we query table hive considers all the data enclosing quotes as one filed.