Impala connection

[Riana]

Hi,

JDBC driver has been installed for Impala(on Unix SAS server) and when we try to connect in SAS EG via a libname getting this:

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Can't open lib 
       '/opt/sas94/jdbc/impala/ClouderaImpalaJDBC_2.5.43.1063/ClouderaImpalaJDBC4_2.5.43/ImpalaJDBC4.jar' : file not found
ERROR: Error in the LIBNAME statement.

The ImpalaJDBC4.jar file is under the folder(above path) with all permission. 

Why we are getting this? 

[JuanS_OCS]

Hello @Riana,

there is a SAS usage note posted 2 years ago, describing your problem and providing the workaround to use a different libname statement.

Usage Note 57439: Cannot open lib 'Cloudera ODBC Driver for Impala 64-bit' errors when using SAS/ACCESS® Interface to Impala http://support.sas.com/kb/57/439.html 

Additionally, the lib variable should not show a jar file, but a tsn variable, as defined in the odbcinst.ini file. perhaps you would like to change that. Follow the line: odbcinst.ini --> ocbdc.ini --> the SAS config file where your Impala environment variables are defined/exported  

http://doc.mapr.com/display/MapR/Configure+the+MapR+Impala+ODBC+Driver+for+Linux+and+Mac+OS+X

When you use SAS/ACCESS Interface to Impala, the following error might occur:

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Can't open lib 'Cloudera ODBC Driver for Impala 64-bit' 
: file not found

The error occurs when you use code similar to this:

libname mylib sasioimp database=database-name  SERVER='server-name'  PORT=port-number; 

If you use the same connection with a data source name such as the one shown below, the connection works correctly.

libname mylib sasioimp dsn="Sample Cloudera Impala DSN 64";

The problem occurs when the odbcinst.ini file contains incorrect header information. The Impala driver requires that the odbcinst.ini file contain the following header:

[Cloudera ODBC Driver for Impala 64-bit]

[Riana]

Thank you for your reply.

could you explain more this part:

Additionally, the lib variable should not show a jar file, but a tsn variable, as defined in the odbcinst.ini file. perhaps you would like to change that. Follow the line: odbcinst.ini --> ocbdc.ini --> the SAS config file where your Impala environment variables are defined/exported 

I'm a little confused by this part of your answer.

where is ocbdc.ini? 

what you mean by: the lib variable should not show a jar file, but a tsn variable, as defined in the odbcinst.ini file

in odbcinst.ini there we have:

[ODBC Drivers]
Cloudera ODBC Driver for Impala 32-bit=Installed
Cloudera ODBC Driver for Impala 64-bit=Installed

[Cloudera ODBC Driver for Impala 32-bit]
Description=Cloudera ODBC Driver for Impala (32-bit)
Driver=/opt/cloudera/impalaodbc/lib/32/libclouderaimpalaodbc32.so

[Cloudera ODBC Driver for Impala 64-bit]
Description=Cloudera ODBC Driver for Impala (64-bit)
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

## The option below is for using unixODBC when compiled with -DSQL_WCHART_CONVERT.
## Execute 'odbc_config --cflags' to determine if you need to uncomment it.
# IconvEncoding=UCS-4LE

there is no tsn variable. 

We have defined SAS variable in sasenv_local:

export ODBCSYSINI=/opt/cloudera/impalaodbc/Setup
export CLOUDERAIMPALAINI=/opt/cloudera/impalaodbc/lib/64/cloudera.impalaodbc.ini
 

 I'm running this libname:

Libname imp impala dsn=Impalaj db=default;

[JuanS_OCS]

Hi there @Riana,

alright, so what I can see here is the following:

• Your libname to a dsn ImpalaJ I cannot see it defined anywhere else.
• The odbc.ini, it should be most probably in the same location as the odbcinst.ini, since one is related to the other. The odbc,ini file will actually have the configuration to your database: host, port, schema, and other details. You will also need to export the variable for ODBC and ODBCINST

In general, i think your first objective would be to focus on testing the Impala connection OUTSIDE from SAS, from the Linux command line. This should help you, hopefully:

https://www.cloudera.com/documentation/enterprise/5-11-x/topics/impala_odbc.html

http://doc.mapr.com/display/MapR/Configure+the+MapR+Impala+ODBC+Driver+for+Linux+and+Mac+OS+X

Once you can validate connections from Linux to Impala databases and query them, you can move forward to the next step: to query them from SAS.