|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 2018.3.4.51585
Windows Server 2012 R2
Vertica ODBC 9.1.1-0
|Vertica Server||Vertica Database 9.1.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 connects to your Vertica database.
For details about Alteryx, visit the Alteryx website.
Install Alteryx Designer
To install a trial version of Alteryx Designer:
- On the Alteryx website, click Free Trial.
- Provide the registration information and click Try Now.
- Follow the installation instructions in the Alteryx documentation.
Install 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 architecture of your operating system and 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.
Connect Alteryx Designer to Vertica
Launch Alteryx Designer by double-clicking
AlteryxGui.exein the installation directory.
Select Options > Advanced Options > Manage Data Connections.
On the Manage Data Connections page, select Add Connection > Other.
On the Other Database Connection page:
- In the Name box, type a name for your database connection.
- In the Connection Type drop-down list, select System or User, depending on whether the DSN you created to connect to Vertica is a System DSN or a User DSN.
In the Connection String drop-down list, select ODBC.
The ODBC Connection page opens.
Select the Data Source Name and provide the login credentials, then click OK.
When the Connection String appears on the Other Database Connection page, 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. We have created two connections: Src and Tgt.
Drag Input Data from the toolbar and drop it onto the workflow.
From the Connect a File or Database drop-down list, select Saved Data Connections > My Computer > Src (System).
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 Output Data icon in the workflow and select Saved Data Connections > My Computer > Tgt (System) to select the Target System DSN for the output data.
On the Output Table page, 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, hold down your left mouse button and drag it to the green dot on the Output icon.
Click the Run icon in the toolbar to run the workflow.
Date values earlier than 1400-01-01 are not supported in Alteryx. The earliest date value in Vertica is 0001-01-01.
Alteryx truncates the milliseconds and time zone offset components of TimeTz and TimestampTz values when reading from and writing into Vertica.
As a workaround:
To read the full value from Vertica, you can use the CHAR() function to cast the value in the SQL statement of the Input Data Component. For example:
SELECT TO_CHAR(<my-timetz-column>) FROM <my-timetz-table>;
- To write the full value into Vertica, make sure that the value is in a string format before loading it into the target column in Vertica.
Alteryx reads and displays Vertica's Long Varbinary data type values correctly, however it does not support writing these values into Vertica. Alteryx may raise the following error message when attempting to write Long Varbinary data into a Vertica target table:
DataWrap2ODBC::SendBatch: ERROR 2052: COPY: Row size 32000028 is too large¶ Insert into <my-schema>.<my-target-table>(<col1>,<col2>,<col3>) Values (?,?,?)
Alteryx supports a maximum precision of 131 digits for numeric values when both reading from and writing to Vertica.
Alteryx displays only the first 256 characters for values of Char, Varchar and LongVarchar datatypes. Alteryx does not truncate these values when reading from and writing into Vertica.