|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 is a business intelligence tool that provides insights for fast visual dashboards. For more information, see the TIBCO 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:
|Desktop platform||Windows Server|
Vertica JDBC client driver 9.1.0
|Vertica Server||Vertica Analytic Database 9.1.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:
- Go to the Vertica Client Drivers page on Vertica.
- 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
- Log in to the JasperReports server using your User ID and password.
- From the left panel, right-click Data Source. Select Add Resource, then select Data Source.
The New Data Source window appears.
- From the Type drop-down list, select JDBC Data Source.
- From the JDBC Driver drop-down list, select NOT INSTALLED: Vertica(com.vertica.jdbc.Driver).
- Click Add Driver.
- Click Browse and select the Vertica JDBC jar file.
- Click Upload.
Enter the following information for your Vertica server:
- Click Test Connection.
When the connection is successful, a confirmation appears.
- Click Save.
- In the Save window, add a Data Source Name and click Save.
The Vertica connection is now displayed in 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:
- Open Jaspersoft Studio.
- In the left panel select Data Adapter > Create Data Adapter.
- Select Database JDBC Connection and click Next.
The Data Adapter Wizard appears.
- From the JDBC drop-down list, select com.vertica.jdbc.Driver.
- Enter your credentials for the Vertica server.
- Click the Driver Classpath tab.
- Add the Vertica JDBC jar file.
- Click Test to test the connection.
- Click Finish.
- From the main panel, click File > New > Jasper Report.
The New Report Wizard opens.
- Select a template and click Next.
- Enter or select the Parent Folder, which will be the container for the report.
- Click Next.
- From the data adapter drop-down menu, select VerticaDS - Database JDBC connection.
- Click Next.
- Write the SELECT statement for the report and click Next.
- Select your dataset fields and click Finish.
The report is created and saved in the workspace in your local file system.
To publish your reports, you must use JasperReports, as follows:
- Open JasperReports.
- From the left panel, right-click Reports > Add Resource > JasperReport.
- Enter the report name.
- Click Browse and select the JRXML file you created.
- From the left panel, select Data Source.
The Select Data Source from Repository window opens.
- Click Browse and select VerticaDS.
- Click Submit.
The report is generated and displayed under the Reports folder.
- 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 does not support the month value for DATE, TIMESTAMP, and TIMESTAMPTZ data types.
- Jaspersoft does not support the INTERVAL YEAR TO MONTH, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, or INTERVAL data types. Jaspersoft returns this error:
There was an error on the server.