CODEWITHSUNDEEP
hive
Ravi Yadav
Ravi Yadav

Reputation: 13

How to import csv file into hive table when it has delimiter value, say comma, as a field value?

I want to import a csv file into hive table. The csv file is having comma (,) as within a field value. How can we escape it?

Upvotes: 1

Views: 4668

Answers (1)

Sudheer
Sudheer

Reputation: 431

You can use CSV SerDe based on below conditions.

If your fields which has comma are in quoted strings.

sam,1,"sam is adventurous, brave"
bob,2,"bob is affectionate, affable"

CREATE EXTERNAL TABLE csv_table(name String, userid BIGINT,comment STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties (
   "separatorChar" = ",",
   "quoteChar"     = "\""   
  )   
STORED AS TEXTFILE
LOCATION 'location_of_csv_file';

If your fields which has comma are escaped as below.

sam,1,sam is adventurous\, brave
bob,2,bob is affectionate\, affable

CREATE EXTERNAL TABLE csv_table(name String, userid BIGINT, comment STRING)
ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties (
   "separatorChar" = ",",
   "escapeChar"    = "\\" 
  )   
STORED AS TEXTFILE
LOCATION '/user/cloudera/input/csv';

In both cases, the output will be as below:

hive> select * from csv_table;
OK
sam 1   sam is adventurous
bob 2   bob is affectionate

Upvotes: 4

Related Questions