Share this article:

Vertica Integration with Apache Nifi: 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 version of Vertica and one version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested those other versions.

Vertica and Apache NiFi Versions Tested

Software Version
Partner Client

Apache NiFi 1.5.0

Desktop Platform

Windows

Vertica Client

Vertica JDBC driver 9.0.1-0

Vertica Server

 Vertica Server 9.0.1-0

NiFi Overview

Apache NiFi is a tool for automating and managing the flow of data between databases. NiFi has a web-based user interface for design, control, feedback, and monitoring of dataflows. NiFi is an open source product designed for extensibility. It is highly configurable, allowing fine-grained control of parameters such as loss tolerance versus guaranteed delivery and low latency versus high throughput. NiFi lets you track dataflow from beginning to end and even modify the flow at runtime.

Nifi uses standard SQL syntax for database operations and JDBC for connectivity. This standards-based approach allows NiFi to support different database systems.

For more information, visit the Apache NiFi website.

Install NiFi

  1. Navigate to the Apache NiFi Download page.
  2. Follow the download procedures using the specified keys.
  3. Follow the installation instructions in the Apache NiFi documentation.

Install the Vertica Client Driver

Before you can connect to Vertica using Nifi, you must install the Vertica JDBC client driver.

To install the driver:

  1. Navigate to the Vertica Client Drivers page.
  2. Download the JDBC driver.

    Note Vertica drivers are forward compatible, so you can connect to the Vertica server using earlier versions of the client.

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

  3. Follow the installation instructions in the Vertica documentation.

    Note To install the Vertica JDBC driver on Windows, follow the instructions in Installing the JDBC Client Driver for Windows.

Connect NiFi to Vertica

Follow the steps in this section to connect NiFi to Vertica as both a source and a target data source.

Note You will create two Nifi processors: one for the data source connection, the other for the data target connection to the same database. A NiFi processor encapsulates a set of actions on dataflows, incorporating data routing, transformation, and mediation between systems.

Start NiFi and Open the User Interface

These instructions explain how to start NiFi on Windows. To start NiFi on other platforms, see the NiFi documentation.

  1. Start NiFi by executing the run-nifi.batx file:
    installation_directory\bin\run-nifi.batx
  2. To open the NiFi user interface, type this URL in your browser:

    http://localhost:8080/nifi

    Note The URL for the NiFi user interface is http://DataSourceName:8080/nifi. The port is 8080 by default. You can change the port by editing the nifi.properties file in the NiFi conf directory.

Create a Connection to Vertica as a Data Source

  1. In the NiFi user interface, drag and drop the Processor icon to the center of the canvas.

    The Add Processor dialog box opens.

  2. Select database from the list of groups and QueryDatabaseTable from the list of types.

  3. Click Add.
  4. Right-click the new processor on the canvas and select Configure.

  5. On the Configure Processor page, open the Properties tab.
  6. Select Database Connection Pooling Service and click the Value box.

    A popup box displays.

    Expand the list of values and select Create new service.

    The Add Controller Service dialog box opens.

  7. Under Compatible Controller Services, select DBCPConnectonPool 1.5.0.

    Under Controller Service Name, type a name for the service.

  8. Click Create.
  9. Back on the Properties tab of the Configure Processor page, select Generic for Database Type and click the arrow icon.

    A message prompts you to save changes before going to the Controller Service. Click Yes.

    The NiFi Flow Configuration page displays.

  10. Select the controller service you have created and click the Configure button.

    The Configure Controller Service page displays.

  11. Open the Properties tab and provide your database connection information:

    • Database Connection URL: jdbc:vertica://IP_address:Port#/Database_Name
    • Database Driver Class Name: com.vertica.jdbc.Driver
    • Database Driver Location(s): Base_Location/vertica-jdbc-9.0.1-0.jar
    • Database User: Database_UserName
    • Password: Database_Password
  12. Click APPLY.
  13. To enable the new Controller service, click the Enable button.

  14. Click Close.

You can now use NiFi to query data in Vertica. To enable write access, follow the steps in the next section.

Create a Connection to Vertica as a Data Target

  1. Create a new processor by dragging and dropping the Processor icon to the center of the canvas.
  2. In the Add Processor dialog box, select database from the list of groups and PutDatabaseRecord from the list of types. Click Add.
  3. Right-click the new processor on the canvas and select Configure.
  4. On the Configure Processor page, open the Properties tab.
  5. Select Record Reader and click the Value box.

    A popup box displays.

    Expand the list of values and select Create new service.

    The Add Controller Service dialog box opens.

  6. Under Compatible Controller Services, select AvroReader 1.5.0.

    Under Controller Service Name, type a name for the service.

    Note The DBCPConnectionPool controller service we used for the data source connection generates and executes a SQL SELECT statement to fetch data from Vertica. The query result is converted by this controller to Avro format. For this reason, we need to use the AvroReader controller service to convert the Avro format to records that can be written to the Vertica target.

  7. Click CREATE.
  8. Back on the Properties tab of the Configure Processor page, select Statement Type and expand the list of values.

    Select the type of write operation that you want to allow in this connection. The choices include: INSERT, UPDATE, DELETE.

  9. Click the right arrow icon.

    A message prompts you to save changes before going to the Controller Service. Click Yes.

    The NiFi Flow Configuration page displays.

  10. Select the controller service you have created and click the Configure button.

    The Configure Controller Service page displays.

  11. Open the Properties tab and provide your database connection information:

    • Database Connection URL: jdbc:vertica://IP_address:Port#/Database_Name
    • Database Driver Class Name: com.vertica.jdbc.Driver
    • Database Driver Location(s): Base_Location/vertica-jdbc-9.0.1-0.jar
    • Database User: Database_UserName
    • Password: Database_Password
  12. Click APPLY.
  13. To enable the controller processor, click the Enable button.
  14. Click Close.

Now you can use NiFi to read data from a Vertica source or write data to a Vertica target.

Known Limitations

  • Nifi does not support the Vertica data tyes UUID, INTERVAL MONTH, and INTERVAL SECOND. The following error is returned:
    Error during database query or conversion data to Aveo format(UUID, Interval Month and Interval Second are not supported in Nifi
  • NiFi loads Vertica LONGVARCHAR values up to 4096 characters.
  • Nifi does not load Vertica BINARY, VARBINARY, and LONG VARBINARY data, because it converts the data to a different type.
  • NiFi truncates the millisecond values for TIME and TIMETZ data types.

Find More Information

Share this article: