# Hive connectivity issues **URL:** \ **Category:** Uncategorized\ **Created:** [January 4, 2019, 7:32am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488 "2019-01-04T07:32:06Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Sneha\_Krishnaswamy](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@Sneha\_Krishnaswamy](https://community.dremio.com/u/Sneha_Krishnaswamy)\ **Post date:** [January 4, 2019, 7:32am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/1 "2019-01-04T07:32:06Z")
Hi, I’m using Hive on top of S3 as my physical datasource on Dremio for evaluation purpose. I’ve a few queries around it: 1. Do all Hive tables be created with s3a filesystem? I get the following error when i try to connect to an external hive table created on top of s3 filesystem - `java.lang.NoClassDefFoundError: org/jets3t/service/S3ServiceException` Is there a way around for dremio read s3 or s3n filesystems as well? 1. How is the access control on Hive tables managed? I’ve a few tables created in hive with s3a filesystem and I was able to connect to them and query them. But i’m getting the following error right now - `2018-12-31 02:40:58,971 [qtp382934088-1539] INFO c.d.e.catalog.ManagedStoragePlugin - User Error Occurred [ErrorId: d3c33d5b-0b9d-4787-ba1a-15b99ca8de30] com.dremio.common.exceptions.UserException: Access denied reading dataset "connstring".schema.tablename.` There is no change in the instance profile that grants the s3 acess. There is no change in the security group to access hive thrift server. Infact i even tried removing the source and adding it back. It retrieves the tables when the new connection is added; but still throws the same Access Denied error when querying. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 8, 2019, 4:03am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/2 "2019-01-08T04:03:57Z")
Hi @Sneha_Krishnaswamy For #1, please add below to the Hive source-advanced-Additional Properties Name: fs.s3.impl Value: org.apache.hadoop.fs.s3a.S3AFileSystem For #2, Can you please send us the profile? Thanks @balaji.ramaswamy ---
**Author:** ![Sneha\_Krishnaswamy](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@Sneha\_Krishnaswamy](https://community.dremio.com/u/Sneha_Krishnaswamy)\ **Post date:** [January 8, 2019, 5:51am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/3 "2019-01-08T05:51:36Z")
I’ve the following set of properties in my Hive connection: store.hive.use\_stats\_in\_metastore = true fs.s3.impl = org.apache.hadoop.fs.s3a.S3AFileSystem fs.s3a.connection.maximum = 5000 fs.s3a.threads.max = 7000 Attached is the query profile [469daa30-a02b-457d-ba45-9fc4e09f49e8.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b9ff5bfcdd6e8d91f18f72c96d2ce6c05929ad19.zip) (3.5 KB) Note: The query failed at the planning stage itself. ---
**Author:** ![Sneha\_Krishnaswamy](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@Sneha\_Krishnaswamy](https://community.dremio.com/u/Sneha_Krishnaswamy)\ **Post date:** [January 14, 2019, 3:56am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/4 "2019-01-14T03:56:41Z")
Bumping it. @balaji.ramaswamy any suggestions for me to try out? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 14, 2019, 5:20am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/5 "2019-01-14T05:20:19Z")
2 things @Sneha_Krishnaswamy 1. connection.maximum should \>= thread.max, so make connection.maximum also 7000 Is impersonation turned on? If yes then does both the process that is running Dremio and the Dremio login use has access to the HDFS folder/file? Thanks @balaji.ramaswamy ---
**Author:** ![Sneha\_Krishnaswamy](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@Sneha\_Krishnaswamy](https://community.dremio.com/u/Sneha_Krishnaswamy)\ **Post date:** [January 16, 2019, 7:36am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/6 "2019-01-16T07:36:44Z")
Made the change in connection.maximum. to be 9000 now. Also, currently I’ve whitelisted the dremio security group to allow access to the hive emr. Do i’ve to specifically do any sort of whitelisting for the core nodes / hdfs? Adding to the above answer; - The Hive tables are created on top of S3. Are you referring to the HDFS of the core nodes on the Hive EMR cluster? - Where is impersonation supposed to be enabled? If its not enabled by default, I don’t think i’ve turned it on. And from what i understand from the documentation, impersonation is enabled while adding HDFS source. I’ve added Hive as a source in dremio and I connect via the Hive thrift server. So I’m not sure where to do this. The issue to me currently looks like, Dremio is able to connect to Hive thrift server, but not to Hive itself. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 16, 2019, 3:58pm UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/7 "2019-01-16T15:58:36Z")
Hi @Sneha_Krishnaswamy Are the Dremio executors running on EC2? If yes then the Dremio executor should be able to talk to the EMR nodes as during query execution Dremio executors will have to query the HDFS nodes that actually contain the Parquet/ORC data. Makes sense? Impersonation can be overidden via the Hive source form on the Dremio UI by going to Advance-Additional Properties Name: hive.server2.enable.doAs Value: False (All queries will hit HDFS as unix user that runs the Dremio process) Value: True (Deep Metadata probe done by unix user that runs the Dremio process while the actual data is queries as the user who logs into Dremio) Note: To check the current impersonation setting on your Hive, open hive-site.xml on the EMR node that runs the metastore and check for value of hive.server2.enable.doAs Thanks @balaji.ramaswamy ---
**Author:** ![mmohan](https://avatars.discourse-cdn.com/v4/letter/m/f05b48/32.png) [@mmohan](https://community.dremio.com/u/mmohan)\ **Post date:** [October 2, 2019, 2:51pm UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/8 "2019-10-02T14:51:56Z")
Hi @balaji.ramaswamy how the user impersonation works when the hive is running in a different cluster. I have dremio user in my cluster where dremio is installed and not in the cluster where hive is. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [October 2, 2019, 3:48pm UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/9 "2019-10-02T15:48:35Z")
@mmohan Check the value of “hive.server2.enable.doAs” in the hive-site.xml on the Hive server. You can override the setting via Dremio using the steps suggested above Thanks @balaji.ramaswamy ---
**Author:** ![mmohan](https://avatars.discourse-cdn.com/v4/letter/m/f05b48/32.png) [@mmohan](https://community.dremio.com/u/mmohan)\ **Post date:** [October 2, 2019, 5:25pm UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/10 "2019-10-02T17:25:08Z")
Thanks @balaji.ramaswamy for quick turnaround. Yes i set the doas property to false in hive connection advanced setup. and in coresite.xml of the cluster where hive is, i setup the parameters ``` hadoop.proxyuser.dremio.hosts * hadoop.proxyuser.dremio.groups * hadoop.proxyuser.dremio.users * ``` do i need to have dremio user in the hive cluster as well? ---
**Author:** ![jalandhar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jalandhar/32/2553_2.png) [@jalandhar](https://community.dremio.com/u/jalandhar)\ **Post date:** [June 9, 2020, 6:31pm UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/11 "2020-06-09T18:31:01Z")
Hello guys, I have been exploring dremio AWS edition for the last few days for our business use cases. However, I am still facing lot of issues in integrating with hive. I have tried almost all the steps that were mentioned in different community threads. As of now, here are my major issues with dremio: 1. org/jets3t/service/ServiceException While accessing the hive tables, i am getting this exception multiple times I have tried below things, but it had no luck Have added below properties in Hive source-advanced-Additional Properties hive.server2.enable.doAs=false fs.s3.impl = org.apache.hadoop.fs.s3a.S3AFileSystem fs.s3a.connection.maximum = 9000 fs.s3a.threads.max = 7000 also have updated below properties in hive cluster: hadoop.proxyuser.dremio.hosts _hadoop.proxyuser.dremio.groups_ hadoop.proxyuser.dremio.users \* But i’m still getting the same error. 1. The second issue, i am getting is: ClassNotFoundException: org.apache.hadoop.hive.contrib.serde2.JsonSerde We are accessing external hive tables created on s3. I tried fixing this by adding hive-json-serde.jar jar /opt/dremio/plugins/connectors/hive2.d/ which has the missing class, but still it din’t solve my problem Could anyone help me on these issues? Our dremio AWS edition details: Build: 4.2.1-202004111451200819-0c3ecaea Edition: AWS Edition Build Time:04/11/2020 20:28:17 Thanks, Jalandhar ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [June 10, 2020, 6:15am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/12 "2020-06-10T06:15:31Z")
@jalandhar For the JsonSerde issue, cd \/plugins/connectors mkdir hive2-ee.d (if using Hive 2.x source) mkdir hive3-ee.d (If using Hive 3.x source) cp -p hive-hcatalog-core-.jar cd \/plugins/connectors/hive2-ee.d (if 2.x Hive source) cp -p hive-hcatalog-core-.jar cd \/plugins/connectors/hive3-ee.d (if 3.x Hive source) Restart Dremio Query the table ---
**Author:** ![jalandhar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jalandhar/32/2553_2.png) [@jalandhar](https://community.dremio.com/u/jalandhar)\ **Post date:** [June 10, 2020, 8:03am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/13 "2020-06-10T08:03:24Z")
Thanks @balaji.ramaswamy I have done the above steps and still i’m seeing the issue. Also when i have seen the dremio server.log, i don’t see the jars from \/plugins/connectors/hive2-ee.d or \/plugins/connectors/hive2.d being loaded in the classpath. I also tried adding jars in \/jars and checked, still dremio is not picking those jars. Do i need to do any more steps for fixing this. Also can you help me where can add our custom jars to dremio’s classpath Thanks, Jalandhar ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [June 22, 2020, 8:07am UTC](https://community.dremio.com/t/hive-connectivity-issues/2488/14 "2020-06-22T08:07:24Z")
@jalandhar If this is Dremio 4.2.2 or later, have you also symlinked DREMIO\_HOME/conf to DREMIO\_HOME/plugins/connector/hive2.d-ee? See documentation below? [http://docs.dremio.com/data-sources/hive.html#hive-configuration](http://docs.dremio.com/data-sources/hive.html#hive-configuration)