Vertica Integration with Qlik Replicate: 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 Qlik Replicate: Latest Versions Tested

Software Version
Partner Product

Qlik Replicate 2021.5.0.745

Partner Product Platform

Windows Server 2019 Standard

Red Hat Enterprise Linux Release 8.3 (Ootpa)

Vertica Client

Vertica ODBC 11.1.0-0

Vertica Server

Vertica Analytic Database 11.1.0-0

Qlik Replicate Overview

Qlik Replicate (formerly known as Attunity Replicate) provides automated, real-time and universal data integration across all major data lakes, streaming systems, databases, data warehouses, and mainframe systems, on premises and in the cloud.

Qlik Replicate empowers organizations to accelerate data replication, ingest and stream across a wide range of heterogeneous databases, data warehouses, and big data platforms.

Installing Qlik Replicate

Qlik Replicate can be installed on both Windows and Linux environments.

To install Qlik Replicate:

  1. Go to the Qlik Replicate download page.
  2. Click Free Trial.
  3. Provide the registration details and click Submit.

  4. Qlik replicate builds the trial hosted on its cloud instance. If you want to install the tool, contact Qlik support to get the installer file.
  5. Follow the installation instructions in the Qlik Replicate documentation.

Installing the Vertica Client Driver

Qlik Replicate uses the ODBC client driver to connect to your Vertica database. To download and install the Vertica client package:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

    Note  

    For details 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:

  4. Create a DSN as described in Creating an ODBC Data Source Name (DSN)

Connecting Qlik Replicate to Vertica

Follow these steps to connect Qlik Replicate to your Vertica database. This creates a source and target for Vertica data.

  1. Open a browser and type the following to go to the Qlik Replicate login page. Enter the credentials to log in:

    Windows:

    https://<IPAddress_or_HostName>/attunityreplicate

    Linux:

    https://<IPAddress_or_HostName>:<Port_ Number>/attunityreplicate
  2. Click the Manage Endpoint Connections tab:

  3. In the Manage Endpoint Connections dialog box, click New Endpoint Connection to create a source and target data source:

  4. Provide the following details to define the source endpoint:

    • Name: Type a source connection name.
    • Description: Provide a description for the source endpoint.
    • Role: Click Source.
    • Type: Select ODBC.
    • Click DSN, then click Browse to locate Vertica DSN.
    • Username: Type the database user name.
    • Password: Type the database password.

  5. Click Test Connection to test the source connection and click Save to save the endpoint.
  6. To define the target endpoint (different from the source ODBC endpoint), click +New Endpoint Connection and provide the following details:

    Note For a target endpoint, you need to select the built-in Vertica connector that Qlik Replicate provides.

    • Name: Target connection name.
    • Description: Description for the target endpoint.
    • Role: Click Target.
    • Type: Select Vertica.
    • Server: Vertica Server host IP address.

    • Port: Vertica database port number. Default is 5433.

    • Username: Database username.

    • Password: Database password.


  7. Click Test Connection to test the target connection.
  8. Click Save to save the Target endpoint and click Close.

Replicating Data from Vertica Source to Vertica Target

Follow these steps to create a replication task:

  1. In the menu bar, click New Task.

  2. The New Task window appears:

  3. Provide the following details:
    • Name: Task name.
    • Description: Description of the replicate task.

    • Replication Profile: Select Unidirectional.
    • Task Options: Select Full Load.
  4. Click OK to create the new replication task.
  5. Open the new replication task:

  6. Drag and drop the source and target endpoints (Vertica_Src and Vertica_tgt in this example) to their correct positions in the Task Designer Window:

  7. Click Table Selection on the right pane of the Task Designer Window:

    The Select Tables/Views dialog appears:

    • Schemas: Select the desired database schema from the drop-down list.
    • Name: Type the name of the database table or type % to fetch the list of all tables for a database schema selected in the Schemas field.


  8. Select the tables for which you want to replicate data to the target database and click OK.
  9. Click Run to execute the replication process:

  10. Click Monitor to check that the replication was successful. A list of replicated tables appears at the bottom of the screen.

Troubleshooting

Review the following known issue and workaround when connecting Qlik Replicate to Vertica:

String data truncation during data replication

Issue: Qlik replicate truncates the column data after 4096-character positions for long string data types and displays the following error:

Truncation of a column occurred while fetching a value

Solution: To resolve this error,

  1. In the Designer window, click Task Settings.

  2. In Task Settings dialog box, click Metadata > Target Metadata and change the value of Limit LOB size to (KB) to the required number.

  3. If the replicate task is a CDC task, then additionally do the following:

    In the Task Settings window, go to Change Processing > Change Processing Tuning and select Transactional apply in the Change Processing Mode drop-down.


Known Limitations

  • For DECIMAL data type, a precision of up to 38 digits is displayed beyond which a table column is converted to string.

  • For TIME and TIMETZ datatypes, milliseconds are truncated.

  • For TIMETZ and TIMESTAMPTZ data types, timezone values are truncated.

  • INTERVALSECOND, INTERVALMONTH, and UUID data types are not displayed in Qlik Replicate CDC tasks.
  • For LONG VARBINARY data type, data can be replicated correctly when
    • Size of Limit LOB Size to (kb) is changed to the required value from 8 (default) and
    • Change Processing Mode drop-down is Transaction Apply under Target Metadata of Task Settings for Vertica CDC processes.

For More Information