Archives of Support Questions (Read Only)

This is an archived board for historical reference. Information and links may no longer be available or relevant
Announcements
This board is archived and read-only for historical reference. To ask a new question, please post a new topic on the appropriate active board.

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