Hive table property to consider consecutive delimiters as one delimiter

Question

jan 18 "value1 is null"
feb  4 "value1 is null"

in the above dataset there is consecutive delimiters between the 1st and 2nd column in second row how to handle consecutive delimiters as one delimiter.

I can see the example but I don't see 2 columns and I don't see any delimiter. I can guess that you want CSV serde in order to treat `"value1 is null"` as a single value. — David דודו Markovitz, Feb 22 '17 at 10:36
there 2 space between feb and 4, as 4 is a single digit in the second row — Mohan M, Feb 22 '17 at 10:39
space delimiter might be confusing let me put it this way. `jan,18,"value1 is null" \n feb,,4,"value1 is null"` — Mohan M, Feb 22 '17 at 10:42

score 0 · Accepted Answer · answered Feb 22 '17 at 10:55

0

create external table mydata 
(
    c1 string
   ,c2 string
   ,c3 string
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties ('input.regex' = '(".*?"|.*?)\\s+(".*?"|.*?)\\s+(".*?"|.*?)')
location '/user/hive/warehouse/mydata'
;

select * from mydata;

+-----------+-----------+------------------+
| mydata.c1 | mydata.c2 |    mydata.c3     |
+-----------+-----------+------------------+
| jan       |        18 | "value1 is null" |
| feb       |         4 | "value1 is null" |
+-----------+-----------+------------------+

answered Feb 22 '17 at 10:55

David דודו Markovitz

42,900
6
64
88

thanks friend. is this the only way possible or by using 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' can we get it. because I have got 75 columns in my data set. – Mohan M Feb 22 '17 at 11:10
MultiDelimitSerDe seems to handle only a fixed length delimiter. – David דודו Markovitz Feb 22 '17 at 11:44

Hive table property to consider consecutive delimiters as one delimiter

1 Answers1