Configuring JReport with Apache Drill to Analyze and Create Reports of Various Data Types

JReport is an embeddable BI solution that empowers users to create reports, dashboards, and data analysis. JReport accesses data from Hadoop, such as the MapR Distribution through Apache Drill, as well as other big data and transactional data sources. By visualizing data through Drill, users can perform their own reporting and data discovery for agile, on-the-fly decision-making.

JReport 13.1 can easily extract and visualize data from the MapR Distribution using the Apache Drill JDBC driver. You can then create reports, dashboards, and data analysis, which can be embedded into your own applications.

Complete the following simple steps to use Apache Drill with JReport:

  1. Install the Drill JDBC Driver with JReport.
  2. Create a new JReport Catalog to manage the Drill connection.
  3. Use JReport Designer to query the data and create a report.

Step 1: Inall the Apache Drill JDBC driver.

Drill provides standard JDBC connectivity to easily integrate with JReport. JReport 13.1 requires Drill 1.0 or later.

For general instruction to install the Drill JDBC driver see http://drill.apache.org/docs/using-jdbc/

1. Locate the JDBC driver in the Drill installation directory on any node where Drill is installed on the cluster

<drill-home>/jars/jdbc-driver/drill-jdbc-all-<drill-version>.jar

Example of the JDBC driver location on a MapR cluster

/opt/mapr/drill/drill-1.0.0/jars/jdbc-driver/drill-jdbc-all-1.0.0-SNAPSHOT.jar

2. Copy the Drill JDBC driver in the lib folder

%REPORTHOME%\lib\

Example for Windows copying the Drill JDBC driver jar file in C:\JReport\Designer\lib folder

C:\JReport\Designer\lib\drill-jdbc-all-1.0.0-SNAPSHOT.jar

3. Add the file path into the JReport CLASSPATH variable

%REPORTHOME%\bin\setenv.bat

For Windows edit the C:\JReport\Designer\bin\setenv.bat file

Drill JDBC driver lib folder

4. Verify that the JReport system can resolve the hostnames of the Zookeeper nodes of the Drill cluster. This can be done by configuring DNS for all the systems, alternatively the hosts file on the JReport system can be edited to include the hostnames and IP addresses of all the Zookeeper nodes used with the Drill cluster.

For Linux systems the hosts file is located at /etc/hosts

For Windows systems the hosts file is located at %WINDIR%\system32\drivers\etc\hosts

Below is an example of a Windows hosts file

Jreport - windows hosts file example

Step 2: Create a new JReport Catalog to manage the Drill connection.

1. Click Create New -> Catalog…

2. Provide a catalog file name and click “…” to choose the file saving location.

3. Click View -> Catalog Browser

4. Right click on “Data Source 1” and select “Add JDBC Connection”

5. Fill in the “Driver”, “URL”, “User” and “Password”

jreport catalog browser

6. Click Options>> select the “Qualifier” tab.

7. In the Quote Qualifier section, choose “User Defined” and change the “Quote Character” from “ to ` (back tick)

quote qualifier section

8. Click “OK”, JReport will verify the connection and save all information.

9. Add Tables and Views to the JReports catalog by right-clicking on the connection node and choose “Add Table”. Now you can browse the schemas and add specific tables you wish to make available for building queries. 

Jreport designer tables

10. Click “Done” once you add all necessary tables.

Step 3: Use JReport Designer to query the data and create a report.

1. In Catalog Browser right-click on “Queries” and select “Add Query…”

2. Define a JReport Query using the Query Editor. You may also import your own SQL statements.

jreport query editor

3. Click “OK” to close the Query Editor and click the “Save Catalog” button to save your progress to the catalog file.

4. NOTE: If there are errors running the report you may need to edit the query and add the schema in front of the table name i.e. Select column from schema.table_name . This can be done by clicking on the SQL button on the Query Editor.

5. Use JReport Designer to query the data and create a report.

crosstab wizard - jreport

jreport 8

jreport final report example

If you have any questions on our to use Apache Drill with JReport, please ask them in the comments sections below. 

no

Streaming Data Architecture:

New Designs Using Apache Kafka and MapR Streams

 

 

 

Download for free