I have confirmed this is working if I'm running R through terminal on Amazon Linux 2 vm. I went ahead and installed rstudio-server (rstudio-server-rhel-1.2.5033-x86_64.rpm) on the same ec2, but cannot get the connection to work. It sounds similar (but the opposite problem) to this question .
> myconn <- DBI::dbConnect(odbc::odbc(), "snowflake", uid="...", pwd='...') Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/home/ec2-user/snowflake_odbc/lib/libSnowflake.so' : file not found
I've slogged through this with zero system/linux admin experience and have little idea of how to proceed. My only ideas are to try the RODBC package instead, or venture into the world of "setting paths" or "symlinked" or environment variables.
Since I can successfully connect through R in the terminal is there any sort of command I could try (at r-terminal) to gather more info on what the issue is? Why would it work through r-terminal, but not rstudio-server?
Thoughts?
More background:
Since I'm using rstudio-server while signed in as an "user" I thought it was a permissions issue. I've changed permissions to /home/ec2-user/snowflake_odbc/lib/libSnowflake.so, still with no luck.
I've tried odbc::odbcListDrivers() from both r-terminal and rstudio-server and they return the exact same results.
Welcome to RStudio Community!! And thanks for the thorough post!
RStudio Server definitely changes some things (LD_LIBRARY_PATH, PATH, etc.) that could cause trouble.
I would compare environment variables between the place where it works and the place where it doesn't.
Also, it might be worth trying with isql from the
terminal outside RStudio
R session outside RStudio
terminal inside RStudio
R session inside RStudio (can even do system("isql"))
If you can isolate the problem to odbc, then I believe you are deeper than I know how to go, and might have something to do with how odbc is loading libraries.
I normally configure system-wide (rather than in a user-specific location) by using:
/etc/odbc.ini
/etc/odbcinst.ini
I know there are user specific analogues for odbc.ini (~/.odbc.ini, maybe?), and there may be similar for odbcinst.ini (which points at driver .so files). It may be worth taking a look at those to see if they resolve your issue!!
Ok, figured it out, snowflake's install odbc documentation is lacking a little. For whatever reason Installing the RPM Package steps worked. Turns out there is a note in there:
"The installation directory is /usr/lib64/snowflake/odbc/ . You’ll need the location later in the instructions."
Probably obvious to lots of people, but /usr is accessible to any user. Versus the TGZ File steps do not hint at the odbc's location being significant when it comes to multiple users.
I originally was following the steps for the TGZ File:
"Copy the resulting snowflake_odbc folder to the directory where you want to install the driver. Make note of this directory. You’ll need the location later in the instructions."
In the case of multiple users on rstudio-server, turns out its significant where this folder sits. So one might still use the TGZ File, as long as they had a clue about where to copy it to.
I got the RPM package to work, so I'll stop there.