jreport.md

Using JReport to visualize data with the Hortonworks Data Platform

###Introduction

JReport is a embedded BI reporting tool can easily extract and visualize data from the Hortonworks Data Platform 2.3 using the Apache Hive JDBC driver. You can then create reports, dashboards, and data analysis, which can be embedded into your own applications.

In this tutorial we are going to walkthrough the folllowing steps to demonstrate Apache Hive with JReport:

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

###Prerequisite

• Download and install the HDP Sandbox
• Download and install JReport

###Steps

####Step 1: Install the Apache Hive JDBC driver with JReport. Hive provides standard JDBC connectivity to easily integrate with JReport. For general instruction to install the Hive JDBC driver see http://hortonworks.com/hadoop/hive/.

1. Download Apache Hive JDBC Driver and Hadoop common library.
2. Copy the JDBC driver and Hadoop common library in the lib folder %REPORTHOME%\lib\ For Windows, copy the JDBC driver jar file into the C:\JReport\Designer\lib folder.

C:\JReport\Designer\lib\hive-jdbc-1.2.1.2.3.0.0-2557-standalone.jar
C:\JReport\Designer\lib\hadoop-common-2.7.1.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, as in Figure 1.

Figure 1: Edit the setenv.bat file.

####Step 2: Create a new JReport Catalog to manage the Hive 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. Enter the “Driver”, “URL”, “User” and “Password”, as in Figure 2.

Figure 2: Enter JDBC connection information in the Catalog Browser.

6. Click “OK”, JReport will verify the connection and save all information.
7. Add Tables and Views to the JReport 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, as in Figure 3.

Figure 3: Add tables in the Catalog Browser.

8. 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, as in Figure 4. You may also import your own SQL statements.

Figure 4: Define SQL queries using the 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. Use JReport Designer to query the data and create a report, as shown in Figures 5, 6 and 7.

Figure 5: Choose the dataset that you want to use.

Figure 6: Choose the data fields that you want to display.

Figure 7: The resulting report that you can continue to modify.

5. Use the chart wizard to add charts to an existing report, as shown in Figures 8 and 9.

Figure 8: Choose the data fields that you want to display in the chart.

Figure 9: The resulting report after charts have been added.

Congratulations on creating a report on the Hortonworks Sandbox using JReport! Next, learn how to embed reports and dashboards directly into your own applications.