Vertica Integration with Logi Info: 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.

Logi Info Overview

Logi Info is a business intelligence tool that provides wonderful insights for fast, visual dashboards. Business users can interact with data for data blending and analysis across disparate data sources. To start learning about Logi Info, see the Tutorials and User Guides documents.

Vertica and Logi Info Software Versions

This document provides guidance using the software listed as follows:

Software Version
Partner Product

Logi Info 12.7.097

Logi Info Client Platform

Microsoft Windows Server 2016

Vertica Client

Vertica ADO.NET and JDBC 9.3.0

Vertica Server

Vertica Analytic Database 9.3.0

Vertica Server Platform CentOS 7.4

Installing Logi Info

Note You must register on the Logi Analytics website before downloading the software.

  1. Go to the Logi DevNet Registration page.
  2. Complete the member registration information and click Join. You will receive a password via email shortly after completing the registration.
  3. Log in to the DevNet site. You will be asked to change your password.
  4. In the Support menu, select Download Products and download Logi Info.
  5. Follow the instructions documented here to install Logi Info on Windows.

Installing the Vertica Client Driver

Logi Info uses either ADO.NET or JDBC driver to connect to Vertica.

To install the drivers,

  1. Go to the Vertica Client Drivers page on the Vertica website.
  2. Download the required client driver that is compatible with the Vertica server version.
  3. Note  

    For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  4. For ADO.NET, double-click the installer and follow the prompts.

  5. For JDBC, copy the .jar file to the client directory for external libraries.

Connecting Logi Info to Vertica

Logi Info can be connected to Vertica using ADO.NET or JDBC.

To create a new application,

  1. Select Studio from the Start menu:

    Note The first time you access Studio, a Getting Started screen appears. Close this to continue using Studio.

  2. From the Welcome screen, select New Application:



    You can also select New Application from the ribbon menu on the Logi Info Studio main page:



    A pop-up appears with the following options for creating a new application:


  3. For using ADO.NET connection, select Microsoft.NET and for JDBC, select Java.

Creating an Application for Microsoft.NET (IIS)

Follow the steps below to create an application for Microsoft.NET:

  1. Select Microsoft.Net from the pop-up window and click OK.
  2. Enter the Application name for the IIS server and click OK. Ensure to note the path in the Folder field. This is the location where the project is stored.



    The New Application Wizard appears.
  3. Run the New Application Wizard to create your application.
  4. After creating the application, locate the Vertica.Data.dll file. For example, for a 64-bit driver package installed on Windows, you can find the .dll at C:\Program Files\Vertica Systems\ADO.NET\Vertica.Data.dll.
  5. Copy the Vertica.Data.dll file into the project bin folder mentioned above such as C:\<Application Folder path>\<ProjectName>\bin.

    This new application has a default report associated with it. You can see the report in the application's left pane. For example:


    Rename the report based on your requirements. You will need this report going forward when using Logi Info.

  6. Under the Settings tab, right-click Connection > Element Wizard > Add a Database Connection.



    The Connection Wizard appears. Enter the following information to complete the Connection wizard:

    Connection ID: Enter a unique connection name.

    Connection Type: Enter Vertica as the connection type.

    Vertica Server Name: Enter the name of your Vertica server.

    Vertica Database: Enter the name of your Vertica database.

    Port number: Enter the port number.

    Vertica User Name: Enter the username you use to log into the Vertica database.

    Vertica Password: Enter the password you use to log into the Vertica database.

  7. Logi Studio tests the connection. If it is successful, the following message appears:

    Test results for Vertica connection:



    Connection Successful.


  8. Click Next and Finish to close the wizard.

Creating an Application for Java

Follow the steps below to create an application for Java:

Note Before you begin you must have an Apache Tomcat server or comparable Java application server on your environment.

  1. Select Java from the popup window and click OK.
  2. Enter the application name for the Tomcat server and provide the path where the new application needs to be deployed. For example, C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps.



    The New Application Wizard appears.
  3. Run the New Application Wizard to create your application.
  4. After creating the application, locate the Vertica.jar file.
  5. Copy Vertica.jar into the Apache Tomcat lib directory that contains JARs. For example, the location on Windows is C:\<Apache Tomcat Application Folder path>\Apache Tomcat\lib.
  6. Restart the Tomcat Server.

    This new application has a default report associated with it. You can see the report in the application's left pane. For example:


    Rename the report based on your requirements. You will need this report going forward when using Logi Info.

  1. Under the Settings tab right-click Connection > Element Wizard > Add a Database Connection.



    The Connection Wizard appears. Enter the following information to complete the Connection wizard:

    Connection ID: Enter a unique connection name.

    Connection Type: Enter Vertica as the connection type.

    Vertica Server Name: Enter the name of your Vertica server.

    Vertica Database: Enter the name of your Vertica database.

    Port number: Enter the port number.

    Vertica User Name: Enter the username you use to log into the Vertica database.

    Vertica Password: Enter the password you use to log into the Vertica database.

  1. Logi Studio tests the connection. If it is successful, the following message appears:

    Test results for Vertica connection:



    Connection Successful.


  2. Click Next and Finish to close the wizard.

Viewing Data in Vertica

View data residing in the Vertica database by creating a sample report as follows:

  1. Open the Default report that was created in the steps above:



  2. Right-click Body and select Element Wizard > Add a Data Table:

  3. In the Data Table Wizard, select SQL from the DataLayer drop-down, and click Next.



  4. Write a custom query to retrieve data from the table or select multiple tables from the SQL Query Builder wizard. Click Next.



    The report appears in the wizard.



  5. Click Next and Finish.

You can also run this report on web browser.


Troubleshooting

You may encounter some issues when connecting Logi Info to Vertica.

If you receive the following error message when using the JDBC connector

[Vertica][VJDBC](4856) ERROR: Syntax error at or near "LIMIT"

remove the semi-colon after the query used.

If you receive the error

Invalid URI: The format of the URI could not be determined.

troubleshoot as follows:

  • Double-click to open the settings file of the project.
  • In the Settings tab, select Path.
  • In the Application field, enter http://localhost:8080/<ProjectName>

Known Limitations

Logi Info uses JDBC and ADO.NET for connecting to Vertica. Before using Logi Info with Vertica, be aware of the following known limitations:

Limitations while Using Java Application

  • LongVarChar data type is not fully supported using Logi Info.
  • Numeric data type supports precision up to 28 digits above which "The value is out of range for a decimal" error is displayed.
  • Time data type supports milliseconds up to 3 digits above which the value is rounded off.
  • Timestamp data type is not supported. If you convert it to DateTime format, the data type is supported but milliseconds get truncated.
  • TimeTZ data type does not support milliseconds as the value is truncated without any error. Also Time is displayed in UTC timezone.
  • TimestampTZ data type is not supported. If you convert it to DateTime format, the data type is supported but milliseconds and time zone get truncated.
  • Binary, VarBinary, and Long Varbinary data type values are displayed in hex. For example, "\377\000\000\000" is displayed as "FF000000".

Limitations while Using .NET Application

  • LongVarChar data type is not fully supported using Logi Info.
  • Numeric data type supports precision up to 28 digits above which the "Specified argument was out of the range of valid values" error is displayed.
  • “interval year to month” data type is not supported and is displayed incorrectly.
  • Time and Timestamp data types are not supported. If you convert Time to LongTime and Timestamp to DateTime, the data type is supported without milliseconds.
  • For TimeTZ and TimestampTZ data types, milliseconds and time zone are truncated. For TimeTZ data type, date is appended. To remove the date part, convert it to LongTime format.
  • Binary, VarBinary, and Long Varbinary data type values are displayed in hex. For example "\377\000\000\000" is displayed as "FF000000".

For More Information