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:
- Install the Drill JDBC Driver with JReport.
- Create a new JReport Catalog to manage the Drill connection.
- 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
Example of the JDBC driver location on a MapR cluster
2. Copy the Drill JDBC driver in the lib folder
Example for Windows copying the Drill JDBC driver jar file in C:\JReport\Designer\lib folder
3. Add the file path into the JReport CLASSPATH variable
For Windows edit the C:\JReport\Designer\bin\setenv.bat file
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
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”
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)
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.
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.
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.
If you have any questions on our to use Apache Drill with JReport, please ask them in the comments sections below.