Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Where should I add hive.support.sql11.reserved.keywords on Ambari Hive?

avatar
Super Collaborator

I found this hive conf for 1.2 version.

hive.support.sql11.reserved.keywords=false

Where should I put it on my Ambari>Hive?

Thanks!

1 ACCEPTED SOLUTION

avatar
Master Mentor
6 REPLIES 6

avatar
Master Mentor

Try custom-hive @Michael Dennis Uanang


screen-shot-2016-03-07-at-21243-pm.png

avatar
Master Mentor

@Michael Dennis Uanang

See this

hive> create table test5 (create string);

FailedPredicateException(identifier,{useSQL11ReservedKeywordsForIdentifier()}?)

at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11644)

at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:45920)

at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:38052)

at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:36183)

at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:5222)

at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2648)

at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1658)

at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1117)

at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:202)

at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)

at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:428)

at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:316)

at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1181)

at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1229)

at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1118)

at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1108)

at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:216)

at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:168)

at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:379)

at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:739)

at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)

at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:624)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.hadoop.util.RunJar.run(RunJar.java:221)

at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

FAILED: ParseException line 1:20 Failed to recognize predicate 'create'. Failed rule: 'identifier' in column specification

hive>

2648-screen-shot-2016-03-07-at-23323-pm.png

avatar
Super Collaborator

Thanks again, @Neeraj Sabharwal!

I'll try it and will post the result.

avatar
Super Collaborator

It worked! Thanks!

avatar
Master Mentor

That's a great question. I faced that choice when my DDL statement had reserved keywords like 'name'. It would not work without that property because name is now a reserved keyword and hive checks for that. Notice you have to always include it as even queries against tables that have these reserved words need to have this property unset

SET hive.support.sql11.reserved.keywords=false;
DROP TABLE IF EXISTS tweets;
CREATE EXTERNAL TABLE tweets (  createddate string,  geolocation string,  tweetmessage string,  `user` struct<geoenabled:boolean, id:int, name:string, screenname:string, userlocation:string>)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'LOCATION '/user/root/';
2. 
SET hive.support.sql11.reserved.keywords=false;SELECT DISTINCT tweetmessage, user.name, createddateFROM tweets WHERE user.name = 'Hortonworks'ORDER BY createddate;

avatar
Rising Star

We had to add this to custom hive-site (custom hive-env does not work). Once added to custom hive-site, this worked fine.

James