question Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers in Support Questions

Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 16 Sep 2022 08:51:59 GMT

Hi All,

I've installed a CDH4.5 Hadoop cluster on Amazon EC2 using the instructions here:

http://blog.cloudera.com/blog/2013/03/how-to-create-a-cdh-cluster-on-amazon-ec2-via-cloudera-manager/

All seems to be working OK, however I can't connect to it from a Windows VM on my laptop using either the Hive or Impala ODBC drivers. I've connected this VM to the Quickstart VM in the past, and connected via the Impala ODBC drivers, but I can't seem to connect to CDH4 running on EC2 at all. Checking one of the EC2 instances, it doesn't even seem if port 10000 (the Hive port) is being used, but Hive is running and in the configuration properties for Hiveserver2 within CM, it says it's using port 10000.

Ports are open within the EC2 security group. Is there something obvious I'm missing here?

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Manikumar Juttukonda — Fri, 03 Jan 2014 19:06:12 GMT

from you Windows VM, do you able to ping EC2 host ?

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 03 Jan 2014 19:36:57 GMT

No, I can't ping it either from the Windows VM running locally, nor the Mac host that it's running on.

According to the jclouds#cloudera-cdh security group that the install wizard sets up, only the original (CDH) instance can ping the hadoop instances; but a whole range of ports (including 10000) are then also open to 0.0.0.0/0. So this (correct?) would explain why I can't ping the instance, but this shouldn't be an issue?

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Manikumar Juttukonda — Fri, 03 Jan 2014 19:45:41 GMT

I'm little confused on your issue. Correct me if I am wrong...
You are running Cloudera Quickstart VM on Windows machine and you have a hadoop cluster set up on EC2. You are trying to access hive from Windows VM to connect EC2 Cluster ?

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 03 Jan 2014 19:49:36 GMT

Sorry, ignore the bit about the Quickstart VM.

I'm running the Cloudera Hadoop cluster on EC2, using the Cloudera Manager 4.5 installer and the standard (free) option. I'm then trying to connect to it from a Windows VM, running on my laptop, using the Cloudera ODBC 2.5.5 Hive drivers; whilst I can connect to CDH on the EC2 setup from a web browser from that VM, I can't get a succesful connection using the ODBC drivers. I'm wondering therefore if there's something else I need to enable, either in the CDH/Hive setup, or in the EC2 setup, to make this work.

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 03 Jan 2014 19:53:12 GMT

Just noticed this other post on the forum - although it's the Quickstart VM, he's had the same problem as me. I also had the sample experience with the Quickstart VM (separately) - could connect to Impala via ODBC, but couldn't connect to Hive.

http://community.cloudera.com/t5/Apache-Hadoop-Concepts-and/QuickStart-VM-Several-Questions/m-p/4033#M124

Unfortunately that post didn't have a solution either...

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Manikumar Juttukonda — Fri, 03 Jan 2014 20:03:33 GMT

Did you configure DSN, host & port on windows host ?

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Manikumar Juttukonda — Fri, 03 Jan 2014 20:06:16 GMT

This might be helpful
http://www.cloudera.com/content/cloudera-content/cloudera-docs/Connectors/PDF/Cloudera-ODBC-Driver-for-Apache-Hive-Install-Guide.pdf

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 03 Jan 2014 20:35:41 GMT

Yes I configured the DSN etc on Windows. I've also seen the ODBC guide as well (thanks though).

I've been able to connect to Hive before, on a different VM (the Oracle "bigdatalite" one), so I know how Hive ODBC connectivity works. It's just that I can't seem to connect to Hive running on a Hadoop cluster built using CDH4 - the other forum post I referenced had the same issue, so I don't think it's just me. But I expect there's some setting I'm not aware of that's making it happen.

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Grizzly — Fri, 03 Jan 2014 21:28:13 GMT

