Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to drop delimiter in hive table

avatar
Expert Contributor

1. I download the file using download.file function in R

2. Save it locally

3. Move it to HDFS

My hive table is mapped to this location of file in HDFS. Now, this file has `,` as the delimiter/separator which I cannot change using download.file function from R.

There is a field that has `,` in its content. How do I tell hive to drop this delimiter if found anywhere within the field contents? I understand we can change the delimiter but is there really a way I can drop it like sqoop allows us to do? My R script is as simple as

download.file()

hdfs.put

Is there a workaround?

1 ACCEPTED SOLUTION

avatar
@Simran Kaur

To handle the delimiters within the data, you can create a table as "ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY '\\';" which will handle the data “1,some text\, with comma in it,123,more text”.

Also, you can have hive table with CSV serde. Please refer to below link:

https://github.com/ogrodnek/csv-serde

View solution in original post

4 REPLIES 4

avatar
@Simran Kaur

To handle the delimiters within the data, you can create a table as "ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY '\\';" which will handle the data “1,some text\, with comma in it,123,more text”.

Also, you can have hive table with CSV serde. Please refer to below link:

https://github.com/ogrodnek/csv-serde

avatar
Expert Contributor

@Sindhu: Thanks. Could you please tell what would be exactly an Alter table command? I have an existing table created over the file using hue and shall alter it to update for the changes.

avatar
Expert Contributor

Oh, I got it.Could you please explain what exactly is CSV serde and how does it work?

avatar
@Simran Kaur

Hive CSV serde is built on Open CSV which support all basic csv type operations. Basically, to design the serde based on the type of data within the csv file. Refer to below link for details:

http://opencsv.sourceforge.net/