question Re: Trouble loading CSV data with embedded double quotes and embedded commas into HIVE Table in Support Questions

Trouble loading CSV data with embedded double quotes and embedded commas into HIVE Table

cortland — Thu, 30 Sep 2021 14:35:00 GMT

I need to load the CSV data into hive table but i am facing issues with embedded double quotes in few column values as well embedded commas in other columns .
Because of this, wherever embedded double quotes and embedded commas are occured , the data from there not loading properly and filled with nulls.
I have tried using below openCSV serde options and all. But no luck!

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = "\t",
"quoteChar" = "'",
"escapeChar" = "\\"
)

theCSV data looks like this

id,name,description,role
"1","peter","He is Data enginer"", "Senior Engineer"
"2","Anee","Hadoop Engineer"","Lead"
"3","James","Data, Architect"","Sr Architect"

hive data should look like this after loading

Id	Name	Description	Role
1	peter	He is Data Engineer	Senior Engineer
2	anee	Hadoop Engineer	Lead
3	james	Data, Architect	Sr Architect

Re: Trouble loading CSV data with embedded double quotes and embedded commas into HIVE Table

asish — Thu, 30 Sep 2021 17:31:22 GMT

@cortland

I am able to acheive,please find my testcase

abc.txt
====

"1","peter","He is Data enginer", "Senior Engineer"
"2","Anee","Hadoop Engineer","Lead"
"3","James","Data, Architect","Sr Architect"

hdfs dfs -put abc.txt /user/hive/

create external table test_csv(num string ,name string ,work string ,designation string )
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties
(
"separatorChar" = ','
,"quoteChar" = '"'
)
STORED AS TEXTFILE;

LOAD DATA INPATH '/user/hive/abc.txt' INTO TABLE test_csv;

Please accept it as solution,if your queries are answered and the testcase works in your scenario.

Re: Trouble loading CSV data with embedded double quotes and embedded commas into HIVE Table

cortland — Thu, 30 Sep 2021 17:39:27 GMT

Thanks for your response Ashish,

If you see my test data , its having two double quotes (extra double quote ) in column values . thats where its creating problems.

"1","peter","He is Data enginer"", "Senior Engineer"
"2","Anee","Hadoop Engineer"","Lead"
"3","James","Data, Architect"","Sr Architect"

Re: Trouble loading CSV data with embedded double quotes and embedded commas into HIVE Table

asish — Thu, 30 Sep 2021 17:48:02 GMT

yes that was indeed a problem 🙂

I was about to comment on that