Share this article:

Vertica Integration with Jaspersoft: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, these other versions may not have been tested.

About this Document

This document explains how to connect Jaspersoft to Vertica.

Jaspersoft Overview

Jaspersoft is a business intelligence tool that provides insights for fast visual dashboards. For more information, see the Jaspersoft website.

To download a community edition of the Jaspersoft software, see Download Jaspersoft Business Intelligence Suite.

Vertica and Jaspersoft Product Versions

The information in this document is based on the testing we have done with the following software versions:

Software Version
JasperReports 6.4
Jaspersoft Studio 6.4
Desktop platform Windows Server
Vertica client

Vertica JDBC client driver 8.1

Vertica Server Vertica Analytic Database 8.1.0-0

Install the Client Driver

Jaspersoft uses JDBC to connect to Vertica. Before you can connect Jaspersoft to Vertica, you must download and install the Vertica client package that includes the driver.

To download and install the Vertica client package:

  1. Go to the Vertica Client Drivers page on MyVertica.
  2. Download the Vertica JDBC driver that matches your operating system and the version of Vertica that you are using.

Note Vertica drivers are forward compatible. You can connect to the Vertica server using previous versions of the client. For more information, see Client Drivers and Server Version Compatibility in the Vertica documentation.

Connect Vertica to Jaspersoft

  1. Log into the JasperReports server using your User ID and password.
  2. From the left panel, right-click Data Sources and select Data Source.
  3. The New Data Source window appears. From the type dropdown menu, select JDBC Data Source.
  4. From the JDBC driver dropdown menu, select NOT INSTALLED: Vertica(com.vertica.jdbc.Driver).
  5. Click Add Driver.
  6. Click Browse and select the Vertica JDBC jar file.
  7. Click Upload.
  8. Enter the following information for your Vertica server:
    1. Host
    2. Port
    3. Database
    4. URL
    5. User Name
    6. Password
  9. Click Test Connection. A connection passed confirmation appears.
  10. Click Save.
  11. The Save window appears. Add a Data Source Name.
  12. Click Save.

The Vertica connection is now displayed on the data sources list:

Visualize Data in Jaspersoft

You can prepare reports using Jaspersoft Studio and then publish the reports to the JasperReports server. Follow these steps to create the reports:

  1. Open Jaspersoft Studio.
  2. In the left panel select Data Adapter > Create Data Adapter.

  3. The Data Adapter Wizard appears. Enter your credentials for the Vertica server.
  4. Click the Driver Classpath tab.
  5. Add the Vertica JDBC jar file.
  6. Click Test to test the connection.
  7. Click Finish.
  8. From the main panel, click File > New > Jasper Report.
  9. The New Report Wizard window opens. Select a template and click Next.
  10. From the data adapter dropdown menu, select VerticaDS - Database JDBC connection.
  11. Click Next.
  12. Write the select query for the report and click Next.
  13. Select your dataset fields and click Finish.

The report is created and saved in the workspace on your local file system.

Publish Reports

To publish your reports, you must use JasperReports.

  1. Open JasperReports.
  2. From the left panel, right-click Reports > Add Resource > JasperReport.
  3. Enter the report name.
  4. Click Browse and select the JRXML file you created from your local file system.
  5. From the left panel, select Data Source.
  6. The Select Data Source from Repository window opens. Click Browse and select VerticaDS.
  7. Click Submit.

The report is generated and displayed under the Reports folder.

Known Limitations

  • Jaspersoft does not support binary, varbinary, and long varbinary data types.
  • Jaspersoft rounds off millisecond values on TimeTZ ,Time, Timestamp, and TimestampTz data types.
  • Jaspersoft appends the default date 1/1/70 to the TimeTZ and Time data types.
  • Jaspersoft does not support the interval second and interval month data types and throws an error.

For More Information

 

Share this article: