HDP Upgrade : Hive Behavioral changes : .14 to 1.21 (HDP 2.1 to HDP 2.4)

[#1]

We have noticed production job failures where customer upgraded their hive from .14 (HDP 2.1) to the latest version(>1.2.x) and resulted in critical jobs failing (not to mention the severity 1 case). This is due to the changes in the reserved words between the source and target hive versions.

For example, Word 'date' is not a reserved word in Hive.14 but inHive 1.2.1 it is. Same is the case with REGEXP and RLIKE.

Here are the reserved keywords which hive.support.sql11.reserved.keywords support. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ReservedKeywor...

There are two ways if the user still would like to use those reserved keywords as identifiers:

1).use quoted identifiers,

2).set hive.support.sql11.reserved.keywords=false.

This would be the best option though code change is required. To illustrate option 1,

For example,

With word "user" being a keyword, We can use it as an identifier like the below SQL.

SELECT createddate, `user`.screenname FROM twitter_json4 WHERE `user`.name LIKE 'Sarah%';

The second option is quite easier to write queries.

However during the upgrade or If we need to enable the hive.support.sql11.reserved.keywords to true for some reason, then the existing queries(without using quotes) hive throws the following error

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)

OPTION 2:

hive> set hive.support.sql11.reserved.keywords; hive.support.sql11.reserved.keywords=false

create table table (user string); ==> table and user are keywords.

OK

Time taken: 1.458 seconds

hive> desc table;

OK user

string

Time taken: 0.34 seconds,

Fetched: 1 row(s)

hive> show tables;

OK

table

Time taken: 0.075 seconds,

Fetched: 1 row(s)

hive> set hive.support.sql11.reserved.keywords=true; ===> Enabling the property.

hive> show tables;

OK table

Time taken: 0.041 seconds,

Fetched: 1 row(s)

hive> show tables;

OK table

Time taken: 0.039 seconds,

Fetched: 1 row(s)

hive> describe table;

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.tabTypeExpr(HiveParser.java:15574)

Setting hive.support.sql11.reserved.keywords to false would allow the user to use the keywords as identifiers without hive throwing any exception. We need to be considerate of the fact that when enabling the hive.support.sql11.reserved.keywords to true would require the use of quotes to differentiate the keyword and identifier.

Feel free to get in touch with Hortonworks Support incase of any issues.