Unable to run Hive CTAS query if user has no write permission on current database

The following was what happens when user hit the issue:

1. user has WRITE permission on database schema1
2. user has ONLY READ permission on database schema2
3. user is currently on database schema2, so the result of after running the query:

   USE schema2;
   

4. user is trying to run the following query:

   CREATE TABLE schema1.table SELECT * FROM schema2.table;
   

The following error will be returned immediately without reaching YARN stage:

Error while compiling statement: FAILED: SemanticException 0:0 Error creating temporary folder on: 
hdfs://nameservice1/user/hive/warehouse/schema2. 
Error encountered near token 'TOK_TMP_FILE' (state=42000,code=40000)

This is caused by Hive bug: HIVE-11427 The workaround is to use the “current” database that is writable by the user who runs the command. So using the above same scenario that user is currently on schema2, if user has WRITE access to “default” or another database, say “schema3”, then the workaround would be to simply switch to either “default” or “schema3” database:

USE schema3;

and then run the same query again:

CREATE TABLE schema1.table SELECT * FROM schema2.table;

This should allow user to by pass the bug. Note: HIVE-11427 is fixed in the following CDH releases: CDH5.6.1, CDH5.7.1