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

Software Version
Partner Product

SAS Base 9.4

Desktop Platform

Windows Server 2019

Vertica Client

Vertica ODBC 11.0.2

Vertica Server Vertica Analytic Database 11.0.2

SAS Overview

This document describes how to access your Vertica database from SAS. SAS/ACCESS features require an installation of Base SAS, and the SAS/ACCESS interface for the database management system you are using.

Each SAS/ACCESS interface contains one or more data access engines that translate read and write requests from SAS into appropriate calls for Vertica. These interfaces allow SAS solutions to read, write, and update data stored in Vertica. SAS uses the ODBC client driver to connect to Vertica.

SAS/ACCESS provides the following methods for accessing relational database:

  • LIBNAME statement: Assigns SAS librefs to database objects such as schemas. After Vertica is associated with a libref, you can use a SAS two-level name to specify any table or view in the database and then work with the table or view as you would with a SAS data set.
  • Pass-Through Facility: Allows you to interact with a data source using its native SQL syntax without leaving your SAS session. The SQL statements are passed directly to the data source for processing.

You can invoke a SAS/ACCESS relational database interface using either a LIBNAME statement or a SQL statement.

Before You Begin

SAS includes the SAS/ACCESS Interface to Vertica and SAS/ACCESS Interface to ODBC. You can connect to Vertica using either of the two interfaces. Both the interfaces connect to Vertica using the ODBC Driver.

Note Ensure you have installed the ODBC Driver. See, Installing the Vertica Client Driver to install the ODBC driver.

To see if the interface is installed, run the following commands in SAS:

proc setinit noalias;
run;

The output of the preceding commands tell you if the required components are installed and lists the interface’s expiration date. Following is an example output:

---SAS/ACCESS to ODBC                   30JUN2022 (CPU A)                                                              
 ---SAS/ACCESS to Vertica               30JUN2022 (CPU A)                                                                      

If these entries are missing from the output, the interface is not installed.

Installing the Vertica Client Driver

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

  1. Go to the Vertica Client Drivers page.
  2. Download the JDBC driver that is compatible with the Vertica server version.

    Note For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Creating an ODBC Data Source Name (DSN) on Windows

Note You must create a DSN only if you are connecting to Vertica using the SAS/ACCESS Interface to ODBC.

You can set up either a 64-bit DSN or a 32-bit DSN, depending on your operating system and SAS version.

Follow these steps to create a DSN:

  1. Navigate to the appropriate folder, depending on which DSN you need to install:
    • 64-bit DSN: Start > Control Panel > Administrative Tools > Data Sources (ODBC)
    • 32-bit DSN: Navigate to C:\Windows\SysWOW64 and locate the program file called odbcad32.exe.

    The ODBC Data Source Administrator window opens.

    sas1.png
     

  2. In the System DSN tab, click Add.
  3. In the Create a New Data Source dialog box, select Vertica driver and click Finish.

    The Vertica ODBC DSN configuration dialog box appears.

  4. In the Basic Settings tab, fill in the following required fields:
    • DSN Name: The name for the DSN.
    • Server: The host name or the IP address of the Vertica server to which you want to connect.
    • Database: The name of the Vertica database.
    • User Name: The name of the user account to use when connecting to the database.
    • Password: The password associated with the user account.
  5. Click OK.

    The Vertica ODBC DSN configuration window closes and your new DSN is listed in the ODBC Data Source Administrator window.

  6. Click OK to close the ODBC Data Source Administrator window.

For more information, see Setting Up an ODBC DSN in the Vertica documentation.

Connecting SAS to Vertica

To connect SAS to Vertica, you need to first test the SAS/ACCESS Interface that you are using.

Testing the SAS/ACCESS Vertica Interface

In this example, V11 is the Vertica library name.

Enter the following information in SAS:

libname V11 vertica  server=<'IP-Address'> port=5433  db='vertica_db' schema='CC' user=dbadmin password=<Vertica_pwd>; 
run;

The log output should show a similar message:

libname V11 vertica  server=<'IP-Address'> port=5433  db='vertica_db' schema='CC' user=dbadmin password=<Vertica_pwd>;
NOTE: Libref V11 was successfully assigned as follows:
      Engine:        VERTICA
      Physical Name: <IP-Address>

Testing SAS/ACCESS ODBC Interface

In this example, V111 is the Vertica ODBC DSN name.

Enter the following information in SAS:

libname V111 odbc datasrc='verticadsn' user=dbadmin password=<Vertica_pwd>;
run;

The log output should show a similar message:

libname V111 odbc datasrc='verticadsn' user=dbadmin password=<Vertica_pwd>;
NOTE: Libref V111 was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: verticadsn

Sample Code

