|Share this article:|
Vertica Integration with Alteryx Designer: 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 Alteryx Designer: Latest Versions Tested
Alteryx Designer 2020.1.2.24185
Windows Server 2016 Standard
Vertica ODBC 9.3.1-0
|Vertica Server||Vertica Database 9.3.1-0|
Alteryx Designer Overview
Alteryx Designer prepares, blends, and analyzes data using repeatable workflows. Alteryx Designer is available for Windows as a 64-bit application and uses ODBC to connect to your Vertica database.
Installing Alteryx Designer
To install a trial version of Alteryx Designer:
- On the Alteryx website, click FREE TRIAL.
- Click DOWNLOAD in DOWNLOAD FULL VERSION.
- Provide the registration information and click TRY NOW.
- Follow the installation instructions in the Alteryx documentation.
Installing the Vertica Client Driver
Before you can connect Alteryx Designer to Vertica, you must install the Vertica ODBC client driver. Follow these steps:
- Navigate to the Client Drivers page on the Vertica website.
Download the client driver that is compatible with the Vertica server version.
For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC only.
Create a 64-bit ODBC DSN as described in the Vertica documentation.
Connecting Alteryx Designer to Vertica
To launch Alteryx Designer, double-click
AlteryxGui.exein the installation directory.
Select Options > Advanced Options > Manage Data Connections.
In the Manage Data Connections window, select Add Connection > Other.
In the Other Database Connection window:
- Name: Type a name for your database connection.
- Connection Type: Select System or User, depending on whether the DSN you created to connect to Vertica is a System DSN or a User DSN.
Connection String: Select ODBC.
The ODBC Connection window opens.
Select the Data Source Name and provide the login credentials, and then click OK.
When the Connection String appears on the Other Database Connection window, click Save.
The connection will be added to the list of active connections.
Moving Data From and Into Vertica: Basic Example
The following example shows how to transfer data from and into Vertica. Both the source table and the target table reside in a Vertica database. Two connections are created in this example: Src and Tgt.
Drag Input Data from the toolbar and drop it onto the workflow.
Click the Connect a File or Database drop-down arrow.
The Data connections dialog box appears. Click Saved and then click Src (System) database connection.
Select a table in the Vertica database from which you want to retrieve data and click OK.
Drag Output Data from the toolbar and drop it onto the workflow.
Click the Write to File or Database drop-down arrow.
The Data connections dialog box appears. Click Saved and then click Tgt (System) database connection.
In the Output Table window, type the name of the output table.
To connect the input and output tables, place your cursor on the green dot on the Input icon, click and drag it to the green dot on the Output icon.
Click the Run icon in the top-right corner of the workflow.
For TIMETZ and TIMESTAMPTZ data types, milliseconds and time zone values are truncated.
For TIME and TIMESTAMP data types, milliseconds are truncated.
NUMERIC data type supports up to 132 digits of precision.
CHAR and VARCHAR data types support displaying up to the first 256 characters.
- LONG VARBINARY supports writing up to 2 million bytes beyond which the following error may be displayed:
DataWrap2ODBC::SendBatch: ERROR 2052: COPY: Row size 32000028 is too large¶ Insert into <my-schema>.<my-target-table>(<col1>,<col2>,<col3>) Values (?,?,?)
LONGVARCHAR data type supports approximately 8 million characters beyond which the following error is displayed:
The Designer x64 reported: InboundNamedPipe GetOverlappedResult: The pipe has been ended..