Solution: ALTER TABLE PARTITION SET LOCATION does not work from script execution using hive –f or from beeline.

[#1]

Steps to replicate:

hdfs dfs -ls /apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz
Found 1 items -rw-rw-rw- 1 hive hive 38258 2017-06-27 21:04 /apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz/000000_0 

USING hive -f script

cat /tmp/test.txt
ALTER TABLE testraj.testtable PARTITION (filename="test.csv.gz") SET LOCATION "hdfs://ip-1-1-1-1.us-west-2.compute.internal:8020/apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz"; 

Error from hive -f scriptname:

[hive@ip-1-1-1-1 rbiswas]$ hive -f /tmp/test.txt 

Logging initialized using configuration in file:/etc/hive/2.5.3.0-37/0/hive-log4j.properties FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter partition. alter is not possible [hive@ip-1-1-1-1 rbiswas]$ 

Error from Beeline:

0: jdbc:hive2://ip-1-1-1-1.us-west-2.com> ALTER TABLE testraj.testtable PARTITION (filename="test.csv.gz") SET LOCATION 'hdfs://ip-1-1-1-1.us-west-2.compute.internal:8020/apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz';

 Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter partition. alter is not possible (state=08S01,code=1) 0: jdbc:hive2://ip-1-1-1-1.us-west-2.com> 

It does works if directly logged into HIVECLI:

hive> ALTER TABLE testraj.testtable PARTITION (filename="test.csv.gz") SET LOCATION "hdfs://ip-1-1-1-1.us-west-2.compute.internal:8020/apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz";
 OK Time taken: 0.605 seconds 

Solution:

In the script, rather than using schema_name.tablename, use 2 separate lines

use dbname;
alter table tablename; --Note no schema name prefix

The Same solution is applicable for beeline.

So the script becomes:

cat /tmp/test.txt

use testraj;
ALTER TABLE testtable PARTITION (filename="test.csv.gz") SET LOCATION "hdfs://ip-1-1-1-1.us-west-2.compute.internal:8020/apps/hive/warehouse/testraj.db/testtable/filename=test.csv.gz";