Hive: How to Handle files which has delimiter in the data file?

I have the below data that needs to be insert into a hive table. The data has the default delimiter in the file. How to insert into a hive table?

10,Andrew,Man”,”ager,DE,PC
11,Arun,Manager,NJ,PC
12,Harish,Sales,NJ,MAC
13,Robert,Manager,PA,MAC
14,Laura,Engineer,PA,MAC


Thanks!

2 Answers
2

Try using CSV Serde

create table test_table(id int,...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "”",
"escapeChar" = "\"
)


change quoteChar based on the data

You have two options first you can used "|" (pipe) as delimiter and then insert into a hive table otherwise you have to enclose your data fields in double quotes " ", then use the OpenCSV Serde when creating the DDL for your Hive table. Now you can query the table and Hive will correctly display the data.

"|"

" "

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.