Using the Drill JDBC Driver in an Application

About this task

You can use the Drill JDBC driver in an application to connect to Drill and query data sources configured in Drill. To use the Drill JDBC Driver with SQuirreL, verify that your system meets the prerequisites and then download the driver, set the classpath, driver class, and connection URL.

Before you download and configure the driver, verify that the system meets the following prerequisites:

  • Java Runtime Environment (JRE), version 7.0 or later, installed on each machine where you plan to use the JDBC driver.
  • Drill installed in distributed mode on one or multiple nodes in a cluster with data sources configured. See Installing Drill on MapR and Connecting Drill to Data Sources.
  • Verify that the system can resolve the hostnames of the ZooKeeper nodes of the Drill cluster. You can do this by configuring DSN for all of the systems. Alternatively, you can edit the hosts file to include the hostnames and IP addresses of all the ZooKeeper nodes used with the Drill cluster :
    • For Windows, create the entry in the %WINDIR%\system32\drivers\etc\hosts.
    • For Linux and Mac, create the entry in /etc/hosts.

      Example: 127.0.1.1 maprdemo

To use the Drill JDBC Driver in an application, complete the following steps:

Procedure

  1. Download the driver. The MapR JDBC Driver for Drill Drill is provided in a ZIP archive file that you download. The ZIP file contains several JAR files that you must include in the classpath of the Java project.
  2. Set the class path to include all the JAR files from the ZIP archive file that you downloaded. The class path is the path that the JRE searches for classes and other resource files. For more information, see Setting the Class Path.
  3. Initialize the driver class. Before you can connect to Drill, you must initialize the appropriate class for your application.
    The Driver classes extend java.sql.Driver. The DataSource classes extend javax.sql.DataSource and javax.sql.ConnectionPoolDataSource. The following classes with the fully-qualified class name (FQCN) are available:
    • com.mapr.drill.jdbc41.Driver
    • com.mapr.drill.jdbc41.DataSource
    The following sample code shows how to use the DriverManager to establish a connection: 
    private static Connection connectViaDM() throws
Exception
{
Connection connection = null;Class.forName(DRIVER_CLASS);
connection = DriverManager.getConnection(CONNECTION_URL);
return connection;
}
    The following sample code shows how to use the DataSource class to establish a connection: 
    private static Connection connectViaDS() throws
Exception{
Connection connection = null;Class.forName(DRIVER_CLASS);
DataSource ds = newcom.mapr.drill.jdbc4.DataSource();ds.setURL(CONNECTION_URL);
connection = ds.getConnection();return connection;
}
  4. Build the connection URL. The connection URL supplies connection information for the data source that you access. You can connect directly to a drillbit or to a ZooKeeper cluster.
    • To connect to a drillbit, use a connection URL in the following form: 
      jdbc:drill:drillbit=Host:Port;Property1=Value;Property2=Value;...
      For example, the following connection URL shows how to connect to a drillbit on host 192.168.1.1:31010 and authenticate the connection with a user name and password: 
      jdbc:drill:drillbit=192.168.1.1:31010;UID=UserName;PWD=password
      The following table lists the connection URL placeholders and their descriptions:
      Placeholder Description
      Host The DNS or IP address of the server.
      Port The port to connect to on the Host.
      Property Any of the connection properties that you can specify. See Driver Configuration Options. Properties are case-sensitive. Do not duplicate properties in the connection URL.
    • To connect to a ZooKeeper cluster, use a connection URL in the following form: 
      jdbc:drill:zk=ServerList/drill/ClusterName;Property1=Value;Property2=Value;...
      For example, the following connection URL shows how to connect to a ZooKeeper cluster and authenticate the connection with a user name and password: 
      jdbc:drill:zk=192.168.39.43:5181,192.168.1.1:31010/drill/drillbits1;UID=UserName;PWD=password
      The following table lists the connection URL placeholders and their descriptions:
      Placeholder Description
      ServerList A comma-separated list of servers in the ZooKeeper cluster.
      ClusterName The unique name of the Drillbit cluster that you want to use.
      Property Any of the connection properties that you can specify. See Driver Configuration Options. Properties are case-sensitive. Do not duplicate properties in the connection URL.