SAS Access to Vertica

[BobD]

odDoes anyone have any advice or infomation about a new product in SAS 9.4 called "SAS/Access to Vertica"?  In particular, what are the advantages to using this new product when compared to plain old "SAS/Access to ODBC"?  I'm sure there must be some differences but I cannot find anything in the new documentation.  The :'system requirements" say that a vendor-supplied ODBC driver is required for this product.

We have a new requirement to begin accesses a "Vertica" database and need to understand which SAS/Access product to license.  Since there are other databases out there that we may need to access in the future using ODBC, I'm inclined to only license the Access to ODBC product.  But if there is something special about this SAS product, I'm eager to understand.

Thanks in advance,

Bob

[JBailey · Accepted solution]

Hi Bob,

I am the SAS Product Manager for the SAS/ACCESS family of products.

Here are the primary differences between SAS/ACCESS Interface to ODBC and SAS/ACCESS Interface to Vertica. The SAS/ACCESS interface to Vertica supports:

Extended Data Type Integration – The SAS/ACCESS Interface to ODBC does not support Vertica specific data types.

Extended Vertica Function Support – The SAS/ACCESS Interface to Vertica will pass down more functions to the Vertica server. The result is that more SQL will be seamlessly passed to the Vertica server. This may result in better query performance.

INSERT/UPDATE/DELETE statements – Our ACCESS developers went to great lengths to add support for INSERT, UPDATE, and DELETE statements. The Vertica ODBC driver does not support updateable cursors. Therefore, SAS/ACCESS to ODBC does not support UPDATE/DELETE when used with the Vertica ODBC driver.

I18N – The Vertica ODBC driver will support I18N. Unfortunately, the ACCESS to ODBC interface cannot make use of this property. If you need I18N support, then you need ACCESS to Vertica.

Best wishes,
Jeff

Slight correction - the Vertica ODBC driver supports INSERT statements. Message was edited by: Jeff Bailey

[BobD]

Perfect response, exactly what I needed to know!

I'm especially grateful for the comment that the Vertica ODBC driver does not support INSERT, UPDATE, and DELETE.  That will probably explain away a Tech Support ticket I have regarding an error I'm getting trying to create a new table in Vertica from SAS.  I had no idea that that was not possible.

Thanks very much!

[JBailey]

My pleasure!

[mnietz]

Hi,

I have a question somehow related to the above discussion - we are talking to a potential customer which is interested in SAS working with Vertica. He is currently runing SAS 9.2 (with no plans to upgrate it anyhow soon), and is curious if SAS Base 9.2 would work with SAS/ACCESS(R) 9.4 for Relational Databases (for Vertica) . So the main question is if SAS 9.2 would work with SAS/Access 9.4 for Vertica?

I'm getting 2 opposite opinions on that from SAS partners - Can you have an official statement clarifying this matter?

Best Regards,

Mikolaj

[LinusH]

I'm not a product manager, but I never heard of that SAS supports a mixture of versions in the same SAS installation, would be very surprised if would work at all. But, there are good chances that a 9.2 client could talk to a 9,4 server (e.g. using SAS/CONNECT) which in turn have the desired SAS/ACCESS product licensed and installed.

[JBailey]

Hi,

Your intuition is absolutely correct. Mixing different versions of SAS/ACCESS engines in a SAS install is not a good idea.

Best wishes,

Jeff

[JBailey]

Hi Mikolaj,

I am the product manager for the SAS/ACCESS family of products.

SAS/ACCESS Interface to Vertica is only available with SAS 9.4. With SAS 9.2 you can use the SAS/ACCESS Interface to ODBC.

Best wishes,

Jeff

[mnietz]

Hi Jeff,

Thanks for the clear statement on that. I have yet another question - pretty soon HP is releasing Vertica 7 - would the SAS/Access 9.4 for Vertica work with Vertica 7?

Regards,

Mikolaj

[RohitV]

Hello Jeff,

I am trying to connect to Vertica thru SAS, but getting below error, do you know what can be the reason for this?

ERROR: Could not load /XXXXX/sas94/SASFoundation/9.4/sasexe/sasvrt (100 images loaded)
ERROR: libverticaodbc.so: cannot open shared object file: No such file or directory
24         libname VZPRD vertica user="XXXXXXX" password=XXXXXXXXXXXXXXXX server="XXXXXXXXX" database="XXXXXX"
24       !  port=5XXX;
ERROR: The SAS/ACCESS Interface to VERTICA cannot be loaded. The SASVRT   code appendage could not be loaded.
ERROR: Error in the LIBNAME statement.

[mklybor]

Where can I find documentation about installing/conffiguring SAS/Access 9.4 for Vertica? In the general 9.4 install manual, SAS/Access section page 58, it only talks about Vertica ODBC. Could you direct me to documentation for the native vertica SAS/Access? I have a new 9.4 installation that we are just starting to use. SAS/Access for Vertica was supposed to be included and I an trying to validate that we have everything installed. Attempts to access Vertica content give me an error the looks suspiciously like it was trying to use ODBC, which is not set up.

[DaveR_SAS]

Mklbor, here is the chapter for SAS/ACCESS to Vertica: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition

[mklybor]

thanks, but I'm looking for installation / configuration documentation, not usage. in the SAS 9.4 install manual, the section on SAS/Access for Vertica talks about installing ODBC drivers and the like. As I understand it, that is not what we have. We should have  "direct" access to Vertica, not ODBC (as was the case for 9.3 and earlier).

specifically, pg. 58 in Configuration Guide for SAS 9.4 Foundation for UNIX Environments says that for Vertica access, an ODBC driver manager etc. must be installed. It was my understanding that ODBC was no longer necessary since we now have native Vertrica SAS/Access. could you clarify whether Vertica access is still ODBC for 9.4? thanks.

[JBailey]

SAS/ACCESS Interface to Vertica is ODBC based.

[mklybor]

in my 9.4 config manual, the vertica config steps consist of setting some shell variables to point to the .ini files. where should these be set? in the unix logon scripts for each of my users? in some SAS startup script? do any servers need to be restarted after setting these up? we have all of the odbc pieces installed and configured. thanks.