Can you describe what your network configuration is within the cluster? More specifically consider these following questions you should be verifying within your deploy (dont post hostnames or IP's plz).

I believe EC2 nodes are multi-homed. Validate for yourself what the host-naming is resolving to across those interfaces. Look at what forward and reverse lookups are returning as well.

Some of the network configuraitons for components have a "wildcard" name that can be found when you search within a service's configuration settings. This is so the service is listening on "all" interfaces.

For yourself, from both the EC2 cluster nodes you are trying to connect to, and your VM, please evaluate what comes back for this command line in comparison to the naming you are using between your VM and the EC2 environment:

# python -c "import socket; print socket.getfqdn(); print socket.gethostbyname(socket.getfqdn())"

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Fri, 03 Jan 2014 21:53:59 GMT

The networking would have been whatever the cloudera installer set up within EC2 - I didn't specifically set anything up, the wizard that run from the initial cloud instance then sets up the hadoop instances, their keypairs, their security groups etc. So - in theory - presumably it should just work.

When I run python -c "import socket; print socket.getfqdn(); print socket.gethostbyname(socket.getfqdn())" SSH'd into one of the instances, it returns the hostname, and the internal IP address (internal to EC2). I wonder - do I have to create an SSH tunnel to get access to the EC2 machines from within the AWS network, like this - http://www.toadworld.com/products/toad-for-cloud-databases/f/102/t/5773.aspx - I'm thinking not though as port 10000 is open in those instances security groups.

Another thing - the other thread I linked to had the same problem, and he was using the Quickstart VM. I've also had the same problem (with Hive, not Impala) on Quickstart too, so is it a generic issue with machines set up using this installer/CDH4?

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Sat, 04 Jan 2014 11:52:54 GMT

I'm getting the impression from lack of responses, and from lack of content on this on the forums etc, that Hive is effectively deprecated in favour of Impala for SQL-like access against CDH; however - it's the only way that I can access Hadoop from ETL tools such as Oracle Data Integrator, which then rules out this platform as a source for "hadoop ETL".

If I come up with a solution I'll post it here, but it's disappointing that I can't get it to work.

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Clint — Mon, 06 Jan 2014 17:07:07 GMT

Mark,

I've raised this thread with some of our subject matter experts internally in hopes somebody has some insight for you.

Regards.

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

dave.bouchillion — Mon, 06 Jan 2014 18:53:02 GMT

Mark,

What is the error you are seeing when trying to connect to HS2?

Do you have security configured on your cluster or is this unsecured? If it is unsecured, I have seen a problem when using the "No Authenticaiton" mechanism in the DSN setup. There is a workaround for this. First, choose the "User name" mechanism. Then type something in the User name option. It doesn't matter what it is.

Can you connect through beeline locally?

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Tue, 07 Jan 2014 21:21:00 GMT

Hi Dave,

The error message that the Cloudera ODBC Driver for Apache Hive Data Source dialog shows, when testing out a new system DSN on Windows Server 2008 R2 64-bit, is:

Driver Version: V2.5.5.1006

Running connectivity tests...

Attempting connection
Failed to establish connection
SQLSTATE: HY000[Cloudera][HiveODBC] (34) Error from Hive: connect() failed: errno = 10061.

TESTS COMPLETED WITH ERROR."

Going over to CDH4, even testing it against the VMWare Quickstart VM using beeline on that VM, I get the error message:

"Error: could not establish connection to jdbc:hive2://localhost:10000: Java.net.ConnectException: connection refused (state=08S01, code=0"

I don't think the Hiveserver2 service is actually installed or running on the VM (or on the EC2 installs). If I try:

sudo service hive-server2 start

I get

hive-server2: unrecognized service

and if I type in:

chkconfig --list

it's not listed there - which makes me think hiveserver2 isn't running (or thrift), and that's why nothing can connect.

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

dave.bouchillion — Wed, 08 Jan 2014 03:31:48 GMT

I believe in your first post you mention that you are using CM. If you're using CM to manage the cluster then you won't see the hive-server2 service from a command line. You'll have to add the instance and start it from CM. The default settings for HiveServer2 are listed in the configuration, but by default the instance is not added or started. Here is the documentation for adding a role instance. Once you have added the hiveserver2 instance then you can start it and should be able to access it straight away.

Hopefully this will get you going. Please let me know your results.

You can also use the following commands on the quickstart vm or your ec2 setup to verify that port 10000 is in use once you start hiveserver2:

sudo netstat -tulpn | grep 10000

Dave

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Wed, 08 Jan 2014 09:36:45 GMT

Hi Dave

Perfect, that was it. The Hiveserver2 service as you say, isn't installed by default, but adding it via those steps via CM enabled it, and now I can connect OK. Thanks for your help.

Is there any reason that this service isn't installed and enabled by default? As you say, looking at the instance details in CM, it looks like it should be there, and no-body would be able to connect via Hive ODBC/JDBC drivers from an external machine until this is set up.

Mark

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Darren — Wed, 08 Jan 2014 16:21:29 GMT

Hi Mark,

HiveServer2 is only available in CDH4.2+, and is considered generally optional in CDH4.x. Many customers use the old Hive CLI on CDH4, since HiveServer2 is somewhat new. CM generally tries not to install optional roles, just required ones.

In CDH5, CM treats HiveServer2 is a required role for Hive, since the old Hive CLI will be deprecated in CDH5 and all clients should instead go through the beeline CLI and/or drivers that talk to HS2.

Hope this clears it up for you!

-Darren

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

markrittman — Wed, 08 Jan 2014 16:27:52 GMT

Thanks Darren. So does that mean that hiveserver1 (with thrift) is installed by default? My take is that there's no thrift server and no hiveserver2 by default, so by default there's no way you can connect via ODBC or JDBC to Hive? Is this correct?

Re: Cannot connect to Hive on CDH4.5 EC2 installation using Cloudera ODBC 2.5.5 Windows x64 drivers

Darren — Wed, 08 Jan 2014 16:50:51 GMT

CM doesn't manage hiveserver1 because it should basically never be used. It notably has no real support for concurrent operations, and you may get corruption in such scenarios.

Since HS2 is not set up by default, there's no way to use O/JDBC to connect to hive in CDH4 by default. You can, however, tell CM to initially install the HS2 role if you click "Inspect Roles" (or something similar) in the setup wizard, on the page where you select which service types you want to install. And as you know, you can also just add the role later.