Connecting to Hive Database with DBeaver

5
votes

I have a Hortonworks Hadoop cluster where the data nodes are on a separate network off of the master/head node. The only way to access the data nodes is through the master node or an edge node. From the edge node, I execute the hive command to connect into my hive database.

I cannot connect to the hive database from my desktop with DBeaver (4.3.0, 64-bit Windows) or the hive command line interface. Through DBeaver, I tried creating an SSH tunnel to my edge node and continually receive "Could not open client transport with JDBC Uri. jdbc:hive2://127.0.0.1:[port#]/[database].

Configuration for Hive/Apache Hive driver:

 General Tab:
     Host: dataNodeName
     Port: 10000
     Database/Schema: databaseName
     User name: myUID

 SSH Tunnel Tab (Network page):
     Checked Use SSH Tunnel
     Host/IP:   edgeNodeServerName
     Port:      22
     User Name: myUID
     Authentication Method:  Password
     Password:  myPWD

     Advanced
       Local port: 0
       Keep-Alive interval (ms): 0

When I select "Test Connection" with local port set to "0", I receive the above error message with random port numbers. If I set the local port to "10000", I receive the above error with port number "10000".

It looks like DBeaver is ignoring the generic JDBC connection settings--the host name in the created JDBC string is 127.0.0.1 instead of the data node name.

What am I missing? How do I setup DBeaver to access a Hive database located on a "hidden" network?

3
hadoopjdbchivedbeaver
Can you not open your HiveServer2's port to the external network? Why do you need an SSH tunnel? – OneCricketeer
In other words, you need to tunnel to the HiveServer for this to work, not an edge node. – OneCricketeer

3 Answers

2
votes

Is your hostname configured with the IP address mentioned in the jdbc connect syntax (127.0.0.1)? Are you able to connect to beeline from your Unix shell? Syntax to connect to beeline(hiveserver2): beeline -u jdbc:hive2://<hostname>:<hive listener port>/<database> -n username> -p <password>

If you're able to connect to beeline, you should be able to connect to hive using same port number and host from DBeaver. Hive listener port by default is configured on 10000, but there's a possibility that your admin can change the port number. Check the port number in hive-site.xml, or get it from admin.

Could you please uncheck the SSH tunnel and try?

I wrote an article on this, please do check if it is of any help.

https://www.linkedin.com/pulse/query-hive-hiveserver2-from-windows-using-universal-database-nimmala

0
votes

Not sure if your environment is Kerberized or not but assuming it is -

Following is what worked for me while connecting to Cloudera -

  1. Fetch the krb5.conf or krb5.ini from your admins and place it in some directory. I normally put the file in a location where I put my keytabs.
  2. Create jaas.conf file and place it at the same location(or the location of your choice)

  3. jaas.conf must look like below(copy paste) -

    Client { com.sun.security.auth.module.Krb5LoginModule required debug=true doNotPrompt=true useKeyTab=true keyTab="C:\Users{user}\krb5cc_{user}" useTicketCache=true renewTGT=true principal="{user}@DOMAIN.ORG" ; };

  4. Edit your dbeaver.ini file and provide the reference to both of this files(append the following lines to existing dbeaver.ini). Make sure you backup dbeaver.ini, with re installations or replacing with newer version, dbeaver.ini may get replaced, in that case you can copy the lines below from your backup dbeaver.ini file -

-Djavax.security.auth.useSubjectCredsOnly=false

-Djava.security.krb5.debug=true

-Dsun.security.krb5.debug=true

-Djava.security.krb5.conf=C:\Users{User}\Documents\Keytabs\krb5.conf

-Djava.security.auth.login.config=C:\Users{User}\Documents\Keytabs\jaas.conf

Last Step(You may need or may not)

I init my keytab before connecting. So I use Shell Commands -

  1. Press F4 after creating the connection
  2. Make sure in user you just put the user name for which you are initializing the keytab and nothing else. It should not be {user}@domain.org.
  3. Use the shell commands to init the keytab

enter image description here

0
votes

I also was having trouble configuring DBeaver to Hive, my solution was to use Cloudera's ODBC Driver. It worked a lot better then the JDBC drivers (auto-complete working, quicker, no need to run kinit), and I could automatize its creation.

The only problem is that you must be admin to install it.