Support Questions

Find answers, ask questions, and share your expertise

Who agreed with this solution

avatar
Master Guru
@Mark

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.

View solution in original post

Who agreed with this solution