Creating hive table with ";" as delimiter

[#1]

I am trying to create a hive table with ";" as delimiter for my project. Because the input file that I am getting from another application having data delimited by ";" .

When I try to create the table by giving row format delimited fields terminated by ',' I am getting the below error.

MismatchedTokenException(-1!=302)
at org.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(BaseRecognizer.java:617)
at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
at org.apache.hadoop.hive.ql.parse.HiveParser.tableRowFormatFieldIdentifier(HiveParser.java:34966)
at org.apache.hadoop.hive.ql.parse.HiveParser.rowFormatDelimited(HiveParser.java:33919)
at org.apache.hadoop.hive.ql.parse.HiveParser.tableRowFormat(HiveParser.java:34195)
at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:4979)
at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2355)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1579)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1057)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:199)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:393)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:307)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1110)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1158)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1047)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1037)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:159)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:370)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:756)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:675)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:615)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 6:62 mismatched input '<EOF>' expecting StringLiteral near 'by' in table row format's field separator

[#2]

@Mahendiran Palani Samy

Create table with unicode character of ; i.e \u003B

create table ... ... row format delimited fields terminated by '\u003B';

[#3]

I fixed this issue by using the below by adding escape sequence.

create table ... ... row format delimited fields terminated by '\;'

[#4]

@Mahendiran Palani Samy

Could you mention which of hive are u using because i tried to execute same exact command in Hive-1 and Hive-2 versions(HDP) but both are failed.

Hive-2 error:

hive> create table u(id int) row format delimited fields terminated by '\;';
Error: Error while compiling statement: FAILED: ParseException line 1:67 mismatched input '<EOF>' expecting StringLiteral near 'by' in table row format's field separator (state=42000,code=40000)

Hive-1 Error:

hive> create table u(id int) row format delimited fields terminated by '\;';
Error: Error while compiling statement: FAILED: ParseException line 1:67 mismatched input '<EOF>' expecting StringLiteral near 'by' in table row format's field separator (state=42000,code=40000)

[#5]

	It is working perfectly for Hive1.1.0-cdh5.4.0. Below I mentioned hive QL I used for creating an dummy table. Also attached the screen shot for the reference.

hive (retail_warehouse)> create table vendor(
                       >                        vendor_id int,
                       >                        vendor_name string)
                       >                        row format delimited fields  terminated by "\;"
                       >                        stored as textfile;
OK
Time taken: 0.857 seconds

[#6]

Please find the screen shot the HIVE QL. screen-shot-2018-11-15-at-34106-pm.png

[#7]

I am using Hive 1.1.0-cdh5.4.0. My HIVE runs in cloudera 5.4.0.

mpalanisamy@01:~$ hive --version 
Hive 1.1.0-cdh5.4.0
Subversion file:///data/jenkins/workspace/generic-package-ubuntu64-14-04/CDH5.4.0-Packaging-Hive-2015-04-21_12-09-14/hive-1.1.0+cdh5.4.0+103-1.cdh5.4.0.p0.56~trusty -r Unknown
Compiled by jenkins on Tue Apr 21 12:12:12 PDT 2015
From source with checksum 2bf708133bf40715eaa74b142056808a