hadoophivebeeline

beeline issue with u0001 delimeter


I have an HQL file which contains a create table statement.

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT 'Employee details'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '^A'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

The table's field delimiter is /u0001 (ctrl-A).When i am running this hql file using hive (hive-f), its working perfectly fine.But when i am running using beeline,I am getting parse exception

[cloudera@quickstart Desktop]$ beeline -u jdbc:hive2://quickstart:10000/default -n admin -d org.apache.hive.jdbc.HiveDriver -f createtable.hql 
Connecting to jdbc:hive2://quickstart:10000/default
Connected to: Apache Hive (version 1.1.0-cdh5.4.2)
Driver: Hive JDBC (version 1.1.0-cdh5.4.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://quickstart:10000/default> CREATE TABLE IF NOT EXISTS employenter code hereee ( eid int, name String,
0: jdbc:hive2://quickstart:10000/default> salary String, destination String)
0: jdbc:hive2://quickstart:10000/default> COMMENT 'Employee details'
0: jdbc:hive2://quickstart:10000/default> ROW FORMAT DELIMITED
0: jdbc:hive2://quickstart:10000/default> 'FIELDS TERMINATED BY '
0: jdbc:hive2://quickstart:10000/default> LINES TERMINATED BY '\n'
0: jdbc:hive2://quickstart:10000/default> STORED AS TEXTFILE;
Error: Error while compiling statement: FAILED: ParseException line 5:0 cannot recognize input near ''FIELDS TERMINATED BY '' 'LINES' 'TERMINATED' in serde properties specification (state=42000,code=40000)

Seems like the beeline shell is not able to parse the non printable character (ctrl a) correctly.But the hive client do not have any issues

Any Help would be appreciated


Solution

  • Use one of the following options:

    ... fields terminated by '\001' (Octal)
    ... fields terminated by '1' (Decimal)
    ... fields terminated by '\u0001' (Hexadecimal)
    

    Please note that there was a bug related to Unicode literals ('\u0001') that is suppose to be fixed in version 2.1, so using the 3rd option won't work on earlier versions. https://issues.apache.org/jira/browse/HIVE-13434