Apache Drill and Jaspersoft Studio

These instructions were kindly contributed by MapR. MapR provides the industry's only converged data platform that integrates the power of the top-ranked Hadoop and Spark with global event streaming, real-time database capabilities, and enterprise storage, enabling customers to harness the enormous power of their data.

This is a guide to connecting TIBCO Jaspersoft^® Studio to Apache Drill via JDBC connector. It was tested with TIBCO Jaspersoft Studio 6.2.0 on Windows 7 using Apache Drill 1.4

http://community.jaspersoft.com/project/jaspersoft-studio/releases

Download Drill JDBC driver

The JAR file should be included with any Drill distribution, the path below is where the MapR Drill JDBC driver is located

/opt/mapr/drill/drill-1.4.0/jars/jdbc-driver/drill-jdbc-all-1.4.0.jar

Copy this jar file to your environment, it was placed in the Download folder for us.

Run Jaspersoft Studio

Create a new Data Adapter

Using the Database JDBC Connection

Add the jar file on the Driver Classpath page

Put the relevant details in the Database Location page.

Note:

‘org.apache.drill.jdbc.Driver’ is not available on the drop down box so it should be typed
drill.url: jdbc:drill:zk=<zkhost:port>,<zkhost:port>,<zkhost:port>/drill/<cluster-id>
the <cluster-id> can be found in the $DRILL_HOME/conf/drill-override.conf file
drill.user: <username>
drill.password: <password>

Press test connection

Create a view in Apache drill on a data source

For this example, a simple csv file was used and placed in the root of the MapR file system

# ls -lrt
-rw-r--r--  1 mapr     mapr   1034773 Mar 16 10:54 customers.all.csv

# head customers.all.csv
16841,"Lisa Wells","tx","FEMALE","26-35",40,"basic"
19905,"Neely Nova","va","MALE","15-20",45,"basic"
17718,"William Bush","il","FEMALE","51-100",29,"basic"
21975,"Gerald Staples","ma","MALE","26-35",28,"basic"
20468,"William Conners","de","MALE","36-50",32,"basic"
16670,"John Rubio","oh","MALE","15-20",42,"basic"
18600,"Jay Holland","fl","FEMALE","21-25",43,"basic"
23438,"James Hogsett","ne","FEMALE","15-20",30,"basic"
20669,"Florence Black","pa","FEMALE","26-35",24,"basic"
22880,"Judith Stuart","ga","MALE","21-25",25,"basic"

Create a view using the following command

# create or replace view `dfs`.`tmp`.`customer` as
SELECT columns[0] as `customer_id`,
       columns[1] as `name`,
       columns[2] as `state`,
       columns[3] as `gender`,
       columns[4] as `age_group`,
       columns[5] as `age`,
       columns[6] as `account_type`
 FROM `dfs`.`default`.`customers.all.csv`

Test the View