question Re: Hive- escaping field delimiter in column value in Support Questions

Hive- escaping field delimiter in column value

rmy1712 — Fri, 25 Aug 2017 04:42:40 GMT

My dataset set is as below:

700,Angus (1995),Comedy

702,Faces (1968),Drama

703,Boys (1996),Drama

704,"Quest, The (1996)",Action|Adventure

705,Cosi (1996),Comedy

747,"Stupids, The (1996)",Comedy

Create table movies(
movieid int,
title String,
genre string)
row format delimited
fields terminated by ','
;

Select title , genre from movies;

Since the rows have comma separated values, the records like 704,"Quest, The (1996)",Action|Adventure returns Quest as title, instead of Quest,The (1996).

And Genre value is shown as The(1996) instead of Action|Adventure.

How to load such data correctly by escaping the delimiter in the value ?

Re: Hive- escaping field delimiter in column value

ssahi — Fri, 25 Aug 2017 05:49:26 GMT

Hi @Ramya Jayathirtha

Try the following:

create table movies(

movieid int,

title String,

genre string)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

This should resolve your issue as the OpenCSVSerde's default properties should work for your use case.

Re: Hive- escaping field delimiter in column value

ssubhas — Fri, 25 Aug 2017 14:33:19 GMT

Hi @Ramya Jayathirtha

Adding to @Sonu Sahi's reply, the CSVSerde is available in Hive 0.14 and greater. The following example creates a TSV (Tab-separated) file.

<code>CREATE TABLE my_table(a string, b string, ...)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'WITH SERDEPROPERTIES ( "separatorChar" = "\t", "quoteChar" = "'", "escapeChar" = "\\" ) STORED AS TEXTFILE; Default properties for SerDe is Comma-Separated (CSV) file

DEFAULT_ESCAPE_CHARACTER \

DEFAULT_QUOTE_CHARACTER "

DEFAULT_SEPARATOR ,

This SerDe works for most CSV data, but does not handle embedded newlines. To use the SerDe, specify the fully qualified class name org.apache.hadoop.hive.serde2.OpenCSVSerde.

If you want to use the TextFile format, then use 'ESCAPED BY' in the DDL.

"Enable escaping for the delimiter characters by using the 'ESCAPED BY' clause (such as ESCAPED BY '\')
Escaping is needed if you want to work with data that can contain these delimiter characters.

A custom NULL format can also be specified using the 'NULL DEFINED AS' clause (default is '\N')."

Re: Hive- escaping field delimiter in column value

rmy1712 — Mon, 28 Aug 2017 03:04:31 GMT

Thank you @Sindhu for the elaborate explanation and the solution..!!

Re: Hive- escaping field delimiter in column value

rmy1712 — Mon, 28 Aug 2017 03:05:09 GMT

Thank You @Sonu Sahi for the solution.

Re: Hive- escaping field delimiter in column value

rajkiranu — Tue, 27 Feb 2018 11:39:08 GMT

I have the same situation in log file. My data set looks like this

2017-11-29 16:19:39,217 DEBUG Conn -> 8=FIXT.1.1|9=76|35=A

2017-11-29 16:19:43,618 DEBUG Conn -> 8=FIXT.1.1|9=52|35=0|49=1151|56=BSEEQ|34=2

How to handle this Regex serde. my create table query is:

CREATE TABLE capital_log1 ( date STRING, filed2 STRING, filed3 STRING, filed4 STRING,filed7 map<bigint,string>) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "([^ ]*) ([^,]*)([^ ]*) (.*\->) (.*\|)", "output.format.string" = "%1$s %2$s %3$s %4$s %5$s" ) STORED AS TEXTFILE; It throws me an exception.

Re: Hive- escaping field delimiter in column value

srini93vasan — Tue, 26 Mar 2019 03:11:38 GMT

Hello Sir,

How do we store if via ETL layer '\n' is manipulated as '\\n'. org.apache.hadoop.hive.serde2.OpenCSVSerde does not work as data gets misaligned. Is there any way to load it without preprocessing?

Re: Hive- escaping field delimiter in column value

bluecean — Wed, 06 Nov 2019 10:26:00 GMT

as you said,the escapeChar '\n' doesn't work? such as "the great wall

in china",although used escapeChar '\n' ,the result were resolved into "the great wall",NULL.

rather than "the great wall in china" in hive table?

Re: Hive- escaping field delimiter in column value

Manoj_hadoop — Fri, 24 Jan 2020 17:04:07 GMT

Hi,

You can use (ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde') as well.

Create Table tablename (columns) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde';

Thanks,

Manoj

Re: Hive- escaping field delimiter in column value

sreeja1 — Sun, 19 Apr 2020 11:44:11 GMT

Hi Manoj,

We are using textfiles with separationchar as '|', but the problem we have embedded new lines in columns which is resulting data empty in hive as its considering as new line.

Rest data is migrating perfectly fine.

Could you please suggest us how to avoid new line characters in between column-data.

Thanks&Regards

Sreeja

Re: Hive- escaping field delimiter in column value

cdsuya — Fri, 19 Aug 2022 11:26:11 GMT

Hello @ssubhas , the above worked however, when we try the same with LazySerde, it is able to escape the delimiter but loads few NULL values at the end. PFB snippet of statement I used: CREATE TABLE test1(5columns string)

ROW FORMAT SERDE

'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES(

'separatorChar'='|',

'escapeChar'='\\'

)

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

NOTE: also tried field.delim=|, format.serialization=|.

It works when serde properties are not mentioned and we use escape by Clause as you suggested, any way to make it work with LazySerde as well? (Data is Pipe delimited & may also have pipe in the data). Please suggest and help.