Vertica Integration with IBM SPSS Statistics: 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 IBM SPSS Statistics: Latest Versions Tested

This document is based on our testing using the following versions:

Software	Version
Partner Product	IBM SPSS Statistics 28
Desktop Platform	Windows Server 2016 Standard
Vertica Client	Vertica ODBC 11.0.0-0
Vertica Server	Vertica Analytic Database 11.0.0-0

IBM SPSS Statistics Overview

IBM SPSS Statistics is a robust statistical tool to develop insights from your data. It also provides computational and machine learning capabilities. It has a user-friendly interface that allows you to quickly compute large and complex data sets. Its advanced statistical procedures ensure high accuracy and help you make more informed decisions.

Installing IBM SPSS Statistics

To download and install IBM SPSS Statistics, refer to Downloading IBM SPSS Statistics 28.0.

Installing the Vertica Client Driver

IBM SPSS Statistics uses the Vertica ODBC driver to connect to your Vertica database. To download and install the Vertica client package:

Navigate to the Vertica Client Drivers page.
Download the ODBC Vertica client package that is compatible with the Vertica server version.

Note 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.
Follow the installation instructions in Installing the Client Drivers and Tools on Windows.
Create an ODBC Data Source Name (DSN) as described in Creating an ODBC Data Source Name (DSN).

Connecting IBM SPSS Statistics to Vertica

Open IBM SPSS Statistics.
Click Login with IBM ID. Use the IBM ID you created while installing the tool.
Type the email address you registered with and click Continue.
Type the password and click Log in.
Select New Dataset and click Open.
Click File > Import Data > Database > New Query to make a connection to the Vertica database.
Select the ODBC Data Source you created when creating the DSN and select the required table types. Click Next.
Select the table from the list of Available Tables and click the arrow to drop the selected table columns to the Retrieve Fields list. Click Next.
To limit the number of records fetched from the database table, you can use the different options provided in the Criteria list based on the columns in the Fields list. Click Next.
The column data types are displayed. In the Result Variable Name list, you can double-click the column to change its name that will be displayed in the result set. Click Next.
The Database Wizard displays the query that will be executed to bring in the required data. You can edit the query based on your requirements. Select Retrieve the data I have selected and click Finish.
The result set is displayed in the IBM SPSS Statistics.

Troubleshooting

Review the following known issue and workaround when connecting IBM SPSS Statistics to Vertica:

String data truncation during data display

Issue: IBM SPSS Statistics displays the following string data truncation error when the data size exceeds the default limit (255 bytes equivalent to 128 characters).

[Vertica][ODBC] (10160) String data right truncation: String data is too big for the output data buffer and has been truncated.

Solution: To resolve this error, import the database table again and change the value of Width for variable-width string fields from 255 to any value less than or equal to 32767 which is the highest byte size supported by the tool.

Known Limitations

For CHAR, VARCHAR, and LONG VARCHAR data types, values up to 16383 characters are supported.
For INTEGER and DOUBLE data types, values up to 15 digits are supported beyond which the value is rounded off.
For DECIMAL data type, values up to 15 digits are supported beyond which the value is rounded off.
INTERVALSECOND, INTERVALMONTH, BINARY, VARBINARY, LONG VARBINARY, and UUID data types are not supported.
For DATE data type, the minimum supported date is 01-01-1800.
For TIME and TIMESTAMP data types, milliseconds are not supported.
For TIMETZ and TIMESTAMP TZ data types, milliseconds and timezone offset is not supported.