Share this article:

Vertica Integration with Datawatch Panopticon: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and Datawatch Panopticon: Latest Versions Tested

Software Version
Datawatch Panopticon

Designer 16.6.0 on Windows

Server 16.6.0 on Windows

Desktop Platform

Windows 10 64-bit

Vertica Client

Vertica JDBC Driver 9.1.1-0

Vertica Server Vertica Analytic Database 9.1.1-0

Datawatch Panopticon Overview

Datawatch Panopticon is a web-based visual analytics solution. Vertica recommends using JDBC to connect to Vertica from Datawatch Panopticon.

The Panopticon platform includes the following components:

  • Panopticon Designer: A graphical interface for creating visualizations and dashboards in files called workbooks, available for Windows as a 32 or 64-bit application. Use Panopticon Designer for authoring the workbooks and publishing them to Panopticon Server.
  • Panopticon Server: A Java Server for data shaping and caching, and HTML5 web client delivery. It is available for Windows or Linux.
  • Panopticon Analyst: An HTML5 Web Client for displaying visual analytics workbooks.

Download and Install Datawatch Panopticon

Visit the Datawatch site to request a demo version of Datawatch Panopticon Designer and Server. Complete the registration information and click Request Trial Version to download a trial version of the software.

To install Panopticon Designer, refer to the Panopticon Designer documentation.

To install Panopticon Server,refer to the Panopticon Server documentation.

Also go here for a complete set of Panopticon documentation.

Download and Install Vertica Client Drivers

Before you can connect to Vertica using Panopticon Designer, you must download and install the Vertica JDBC Driver on your Panopticon Server machine.

Note Install Vertica’s JDBC driver on the Panopticion Server machine only. No installation is required on the Panopticon Desktop machine.

  1. Navigate to the Client Drivers page on the Vertica website and download the JDBC client.
  2. Copy the Vertica JDBC .jar file from the folder where you downloaded it (for example, vertica-jdbc-x.x.x-x.jar), and paste it into the Tomcat/Lib folder of the Panopticon Server. For example, C:\Program Files\Apache Software Foundation\Tomcat 8.5\lib.
  3. Restart Panopticon server's Tomcat server.

Connect Panopticon Designer to Panopticon Server

Follow the steps below to connect the Panopticon Designer to the Panopticon Server:

  1. Launch the Datawatch Panopticon Designer application.
  2. Select Tools > Options.

    The Datawatch Panopticon Options dialog appears:

  3. Select the System tab.
  4. Enter the URL you received when you completed the installation of Panopticon Server in the Server URL field.
  5. Click OK to connect to the server.

Connect Panopticon Designer to Vertica via the Panopticon Server using JDBC

Panopticon Designer connects to Vertica via JDBC throught the Panopticon Server. The JDBC database connections are proxied by the Panopticon Server.

Before you start

Before beginning the connection procedure, make sure the following prerequisites are met:

Follow the steps below to connect the Panopticon Designer to Vertica:

  1. Launch the Datawatch Panopticon Designer application.
  2. Select File > New Workbook.

    The Connect to Data dialog appears:

  1. Select JDBC Database.

    The JDBC Database Connection dialog appears:

  1. Select URL from the top dropdown and enter the following:

    JDBC URL: Jdbc:vertica://<my-vertica-server>:<port>/<database-name>

    Driver Class Name: com.vertica.jdbc.Driver

    Username: database user name

    Password: database password
  2. In the Query Execution Settings section, select Vertica from the SQL Dialect dropdown.
  3. Expand the Query section.
  4. Enter a query to select the data required for your analysis. For example:


  5. Click OK to execute the query and retrieve data.

    After a successful connection, the data appears in the Workbook interface and a copy of the data gets transferred to the Panopticon Designer:


Note Vertica recommends using the Query option as opposed to Tables & Views. Vertica recommends to using the Query option to narrow down the rows you transfer from Vertica into Panopticon Desktop for your analysis.

Known Issues

Known limitations for using Vertica with Datawatch Panopticon are as follows.

Connection

Note Panopticon Designer provides the following methods of connecting to Vertica in the Connect to Data window:

  • JDBC Database: Uses Vertica JDBC driver. This document focuses on this connection method.
  • Database: Two options are available, Vertica OLEDB and Microsoft OLEDB provider for ODBC Driver.

The first bullet item below provides more detail

  • The Panopticon Server is a Java Application connecting to Vertica via JDBC. When the Database connector has been selected in the Panopticon Designer Connect to Data window, and you use the Vertica OLEDB driver or the Microsoft OLEDB provider for ODBC Driver, you must complete the JDBC URL or JNDI Name in Java Connection Details section before the workbook can be successfully published to the Panopticon Server. If you do not complete the Java Connection Details section, the following error appears:

    Error message: The parameter 'javaJndiResourceName and javaUrl' cannot be null

  • The Tables & Views join feature is limited. You can only join tables from schemas that are in the user’s search path. In addition, Tables & Views does not support joins that consist of composite keys. To resolve this, connect to Vertica using the Query option or update the user’s search path by adding the additional schemas that you require for your joins.
  • Tables & Views does not support joins that consist of a composite key. For example, the join between inventory_fact and product_dimension using the composite key: version_key and product_key is not supported. As a workaround, use the Query option to build this type of join.

Datatype Support Limitations

Datawatch Panopticon does not support all data types supported by Vertica. The following is a list of known limitations for data types:

  • NaN is not supported in the Float datatype.
  • Null values for Numeric, Float, Time, TimeStamp, TimestampTz, and TimeTz datatypes are displayed as N/A.

  • For Int, Float and Numeric data types, Panopticon converts to a double precision floating point format, rounding off the value. Max precision is 15 digits, for example 9223372036854775807 appears as 9223372036854780000.
  • Time data types (Time and Timetz) are converted to a timestamp, with the current system date appended to the supplied time. Dates are also converted to a timestamp, with the time set to midnight (00:00:00.000).

  • Interval type is not supported, the following error appears:
    Invalid Column Type
  • Binary, VarBinary, and LongVarBinary data types are not supported, the following error appears:
    Invalid Column Type
  • UUID data type is not supported, the following error appears:
    Unknown SQL Type

For a complete list of Vertica-supported data types, see SQL Data Types in the Vertica documentation. 

For More Information

 

Share this article: