topic Re: SAS-Vertica Integration through ODBC Connection in Administration and Deployment

SAS-Vertica Integration through ODBC Connection

k_laiz — Mon, 21 May 2012 09:09:50 GMT

Hi Everyone,

A newbie here.

May i ask for your help on this. The setup is as follows.

Client Application (SAS) <-> ODBC Driver Manager <-> ODBC Driver <-> Datasource (Vertica)

Server A (Windows Platform)

1. Client Application

Server B (Unix Platform)

1. ODBC Driver Manager (unixODBC)

2. ODBC Driver (installed with Vertica DB)

3. DSN (Vertica)

Notes:

1. I have a working SAS base foundation 9.2 working at my local machine with working SAS/CONNECT for ODBC interface.

2. Vertica DB is setup at the unix platform end together with the driver.

3. unixODBC - still to be configured.

4. Plan to use LIBNAME statement and/or SQL Pass-through facility to access Vertica DB in SAS app.

Sample ODBC configuration files that would be configured in unix env:

ODBC.INI

Location: /opt/etc

[VerticaDSN]

Description = VerticaDSN ODBC driver

Driver = /opt/vertica/lib/libverticaodbc_unixodbc.so

Database = <databasename>

Servername = localhost

UserName = dbadmin

Password =

Port = 5433

ConnSettings =

SSLKeyFile = /home/dbadmin/client.key

SSLCertFile = /home/dbadmin/client.crt

Locale = en_US

ODBCINST.INI

Location: /opt/etc

[VerticaDSN]

Description = ClickStream example database

Driver = /opt/vertica/lib/libverticaodbc_unixodbc.so

[ODBC]

Threading = 1

Questions/Concerns:

1. With the assumption that the unix platform end is configured successfully, i.e. Vertica DB data is accessible through ODBC but locally, is there a way that SAS (in Server A) can discover the ODBC data source in Server B without any configuring needed on Server A side? Though i haven't tried the direct approach, say below, since i haven't configured unixODBC just yet.

libname vertica odbc dsn=verticaDSN user=dbadmin Password=;

2. Would there be any other setup in case the above configuration is not feasible?

Any help is appreciated. Thank you!

Re: SAS-Vertica Integration through ODBC Connection

LinusH — Mon, 21 May 2012 10:47:54 GMT

My belief is that you need to specify, or configure, a data source entry in the local host ODBC manager. I'm not aware if there are any other setups available.

I think that OLEDB might be more flexible...?

Re: SAS-Vertica Integration through ODBC Connection

k_laiz — Mon, 21 May 2012 13:29:28 GMT

Hi LinusH,

Thank you for your reply.

Yes, I should agree with you with the configuration on the local host ODBC manager (unixODBC for unix platform). However, the question that still lingers in my mind is that how will the SAS application from windows platform be able to 'talk' or 'find' the ODBC configured setup in the unix platform.

On the OLE DB, i'm not familiar as well on the configuration. As i've checked the SAS Help Facility, I saw from the featured host that unix is not supported by such configuration.

Re: SAS-Vertica Integration through ODBC Connection

LinusH — Mon, 21 May 2012 13:40:20 GMT

About finding the UNIX host in ODBC - that's the role for the data source definition (in the local Windows host). So SAS itself has no idea where data is coming from.

About support on different platforms, the SAS/ACCESS support is usually based on what platform the client application is running, not where the external database is executing. You might want to talk to a SAS representative about different set-up possibilities.

Re: SAS-Vertica Integration through ODBC Connection

k_laiz — Mon, 21 May 2012 13:48:08 GMT

For your first comment, would it mean that the setup can be ODBC(windows) to ODBC(unix) connection, somewhat like that? Or the setup should be Server A includes the SAS app, ODBC Manager and the driver?

For the second one, that somehow clarifies a bit about the SAS/ACCESS. I'll try to contact one as you recommended.

Re: SAS-Vertica Integration through ODBC Connection

LinusH — Mon, 21 May 2012 13:57:36 GMT

Well, you obviously need the driver for your database server.

Then you need to define a data source in the ODBC Manager.

And then you define a libref in SAS.

All this takes place on server A, and assuming that the necessary preparations already have been done on Server B.

An overview:

http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#n1ht6mv0wybbzkn1x4np6nnlwzu6.htm

Re: SAS-Vertica Integration through ODBC Connection

k_laiz — Mon, 21 May 2012 14:13:07 GMT

Thanks Linush.. Will look for the Vertica ODBC driver for windows and try that kind of setup.

Re: SAS-Vertica Integration through ODBC Connection

k_laiz — Tue, 22 May 2012 00:31:42 GMT

Done with the setup and the connection works perfectly when assigned a LIBNAME statement.

However, I wasn't able to view the tables using the explorer in SAS 9.2. I used the libname statement:

libname vertica odbc dsn=VerticaDSN user=dbadmin;

and the note was:

1 libname vertica odbc dsn=VerticaDSN user=dbadmin;

NOTE: Libref VERTICA was successfully assigned as follows:

Engine: ODBC

Physical Name: VerticaDSN

Using SQL Pass-through facility, I was able to query the contents with this code:

proc sql;

connect to odbc (dsn=VerticaDSN=dbadmin);

create table test as select * from connection to odbc(ODBC::SQLTables);

disconnect from odbc;

quit ;

Would you have an idea on how I could make the tables visible in the Explorer? I tried adding SCHEMA= option, but it didn't work.

Thanks again!

Re: SAS-Vertica Integration through ODBC Connection

LinusH — Tue, 22 May 2012 06:46:54 GMT

Maybe this usage note applies to your situation?

http://support.sas.com/kb/2/479.html

Re: SAS-Vertica Integration through ODBC Connection

k_laiz — Wed, 23 May 2012 08:17:07 GMT

I didn't resolve my issue.

However, using SQL Pass through facility, I was able to retrieve the tables from Vertica DB.

Maybe this would be enough for the current requriement.

Thanks for all your advise/help!

Re: SAS-Vertica Integration through ODBC Connection

shivas — Thu, 24 May 2012 08:55:23 GMT

Hi,

I faced the similar issue with teradata database.I was able to connect to database successfully but unable to see the tables in SAS editor.

So I have changed the user name and pwd to admin user of teradata(DBC) and checked then it worked perfectly(I was able to see the tables)..this is because of the role and permissions issue with the user which I was using earlier.

So check with administrator user....

Thanks,

Shiva