I've got a real head scratcher that I think I'm struggling with because I don't know exactly how the Knit button works in R Studio. I have a db connection in my rmarkdown document. It's a dead simple odbc connection to redshift that looks like this:
con <- DBI::dbConnect(
odbc::odbc(),
Driver = "Amazon Redshift",
Database = "my_db",
port = 5439,
host = "redshift.me.com",
user = "uid",
password = "pdw"
)
I use this thing ALL the time. Works great.
I had a hardware issue and had to reinstall everything on my Mac and thought all was well. However the connection above works great in an R file or in the Console... or if I use rmarkdown::render('Untitled.Rmd') however, and here is where things get weird, if I click on the "Knit" button in RStudio I get the following error:
Quitting from lines 14-25 (Untitled.Rmd)
Error: nanodbc/nanodbc.cpp:1021: 00000: [Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Amazon][DSI] An error occurred while attempting to retrieve the error message for key 'LibsLoadErr' with message parameters ['"", "libodbcinst.dylib"'] and component ID 3: Message not found in file "/opt/amazon/redshift/ErrorMessages/en-US/ODBCMessages.xml"
Execution halted
That's a bit of a red herring. I know what causes that error. That's the error I get if I had the path for my Redshift driver file wrong in my odbcinst.ini ... but I don't. So it's like the R Markdown is being somehow run differently when I click "Render" as opposed to running rmarkdown::render('Untitled.Rmd')
I added a getwd() to the R Markdown and all ways of running it seem to be executing in the same file path. And I confirmed that all methods are reading my .Rprofile file, though that should not impact this build as I have nothing in there related to database drivers. But somehow when I click "Knit" I'm having a different experience from the other ways of running the code.
Things I have checked:
- knit directory is set to the document directory
- tried changing knitting directory
- I tried clearing knit cache
- tried knitting to different format
- moved the Rmd file to a different directory
Any ideas?
