MapR 5.0 Documentation : Using the Drill JDBC Driver with SQuirreL

You can use the Drill JDBC driver with SQuirreL 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 and configure the driver.

When you configure the driver, you define the driver and create an alias. The alias is a specific instance of the driver configuration. SQuirreL uses the driver definition and alias to connect to Drill so you can access data sources that you have registered with Drill. When you create the alias, you provide a connection URL that includes the name of the Drill directory stored in ZooKeeper and the cluster ID. The URL has the following format:

jdbc:drill:zk=<zookeeper_quorum>/<drill_directory_in_zookeeper>/<cluster_ID>

The following examples show URLs for Drill installed on a single node:

 jdbc:drill:zk=10.10.100.56:5181/drill/demo_mapr_com-drillbits
 jdbc:drill:zk=10.10.100.24:2181/drill/drillbits1

The following example shows a URL for Drill installed in distributed mode with a connection to a ZooKeeper quorum:

jdbc:drill:zk=10.10.100.30:5181,10.10.100.31:5181,10.10.100.32:5181/drill/drillbits1

Note the following:

  • The ZooKeeper port is 2181. In a MapR cluster, the ZooKeeper port is 5181.
  • The Drill directory stored in ZooKeeper is /drill.
  • The Drill default cluster ID is drillbits1. To determine the cluster ID, check the following file:
    <drill-installation>/conf/drill-override.conf
    For example:

    ... drill.exec: { cluster-id: "docs41cluster-drillbits", zk.connect: "centos23.lab:5181,centos28.lab:5181,centos29.lab:5181" } ...

Prerequisites

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 DNS 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

Downloading and Configuring the Driver

To use the Drill JDBC driver with SQuirreL, complete the following steps:

  1. Download the Drill JDBC Driver and then unzip the file. The Drill JDBC Driver JAR files must exist in a directory on your machine before you can configure the driver in the SQuirreL client.
  2. Define the driver.
    1. Open the SQuirreL client.
    2. In the SQuirreL toolbar, select Drivers > New Driver. The Add Driver dialog appears
      .
    3. Enter the following information:
      • Name - Name for the Drill JDBC Driver 
      • Example URL - jdbc:drill:zk=<zookeeper_quorum>
        Example: jdbc:drill:zk=maprdemo:5181
      • Website URL - jdbc:drill:zk=<zookeeper_quorum>
        Example: jdbc:drill:zk=maprdemo:5181
        Example: jdbc:drill:zk=10.10.100.113:5181,10.10.100.115:5181

    4. Select Extra Class Path, and click Add.

    5. Navigate to the directory that contains the JDBC JAR files.
    6. Select all of the files in the directory, and click Choose.
    7. In the Class Name drop-down field, select com.mapr.drill.jdbc41.Driver. Type com.mapr.drill.jdbc41.Driver in the field if the option does not appear.
    8. Click Ok.  The SQuirreL client displays a message stating that the driver registration is successful, and you can see the driver in the Drivers panel.  
  3. Create a database alias.
    1. Select the Aliases tab.
    2. In the SQuirreL toolbar, select Aliases > New Alias. The Add Alias dialog box appears.
       
    3. Enter the following information:
      • Alias Name - A unique name for the Drill JDBC Driver alias 
      • Driver - Select the Drill JDBC Driver 
      • URL -  Enter the connection URL with the name of the Drill directory stored in ZooKeeper and the cluster ID.
      • User Name - admin
      • Password - admin  
      1. Click Ok. The Connect to: dialog appears.
         
    4. Click Connect. SQuirreL displays a message stating that the connection is successful.
    5. Click Ok. SQuirrel is connected to Drill through the Drill JDBC driver. You can run your queries.
       

Running a Drill Query from SQuirreL

Once SQuirreL is successfully connected to your cluster through the Drill JDBC Driver, you can issue queries from the SQuirreL client. Run a test query on some sample data included in the Drill installation to test the Drill connection.

To query sample data with Squirrel, complete the following steps:

  1. Click the SQL tab.
  2. Enter the following query in the query box:   
    SELECT * FROM cp.`employee.json`;
  3. Press Ctrl+Enter to run the query. The following query results display. 
    You have successfully run a Drill query from the SQuirreL client!

 

Attachments: