Share this article:

Vertica Integration with Datawatch Monarch: Connection Guide

Click here for a PDF version of this document. 

Applies to Vertica 8.1.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 Monarch as of May, 2017.

Datawatch Monarch Overview

Datawatch Monarch is a solution for self-service data preparation and visual discovery, available as a Windows 32-bit or 64-bit application. Monarch uses the ODBC driver to connect to your Vertica database.

This document is based on the results of testing Datawatch Monarch 13.5 with Vertica 8.1.x using Vertica ODBC 8.1.x.

Note Versions of Datawatch Monarch prior to 13.2 are not compatible with Vertica.

Download and Install Datawatch Monarch

Before you install Datawatch Monarch, review the system requirements and prerequisites for installation from the Monarch Installation Guide.

Download the latest version of Monarch that corresponds to your Windows system (either 32-bit or 64-bit). A free trial version of Datawatch Monarch is available here.

Install Monarch as an administrator, following the instructions in the installation guide.  

Download and Install the Vertica Client Drivers

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

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers.
  2. Download the version of the Vertica client package that is compatible with the architecture of 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 about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Create an ODBC Data Source Name (DSN)

You must create a DSN to use the ODBC driver to connect to Vertica from Datawatch Monarch. You can set up either a 64-bit DSN or a 32-bit DSN, depending on your operating system and Datawatch Monarch version.

Follow these steps to create a DSN:

  1. Navigate to the appropriate folder, depending on which DSN you need to install:

    • 64-bit DSN: Start > Control Panel > Administrative Tools > Data Sources (ODBC).
    • 32-bit DSN: Navigate to C:\Windows\SysWOW64 and locate the program file called exe.

    monarch1.png
     

  2. The ODBC Data Source Administrator displays. In the System DSN tab, click Add.
  3. In the Create a New Data Source dialog box, select Vertica driver and click Finish. The Vertica ODBC DSN configuration dialog box appears.
  4. In the Basic Settings tab, fill in the following required fields:

    • DSN Name: The name for the DSN.
    • Server: The host name or the IP address of the Vertica server to which you want to connect.
    • Database: The name of the Vertica database.
    • User Name: The name of the user account to use when connecting to the database.

    monarch2.png

  5. Click OK. The Vertica ODBC DSN configuration window closes and your new DSN in listed in the ODBC Data Source Administrator window.
  6. Click OK to close the ODBC Data Source Administrator window.

For more information, see Setting Up a DSN in the Vertica documentation.

Connect Vertica to Datawatch Monarch

  1. Launch the Datawatch Monarch application.
  2. Click the Open Data button that appears on the main screen.
  3. The Select Data Source to Open window appears. Click ODBC from the list of available connections.

    monarch3.png

  4. The ODBC DSN window opens. In the drop-down menu, select the DSN to your Vertica database you previously created.

     monarch4.png

  5. Enter your user ID and password.
  6. Click Test Connection. If the connection is successful, a message appears.
  7. Click OK to continue.

Datawatch Monarch automatically lists the tables that you have permission to access. Select a table to start exploring and preparing data.

Known Limitations

Known limitations using the ODBC driver are:

  • Datawatch Monarch does not support BINARY, VARBINARY, and LONG VARBINARY data types.
  • Datawatch Monarch does not support TIME, TIMETZ, and INTERVAL data types.
  • Datawatch Monarch displays NAN values as NULL.
  • When exported to a CSV file, Datawatch Monarch truncates LONG VARCHAR values that are greater than 30,000 characters to 29,998 characters.
  • Datawatch Monarch truncates NUMERIC and INTEGER data types to 14 digits and displays the extra digits as zeroes.

For More Information

For More Information About… See…

Datawatch Monarch

http://docs.datawatch.com/

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

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

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: