JDBC and Drill

Drill is implemented in Java, and as such it makes sense that Drill should implement the JDBC standard to enable other Java-based programs to connect to it. To use JDBC, you first need a copy of the Drill JDBC driver on your machine. The JDBC driver is included with Drill and is located at $DRILL_HOME/jars/jdbc-driver/drill-jdbc-all-version.jar. The driver class’s name is org.apache.drill.jdbc.Driver.² Copy it to the machine that has the application you want to connect to Drill, then add it to that application’s class path. Consult the documentation of your application for details.

Regardless of the application, to connect to Drill, you need to craft a JDBC connection URL. If you are using Drill in embedded mode on your local machine, that URL will be as follows:

jdbc:drill:drillbit=localhost:31010

You can of course, replace localhost with your hostname or IP address.

If you are connecting to Drill in distributed mode, you will need to know the name of your ZooKeeper cluster and you will use this form of URL: jdbc:drill:zk=zkhost1:2818,zkhost2:2181,.... Additionally, there are various optional configuration variables that you can pass in your JDBC connection URL, including the following:

cluster ID

The cluster name as set in drill-override.conf. The default is drillbits1. The default is typically fine unless you’ve customized this property in Drill’s drill-override.conf file.

directory

The ZooKeeper root znode for the Drill cluster. The default is root. Again, the default is typically fine unless you’ve customized this property in Drill’s drill-override.conf file.

port

The connection port. The port to connect directly to a Drillbit is 31010 (unless you changed this value in drill-override.conf.) The ZooKeeper default is 2181, and for a MapR cluster it is 5181.

schema

The default storage plug-in for the connection. This parameter is optional.

tries

The number of connection attempts. The default is 5.

These values must be the same as those specified in the drill-override.conf file for the target Drill cluster.

In the following code snippet, you see the first of two examples of JDBC connection strings. This one demonstrates how to connect to Drill via a single ZooKeeper instance, as is used for development:

jdbc:drill:zk=zkhost:2181

The second example demonstrates how to connect to Drill via a multinode ZooKeeper cluster:

jdbc:drill:zk=10.20.100.20:2181,
              10.20.100.21:2181,
              10.20.100.22:2181;schema=mongodb

When there is more than one ZooKeeper node, Drill randomly connects to any of them and then randomly picks a Drillbit. If the ZooKeeper node happens to be down, Drill will try another. The complete documentation about connecting to Drill with JDBC is available in the documentation on the Drill website.

ODBC and Drill

In addition to connecting to Drill using JDBC, Drill supports using ODBC as a connection method. Configuring ODBC requires installing the ODBC driver on your system and configuring some files on your system.

export ODBCINI=~/.odbc.ini
export MAPRDRILLINI=~/.mapr.drillodbc.ini
export LD_LIBRARY_PATH=/usr/local/lib

Querying Drill from R Using sergeant

To use sergeant, of course you must install it, which you can accomplish by using the following command:

devtools::install_github("hrbrmstr/sergeant")

After you’ve installed sergeant, the process for querying Drill is much like that for using one of the Python modules:

1. Create the connection.

2. Optionally check to see if the connection succeeded.

3. Execute the query.

The following code snippet demonstrates how to execute a query in Drill through R:

#Import sergeant
library(sergeant)

#Create the connection to Drill
conn <- drill_connection("hostname") 

#Check to see if the connection was successful
drill_active(conn)

#Execute the query
drill_query(conn, "query")

As you can see, it really isn’t too difficult to use R to query Drill.

Using the Connector

The first step is to connect to Drill. The module uses Drill’s RESTful interface, so it doesn’t really make a “connection” in the same sense as with MySQL:

$drill = new DrillConnection('localhost', 8047);

As mentioned earlier, this creates the object, but doesn’t actually send anything to Drill. You can use the is_active() method to verify that your connection is active:

 if($drill->is_active()) {
    print("Connection Active");
  } else {
    print("Connection Inactive");
  } 

Querying Drill from PHP

Now that you’ve connected to Drill, you can query it in a similar way as MySQL, by calling the query() method. After you’ve called the query() method, you can use one of the fetch() methods to retrieve the results. Currently the Drill connector has the following methods:

fetch_all()

Returns all query results in an associative array

fetch_assoc()

Returns a single query row as an associative array

fetch_object()

Returns a single row as a PHP object

