[SOLVED] Can I use 2 fields terminators(like ',' and '.') at a time in hive while creating table?

Can I use 2 fields terminators(like ',' and '.') at a time in hive while creating table?

I have a file with id and year. My fields are separated by , and .. Is there any chance I can in the place of fields terminated by can I use , and .?

Solution

This is possible using RegexSerDe.

hive> CREATE EXTERNAL TABLE citiesr1 (id int, city_org string, ppl float) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ('input.regex'='^(\\d+)\\.(\\S+),(\\d++.\\d++)\\t.*')
LOCATION '/user/it1/hive/serde/regex';

In the regex above three regex groups are defined.

(\\d+) leading digits is the int id column
dot . is a separator
(\\S+) - string without spaces is the city_org string column
comma , is a separator
(\\d++.\\d++) - float column
\\t - tab separator

See details here: https://community.hortonworks.com/articles/58591/using-regular-expressions-to-extract-fields-for-hi.html