question Re: Where should I add hive.support.sql11.reserved.keywords on Ambari Hive? in Archives of Support Questions (Read Only)

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

muanang — Mon, 07 Mar 2016 16:38:52 GMT

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!

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

nsabharwal — Mon, 07 Mar 2016 16:43:36 GMT

Try custom-hive @Michael Dennis Uanang

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

nsabharwal — Mon, 19 Aug 2019 11:07:57 GMT

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

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

muanang — Mon, 07 Mar 2016 17:13:17 GMT

Thanks again, @Neeraj Sabharwal!

I'll try it and will post the result.

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

aervits — Mon, 07 Mar 2016 20:22:31 GMT

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;

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

muanang — Tue, 08 Mar 2016 08:36:51 GMT

It worked! Thanks!

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

james1 — Tue, 03 Jul 2018 18:34:21 GMT

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