topic Re: Impala connection in Administration and Deployment

Impala connection

Riana — Mon, 07 May 2018 13:36:03 GMT

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?

Re: Impala connection

JuanS_OCS — Mon, 07 May 2018 13:49:06 GMT

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]

Re: Impala connection

Riana — Mon, 07 May 2018 14:23:06 GMT

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;

Re: Impala connection

JuanS_OCS — Mon, 07 May 2018 15:28:45 GMT

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.