question Re: How to drop delimiter in hive table in Support Questions

How to drop delimiter in hive table

simran_k — Mon, 08 Aug 2016 13:33:46 GMT

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?

Re: How to drop delimiter in hive table

ssubhas — Mon, 08 Aug 2016 13:46:48 GMT

@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

Re: How to drop delimiter in hive table

simran_k — Mon, 08 Aug 2016 13:48:51 GMT

@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.

Re: How to drop delimiter in hive table

simran_k — Mon, 08 Aug 2016 14:03:28 GMT

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

Re: How to drop delimiter in hive table

ssubhas — Mon, 08 Aug 2016 16:04:02 GMT

@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/