Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create HIVE Table with multi character delimiter

Tags:

hadoop

hive

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 "~*".

like image 786
Varun Gupta Avatar asked Sep 21 '13 10:09

Varun Gupta


People also ask

How do I create a Hive table with multi character delimiter?

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

How do I change the delimiter of a table in Hive?

You can change the delimiter using the below alter table command.

What is delimiter in Hive?

Introduction. Introduced in HIVE-5871, MultiDelimitSerDe allows user to specify multiple-character string as the field delimiter when creating a table.


1 Answers

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';
like image 56
Hari Menon Avatar answered Oct 17 '22 10:10

Hari Menon