|Share this article:|
Vertica Integration with Attunity Replicate: 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.
Attunity Replicate Overview
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.
Attunity Replicate empowers organizations to accelerate data replication, ingest and streaming across a wide range of heterogeneous databases, data warehouses and Big Data platforms. For more information about Attunity, visit the Attunity Website.
For information about Attunity Replicate, visit the Attunity website.
Vertica and Attunity Versions Tested
Install Attunity Replicate
Go to the Attunity website to download and install Attunity Replicate.
Download and Install the Vertica Client Drivers
Before you can connect to Vertica using Attunity Replicate you must install the Vertica client package. This package includes the ODBC/ client driver that Attunity Replicate uses to connect to Vertica.
Download Vertica Client Drivers
- Go to the Vertica Client Drivers page.
- 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 earlier versions of the client.
In Vertica 9.0, ODBC is both forward and backward compatible. The ODBC driver was made backwards compatible starting in Vertica 8.1.1 to enable connection to most Vertica server versions.
For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Install Vertica Client Drivers
Install the ODBC driver and create a DSN, as described in the Vertica documentation.
Connect Attunity Replicate to Vertica
Follow the procedure below to connect Attunity Replicate to Vertica. These steps create a source and target for Vertica data.
- Open a browser and enter the following to start Attunity Replicate:
https://<IPAddress or HostName>/attunityreplicate
- Click the Manage Endpoint Connection tab:
The Manage Endpoint Connection dialog box appears.
- Click the New Endpoint Connection button to create a source/target data source.
The following appears. Use this dialog to create a Vertica connection as Source.
- Enter Vertica_Src in the Name field, or a name that suits your organization's needs.
- Select the Source radio button from the Role field.
- Enter ODBC in the Type field.
- Select the DSN radio button. Click the Browse button to find your DSN.
- Click Test Connection.
- Upon a successful test, the following appears:
Use this dialog to create a Vertica connection as Target.
- Enter Vertica_TGT in the Name field, or enter a name that suits your organization.
- Select Target in the Role field.
- Enter HP Vertica in the Type field.
- Enter the following database credentials:
- Click Test Connection. Upon a successful connection you are now ready to create a replication tasks. See the next section.
Replicate Data from Vertica to Vertica
Follow these procedures to create a replication task:
- Click New Tasks from the left-hand side of the interface:
The New Task dialog appears
- Enter VerticatoVertica in the Name field, or use a name that best suits your organization's needs.
- Select the Unidirectional radio button from the Replicate Profile field.
- Select Full Load from the Task Options field.
- Click OK to create the new replication task VerticatoVertica.
- Open the VerticatoVertica task:
- Drag and drop your source and target (Vertica_Src and Vertica_TGT in our example) to their correct positions in the Task Designer Window:
- Click Table Selection to the right of the Task Designer Window:
The Select Tables/Views dialog appears:
- Select the tables for which you want to replicate data to the Target database and click OK.
- Click Run to execute the replication process:
- Click the Monitor button to check that the replication was successful. A list of completed tables appears at the bottom.
Before using Attunity Replicate with Vertica, be aware of the following known limitations:
- Max Values for Longvarchar are loaded up to 4096 characters.
- All test-cases for Long VarBinary failed, data was not loaded.
- The Timezone and millisecond values for TimeStamp, TimestampTZ and TimeTZ datatypes are truncated.