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

[#1]

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!

[#2 · Accepted solution]

Try custom-hive @Michael Dennis Uanang

[#3]

@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>

[#4]

Thanks again, @Neeraj Sabharwal!

I'll try it and will post the result.

[#5]

It worked! Thanks!

[#6]

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;

[#7]

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