How to Troubleshoot ODBC Connection for HANA and HIVE

[Former Member]

Hi -

I was following SDA document for HIVE odbc configuration , I got the following error. If you are getting the following error, here is the troubleshoot method...

Error:

SIMBA:

The error message NoSQLGetPrivateProfileString could not be found in the en-US locale. Check that [INSTALLDIR]/simba/hiveodbc/ErrorMessages//en-US/ODBCMessages.xml exists.

[ISQL]ERROR: Could not SQLConnect

Cloudera:

[S1000][unixODBC][Cloudera][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.

[ISQL]ERROR: Could not SQLConnect

Run the following command and look for where the ini file read location and the edit the parameters.

strace -e trace=open isql HIVE 2>&1 | grep odbc.ini

Edit the simba.hiveodbc.ini or cloudera.hiveodbc.ini under [Installation Path]/<driver>/hiveodbc/lib/64/<Driver>

Thanks,

Prakash

[Former Member]

hi Parakash

Can u please guide me how to configure cloudera odbc for hive?

[Former Member]

Hi All!

I have the same issue

My configuration files are as follow

simba..hiveodbc.ini

DriverManagerEncoding=UTF-16

ErrorMessagesPath=/drivers/simba/hiveodbc/ErrorMessages/

LogLevel=0

LogPath=

SwapFilePath=/tmp

Driver=/drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

#   SimbaDM / unixODBC

ODBCInstLib=libodbcinst.so

.ODBC.INI

[HIVE]

  #Description=Hortonworks Hive
ODBC Driver(64-bit)DSN

Driver=/drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

  HOST=ip

  PORT=10000

  HiveServerType=2

HS2AuthMech=2

and face following error

[S1000][unixODBC][DSI] The error message NoSQLGetPrivateProfileString could not be found in the en-US locale. Check that [INSTALLDIR]/simba/hiveodbc/ErrorMessages//en-US/ODBCMessages.xml exists.

[ISQL]ERROR: Could not SQLConnect

[Former Member]

Hi Khalid,

Whats all drivers you have installed in HANA other than simba.

where is your HANA hosted?

[Former Member]

My hana is hosted on aws
and i have installed unixodbc drives and simba driver only

[Former Member]

basically I am using hana one instance on aws

[Former Member]

Hi Khalid,

Your configuration files look fine as far as I can tell, assuming you are using unixODBC driver manager.

You mentioned that you have the file simba..hiveodbc.ini, do you mean simba.hiveodbc.ini or .simba.hiveodbc.ini?

Do you know what version of the Simba Hive ODBC driver you are using? You can check to release notes that comes with the driver to find out. For recent releases the driver searches for the the simba.hiveodbc.ini file in the following order:

1. The SIMBAHIVEINI environment variable, if defined.

2. The directory containing the driver's library, in your environment this would be /drivers/simba/hiveodbc/lib/64.

3. The current working directory.

4. The hidden file named .simba.hiveodbc.ini (notice the dot in front) in the current user's home directory.

5. The file /etc/simba.hiveodbc.ini.

Hope this helps.

Cheers,

Holman

[Former Member]

Error was resolved by changing simba.hiveodbc.ini placed in /drivers/simba/hiveodbc/lib/64

[Former Member]

Hi Holman!

Now I am getting some different error as mentioned in this thread

https://scn.sap.com/thread/3734551

any suggestion ?

[Former Member]

Hi Khalid,

In that thread you are getting the error "The error message LicFileNotFound could not be found in the en-US locale". I hope the error message not found error is resolved now that you have configured the correct simba.hiveodbc.ini file.

Regarding the LicFileNotFound error, have you tried putting the license file in the driver's lib directory (the /simbadriver/simba/hiveodbc/lib/64 directory in your environment)? You can also try placing the license file in the current user's home directory.

Let me know if that helps.

Cheers,

Holman

[Former Member]

Hi Holman!

I have placed in the home directory of user

~/SimbaApacheHiveODBCDRiver.lic

But it did not work

one more thing.

Can we use some other odbc driver instead of Simba with hive hana?

If yes then please guide me in this regard

Thanks

[Former Member]

Hi Khalid,

Placing the license file to either the current user's home directory or the driver's lib directory seems to work fine for me. Are you still getting the same error (LicFileNotFound) from the driver, or are you seeing a different error?

Cheers,

Holman

[Former Member]

Hi Khalid,

There seems to be a typo in the license file name. It should be SimbaApacheHiveODBCDriver.lic with a lower case "r" in Driver. Could you try fixing the file name to see if it helps? Please make sure to still place the license file in the current user's home directly or the driver's lib directory.

Cheers,

Holman

[Former Member]

Thanks Holman it's working now

[Former Member]

Great to hear it's working now

Cheers,

Holman

[Former Member]

Hello Holman,

I am getting exactly same issue, also tried above mentioned fix. Still getting below error

ics4sb:/usr/sap/HSS/HDB00> isql -v hive1

[S1000][unixODBC][DSI] The error message NoSQLGetPrivateProfileString could not be found in the en-US locale. Check that /en-US/ODBCMessages.xml exists.

[ISQL]ERROR: Could not SQLConnect

ics4sb:/usr/sap/HSS/HDB00> cd /opt/simba/hiveodbc/lib/64

ics4sb:/opt/simba/hiveodbc/lib/64> ls

cacerts.pem            SimbaApacheHiveODBCDriver.lic   SimbaHiveODBC.did

libsimbahiveodbc64.so  SimbaApacheSparkODBCDriver.lic  simba.hiveodbc.ini

created separate thread -

Thank you,

Amit

[Former Member]

Hi Prakash,

Thanks for posting us the information

Recently iam also facing the same issue with connectivity from HANA -HADOOP

i have followed the HANA admin guide & SDA guide & installed the UNIX drivers(2.3.0)

• SIMBAdrivers
• Hortonworks driver

i was trying to fire isql -v
HIVE , HDP queries  but i get the same
error which u mentioned

  and usr/home contains
(.ODBC.ini ,simba.hiveodbc.ini. , hortonworks.hiveodbc.ini )

File contents

.ODBC.INI

[HIVE]

  #Description=Hortonworks Hive
ODBC Driver(64-bit)DSN

Driver=/_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

  HOST=ec2-54-153-2-16.us-west-1.compute.amazonaws.com

  HS2HostFQDN=ec2-54-153-2-16.us-west-1.compute.amazonaws.com

  PORT=10000

  Schema=default

  FastSQLPrepare=0

  UseNativeQuery=0

  HiveServerType=2

  HS2AuthMech=2

  UserName=hive

   .simba.hiveodbc.ini

DriverManagerEncoding=UTF-16

  ErrorMessagesPath=/_drivers/simba/hiveodbc/ErrorMessages/

  LogLevel=0

  LogPath=

  SwapFilePath=/tmp

  Driver=/_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

#   SimbaDM / unixODBC

  ODBCInstLib=/usr/local/lib/libodbcinst.so

i tried all the ways to set environmental variables, i made all  directories sid adm as owner still i face the same error

Error [Simba]

<HOSTNAME.>:~> isql -vHIVE

[S1000][unixODBC][DSI] The
error message NoSQLGetPrivateProfileString could not be found in the en-US
locale. Check that[INSTALLDIR]/simba/hiveodbc/ErrorMessages//en-US/ODBCMessages.xml exists.

[ISQL]ERROR: Could notSQLConnect

[Horton]

<HOSTNAME>~> isql -vHDP

  [S1000][unixODBC][Hortonworks][ODBC](11560) Unable to locate SQLGetPrivateProfileString function.

[ISQL]ERROR: Could notSQLConnect

PLEASE SUGGEST ME TO RESOLVE HE ISSUE

[Former Member]

Any suggestion ??

[Former Member]

Hello,

There are at least two issues here that might be originated from the same root cause, i.e. the simba.hiveodbc.ini or the hortonworks.hiveodbc.ini file is not configured correctly.

For your first connection using the Simba driver, the driver is not able to locate the file containing the error message string for NoSQLGetPrivateProfileString type of error. It is trying to look for the error messages files under [INSTALLDIR]/simba/hiveodbc/ErrorMessages. You may want to configure the ErrorMessagesPath setting in your simba.hiveodbc.ini file and replace [INSTALLDIR] with the actual install location of the Simba Hive ODBC driver.

For your second connection using the Hortonworks driver, the driver is able to locate the error messages file but is not able to find the driver manager library exporting the SQLGetPrivateProfileString function. For this type of error please ensure the driver manager's libraries are on the LD_LIBRARY_PATH and the ODBCInstLib configuration in the hortonworks.hiveodbc.ini file is set correctly.

The ODBCInstLib configuration in hortonworks.hiveodbc.ini (as well as simba.hiveodbc.ini) depends on the driver manager you are using. If you are using iODBC then please make sure you uncomment the ODBCInstLib=libiodbcinst.so (notice the "i" beore odbcinst) and comment out ODBCInstLib=libodbcinst.so (notice there is on "i" before odbcinst). If you are using unixODBC the please make sure you uncomment the ODBCInstLib=libodbcinst.so (without "i" before odbcinst) and comment out ODBCInstLib=libiodbcinst.so (with "i" beore odbcinst).

The DriverManagerEncoding configuration in simba.hiveodbc.ini and hortonworks.hiveodbc.ini also depends on the driver manager you are using. On Linux, if you are using iODBC please set DriverManagerEncoding=UTF-32, and if you are using unixODBC please set DriverManagerEncoding=UTF-16.

Hope this helps.

Cheers,

Holman

[Former Member]

Hi Prakash,

Can you please help me with the solution you found to resolve this issue. I am also facing the same issue.

[Former Member]

Hi Naresh-

Please check your ini file path by running the following command

strace -e trace=open isql  HIVE 2>&1 |grep odbc.ini

send me the out put and also send me the your ini file info..

Thanks,

Prakash

[Former Member]

Hi Prakash,

Thanks for your reply. Following is the output of the command

saphd8:/root/Desktop> strace -e trace=open isql  HIVE 2>&1 |grep odbc.ini

open("/usr/sap/HD8/home/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

open("/usr/sap/HD8/home/.odbc.ini", O_RDONLY) = 3

open("/usr/sap/HD8/home/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

open("/usr/sap/HD8/home/.odbc.ini", O_RDONLY) = 3

open("/usr/sap/HD8/home/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

open("/usr/sap/HD8/home/.odbc.ini", O_RDONLY) = 3

open("/usr/sap/HD8/home/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

open("/usr/sap/HD8/home/.odbc.ini", O_RDONLY) = 3

open("/ODBC_Drivers/simba/hiveodbc/lib/64//simba.hiveodbc.ini", O_RDONLY) = 3

open("/ODBC_Drivers/simba/hiveodbc/lib/64//simba.hiveodbc.ini", O_RDONLY) = 3

and following is the ini file info...

[ODBC Data Sources]

RDS9=ODBC Driver for SQL RDS9

HIVE=ODBC Drivers for Hadoop

[RDS9]

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0^M

Server=192.168.1.9,1433

[HIVE]

Driver=/ODBC_Drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

Host=192.168.1.13

Port=10000

HiveServerType=2

AuthMech=2

Please check and suggest me the solution.

[Former Member]

Hi Naresh-

You need to edit this file "/ODBC_Drivers/simba/hiveodbc/lib/64//simba.hiveodbc.ini" for some reason it is not reading from the <sidadm>/home directory.

Please uncomment the file from "/ODBC_Drivers/simba/hiveodbc/lib/64/simba.hiveodbc.ini" DriverManagerEncoding=UTF-16 ErrorMessagesPath=/opt/simba/hiveodbc/ErrorMessages/ LogLevel=0 LogPath= SwapFilePath=/tmp #  SimbaDM / unixODBC ODBCInstLib=libodbcinst.so

thanks,

prakash

[Former Member]

Hi Prakash,

I have edited the simba.hiveodbc.ini file as you said. Now i am getting the following error.

saphd8:/root> isql -v HIVE

[S1000][unixODBC][DSI] The error message HardyHiveError could not be found in the en-US locale. Check that /ODBC_Drviers/simba/hiveodbc/ErrorMessages//en-US/SimbaHiveODBCMessages.xml exists.

[ISQL]ERROR: Could not SQLConnect

Please help me to fix this issue.... .

[Former Member]

Hi Naresh-

Please place your SIMBA license file ...

Thanks,

Prakash

[Former Member]

Hi Prakash,

There seem be a typo in the ErrorMessagesPath setting in your simba.hiveodbc.ini file. It seems to be pointing at /ODBC_Drviers/simba/hiveodbc/ErrorMessages but should it be /ODBC_Drivers/simba/hiveodbc/ErrorMessages instead (you are missing an "i" in ODBC_Drviers)?

Based on the error message you are currently getting:

The error message HardyHiveError could not be found in the en-US locale. Check that /ODBC_Drviers/simba/hiveodbc/ErrorMessages//en-US/SimbaHiveODBCMessages.xml exists.

The driver is trying to display an error message of type HardyHiveError. The driver usually returns this type of error when there is an issue trying to establish a connection to Hive or Hive is returning error or an operation.

Hopefully after getting the ErrorMessagesPath setting in your simba.hiveodbc.ini file straighten out you will get more informative error message to show what the actual problem is.

Cheers,

Holman