Hive ignores hive.security.authorization.sqlstd.confwhitelist.append when added in Hive Custom Site

[#1]

I have a problem with running HQL scripts where I need to set user defined variables at run-time. Any kind of statement like "set myvar=1" I get an exception "Cannot modify myvar1 at runtime. It is not in list of params that are allowed to be modified at runtime". So, I went and whitelisted this variable as per hive manual in Hive custom site. Restarted all affected services, tried again just to see the same error. I can however assign values to variables which are already whitelisted.

Environment: off the shelf HDP 2.3.2 sandbox running on vmware esxi 5.5

Any clues?

[#2]

What is your mode of access? I am guessing you have enabled SQL Standard Authorization and are accessing using the HiveServer2, in that case have you restarted the HiveServer2? Also how did you make the change and restart services, make sure you do it through Ambari UI.

[#3 · Accepted solution]

So, the solution is to properly define the regex list in

The above definition is incorrect for myvar1. The manual states that the list has to be defined as regex, and in my case delimited by pipe.

The correct value has to be "|myvar.*" (without double quotes). The ".*" may not be necessary.

This custom list will then be appended to an existing whitelist. Also I had to restart all hive related services.