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,660 points
2,841 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,260 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
• 68,840 points
686 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, 2019 in Big Data Hadoop by Omkar
• 68,840 points
461 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
335 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 19, 2019 in Big Data Hadoop by Gitika
• 25,440 points
186 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,660 points
275 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
• 68,840 points
524 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
• 10,840 points
3,843 views
+1 vote
11 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
20,338 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,290 points
1,439 views
0 votes
1 answer