Not able to set dfs.replication while running beeline command for HIVE table

[#1]

Hello Team,

I am trying to run beeline commandline to export the hive table to a location. i am trying to set replication factor to 1 but it if failing with following error :
Error: Error while processing statement: Cannot modify dfs.replication at runtime. It is not in list of params that are allowed to be modified at runtime (state=42000,code=1)

Cluster details :
3 nodes cluster 
and in all hvie-site.xml i have set the following property and then restarted hive and hiveserver2 
<property>
<name>hive.security.authorization.sqlstd.confwhitelist.append</name>
<value>mapred.*|hive.*|mapreduce.*|spark.*|dfs.*</value>
</property>

Beeline Command : 

sudo -u hive beeline -u "jdbc:hive2://machine1.dev.domain.com:2181/default;password=hive;principal=hive/_HOST@DEV.domain.COM;serviceDiscoveryMode=zooKeeper;ssl=true;sslTrustStore=/var/lib/cloudera-scm-agent/agent-cert/cm-auto-global_truststore.jks;trustStorePassword=****;user=hive;zooKeeperNamespace=hiveserver2" -n hive--showHeader=false --outputformat=tsv2 -e "Set dfs.replication=1; use testdb; export table newt1 to '/staging/exporttable/testdb/newt1';"

---------------------------------------------------------------------------------------------------------------------

sudo -u hive beeline -u "jdbc:hive2://machine1.dev.domain.com:2181/default;password=hive;principal=hive/_HOST@DEV.domain.COM;serviceDiscoveryMode=zooKeeper;ssl=true;sslTrustStore=/var/lib/cloudera-scm-agent/agent-cert/cm-auto-global_truststore.jks;trustStorePassword=****;user=hive;zooKeeperNamespace=hiveserver2"  --hiveconf dfs.replication=1  -n hive--showHeader=false --outputformat=tsv2 -e "use testdb; export table newt1 to '/staging/exporttable/testdb/newt1';"

Error : 

23/10/31 02:01:14 [main]: ERROR jdbc.Utils: Unable to read HiveServer2 configs from ZooKeeper
Error: Could not open client transport for any of the Server URI's in ZooKeeper: Failed to open new session: java.lang.IllegalArgumentException: Cannot modify dfs.replication at runtime. It is not in list of params that are allowed to be modified at runtime (state=08S01,code=0)

[#2]

Hi Jayes,

Please try first logging into beeline interactive shell and then run each command separately and see if it is working fine. 

If the issue still persist check if you are able to run from another user who has access to the DB, table. 

[#3]

0: jdbc:hive2://machine1.***.***.c> show databases;
INFO : Compiling command(queryId=hive_20231107010124_eaff8e06-cd0a-4484-a1b0-80f9eb2bae64): show databases
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:database_name, type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling command(queryId=hive_20231107010124_eaff8e06-cd0a-4484-a1b0-80f9eb2bae64); Time taken: 0.038 seconds
INFO : Executing command(queryId=hive_20231107010124_eaff8e06-cd0a-4484-a1b0-80f9eb2bae64): show databases
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20231107010124_eaff8e06-cd0a-4484-a1b0-80f9eb2bae64); Time taken: 0.037 seconds
INFO : OK
+---------------------+
| database_name |
+---------------------+
| default |
| information_schema |
| largedb |
| newtest |
| sys |
| testdb |
| underscore |
| underscoretest |
+---------------------+

0: jdbc:hive2://machine1.***.***.c> set def-replication=1;
Error: Error while processing statement: Cannot modify def-replication at runtime. It is not in list of params that are allowed to be modified at runtime (state=42000,code=1)

[#4]

IT is still not working tried from the beeline shell too

[#5 · Accepted solution]

@jayes Hive reads this parameter value from hdfs-site.xml. So, you should probably consider setting the value under HDFS service. Nevertheless, if you want to export that parameter in Hive/beeline cli, you could try setting 'hive.security.authorization.sqlstd.confwhitelist.append' correctly.

e.g.

<name>hive.security.authorization.sqlstd.confwhitelist.append</name>
<value>mapred\..*|hive\..*|mapreduce\..*|spark\..*|dfs\..*</value>

[#6]

Hi @smruti ,

Yes agree that we can set parameter in hdfs-site.xml, so that it will reflect, but i dont want to change replication factor of whole cluster. Its just while running command i want to change.

i tried setting the property in hive-site.xml on all 3 nodes of cluster and restarted the hive service, but still it is failing with same error.

<property>
<name>hive.security.authorization.sqlstd.confwhitelist.append</name>
<value>mapred\..*|hive\..*|mapreduce\..*|spark\..*|dfs\..*</value>
</property>

Error :

Connected to: Apache Hive (version 3.1.3000.7.1.7.0-551)
Driver: Hive JDBC (version 3.1.3000.7.1.7.0-551)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Error: Error while processing statement: Cannot modify dfs.replication at runtime. It is not in list of params that are allowed to be modified at runtime (state=42000,code=1)

[#7]

@jayes Please make sure that you have set this property in "HiveServer2 Advanced Configuration Snippet (Safety Valve) for hive-site.xml" under Hive on Tez configuration.

I tried this and it works for me:

Beeline version 3.1.3000.7.1.7.2000-305 by Apache Hive
0: jdbc:hive2://c1649-node2.coelab.cloudera.c> set  dfs.replication=1;
No rows affected (0.208 seconds)
0: jdbc:hive2://c1649-node2.coelab.cloudera.c> set hive.security.authorization.sqlstd.confwhitelist.append;
+----------------------------------------------------+
|                        set                         |
+----------------------------------------------------+
| hive.security.authorization.sqlstd.confwhitelist.append=mapred\..*|hive\..*|mapreduce\..*|spark\..*|dfs\..* |
+----------------------------------------------------+
1 row selected (0.109 seconds)