Vertica Integration with Datawatch Panopticon: Connection Guide

Applies to Vertica 8.0.x and earlier 

 

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, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Datawatch Panopticon as of November, 2016.

Datawatch Panopticon Overview

Datawatch Panopticon along with Datawatch Monarch, allows for self-service data preparation and fast visualization. Datawatch is available for Windows as a 32-bit or 64-bit application. Datawatch Panopticon has a Vertica-named connection that uses the ODBC driver to connect to Vertica.

This document is based on the results of testing Vertica 8.0.x with Datawatch Panopticon 16.0.0 on a Windows 64-bit platform.

Download and Install Datawatch

Panopticon

Visit the Datawatch site to request a demo version of Datawatch Panopticon and download a trial version of the software. Follow the instructions from Datawatch.

To install Datawatch Panopticon, follow the instructions provided by Datawatch. Double-click the installer, and a series of prompts leads you through the installation process. The installer checks if your machine meets all the prerequisites required to install the application. If some prerequisites are not met, the installer attempts to install them. 

Download and Install Vertica Client Drivers

Before you can connect to Vertica using Datawatch Designer, you must download and install the Vertica client package. This package includes the ODBC driver that Datawatch Designer uses to connect to Vertica.

Download Vertica Client Driver

  1. Go to the Vertica Client Drivers page. 
  1. Download the version (32-bit or 64-bit) of theVertica client package that matches your operating system and Vertica server version.

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

Install Vertica Client Driver

As a Windows administrator, double-click the installer and follow the wizard to complete the installation process. You should only install the ODBC driver.

Note: If you use Datawatch Panopticon version 13.7 or later, you do not need to create a DSN. If you use an earlier version of Datawatch Panopticon, you must create a DSN.

For more information, see Creating an ODBC DSN for Windows Clients.

Connect Datawatch Panopticon to Vertica

  1. Launch the Datawatch Panopticon application.
  2. Select Work with Datawatch Panopticon stand alone
  3. On the top navigation menu, select File > New Workbook. The Connect to Data dialog box opens.
  4. From the list of available connectors, in the Database column, select Vertica. The Vertica connection window opens.datawatch.png
  5. In the connection window, enter the following information for your database: 
    • Host Name
    • Port
    • User ID
    • Password
    • Database
  6. Click Load Tables
  7. Select a table you want to explore or select query to write a query. verticaconnection.png
  8. If you choose to write a query, use aliases for the columns. 
  9. To execute the query and retrieve the data, click OK
  10. Click OK. The main workbook interface opens. 

Known Limitations

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

  • Datawatch Panopticon displays NAN numeric values and NULL values as n/a.
  • Datawatch Panopticon does not support the INTERVAL data type.
  • Datawatch Panopticon does not support the TIME and TIMETZ data types. Datawatch Panopticon displays the current date instead of the TIME and TIMETZ values. To display the time part of the value, Right-click Change Data Type > Text.
  • Datawatch Panopticon displays only the date from the TIMESTAMP and TIMESTAMPTZ data types. The time is not displayed. To display the time part of the value, Right-click Change Data Type > Text.
  • Large numeric values may be truncated. The maximum number of digits Datawatch Panopticon supports is 15. For example 0335 is displayed as 7,922,816,251,426,430,000,000,000.00
  • Datawatch Panopticon rounds some decimal values. For example, 1234.567890 is displayed as 1234.57
  • By default, Datawatch Panopticon displays 2 digits to the right of a decimal point. Some values require formatting so that they display correctly. Right click on the value and select Change Format to set the proper display.

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

 

For More Information

For More Information About… …See

Datawatch Panopticon

http://www.datawatch.com/our-platform/panopticon/ 

Vertica Community Edition

https://vertica.com/community/

Vertica Documentation

http://vertica.com/docs/latest/HTML/index.htm