How can I connect a remote server through Hive?

[#1]

Hi, I am back with another question and as usual I hope I will get a great answer from our members.

I am trying to connect to a hive server in clusterB from clusterA. I am firing the below command

$ hive -h clusterB-ip -p 9083

It connects but when I issue the below hive query I get an error

hive> show tables;
[Thrift Error]: Invalid method name: 'execute'
[Thrift Error]: Hive server is not cleaned due to thrift exception: Invalid method name: 'clean'

I have tried with different Hive server IP's with port combination of 9083 and 10000 which connects but cannot execute any hive query. Any help is much appreciated.

[#2]

@Alex Raj

You are trying to connect to metastore port 9083 which doesn't understand the thrift calls, can you please check whether you have running hiveserver2 instance in your cluster probably on 10000 or 10001?

[#3]

Hiveserver2 instance is running at port 10000 which I have tried already.

[#4 · Accepted solution]

Ok!!!!

Finally I have figured out an alternative for this issue. I used beeline to connect the hiveserver2 of clusterB from clusterA and it worked. happy days!!!!

[#5]

Can be please help me with the command using beeline to connect hiveserver2 of clusterB from clusterA

[#6]

Hi Alex,

Just in case someone else has this issue, I'll try to expand.

Yes, indeed, beeline should be the tool of choice when connecting remotely or even locally as it supports more features and things like security work as expected.

Beeline just creates a TCP connection to the HiveServer2 (on port 10000) and everything else is managed by the HiveServer2 process.

Hive CLI starts an embedded HiverServer so it needs direct access to the Hive Metastore (running on port 9083) and HDFS. This bypasses things like Ranger Hive policies as it can access directly HDFS files. Latest versions of hive CLI don't even include the -h option.

However, there might be reasons why you want the CLI, for example to not overload a production HiveServer2 with an experimental query that uses MapJoins.

To connect using the hive CLI to a different cluster, you can copy the hive-site.xml file from the remote cluster to any local folder and set the HIVE_CONF_DIR variable to this folder:

export HIVE_CONF_DIR=/home/alex/remote

This will allow hive CLI to load the configuration variables needed to access the remote metastore.

Make sure all cluster nodes can resolve the hostnames of all nodes from the remote cluster (update /etc/hosts if you're not using a DNS server).

Then you need to set the fs.defaultFS variable to the remote NameNode address:

hive --hiveconf fs.defaultFS=hdfs://<REMOTE>:8020

Best,

Alex