You might also find these functions useful:

data_seek($n)

Returns the row at index $n and sets the current row to $n

num_rows()

Returns the number of rows returned by the query

field_count()

Returns the number of columns returned by the query

Thus, if you want to execute a query in Drill, you can do so as follows:

$query_result = $drill->query("SELECT * FROM cp.`employee.json` LIMIT 20");
while($row = $query_result->fetch_assoc()) {
  print("Field 1: {$row['field1']}
");
  print("Field 2: {$row['field2']}
");
}

Interacting with Drill from PHP

You can also use the connector to activate and deactivate Drill’s storage as well as get information about Drill’s plug-ins, with the following methods:

disable_plugin($plugin)

Disables the given plug-in. Returns true if successful, false if not.

enable_plugin($plugin)

Enables the given plug-in. Returns true if successful, false if not.

get_all_storage_plugins()

Returns an array of all storage plug-ins.

get_disabled_storage_plugins()

Returns an array of all disabled plug-ins.

get_enabled_storage_plugins()

Returns an array of all enabled plug-ins.

get_storage_plugins()

Returns an associative array of plug-ins and associated configuration options for all plug-ins.

get_storage_plugin_info($plugin)

Returns an associative array of configuration options for a given plug-in.

Exploring Data with Apache Zeppelin and Drill

Apache Zeppelin is a notebooking tool that is becoming quite popular due to its tight integration with a variety of big data tools, such as Apache Spark. Zeppelin’s simplicity and visualization capabilities, demonstrated in Figure 7-1, make it an ideal companion tool for working with Apache Drill.

Figure 7-1. Zeppelin rendering Drill sample data

The easiest way to use Zeppelin with Apache Drill is to use the JDBC interpreter that ships with Zeppelin. If you didn’t install the JDBC interpreter with Zeppelin, you’ll need to install it using the following command:

$ZEPPELIN_HOME/bin/install-interpreter.sh --name jdbc

After you’ve installed the Zeppelin JDBC interpreter, you need to configure it within the Zeppelin interface.

Configuring Zeppelin to query Drill

The first step is to add Drill as a data source. To do that, in the upper-right corner of the screen, click the user settings menu, and then navigate to the Interpreter screen, as shown in Figure 7-2. Click +Create to create a new interpreter.

Figure 7-2. Zeppelin settings menu

On the Create screen, choose JDBC as the interface type, and then set the following configuration variables, as shown in Figure 7-3:

default.driver

This should be set to org.apache.drill.jdbc.Driver.

default.password

This is your account password.

default.url

This is your JDBC connection string to Drill. To use Drill with Zeppelin in embedded mode or when a single Drillbit is running on your local machine, the connection string is jdbc:drill:drillbit=localhost:31010.

At the bottom of the screen, in the Dependencies section, add the path to the Drill JDBC driver as an artifact.

Figure 7-3. Configuring the Zeppelin interpreter

At this point, you should be able to query Drill in a Zeppelin notebook by simply setting the notebook’s default interpreter to Drill.

Querying Drill from a Zeppelin notebook

If you’ve successfully set up Drill as an interpreter in Zeppelin, all you need to do to query Drill is simply type a SQL query into a box in a Zeppelin notebook, as illustrated here:

SELECT * 
FROM cp.`employee.json`

This query is the default query for Drill. If you enter it in Zeppelin, you will see the query results depicted in Figure 7-4.

Figure 7-4. Query results in Zeppelin

By itself this doesn’t really add much value beyond Drill’s user interface; however, you can perform aggregations and other operations directly in the Zeppelin notebook. As an example, let’s say that you wanted to find the average salary by education level in the sample data file included with Drill. You could write the following query:

SELECT education_level, AVG(CAST(salary AS FLOAT)) AS avg_salary
FROM cp.`employee.json`
GROUP BY education_level

However, in Zeppelin, you can accomplish the same task starting from the original query, which outputs all the results. In Figure 7-5, you can see how to take the original results, perform the aggregation, and visualize the results.

Figure 7-5. Query results in Zeppelin

Adding interactivity in Zeppelin

One of the features that makes Zeppelin powerful is the ability to create interactive reports directly from a SQL query. In the WHERE clause of a query, you can specify variables that you can change and cause Zeppelin to dynamically redraw the visualization.

The following query has a WHERE clause of gender='${gender=M,M|F}', which creates a drop-down menu with two options for the gender:

SELECT * 
FROM cp.`employee.json`
WHERE `gender`='${gender=M,M|F}'

Changing the drop-down menu selection causes the visualization to be redrawn, as shown in Figure 7-6.

Figure 7-6. Visualization with dynamic variable

As you can see, Zeppelin is a good companion tool for Drill and requires little special configuration to quickly visualize and explore your query results.

Exploring Data with Apache Superset

Another open source BI tool that works well with Drill is a relatively new one, which as of this writing is in incubation with the Apache Software Foundation. This tool is called Superset, and it was developed by Airbnb and is implemented in Python using SQLAlchemy as a means of interacting with SQL-based data sources. Superset offers more sophisticated visualizations than Zeppelin, but at the time of writing, the project is still being developed and not all features work well with Drill.

According to the documentation, Superset’s features include:

• A rich set of data visualizations

• An easy-to-use interface for exploring and visualizing data

• The ability to create and share dashboards

• Enterprise-ready authentication with integration with major authentication providers (database, OpenID, LDAP, OAuth, and REMOTE_USER through Flask AppBuilder)

• An extensible, high-granularity security/permission model allowing intricate rules on who can access individual features and the dataset

• A simple semantic layer, allowing users to control how data sources are displayed in the UI by defining which fields should show up in which drop-down and which aggregation and function metrics are made available to the user

Windows does not support Superset, but it works well on Linux and macOS machines. You can install Superset via the following commands:

pip install superset
fabmanager create-admin --app superset
superset db upgrade
superset load_examples
superset init

In addition to the standard SQLAlchemy, you need to install the Drill dialect for SQLAlchemy before running Superset. To accomplish this step, either clone the sqlalchemy-drill GitHub repository or download and unzip the files directly from GitHub. When you’ve done that, navigate to the folder you just cloned or extracted and execute the following command:

python setup.py install

At this point, you are ready to configure Superset to work with Drill.

Configuring Superset to work with Drill

After you install Superset and the additional drivers, you need to add Drill as a data source. Click the Sources menu and select the Databases option, as shown in Figure 7-7.

Figure 7-7. Adding Drill as a data source

When you are in the databases section, in the upper-right corner of the List Databases pane, click the + button to add a new data source, as shown in Figure 7-8.

Figure 7-8. Adding Drill as a data source to Superset

On the Add Database screen, set up the configuration to allow Superset to communicate with Drill. You need to write a SQLAlchemy URI in the format shown here:

drill+sadrill://username:password@host:port/storage_plugin/workspace
?use_ssl=True

If you are connecting to Drill in embedded mode, you can use this connection string:

drill+sadrill://localhost:8047/dfs/drillclass?use_ssl=False

Enter your connection URI in the appropriate field, and you should be able to successfully test the connection, as demonstrated in Figure 7-9.

Figure 7-9. Drill configuration in Superset

You will want to select the “Expose in SQL Lab” and “Allow Run Sync” checkboxes. If you are querying large datasets or have queries that will take a long time to run, it would also be a good idea to set up Celery⁴ to allow asynchronous connections.

Building a demonstration visualization using Drill and Superset

Now that you have Superset working with Drill, this section walks you through building a visualization. In the GitHub repository for this book, you will find a data file called baltimore_salaries_2016.csvh, which contains data about the salaries of all of the employees of the City of Baltimore.⁵

For this example, we will calculate the average salary per department and visualize the results. The following query will return those results:

SELECT Agency, TO_NUMBER(`AnnualSalary`, '¤') AS AnnualSalary
FROM dfs.demo.`baltimore_salaries_2016.csvh`

To visualize this data, the first step is to open SQL Lab and enter the query. Next, click the Run Query button; the results should look like those shown in Figure 7-10.

Figure 7-10. Query results in SQL Lab

When you have the query results, click the Visualize button, set the options as depicted in Figure 7-11, and then click Visualize.

Figure 7-11. Superset chart configuration

At this point, after some minor configuration changes, you should have a visualization similar to the one shown in Figure 7-12.

Figure 7-12. Visualization in Superset

When you save the visualization, you can add it to a pre-existing dashboard or create a new dashboard.

Using Drill with Other Visualization Tools

You can use Drill with many other visualization tools, including Tableau, Qlix, JReport, MicroStrategy Analytics, and others that have ODBC or JDBC support.