Create HIVE Table with multi character delimiter

+2 votes

I want to create a HIVE Table with multi string character as a delimiter such as

CREATE EXTERNAL TABlE tableex(id INT, name STRING) 
ROW FORMAT delimited fields terminated by ','
LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/user/myusername';

I want to have delimiter as a multi string like "~*".

Oct 3, 2018 in Big Data Hadoop by digger
• 26,740 points
14,597 views

2 answers to this question.

+1 vote
Best answer

FILELDS TERMINATED BY does not support multi-character delimiters. The easiest way to do this is to use RegexSerDe:

CREATE EXTERNAL TABlE tableex(id INT, name STRING) 
ROW FORMAT 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "^(\\d+)~\\*(.*)$"
)
STORED AS TEXTFILE 
LOCATION '/user/myusername';
answered Oct 3, 2018 by slayer
• 29,370 points

selected Oct 12, 2018 by Priyaj
+1 vote

Please use MultiDelimitSerde

CREATE EXTERNAL TABlE tableex(id INT, name STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
WITH SERDEPROPERTIES ("field.delim"="~*")
STORED AS TEXTFILE
LOCATION '/user/myusername';
answered Oct 12, 2018 by Aditya
Above solution is working but after setting serde properties, its removing my column comments to 'from deserializer'.

Any idea?
Hey, @Atul,

Will you please post what exactly is happening? It will be helpful to understand and resolve your query.

Related Questions In Big Data Hadoop

0 votes
1 answer

How to create a Hive table with a sequence file?

In Hive we can create a sequence ...READ MORE

answered Dec 17, 2018 in Big Data Hadoop by Omkar
• 69,220 points
3,613 views
–1 vote
1 answer

Create Hive table with bucketing and ORC file format

create table hive_dml (emp_id int, first_name string, last_name ...READ MORE

answered Jan 5, 2019 in Big Data Hadoop by Omkar
• 69,220 points
3,743 views
0 votes
1 answer

Hive: LOCATION and WITH DBPROPERTIES command in create table command

By default, the Hive database will be ...READ MORE

answered May 22, 2019 in Big Data Hadoop by Firoz
4,127 views
0 votes
1 answer

How can create the dynamic partition table with data in Hive?

Hi, We can see some steps to do ...READ MORE

answered Jun 20, 2019 in Big Data Hadoop by Gitika
• 65,770 points
4,580 views
0 votes
1 answer

Create hive table with location.

Hi@MD, We can also create a hive table ...READ MORE

answered Oct 16, 2020 in Big Data Hadoop by akhtar
• 38,260 points
629 views
0 votes
1 answer

How to create smaller table from big table in HIVE?

You could probably best use Hive's built-in sampling ...READ MORE

answered Sep 24, 2018 in Big Data Hadoop by digger
• 26,740 points
1,754 views
+1 vote
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 11,380 points
11,058 views
+2 votes
11 answers

hadoop fs -put command?

Hi, You can create one directory in HDFS ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by nitinrawat895
• 11,380 points
108,987 views
–1 vote
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,350 points
4,629 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP