"Unable to locate SQLGetPrivateProfileString function" when connecting
Closed this issue · 14 comments
I know this is a long shot and difficult to reproduce, but still I want to point it out, because it might be solved.
When connecting to Impala I get the following error message and no connection is made:
Error: nanodbc/nanodbc.cpp:950: HY000: [unixODBC][Simba][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.
Now strangely enough, when I connect to a different data source first (sqlite) in the R session I have no problem connecting to Impala afterwords. The following works:
library(implyr); library(odbc)
dbConnect(odbc::odbc(), dsn = "SQLite")
impala <- src_impala(
odbc::odbc(),
dsn = "Impala")
But it would not work if the second part does not run. I am in the dark why this is, maybe an environment variable is set correctly by connecting to sqlite first. Lately a colleague had the exact same error on a fresh system, and the same fixed work.
Again, I know it is long shot but maybe @ianmcook or @jimhester can think of a direction of resolving this.
I am on macOS 10.12.6.
My /etc/odbcinst.ini file looks like
[SQLite Driver]
Driver = /usr/local/lib/libsqlite3odbc.dylib
[Cloudera ODBC Driver for Impala]
Driver = /opt/cloudera/impalaodbc/lib/universal/libclouderaimpalaodbc.dylib
and my .Renviron file looks like
# ODBC
DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/usr/local/lib
ODBCINI=/etc/odbc.ini
ODBCSYSINI=/etc
Let me know if you need any more information.
Which version of the Impala ODBC driver are you using, and what's the version of Impala you're connecting to?
Impala version as returned by impala-shell is 2.5.0-cdh5.7.0
I am not sure about the driver version, is there a way to return the version of the current install? At least I know that we needed to install an older version to mach our Impala version.
To see the driver version, please run this command in Terminal:
pkgutil --info cloudera.impalaodbc
Thanks, version is: 2.5.20.1021
That's a pretty old version of the driver (> 3 yrs old). I'll see if I can repro this using that old version. You might also try to upgrade to the latest driver if that's possible. The latest version is 2.5.40, and the documentation indicates that it supports Impala 2.5.0/CDH 5.7.0:
The Cloudera ODBC Driver for Impala is recommended for Impala versions 1.0.1 through 2.9, and
CDH versions 5.0 through 5.10. The driver also supports later minor versions of CDH 5.
I know it is old. Last time we checked we couldn't get it to work with a newer version. Will check again.
New driver does indeed work, so apparently we had some upgrade that I missed.
However, situation remains unchanged. I still have to run the sqlite connection before connecting to implyr.
Ok, thanks for confirming that the problem is unrelated to that specific older driver version. I haven't been able to reproduce but I'll keep trying. Can you send me the output of the R command sessionInfo() so I can see the exact versions of R and of all the packages? Thanks.
I was already afraid reproduction would be an issue, since it involves other things than implyr. Thanks for trying. Here is the info.
R version 3.3.2 (2016-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: macOS Sierra 10.12.6
locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] implyr_0.2.1 dplyr_0.7.3 odbc_1.1.1
loaded via a namespace (and not attached):
[1] Rcpp_0.12.12 knitr_1.14 bindr_0.1 magrittr_1.5 hms_0.3 bit_1.1-12 mnormt_1.5-5 lattice_0.20-35 R6_2.2.2 rlang_0.1.2 blob_1.1.0 stringr_1.2.0 plyr_1.8.4 tools_3.3.2
[15] parallel_3.3.2 grid_3.3.2 broom_0.4.1 nlme_3.1-128 psych_1.7.3.21 DBI_0.7 dbplyr_1.1.0 bit64_0.9-7 yaml_2.1.13 assertthat_0.2.0 tibble_1.3.4 bindrcpp_0.2 tidyr_0.6.3 reshape2_1.4.2
[29] formatR_1.4 glue_1.1.1 stringi_1.1.5 forcats_0.1.1 foreign_0.8-67 pkgconfig_2.0.1
I'm still having no luck reproducing this. I suspect it has to do with the ODBC environment variables. It could be that they're unset at first, then they get set when you connect to SQLite. Please take a look at the sections "Specifying ODBC Driver Managers on Non-Windows Machines" and "Specifying the Locations of the Driver Configuration Files" on page 31-32 of the Impala ODBC driver install guide. You could try setting the environment variables DYLD_LIBRARY_PATH, ODBCINI and ODBCSYSINI as described there.
I am currently running into the same problem while trying to move towards specifying the DSN only.
Error: nanodbc/nanodbc.cpp:950: HY000: [unixODBC][Cloudera][ODBC] (11560) Unable to locate
SQLGetPrivateProfileString function.
Interestingly callingpyodbc.connect('DSN=DSN_NAME', autocommit=True) works for the same user and the output from Sys.getenv() seems comparable?
Any thoughts on in which direction to look for?
(Could also take a look at the versions of Impala/ Driver later)
Hey all - I had a similar issue, but not with imply or impala (or Cloudera). Seems Macs have a bunch of different .ini files scattered all about...
Anyway, for me the issue was product-specific driver .ini files had the wrong value of ODBCInstLib set. @EdwinTh my guess is you'll have some sort of .ini file in the folder /opt/cloudera/impalaodbc/lib/universal/, containing the variable ODBCInstLib set to the "wrong" value (or maybe just not set at all)? At least for me, the location wasn't where the DYLD_LIBRARY_PATH was looking. @henningsway maybe you'll have something similar going on.
For my two issues, I just had to set ODBCInstLib to be the hardcoded location of my libodbcinst.dylib (which was /usr/local/Cellar/unixodbc/<version>/lib/libodbcinst.dylib).
This could be totally off-base, but it helped me, so I thought I might as well see if it could help either of you.
On stackoverflow, someone said to set LD_PRELOAD, ,
export LD_PRELOAD=/usr/local/libodbcinst.so
But I don't want to set env variable each time and I need to run scripts automatically. So I searched and found a solution:
echo "/usr/lib/x86_64-linux-gnu/libodbcinst.so" >> /etc/ld.so.preload
Note, you should find where your libodbcinst.so locates and use its absolute path.
I spent hours on this, the resolution for me was to edit /opt/cloudera/impalaodbc/lib/universal/cloudera.impalaodbc.ini to:
[Driver]
ErrorMessagesPath=/opt/cloudera/impalaodbc/ErrorMessages/
LogLevel=0
LogPath=
ODBCInstLib=/usr/local/lib/libodbcinst.dylib
DriverManagerEncoding=UTF-16
The two lines I added being:
# use unixODBC not iODBC (i think????)
ODBCInstLib=/usr/local/lib/libodbcinst.dylib
# unixODBC wants UTF-16 encoding, otherwise, you'll see letters in the connection pane
DriverManagerEncoding=UTF-16
Source (page 14): https://docs.cloudera.com/documentation/other/connectors/impala-odbc/2-5-15/Cloudera-ODBC-Driver-for-Impala-Install-Guide-2-5-15.pdf