Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Explorer

Need instructions on configuring Squirrel SQL for Phoenix on a cluster using knox authentication (cluster is also kerberoized)

6 REPLIES 6
Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Super Guru

@sparepally Please take a look at this post. I don't believe kerberose is supported through squirrel as there is not please to identify your keytab file. you can use another tool like dbvisualizer, sql developer, or any other tool with supports jdbc on kerberized cluster.

Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

I'm not familiar with Squirrel SQL but it looks like a fairly standard JDBC based client. If that is the case then this post provides the information required to get the driver installed.

https://community.hortonworks.com/questions/830/how-to-do-i-get-the-hive-jdbc-driver-for-my-clinet.h...

Once you have that then you need three things.

  1. Have HiveServer2 running in HTTP transport mode
  2. Have you Knox topology file configured
  3. Have the correct JDBC connection URL

For #1 in hive-site you will need know/set the following values. Make sure you restart HS2 if you change any of them. hive.server2.transport.mode=http hive.server2.thrift.http.port=10001 hive.server2.thrift.http.path=cliservice

For #2 the url for the HIVE role service should have the matching port and path from above.

For #3 the JDBC connection URL will look something like this:

jdbc:hive2://<knox-host>:<knox-port>/;ssl=true;sslTrustStore=<trust-store-file-name>;trustStorePassword=<trust-store-password>;transportMode=http;httpPath=gateway/<topology-name>/hive

where

  • <knox-host> is the host where your Knox gateway instance is running.
  • <knox-port> is the port on which your Knox gateway instance is listening.
  • <trust-store-file-name> the name of the keystore file that contains the Knox SSL public key
  • <trust-store-password> the password of the keystore file that contains the Knox SSL public key
  • <topology-name> the topology or cluster name that contains the HIVE service (e.g. default)

The sslTrustStore and trustStorePassword are not required if the Knox SSL public key is added to the default truststore however.

Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Contributor

I upvoted this but didn't realize that it the OP was looking for HBase with Phoenix. This may not be the correct answer.

Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Oh you are probably correct. I was answering this in the context of HiveServer2. We don't currently have any tested support of Phoenix via Knox.

Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Contributor

@Kevin Minder

Hello Kevin,

Can you please help me with JBDC hive server 2 connectivity using SQuirrel SQL client or any other.

I have followed the steps mentioned at the link mentioned(also written below) but facing issues while doing JDBC connection .Please find the attached log. I believe that it is because of the SSL issue .(AS in the ODBC client i am able to connect tableu with hive server2 with knox and ldap, using hortonworks ODBC driver . In ODBC i can select http mode and added SSL options , enable SSL and allow Self Signed server certficate.Bingo !! test successful and data is there) )But in JDBC i facing issues how to add ssl certificate.

1.Installed Squirrel Client

2.Added all the jars

3.Settings at the hive xml file done

4 Beeline is connected to my cluster and ran below command there , i am able to connect but when i am passing this through SQuirrel I am facing attached issue.errorlog.txt

jdbc:hive2://sandbox:8443/xademo;ssl=true;sslTrustStore=/var/lib/knox/data-2.3.2.0-2950/security/keystores/gateway.jks;trustStorePassword=knox?hive.server2.transport.mode=http;hive.server2.thrift.http.path=gateway/sandbox/hiv

Also ,what does it means "The sslTrustStore and trustStorePassword are not required if the Knox SSL public key is added to the default truststore however." Please explain a bit.

Highlighted

Re: Hbase Phoenix connectivity from JDBC client like Squirrel SQL on Kerberoized cluster/Knox Enabled cluster

Contributor

Hi @Kevin Minder

It is done as above after doing two tweaks:-

Set my SSL mode OFF as we do not need it now

Used SQuirrel JDBC client as i believe TD Studio has some issues.

Note: In SQuirrel JDBC client we need to remove the hadoop-comman.jar as it is pretty old one , download a new and rename it .

Don't have an account?
Coming from Hortonworks? Activate your account here