SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS Access to Vertica

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

SAS Access to Vertica

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


Accepted Solutions
Solution
‎07-10-2013 02:55 PM
SAS Employee
Posts: 203

Re: SAS Access to Vertica

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

View solution in original post


All Replies
Solution
‎07-10-2013 02:55 PM
SAS Employee
Posts: 203

Re: SAS Access to Vertica

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

Contributor
Posts: 43

Re: SAS Access to Vertica

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!

SAS Employee
Posts: 203

Re: SAS Access to Vertica

My pleasure!

New Contributor
Posts: 2

Re: SAS Access to Vertica

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

Super User
Posts: 5,256

Re: SAS Access to Vertica

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.

Data never sleeps
SAS Employee
Posts: 203

Re: SAS Access to Vertica

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

SAS Employee
Posts: 203

Re: SAS Access to Vertica

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

New Contributor
Posts: 2

Re: SAS Access to Vertica

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

New Contributor
Posts: 4

Re: SAS Access to Vertica

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.

Occasional Contributor
Posts: 11

Re: SAS Access to Vertica

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.

SAS Employee
Posts: 75

Re: SAS Access to Vertica

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

Occasional Contributor
Posts: 11

Re: SAS Access to Vertica

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.

SAS Employee
Posts: 203

Re: SAS Access to Vertica

SAS/ACCESS Interface to Vertica is ODBC based.

Occasional Contributor
Posts: 11

Re: SAS Access to Vertica

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 23 replies
  • 3133 views
  • 1 like
  • 8 in conversation