SAS interacts with Vertica using

  • SAS/ACCESS interface for Vertica
  • Or

  • SASACCESS interface for ODBC

This section demonstrates how you can use the LIBNAME statement and SQL Pass-Through queries on each of the interfaces.

SAS/ACCESS Interface for Vertica

The following sample code shows how to test the connection to Vertica using LIBNAME:

LIBNAME libref vertica <connection-options> <LIBNAME-options>; libname mydblib vertica server="mysrv1" port=5433
user=myusr1 password=mypwd1 database=mydb1; libname mydblib vertica dsn=mydsn1
user=myusr1 password=mypwd1;

The following sample code shows how to use CREATE, INSERT and SELECT statements after connecting to Vertica, using LIBNAME:

libname V11 vertica dsn='verticadsn' user=dbadmin password=<Vertica_pwd>;
proc sql;
CREATE table V11.paylist (IdNum char(4),
Gender char(1), Jobcode char(3), Salary num,
Birth num informat=date7. format=date7.,
Hired num informat=date7. format=date7.);
insert into V11.paylist
values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)
values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)
values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd) values('1561','M',null,36514,'30NOV63'd,'07OCT87'd) values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);
title 'V11.PAYLIST Table'; SELECT*	from V11.paylist; 
proc printto; 
run;

The following code shows deletion of records :

LIBNAME TEST123 VERTICA server=<'IP-Address'>  port=5433  db='vertica_db' schema='CC' user='dbadmin' password=<Vertica_pwd>;

proc sql;
delete * from TEST123.Bool_Table;
quit;

The following example shows how SAS/ACCESS interacts with Vertica using SQL Pass-Through. This example uses the DBCON alias to connect to the mydb1 Vertica database, execute several queries, and disconnect.

Note Using the connection alias is optional.

proc sql;
connect to vertica as dbcon
(server=<'IP-Address'> port=5433 user=dbadmin password=<Vertica_pwd> db=Vertica_db);
execute (create table if not exists scnpastbl (id int,	name char(13),
tel char(11), primary key(id, tel))) by dbcon;
execute (insert into scnpastbl values (1, '111', '1-1-1')) by dbcon;
SELECT* from connection to dbcon (select* from scnpastbl); disconnect from dbcon;
quit;

SAS/ACCESS Interface to ODBC

The following sample code shows how to use CREATE, INSERT, SELECT and DELETE statements after connecting to Vertica, using LIBNAME:

LIBNAME libref vertica <connection-options> <LIBNAME-options>; 

Libname proclib odbc datasrc='verticadsn' user=dbadmin password=<Vertica_pwd> procsql:
drop table proclib.paylist;
create table proclib.paylist (IdNum char(4),
Gender char(1), Jobcode char(3), Salary num,
Birth num informat=date7.
format=date7., Hired num informat=date7.
format=date7.); insert into proclib.paylist
values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)
values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)
values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd) values('1561','M',null,36514,'30NOV63'd,'07OCT87'd) values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);
title 'PROCLIB.PAYLIST Table'; SELECT*	from proclib.paylist; proc printto; 
run;

The following code depicts deletion of records :

LIBNAME TEST123 ODBC datasrc='verticadsn' user='dbadmin' password=<Vertica_pwd>;

proc sql;
delete * from TEST123.Bool_Table;
quit;

The following example shows how to execute SQL queries on Vertica use SQL Pass-Through:  

proc sql;
connect to odbc as dbcon
(data src=verticadsn user=dbadmin password=<Vertica_pwd>)
execute(create table if not exists scnpastbl (id int,	name char(13), tel char(11), primary key(id, tel))) by dbcon;
execute (insert into scnpastbl values (1, '111', '1-1-1')) by dbcon;
SELECT* from connection to dbcon (select* from scnpastbl); disconnect from dbcon;
quit;

The default value of InsertBuff is 1 for SAS/ACCESS interface for ODBC. For SAS/ACCESS engine to Vertica, the InsertBuff value is calculated based on row length. When you use SAS/ACCESS interface to ODBC, you must increase the InsertBuff value to the optimal value, depending on the resources used for better performance.

Known Limitations

  • CHAR, VARCHAR, and LONG VARCHAR data types are displayed up to 1024 characters.
  • INTEGER data types are displayed up to 15 digits beyond which an incorrect value is displayed.
  • For DECIMAL data type, up to 11 digits are displayed beyond which the value is rounded off.
  • BINARY, VARBINARY, LONG VARBINARY, and UUID data types are displayed incorrectly.
  • For DATE, TIMESTAMP, and TIMESTAMPTZ data types, the minimum date displayed is 01-01-1582.
  • For TIMETZ and TIMESTAMPTZ data types, timezone offset is not displayed.
  • For TIME and TIMETZ data types, milliseconds are not displayed.

For More Information