ERROR: "Permission denied: user [xxx] does not have [WRITE] privilege on [/xxx]" when running INSERT OVERWRITE LOCAL DIRECTORY query from Beeli

[#1]

0: jdbc:hive2://xxx.com:10000> insert overwrite local directory '/xxx row format delimited fields terminated by '|' null defined as '' select * from xx.yy limit 20;
Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [xxx] does not have [WRITE] privilege on [/xxx] (state=42000,code=40000)

Same query (INSERT OVERWRITE LOCAL DIRECTORY) works fine with Hive CLI:

hive> insert overwrite local directory '/xxx' row format delimited fields terminated by '|' null defined as '' select * from xx.yy limit 20;
Query ID = xx_abae1e37-0e40-4743-a7c6-a33ca9e5156c
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1492503032060_2596)
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 4 4 0 0 0 0
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 10.13 s
--------------------------------------------------------------------------------
Moving data to local directory /xxx
OK
Time taken: 14.594 seconds

Cause

The issue is related to Jira HIVE-11666 and behavioral difference between Hive CLI and Beeline.

When the INSERT OVERWRITE LOCAL DIRECTORY query is run from Hive CLI, it writes local host whereas Beeline writes to node directory where Hiveserver2 is running.

Solution

This is a known limitation and Hortonworks feature request RMP-8974 has been raised to address this behavioural difference between Hive CLI and Beeline in a future release.

Workaround

Provide permissions for xyz user on /xyz directory on Hiveserver2 machine.

[#2]

Hi Sindhu

We are facing the same issue with insert ovewrite but it is not a local directory. We are facing this issue after upgrade from 2.5.3 to 2.6.1

Tried running with different destination . It created the folder but fails with below error

Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [xyz] does not have [WRITE] privilege on [/tmp/*] (state=42000,code=40000) Closing: 0: jdbc:hive2://host:2181,host:2181,host:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2 Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [xyz] does not have [WRITE] privilege on [/user/*] (state=42000,code=40000) Closing: 0: jdbc:hive2://host:2181,host:2181,host:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2