# How connect from Dremio to Hive Hadoop (without Ranger) **URL:** \ **Category:** Uncategorized\ **Created:** [May 7, 2019, 2:11pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318 "2019-05-07T14:11:36Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 7, 2019, 2:11pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/1 "2019-05-07T14:11:36Z")
How can I connect from Dremio to Hive Hadoop (without Ranger)? ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 7, 2019, 2:42pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/2 "2019-05-07T14:42:07Z")
Hi @oleksandr To connect hive, you can select **Storage Based with User Impersonation** in the **Authorization** And provide the **Hive Metastore Host** hostname. In the advanced options set this property name: hive.server2.enable.doAs value: false ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 7, 2019, 6:04pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/3 "2019-05-07T18:04:34Z")
Hi Venugopal, Thanks for your reply. I’m looking for possibility to login using hive username and password. Is there any way to connect? ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 8, 2019, 6:01am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/4 "2019-05-08T06:01:28Z")
Hi @oleksandr Dremio does not connect to hiveserver2 , we connect to metastore just to get the schema information and directly access the file on HDFS. ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 7:19am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/5 "2019-05-08T07:19:15Z")
Hi Venugopal, If I’m connecting ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/d7b94c50403c7aacefea0a10f77ed09689e5e1ed.jpeg) ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b054da22d735738e777db46315184d55827e97d2.png) I can see tables but once I’m accessing data I’m getting the error: Failure while retrieving dataset [Hive\_test.acquire\_v.ad\_user\_roles]. Please advise. Thanks, Oleksandr ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 8, 2019, 7:49am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/6 "2019-05-08T07:49:05Z")
Hi @oleksandr Can you check at the Profile for the ERROR details, if not please check at the server.log when the query runs. ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 8:38am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/7 "2019-05-08T08:38:37Z")
What is the file name I should look inside? ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/c/c6729b410e1635dbfb0ac48b6e33fde344589499.png) ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 8, 2019, 9:11am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/8 "2019-05-08T09:11:05Z")
@oleksandr It is Server.log. You can check the error stack in the job profile as well under “ **error** ” field @venu ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 12:01pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/9 "2019-05-08T12:01:36Z")
I have found the following: 2019-05-08 14:57:27,698 [metadata-refresh-Hive] WARN c.dremio.exec.store.hive.HiveClient - Failure to run Hive command. Will retry once. org.apache.hadoop.hive.metastore.api.MetaException: org.apache.hadoop.security.AccessControlException: Permission denied: user=username, access=EXECUTE, inode="/apps/hive/warehouse/mdss\_datamart\_work.db/awb\_mdr\_metrics\_z2":hdpmdss:hdpmdss:drwxr-x— what is the interesting that it’s not the table I’m accessing @oleksandr ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 8, 2019, 1:17pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/10 "2019-05-08T13:17:54Z")
@oleksandr can you attach the server.log and profile,will check and let you know. ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 1:45pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/11 "2019-05-08T13:45:00Z")
There was no job executed ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/077fda86e80eaabf530e8ca91a434b812cf2c0f5.png) (Attachment server.log is missing) ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 2:04pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/12 "2019-05-08T14:04:26Z")
Resent [server.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b2788ee861db34a6e2a820610e07be97eb4e6876.zip) (194 KB) ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 8, 2019, 2:41pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/13 "2019-05-08T14:41:40Z")
Can you tell me the table you are tryng to access. from the log i can see access denied for the user “altohnat”. I believe the user is notpart of the group “hdpmass”. 2019-05-08 17:03:36,985 [metadata-refresh-Hive] INFO c.dremio.exec.store.hive.HiveClient - Failure while trying to read table pre\_otp\_load\_z2\_all\_file from db mdss\_datamart\_load org.apache.hadoop.hive.metastore.api.MetaException: org.apache.hadoop.security.AccessControlException: Permission denied: user=altohnat, access=EXECUTE, inode="/data/mdss/datamart/load/ otp/z2/preload\_all\_file":hdpmdss:hdpmdss:drwx------ at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:353) at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkTraverse(FSPermissionChecker.java:292) at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:238) I am able to repro a similar issue by altering the file permissions under the warehouse directory. {code} (org.apache.hadoop.security.AccessControlException) Permission denied: user=dremio, access=READ, inode="/apps/hive/warehouse/voter\_text/voterhive.tsv":hive:hadoop:-rwx------ at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:353) at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:252) at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:190) {code} Permissions on the HDFS: [hive@venumaprclinet dvenu]$ hadoop fs -ls /apps/hive/warehouse/voter\_text/voterhive.tsv -rwx------ 3 hive hadoop 14621 2019-05-02 07:15 /apps/hive/warehouse/voter\_text/voterhive.tsv Can you try by changing the permissions on the HDFS or add the user to the group and try. Thanks @Venugopal_Menda ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 8, 2019, 2:59pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/14 "2019-05-08T14:59:17Z")
Table I’m accessing: ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/3/3e23fc61edb7b20ef06db834486da163baa815ac.jpeg) I will revert to you as soon as I have changed permissions for my user…. It takes time. Thanks a lot ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 27, 2019, 5:09pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/15 "2019-05-27T17:09:58Z")
Hi Venugopal, I’m running on Windows 10 Dremio server and Hive is running on Unix/Linux. I have got one question user in the email chain below “altohnat” (from log files), is this user who started Dremio server or it’s user who logged in to Windows box? How can I change this user name? Best regards, Oleksandr ---
**Author:** ![Venugopal\_Menda](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/venugopal_menda/32/1457_2.png) [@Venugopal\_Menda](https://community.dremio.com/u/Venugopal_Menda)\ **Post date:** [May 28, 2019, 11:21am UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/16 "2019-05-28T11:21:07Z")
Hi @oleksandr The ‘altohnat’ is the user who is accessing the table from Dremio UI on your Windows box. Thanks @Venugopal_Menda ---
**Author:** ![oleksandr](https://avatars.discourse-cdn.com/v4/letter/o/dfb087/32.png) [@oleksandr](https://community.dremio.com/u/oleksandr)\ **Post date:** [May 28, 2019, 12:48pm UTC](https://community.dremio.com/t/how-connect-from-dremio-to-hive-hadoop-without-ranger/3318/17 "2019-05-28T12:48:51Z")
Hi [ **@Venugopal\_Menda** ](https://urldefense.proofpoint.com/v2/url?u=https-3A__community.dremio.com_u_venugopal-5Fmenda&d=DwMFaQ&c=ZP_H8hVPcMPysQ_qMWzcHQ&r=IkSo1UlDjxSZncsLrm2N9JfP3zP2raxPngg0ojK60po&m=0EIq4WpPdYh3pi3i-QQQ7iCKUdPFuT6U6yo3WLrX_is&s=auVpy2dLQec8yf3NZoBCdxywMOvFBRwwXU7dVAx0ajA&e=) It’s not really clear for me. As Dremio server is started using ‘altohnat’ user and I’m logged to Dremio UI using ‘john.smith’ user. Thanks Oleksandr