Troubleshooting R <> Redshift connection via ODBC

[eamcvey]

I am trying to connect to Redshift from the RStudio IDE on my Mac, and am stuck on this error: Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib' : file not found

The dylib file is present at this location, so it feels a lot like a "there is no paper jam" situation. I assume there is another underlying issue but am struggling to diagnose it. I suspect it stems from installing with homebrew or the iodbc / unixodbc issue (since the instructions I followed from Amazon started with iodbc). It may be that all the tweaks I've tried along the way have created issues of their own.

I have attempted the solutions that resolved the issues in other threads (here and here). Specifically:

• Confirmed LD_LIBRARY_PATH=/opt/amazon/redshift/lib
• Added DriverManagerEncoding=UTF-16 and ODBCInstLib=libodbcinst.so to opt/amazon/redshift/lib/amazon.redshiftodbc.ini

The contents of /opt/homebrew/etc/odbc.ini are:
`[ODBC Data Sources]
Amazon_Redshift_dylib=Amazon Redshift DSN for macOS X

[ODBC Drivers]
Amazon Redshift=Installed

[Amazon Redshift DSN for macOS X]
Driver=/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib
Host=serveraddressxyz
Port=5439
Database=my_data_warehouse
locale=en-US

`

and contents of /opt/homebrew/etc/odbcinst.ini are:
[AWS Redshift Driver] Driver = /opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

Running odbc::odbcListDrivers() in R returns:
name attribute value 1 AWS Redshift Driver Driver /opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

And I get the error when I run:
con <- DBI::dbConnect(odbc::odbc(), Driver = "/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib", servername = "serveraddressxyz", database = "my_data_warehouse", UID = "username", PWD = "password", Port = 5439)

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib' : file not found

If anyone can point me in the right direction to diagnose the root cause of this issue, it would be greatly appreciated!! Several aspects of this setup are new to me.

[felipe_dalton]

Hello @eamcvey! I remember having a similar issue when connecting Redshift, I can't give much color on the setup/resolution itself as it was largely news to me as well, but here is the thread that I had opened at the time in case it helps: RStudio Connection Tab Error MacOS (Error: nanodbc/nanodbc.cpp:1021: 00000 - file not found)

[eamcvey]

Thanks @felipe_dalton !

I have taken a careful look at this thread, and I think I have done all the things suggested, though clearly I'm missing something.

I interpret from your reply that you had multiple obdc.ini files, and that was ok in the end as long as you modified the one R was looking for -- though I'm not sure how to know which one that is, I believe I've modified both to have the correct info.

I'm debating whether to try to uninstall the driver and unixodbc and try again or if that might make things worse. These types of issues are so ... humbling.

[eamcvey]

For the record, it seems the root issue is not specific to R but rather that the AWS Redshift ODBC driver is not compatible with M1 Macs (though they don't say so).