Resolve “Cannot modify at runtime. It is not in list of params that are allowed to be modified at runtime” in Hive

“Cannot modify at runtime. It is not in the list of params that are allowed to be modified at runtime” this shows that you have added a property, Which is not allowed to be edited in the runtime

Symptom

Due to this issue, you might be observing the below symptoms

• If you have added or edited this property (In my case, I have added “hive.input.format”) in the client config files, This will cause connection failure with the Hive Server 2 (Unable to connect to HS2 using any client)
• If you are editing the property in the Beeline shell session, It will terminate the connection or cause query failure 

In both the case, You will see the below exception in the Hive Server 2 logs and in the Client logs 

Console Log:

0: jdbc:hive2://-> set hive.input.format=parq;

Error: Error while processing statement: Cannot modify hive.input.format at runtime. It is not in list of params that are allowed to be modified at runtime (state=42000,code=1)

Hive server 2 logs:

[main]: WARN jdbc.HiveConnection: Failed to connect to <HS2 hostname>:10000
Error: Could not open client transport with JDBC Uri: jdbc:hive2://;: Failed to open new session: java.lang.IllegalArgumentException: Cannot modify hive.input.format  at runtime. It is not in list of params that are allowed to be modified at runtime (state=08G01,code=0)
java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://;: Failed to open new session: java.lang.IllegalArgumentException: Cannot modify hive.input.format at runtime. It is not in list of params that are allowed to be modified at runtime
        at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:364)
        at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:107)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:208)
        at org.apache.hive.beeline.DatabaseConnection.connect(DatabaseConnection.java:145)
        at org.apache.hive.beeline.DatabaseConnection.getConnection(DatabaseConnection.java:209)
        at org.apache.hive.beeline.Commands.connect(Commands.java:1679)
        at org.apache.hive.beeline.Commands.connect(Commands.java:1574)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

Resolution

We need to whitelist the property that we are trying to edit to resolve the issue, Which means, We are allowing the client to edit this property in the runtime

Add the below property in the Hive service config (hive-site.xml), This requires a Hive service restart

property: hive.security.authorization.sqlstd.confwhitelist.append

value:      hive\.input\.format

If you are using Cloudera Distribution, We can add as below

CM–> Hive on Tez –>Hive Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml:

property: hive.security.authorization.sqlstd.confwhitelist.append

value:      hive\.input\.format

If you want to add multiple properties in the whitelist, We can use the “|” symbol and add multiple properties as below

hive\.acid\.direct\.insert\.enabled|hive\.input\.format

Adding this property to the white list would allow us to edit this property in the runtime as below

0: jdbc:hive2://-> set hive.input.format=parq;

No rows affected (0.176 seconds)

0: jdbc:hive2://-> 

Good Luck with your Learning!