Connecting SQuirrel JDBC Client to Hive on IBM Analytics Engine

SQuirreL SQL Client is an open-source Java SQL Client program for any JDBC compliant database. This article describes how to connect a remote Squirrel JDBC client to the Hive endpoint on IBM Analytics Engine.

1. Begin by provisioning an IBM Analytics Engine service instance .
2. Then retrieve the Hive JDBC service endpoint for the cluster.

3. Launch the Ambari console from the cluster management page, and click on the lock icon to view the certificate. Export the certificate from the browser.

4. Generate a jks file from the certificate file you exported in step 3 in the following way.

keytool -import -v -trustcacerts -alias keyAlias -file “//IAEcert.cer” -keystore “//iaecert.jks” -keypass changeitnow

5. Now launch the SQuirrel client and select Drivers > New Driver…. Enter an arbitrary driver name and example URL by using the following format:

jdbc:hive2://localhost:10000/default

6. Select Extra Class Path > Add and reference the following JAR files —

hive-jdbc-1.2.1000.2.6.5.0–292-standalone.jar 
hadoop-common-2.7.3.jar

IBM Analytics Engine’s Hive instance only requires these two driver files in order to establish remote connectivity. Retrieve the two files from your IBM Analytics Engine SSH node and put them on your local workstation where you have installed SQuirrel, for example:

/usr/hdp/current/hive-client/jdbc/hive-jdbc-1.2.1000.2.6.5.0–292-standalone.jar
/var/lib/ambari-agent/cred/lib/hadoop-common-2.7.3.jar 

Note: The file name and version numbers used might vary slightly from what you see in the example.

7. Next you need to create an alias which references the Hive endpoint to the Hive instance of your IBM Analytics Engine instance. In the SQuirrel client, select Aliases > Add Alias… to create a connection. From the Driver list, select the new Hive driver your created in step 5. Fill out the input fields with your values as shown in the following sample screenshot.

Note: The URL field contains the ‘hive_jdbc’ value from your IBM Analytics Engine management page to which you must add the ‘sslTrustStore’ and the ‘trustStorePassword’ values. The ‘hive_jdbc’ value should have the format shown in the following example:

jdbc:hive2://<changeMeClusterUrl>:8443/;ssl=true;sslTrustStore=/<myCertificateDownloadFolderPath>/iaecert.jks;trustStorePassword=<changeitnow>;transportMode=http;httpPath=gateway/default/hive

8. After you have successfully connected to the Hive instance, you can start running your queries in the SQL editor.

Some help while connecting…

Here is some help in case you should run up against problems when opening an SQL connection.

Unexpected Error occurred attempting to open an SQL connection

If you’re told that an unexpected error occurred while trying to connect to the IBM Analytics Engine cluster, and the stack trace begins with the following lines:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.NoSuchMethodError: org.apache.commons.codec.binary.Base64.<init>(I)V
at java.util.concurrent.FutureTas…(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:206)

Then, you are using an older version of the `commons-codec.jar` file in your local SQuirreL client.

To fix this, perform the following steps:

1. Download the commons-codec jar file from your IAE cluster.

scp USERNAME@CLUSTERNAME:/usr/hdp/current/hive-client/lib/commons-codec*.jar ./commons-codec.jar

2. Close the SQuirrel client on your machine.

3. Now navigate to the directory where SQuirreL is installed on your system. In the SQuirreL directory, under `lib`, replace the existing `commons-codec.jar` with the one you downloaded from the IBM Analytics Engine cluster.

4. Restart your SQuirreL client. You should now be able to connect to the IBM Analytics Engine cluster.

Timeout error

If you get a connection timeout error while trying to connect to Hive on the IBM Analytics Engine cluster, and the stack trace looks as follows:

Error: org.apache.thrift.transport.TTransportException:
java.net.SocketTimeoutException: Read timed out
SQLState: 08S01
ErrorCode: 0

Then you need to change the login timeout setting. The cause is related to the following known bug .

To change the timeout setting:

1. In the SQuirrel client, select File > Global Preferences, and then click the SQL tab.
2. Change the Login Timeout value to 0. Then click OK.
2. Reconnect the session by clicking Session > Reconnect.

Alternatively, you can also try using the IBM Analytics Engine cluster IP instead of the hostname.