# Dremio Connection With Remote Hive **URL:** \ **Category:** Uncategorized\ **Created:** [June 23, 2018, 8:56am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344 "2018-06-23T08:56:59Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![vikasjlife](https://avatars.discourse-cdn.com/v4/letter/v/fbc32d/32.png) [@vikasjlife](https://community.dremio.com/u/vikasjlife)\ **Post date:** [June 23, 2018, 8:56am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/1 "2018-06-23T08:56:59Z")
Hi All, Am new to dremio community. i have to connect my lappy dremio to my hive server. but its giving error. where as if dremio and hive both on the same server. than its working fine. Hive Server IP : 192.168.0.72 Dremio installed on : 192.168.0.192 ![Screenshot%20from%202018-06-23%2014-21-59](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/5349aee94415b30fc99718b253b2147e4e0d71e0.png) ---
**Author:** ![anthony](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/anthony/32/670_2.png) [@anthony](https://community.dremio.com/u/anthony)\ **Post date:** [June 23, 2018, 1:06pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/2 "2018-06-23T13:06:55Z")
We definitely allow Hive to be on a different server. In fact, most (if not all) of our deployments working with Hive has it on another box. What exactly does your error say? I can’t see the full error from the screenshot (need to scroll more to the right). I am guessing you may be running into some networking connection issue where you may have to open/allow ports. ---
**Author:** ![vikasjlife](https://avatars.discourse-cdn.com/v4/letter/v/fbc32d/32.png) [@vikasjlife](https://community.dremio.com/u/vikasjlife)\ **Post date:** [June 25, 2018, 5:00am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/3 "2018-06-25T05:00:10Z")
Hi Anthony Tried everything … please refer the below error PLAN ERROR: Failure while retrieving metadata for table hive.dremio.sample. Sql Query SELECT \* FROM hive.dremio.sample (java.net.ConnectException) Call From vikas/192.168.0.192 to localhost:9000 failed on connection exception: java.net.ConnectException: Connection refused; For more details see: [http://wiki.apache.org/hadoop/ConnectionRefused](http://wiki.apache.org/hadoop/ConnectionRefused) sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2 sun.reflect.NativeConstructorAccessorImpl.newInstance():62 sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45 java.lang.reflect.Constructor.newInstance():423 org.apache.hadoop.net.NetUtils.wrapWithMessage():801 org.apache.hadoop.net.NetUtils.wrapException():732 org.apache.hadoop.ipc.Client.getRpcResponse():1485 org.apache.hadoop.ipc.Client.call():1427 org.apache.hadoop.ipc.Client.call():1337 org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke():227 org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke():116 com.sun.proxy.$Proxy116.getFileInfo():-1 org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getFileInfo():787 sun.reflect.NativeMethodAccessorImpl.invoke0():-2 sun.reflect.NativeMethodAccessorImpl.invoke():62 sun.reflect.DelegatingMethodAccessorImpl.invoke():43 java.lang.reflect.Method.invoke():498 org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod():398 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeMethod():163 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invoke():155 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeOnce():95 org.apache.hadoop.io.retry.RetryInvocationHandler.invoke():335 com.sun.proxy.$Proxy117.getFileInfo():-1 org.apache.hadoop.hdfs.DFSClient.getFileInfo():1700 org.apache.hadoop.hdfs.DistributedFileSystem$27.doCall():1436 org.apache.hadoop.hdfs.DistributedFileSystem$27.doCall():1433 org.apache.hadoop.fs.FileSystemLinkResolver.resolve():81 org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus():1433 org.apache.hadoop.fs.FileSystem.exists():1436 com.dremio.exec.store.dfs.FileSystemWrapper.exists():942 com.dremio.exec.store.hive.DatasetBuilder.addInputPath():626 com.dremio.exec.store.hive.DatasetBuilder.buildSplits():442 com.dremio.exec.store.hive.DatasetBuilder.buildIfNecessary():285 com.dremio.exec.store.hive.DatasetBuilder.getDataset():204 com.dremio.exec.store.SimpleSchema.getTableFromSource():366 com.dremio.exec.store.SimpleSchema.getTableWithRegistry():284 com.dremio.exec.store.SimpleSchema.getTable():406 org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable():67 org.apache.calcite.jdbc.CalciteSchema.getTable():219 org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom():117 org.apache.calcite.prepare.CalciteCatalogReader.getTable():106 org.apache.calcite.prepare.CalciteCatalogReader.getTable():73 org.apache.calcite.sql.validate.EmptyScope.getTableNamespace():71 org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace():189 org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():104 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844 org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077 org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891 org.apache.calcite.sql.SqlSelect.validate():208 org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866 org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577 com.dremio.exec.planner.sql.SqlConverter.validate():188 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():165 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():153 com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43 com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66 com.dremio.exec.work.foreman.AttemptManager.run():293 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 Caused By (java.net.ConnectException) Connection refused sun.nio.ch.SocketChannelImpl.checkConnect():-2 sun.nio.ch.SocketChannelImpl.finishConnect():717 org.apache.hadoop.net.SocketIOWithTimeout.connect():206 org.apache.hadoop.net.NetUtils.connect():531 org.apache.hadoop.net.NetUtils.connect():495 org.apache.hadoop.ipc.Client$Connection.setupConnection():681 org.apache.hadoop.ipc.Client$Connection.setupIOstreams():777 org.apache.hadoop.ipc.Client$Connection.access$3500():409 org.apache.hadoop.ipc.Client.getConnection():1542 org.apache.hadoop.ipc.Client.call():1373 org.apache.hadoop.ipc.Client.call():1337 org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke():227 org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke():116 com.sun.proxy.$Proxy116.getFileInfo():-1 org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getFileInfo():787 sun.reflect.NativeMethodAccessorImpl.invoke0():-2 sun.reflect.NativeMethodAccessorImpl.invoke():62 sun.reflect.DelegatingMethodAccessorImpl.invoke():43 java.lang.reflect.Method.invoke():498 org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod():398 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeMethod():163 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invoke():155 org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeOnce():95 org.apache.hadoop.io.retry.RetryInvocationHandler.invoke():335 com.sun.proxy.$Proxy117.getFileInfo():-1 org.apache.hadoop.hdfs.DFSClient.getFileInfo():1700 org.apache.hadoop.hdfs.DistributedFileSystem$27.doCall():1436 org.apache.hadoop.hdfs.DistributedFileSystem$27.doCall():1433 org.apache.hadoop.fs.FileSystemLinkResolver.resolve():81 org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus():1433 org.apache.hadoop.fs.FileSystem.exists():1436 com.dremio.exec.store.dfs.FileSystemWrapper.exists():942 com.dremio.exec.store.hive.DatasetBuilder.addInputPath():626 com.dremio.exec.store.hive.DatasetBuilder.buildSplits():442 com.dremio.exec.store.hive.DatasetBuilder.buildIfNecessary():285 com.dremio.exec.store.hive.DatasetBuilder.getDataset():204 com.dremio.exec.store.SimpleSchema.getTableFromSource():366 com.dremio.exec.store.SimpleSchema.getTableWithRegistry():284 com.dremio.exec.store.SimpleSchema.getTable():406 org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable():67 org.apache.calcite.jdbc.CalciteSchema.getTable():219 org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom():117 org.apache.calcite.prepare.CalciteCatalogReader.getTable():106 org.apache.calcite.prepare.CalciteCatalogReader.getTable():73 org.apache.calcite.sql.validate.EmptyScope.getTableNamespace():71 org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace():189 org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():104 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859 org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844 org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077 org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60 org.apache.calcite.sql.validate.AbstractNamespace.validate():84 org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910 org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891 org.apache.calcite.sql.SqlSelect.validate():208 org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866 org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577 com.dremio.exec.planner.sql.SqlConverter.validate():188 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():165 com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():153 com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43 com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66 com.dremio.exec.work.foreman.AttemptManager.run():293 java.util.concurrent.ThreadPoolExecutor.runWorker():1149 java.util.concurrent.ThreadPoolExecutor$Worker.run():624 java.lang.Thread.run():748 ---
**Author:** ![yufeldman](https://avatars.discourse-cdn.com/v4/letter/y/c77e96/32.png) [@yufeldman](https://community.dremio.com/u/yufeldman)\ **Post date:** [June 25, 2018, 6:00am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/4 "2018-06-25T06:00:07Z")
could you try to include core-site.xml on dremio classpath - linking to dremio conf directory? Or may be try to include namenode as an advanced property while setting hive source. It feels like Dremio can’t connect to HDFS as it does not know namenode host/port. ---
**Author:** ![jason](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jason/32/3129_2.png) [@jason](https://community.dremio.com/u/jason)\ **Post date:** [June 25, 2018, 10:54pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/5 "2018-06-25T22:54:31Z")
Looks like there’s a reference to localhost somewhere in hive or hdfs that gets passed to dremio and dremio tries to use that hostname to connect to the namenode. Could you post a screenshot of the source configuration within dremio and a copy of core-site and hdfs-site? ---
**Author:** ![yufeldman](https://avatars.discourse-cdn.com/v4/letter/y/c77e96/32.png) [@yufeldman](https://community.dremio.com/u/yufeldman)\ **Post date:** [June 25, 2018, 11:16pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/6 "2018-06-25T23:16:44Z")
Here is screenshot while setting up Hive Source: ![55%20PM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/86c8a0481048c5b2fa09e9037c6d22c75a3ab4eb.png) By clicking on “Add Property” you should be able to add property defining NameNode location. As far as having core-site.xml on Dremio classpath - it depends on your installation - you either copy core-site.xml into “conf” directory of your Dremio installation or link core-site.xml to that location. Not sure if screenshot is going to help here (it is on command line) ---
**Author:** ![vikasjlife](https://avatars.discourse-cdn.com/v4/letter/v/fbc32d/32.png) [@vikasjlife](https://community.dremio.com/u/vikasjlife)\ **Post date:** [June 27, 2018, 6:17am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/7 "2018-06-27T06:17:21Z")
i have copy and paste the core-sites.xml the file in dremio conf folder. but its still not working ---
**Author:** ![jason](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jason/32/3129_2.png) [@jason](https://community.dremio.com/u/jason)\ **Post date:** [June 27, 2018, 4:56pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/8 "2018-06-27T16:56:34Z")
Hi Vikas, Could you upload a screenshot of the source configuration within dremio and copies of core-site and hdfs-site? ---
**Author:** ![vikasjlife](https://avatars.discourse-cdn.com/v4/letter/v/fbc32d/32.png) [@vikasjlife](https://community.dremio.com/u/vikasjlife)\ **Post date:** [June 30, 2018, 10:34am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/9 "2018-06-30T10:34:13Z")
Hi jason please check attached files [files.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/2e90e87bc40cb207dfbe816ffb260aa9a517c7f4.zip) (3.1 KB) ---
**Author:** ![jason](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jason/32/3129_2.png) [@jason](https://community.dremio.com/u/jason)\ **Post date:** [June 30, 2018, 5:24pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/10 "2018-06-30T17:24:48Z")
it looks looks like the issue is that `fs.defaultFS` is set to `hdfs://localhost:9000` so `localhost` is the host that Dremio gets from the configuration to access HDFS. Change this property in Ambari/Cloudera Manager to be `hdfs://192.168.0.72:9000`, restart Hadoop or the necessary services then remove the source from dremio and re-add it ---
**Author:** ![cbhatnagar7101](https://avatars.discourse-cdn.com/v4/letter/c/ccd318/32.png) [@cbhatnagar7101](https://community.dremio.com/u/cbhatnagar7101)\ **Post date:** [October 18, 2018, 3:41am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/11 "2018-10-18T03:41:41Z")
Hi Jason, I tried all the steps, and removed all hardcodings to localhost. I have configured all as per the above in core-site.xml . Now I get a different kind of error : DATA\_READ ERROR: Failed to initialize Hive record reader Dataset split key hdfs://sandbox.kylo.io/user/hive/warehouse/test.db/t\_customer\_\_0 HIVE\_SUB\_SCAN Location 0:0:7 SqlOperatorImpl HIVE\_SUB\_SCAN Location 0:0:7 Fragment 0:0 [Error Id: 2df0ce62-cba8-4339-ae9f-d43da058ccfc on localhost:31010] (org.apache.hadoop.hdfs.BlockMissingException) Could not obtain block: BP-82876869-127.0.0.1-1527287107078:blk\_1073742969\_2157 file=/user/hive/warehouse/test.db/t\_customer/20180925\_162010\_00014\_utnbv\_090cff90-1bf9-47df-bad7-65a2c9c52a1c org.apache.hadoop.hdfs.DFSInputStream.refetchLocations():1052 org.apache.hadoop.hdfs.DFSInputStream.chooseDataNode():1036 org.apache.hadoop.hdfs.DFSInputStream.chooseDataNode():1015 org.apache.hadoop.hdfs.DFSInputStream.blockSeekTo():647 org.apache.hadoop.hdfs.DFSInputStream.readWithStrategy():926 org.apache.hadoop.hdfs.DFSInputStream.read():982 java.io.DataInputStream.readFully():195 java.io.DataInputStream.readFully():169 org.apache.hadoop.hive.ql.io.RCFile$Reader.init():1462 org.apache.hadoop.hive.ql.io.RCFile$Reader.():1363 org.apache.hadoop.hive.ql.io.RCFile$Reader.():1343 org.apache.hadoop.hive.ql.io.RCFileRecordReader.():100 org.apache.hadoop.hive.ql.io.RCFileInputFormat.getRecordReader():58 com.dremio.exec.store.hive.exec.HiveRCFileReader.internalInit():52 com.dremio.exec.store.hive.exec.HiveAbstractReader.setup():198 com.dremio.sabot.op.scan.ScanOperator$1.run():189 com.dremio.sabot.op.scan.ScanOperator$1.run():185 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1836 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():185 com.dremio.sabot.op.scan.ScanOperator.setupReader():177 com.dremio.sabot.op.scan.ScanOperator.setup():163 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():560 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():530 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():344 com.dremio.sabot.exec.fragment.FragmentExecutor.run():234 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():86 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():591 com.dremio.sabot.task.AsyncTaskWrapper.run():107 com.dremio.sabot.task.slicing.SlicingThread.run():102 Note : the above works if I have the worker disabled and master executing. The master is running on the VM with hadoop, and worker is on another VM with just vanilla linux. ---
**Author:** ![jason](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jason/32/3129_2.png) [@jason](https://community.dremio.com/u/jason)\ **Post date:** [October 18, 2018, 9:42pm UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/12 "2018-10-18T21:42:42Z")
hi @cbhatnagar7101 could you provide core-site.xml, hdfs-site.xml, and server.log from the executor that is on the other VM (not the hadoop VM)? and also the profile from the failed query. Thanks, Jason ---
**Author:** ![anthony](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/anthony/32/670_2.png) [@anthony](https://community.dremio.com/u/anthony)\ **Post date:** [October 19, 2018, 1:18am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/13 "2018-10-19T01:18:41Z")
`Failed to initialize Hive record reader Dataset split key` can be caused by security issues. Can you talk a little bit about your cluster’s security setup? Also, hive-site.xml would help too ---
**Author:** ![cbhatnagar7101](https://avatars.discourse-cdn.com/v4/letter/c/ccd318/32.png) [@cbhatnagar7101](https://community.dremio.com/u/cbhatnagar7101)\ **Post date:** [October 19, 2018, 3:09am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/14 "2018-10-19T03:09:19Z")
hi Jason and Anthony, my setup is simple. It is a single node VM from Kylo. I have disabled the firewalls to ensure no security issues create this. I have attached : - Core-Site.xml and Hive-Site.xml - Query Profiles - Server Log from executer Thanks for the help.[server.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/0e7fa67f14c308b9cabccf80f31302f491146c2b.zip) (53.5 KB) ---
**Author:** ![cbhatnagar7101](https://avatars.discourse-cdn.com/v4/letter/c/ccd318/32.png) [@cbhatnagar7101](https://community.dremio.com/u/cbhatnagar7101)\ **Post date:** [October 19, 2018, 4:36am UTC](https://community.dremio.com/t/dremio-connection-with-remote-hive/1344/15 "2018-10-19T04:36:20Z")
Thanks guys. I figured it out. It was the way mysql was behaving when it was being targeted to log in with hive. I used a new hosts entry to tackle this, and bring hive services up with another one. It worked.