Let us take a closer look at how to configure the SQL server framework JDBC hive driver with a few simple steps with the assistance of our MSSQL support Servies at Bobcares.

SQL Server Framework and JDBC Hive Driver

• A Hadoop cluster powered by HDInsight. To begin with, Azure HDInsight, read Get started with Azure HDInsight. Check that the HiveServer2 service is running.
• Version 11 or higher of the Java Developer Kit (JDK).
• SQL SQuirreL SQuirreL is a JDBC client program.

JDBC connection string

JDBC connections to an Azure HDInsight cluster are made over port 443. TLS/SSL in here can secure the traffic.

The clusters’ public gateway routes traffic to the port that HiveServer2 is actually listening on. The format to use for HDInsight is shown in the connection string below:

HTTP

jdbc:hive2://CLUSTERNAME.azurehdinsight.net:443/default;transportMode=http;ssl=true;httpPath=/hive2,

Replace the clustername with the name of the HDInsight cluster.

The hostname in the connection string

In the connection string, the hostname ‘CLUSTERNAME.azurehdinsight.net’ corresponds to the cluster URL. We can obtain it using the Azure portal.

Port in the connection string

We can only access the cluster via port 443 from locations outside of the Azure virtual network. As HDInsight is a managed service, all connections to the cluster are managed through a secure Gateway.

We are unable to connect directly to HiveServer 2 on ports 10001 or 10000. These ports are not visible from the outside.

Authentication

To authenticate, use the HDInsight cluster admin name and password when connecting. Enter admin name and password in client settings from JDBC clients such as SQuirreL SQL.

When connecting from a Java application, we must use the name and password. For instance, the following Java code establishes a new connection:

Java

DriverManager.getConnection(connectionString,clusterAdmin,clusterPassword);

Connect with SQuirreL SQL client

SQuirreL SQL is a JDBC client for remotely running Hive queries with the HDInsight cluster. The instructions that follow assume that SQuirreL SQL has already been installed.

• Firstly, make a directory to hold the files to copy from the cluster.
• After that, replace sshuser with the cluster’s SSH user account name in the following script. Replace the CLUSTERNAME with the name of the HDInsight cluster.
• Change the working directory to the one we created in the previous step, and then run the following command to copy files from an HDInsight cluster: cmd

  scp sshuser@CLUSTERNAME-ssh.azurehdinsight.net:/usr/hdp/current/hadoop-client/{hadoop-auth.jar,hadoop-common.jar,lib/log4j-*.jar,lib/slf4j-*.jar,lib/curator-*.jar} .

  scp sshuser@CLUSTERNAME-ssh.azurehdinsight.net:/usr/hdp/current/hive-client/lib/{commons-codec*.jar,commons-logging-*.jar,hive-*-*.jar,httpclient-*.jar,httpcore-*.jar,libfb*.jar,libthrift-*.jar} .
• Start the SQuirreL SQL program. Select Drivers from the left-hand menu.
• After that to build a driver, click the Plus icon at the top of the Drivers dialog.
• Add the following information to the Add Driver dialog:

  Property:Value
Name: Hive
Example URL:jdbc:hive2://localhost:443/default;transportMode=http;ssl=true;httpPath=/hive2 Extra Calss Path: Use the Add button to add the all of jar files downloaded earlier. Class Name: org.apache.hive.jdbc.HiveDriver

• After that to preserve these settings, click OK.
• Select Aliases on the left side of the SQuirreL SQL window. Then click the Add button to add a connection alias.

For the Add Alias dialog, enter the following values:

Property: Value
Name: Hive on HDinsight
Driver: USe the drop-down to select the Hive driver
URL:jdbc:hive2:CLUSTERNAME.azurehdinsight.net:443default;transportMode=http;ssl=true;httpPath=hive2.
CLUSTERNAME should be replaced with the name of the HDInsight cluster.
User Name: The cluster login account name for the HDInsight cluster. The default is admin.
Password: The password for the cluster login account.

Important

To ensure that the connection is operational, press the Test button. When the Connect to: Hive on HDInsight box displays, click Connect to begin testing.

If the test is successful, we will get the Connection Successful dialog.

• Use the Ok button at the bottom of the Add Alias window to save the connection alias.
• Select Hive on HDInsight from the Connect to dropdown at the top of SQuirreL SQL. Select Connect when requested.
• Finally, after connecting, type the following query into the SQL query dialog and press the Run button (a running person). The query results will open up in the results area.

HiveQL select * from hivesampletable limit 10;

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now learned more about the SQL server framework JDBC hive driver, the requirements for configuring it, and how to connect it with the SQuirreL SQL client in a few simple steps with the assistance of our MSSQL Support services.