Using Redshift to source data to RMarkdown published to RStudio Connect

rstudioconnect
odbc
redshift

#1

I’ve got a trial of RStudio Connect running on an EC2 in a VPC which also has a Redshift DB in it. I have developed a trivial markdown which uses the odbc and DBI packages to connect to the database similar to this documentation.

My issue is that I can’t seem to make the connection work on the RStudio connect side. To my knowledge RStudio connect takes care of isolating all your code. Does this invalidate the approach of installing odbc drivers, and should you use JDBC drivers local to the project? Is there a permission that I need to enable on the RStudio side?

I don’t have an MWE due to issues of security and portablity, but was wondering if there is some documentation specific to Connect that I’ve missed in this instance, or if there is a different approach I should be using?


#2

Hi @DaveRGP, can you take a look at this section and let me know if it helps? http://db.rstudio.com/best-practices/portable-code/#deploying-with-config-package


#3

Thanks for the pointer. It turns out that the problem was actually the location of my .odbc.ini and .odbcinst.ini files.

I had ‘read’ the deployment and setup notes, but had misinterpreted the following paragraph. All my mistake.

Because of prior applications and this paragraph from AWS:

By default, your Linux or Mac OS X operating system should have an .odbc.ini file and an .odbcinst.ini file in the user’s home directory (/home/$USER or ~/.). These default files are hidden files, which are indicated by the dot (.) in front of the file name, and they will only display when you use the -a flag to list the directory contents.

I had interpreted that using the ~ folder to hold the files was sufficient. However, reading the odbc package docs pointed me to odbcinst -j (which I now notice is also in the rdocs paragraph) I discovered that the target was default to be /etc/odbc.ini and /etc/odbcinst.ini on the EC2 instance I was running. My end solution was just to move the files out of the ~ folder and losing the . prefix from the file name.

Additional: I’ve looked at the config package, which I will try and work with in the future. Not managed to get it up yet, as the connection fro some reason times out when using it (both locally and on RStudio Connect), however I see the application and will tag that as the next thing to setup :slight_smile: