JDBC Connections

You can connect to Impala through a JDBC client tool, such as the SQuirreL client. You can also use the driver in a Maven application.

Using the JDBC Driver with a Client Application

You can download the Impala JDBC driver from https://package.mapr.com/tools/MapR-JDBC/MapR_Impala/MapR_Impala_jdbc_v1.0.46.1057/. After downloading the driver, refer to the documentation at Impala JDBC Driver to install and configure the JDBC driver.

Before you connect to Impala from a JDBC client tool, complete the following tasks:
  1. Configuring the JDBC Port. The default JDBC port that Impala accepts connections through is port 21050. Verify that this port can communicate with other hosts on your network. If your JDBC application connects through a different port, use the - - hs2_port option when you start impalad to specify the port number.
  2. Enable the JDBC driver on client machines. The JDBC driver is packaged in JAR files. From a system that has Hive installed, download the following JAR files to each client machine:
    • The JDBC driver is in /opt/mapr/hive/hive-2.1/lib/hive-jdbc-2.1.1-mapr-1710.jar.
    • The hadoop-common-2.7.0 jar file for MRv2 (YARN) is in /opt/mapr/hadoop/hadoop-2.7.0/share/hadoop/common.
    • The other required JAR files are in /opt/mapr/hive/hive-2.1/lib/.
    Download the following JARs:
    commons-logging-1.1.3.jar
    guava-14.0.1.jar
    hive-exec-2.1.1-mapr-1710.jar
    hive-jdbc-2.1.1-mapr-1710.jar
    hive-metastore-2.1.1-mapr-1710.jar
    hive-service-2.1.1-mapr-1710.jar
    hive-shims-2.1.1-mapr-1710.jar
    httpclient-4.4.jar
    httpcore-4.4.jar
    libfb303-0.9.2.jar
    libthrift-0.9.2.jar
    Note: You may have a different Hive 2.1 version installed. This list pertains to the 1710 version of Hive 2.1.You also need the slf4j-api-1.7.5.jar and slf4j-log4j12-1.7.5.jar files, which are available in /opt/mapr/lib.
  3. Update the CLASSPATH. The client application needs to locate the JAR files. Set the CLASSPATH for the client process to include the JARs. Consult the documentation for your JDBC client application for more details on how to install new JDBC drivers.

    If the existing CLASSPATH on your client machine refers to an older version of the Hive JARs, verify that the new JARs are listed first. You can put the new JAR files ahead of the older JAR files, or delete the other references to Hive JAR files.

  4. Specify a connection string to establish a connection between Impala and JDBC. The JDBC driver classpath is org.apache.hive.jdbc.HiveDriver.Use the following connection string to establish the connection:
    jdbc:hive2://host:port/;auth=noSasl

    Example:

    jdbc:hive2://myhost.example.com:21050/;auth=noSasl

Using the JDBC Driver in a Maven Application

To use the JDBC driver in a Maven application, complete the following steps:

  1. Configure a MapR Maven repository. Refer to Maven Artifacts for MapR to configure MapR’s Maven repository in your Java application.
  2. Add the following dependency in the pom.xml:

    <dependency>                      
        <groupId>org.apache.hive</groupId>                      
        <artifactId>hive-jdbc</artifactId>                      
        <version>0.12-mapr[[mapr-version-number]]</version>              
    </dependency>
  3. Specify the connection string in the Java application. The following code shows a working example:
    import java.sql.*;
    public class ImpalaJdbcExample
    {
           private static String connectionString = "jdbc:hive2://10.10.80.231:21050/;auth=noSasl";
           private static String driverName = "org.apache.hive.jdbc.HiveDriver";
           private static String queryString = "select count(*) from customers";
           private static Connection con;
           private static ResultSet resultSet;
           private static Statement sqlStatement;
           public static void main(String[] args)
           {
                   System.out.println("Loaded the driver successfully. Trying to establish connection");
                   try
                   {
                           Class.forName(driverName);
                           con = DriverManager.getConnection(connectionString);
                           System.out.println("Created connection. Preparing statement");
                           sqlStatement = con.createStatement();
                           System.out.println("Executing "+queryString);
                           resultSet = sqlStatement.executeQuery(queryString);
                           while(resultSet.next())
                           {
                                   System.out.println("Result set "+resultSet.getString(1));
                           }
                           con.close();
                   }
                   catch(SQLException sqle)
                   {
                           System.out.println("Got sql exception");
                           sqle.printStackTrace();
                   }
                   catch(Exception e)
                   {
                           System.out.println("Got exception");
                           e.printStackTrace();
                   }
           }
    }