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,550 points
2,244 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,170 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

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
• 67,660 points
537 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 4 in Big Data Hadoop by Omkar
• 67,660 points
335 views
0 votes
1 answer
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 19 in Big Data Hadoop by Gitika
• 25,340 points
111 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,550 points
213 views
0 votes
1 answer

Not able to create Hive table from HDFS file

You dont have to specify the file name ...READ MORE

answered Dec 5, 2018 in Big Data Hadoop by Omkar
• 67,660 points
361 views
0 votes
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
• 10,710 points
3,334 views
0 votes
10 answers

hadoop fs -put command?

put syntax: put <localSrc> <dest> copy syntax: copyFr ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Aditya
16,496 views
0 votes
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,260 points
1,208 views
0 votes
1 answer