Share this article:

Vertica and SAS: Connection Guide

Click here for a PDF version of this document.  

Applies to Vertica 7.2.x and earlier 

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 specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, the other versions may not have been tested. This document provides guidance using the latest versions of Vertica and SAS as of December, 2015.

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 data:

  • 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 PROC SQL statement.

This document is based on testing done using Vertica 7.2.x with SAS Base 9.4, and assumes familiarity with both SAS and Vertica.

Before You Begin

Before you can connect SAS to Vertica, you must make sure the SAS/ACCESS connector for Vertica and ODBC is installed.

To see if the connector 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 connector’s expiration date. An example output follows:

---SAS/ACCESS to ODBC                   11DEC2015(CPUA)                                                              
 ---SAS/ACCESS to Vertica               11 DEC2015 (CPU A)                                                                      

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

Download and Install Vertica Client Drivers

Before you can connect to Vertica using SAS, you must download and install the Vertica client package. This package includes the ODBC driver that SAS uses to connect to Vertica.

Download Vertica Client Driver

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version. For a 32-bit operating system, download the 32-bit version and for a 64-bit operating system, download the 64-bit version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Driver

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Create an ODBC Data Source Name (DSN) on Windows

You must create a DSN to use the ODBC driver to connect to Vertica from SAS. 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.

sas1.png
 

  1. The ODBC Data Source Administrator displays. In the System DSN tab, click Add.
  2. In the Create a New Data Source dialog box, select Vertica driver and click Finish. The Vertica ODBC DSN configuration dialog box appears.
  3. 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.

 sas2.png

  1. Click OK. The Vertica ODBC DSN configuration window closes and your new DSN is listed in the ODBC Data Source Administrator window.
  2. Click OK to close the ODBC Data Source Administrator window.

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

Create a Data Source Name (DSN) on Linux

You must download and install the Vertica Linux drivers on the SAS box. SAS ships the Data Direct Driver Manager that is used to connect to Vertica, as shown in the following:

/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/libodbcinst.so.2

The odbc.ini file is located under SASHOME at the following location:

/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/

The following shows the contents of the odbc.ini file and shows that the PartnerDB DSN is configured:

[root@intcent65-71-72]# cat /data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/odbc.ini
[ODBC Data Sources]
PartnerDB=vertica

[Driver}
ODBCInstLib=/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/libodbcinst.so.2
ErrorMessagesPath=/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/vertica/opt/vertica/lib64/
LogPath=/tmp
LogLevel=6
DriverManagerEncoding=UTF-8

[postgres]
Driver=<path>/psqlodbc/lib/spqlodbcw.so
ServerName=<localhost or hostname or ip>
username=<user name>
password=<password>
database=<database>
port=5432

[PartnerDB]
Driver=/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/libverticaodbc.so
Host=localhost
Server=
ServerName=
Database=common_ecodb
UserName=dbadmin
Password=
UID=dbadmin
Port=5433

You must set up the environment variables as follows using the export command:

export VERTICAINI=/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/odbc.ini
export LD_LIBRARY_PATH=/data/SAS9.4Home/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver:$LD_LIBRARY_PATh
export ODBCINI=/data/SAS9.4Home/SASODBCDriverfortheWebInfrastructurePlatformDataServer/9.4/Driver/odbc.ini
export Path=$Path:/data/SAS9.4Home/SASFoundation/9.4


Test to verify that dependent libraries in your Linux OS library are present on your system using the ldd command.

sas3.png

ldd /data/SAS9.4Home/SASFoundation/9.4/sasexe/sasodb:

 

ldd /data/SAS9.4Home/SASFoundation/9.4/sasexe/sasvrt:

Test the Connectors

Test the SAS/ACCESS Vertica Connector

Enter the following information into SAS:

libname dt_ver vertica DSN='PartnerDB'user=dbadmin password=password;
 run;

The log output should show a message similar to the following:

libname dt_ver vertica  DSN='PartnerDB'  user=dbadmin password=XXXXXXXX;
NOTE: Libref DT_VER was successfully assigned as follows:
Engine:        VERTICA
Physical Name: PartnerDB

Test SAS/ACCESS ODBC Connector

Enter the following information into SAS:

libname dt_odbc odbc datasrc='PartnerDB'user=dbadmin password=password;
run;

The log output should show a message similar to the following:

libname dt_odbc odbc  datasrc='PartnerDB'  user=dbadmin password=XXXXXXXX;
NOTE: Libref DT_VER was successfully assigned as follows:
Engine:        ODBC
Physical Name: PartnerDB

Sample Code

SAS interacts with Vertica using:

  • SAS/ACCESS interface for ODBC
  • SAS/ACCESS interface for Vertica

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 connectivity with 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 proclib vertica  dsn='testdb'  user=dbadmin password=password;
proc sql;
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 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='10.10.7.174' port=5433 user=dbadmin password=password db=testdb);
 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 and SELECT statements after connecting to Vertica, using LIBNAME:

LIBNAME libref vertica <connection-options> <LIBNAME-options>;
Libname proclib odbc datasrc='testdb' user=dbadmin password=password
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 example shows how to execute SQL queries on Vertica use SQL Pass-Through:  

proc sql;
      connect to odbc as dbcon
      (data src=testdb user=dbadmin password=password)
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.

For More Information

For More Information About… …See

SAS

http://www.sas.com

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: