CODEWITHSUNDEEP
rdockershinydockerfileodbc
zesla
zesla

Reputation: 11793

Need odbc driver in dockerfile for my r shiny app

I'm making a R shiny app, which calls the database (SQL Server) using DBI and odbc and displays a table. My app works perfectly on my local machine. However, when I tried to dockerize the shiny app, it just failed to start. No clue why that does not work.

I feel like I might need to install the odbc drive in my dockerfile. Maybe I'm also missing something else...

I'm new to docker. Appreciate it if someone can show how I can modify my dockerfile so that my shiny app also works as docker container.

below is my dockerfile:

# Base image https://hub.docker.com/u/rocker/
FROM rocker/shiny:latest

# system libraries of general use
RUN apt-get update -qq && apt-get -y --no-install-recommends install \
    libxml2-dev \
    libcairo2-dev \
    libsqlite3-dev \
    libmariadbd-dev \
    libpq-dev \
    libssh2-1-dev \
    unixodbc-dev \
    libcurl4-openssl-dev \
    libssl-dev

## update system libraries
RUN apt-get update && \
    apt-get upgrade -y && \
    apt-get clean

# Install R packages that are required
RUN R -e "install.packages(c('dplyr','DT', 'DBI', 'odbc'), repos='http://cran.rstudio.com/')"
    
# Copy configuration files into the Docker image
COPY shiny-server.conf  /etc/shiny-server/shiny-server.conf
COPY /app /srv/shiny-server/

# Make the ShinyApp available at port 80
EXPOSE 80

# Copy further configuration files into the Docker image
COPY shiny-server.sh /usr/bin/shiny-server.sh

CMD ["/usr/bin/shiny-server.sh"]

below is my shiny ui.r:

ui <- fluidPage(
    
    titlePanel("Database table"),    
    sidebarLayout(
        sidebarPanel(),
        mainPanel(
            fluidRow (
                column(12, DT::dataTableOutput('datatable')) )
        )
    )
)

my server.r

server <- function(input, output, session) {
    conn <- DBI::dbConnect(odbc::odbc(),
                           Driver = "ODBC Driver 17 for SQL Server",
                           Server = 'xxxxxx',
                           Database = "xxxxxx",
                           UID = "xxxxxx",
                           PWD = 'xxxxxxx',
                           port=1433)
    df = dbGetQuery(conn, "SELECT * FROM myTable")
    dbDisconnect(conn)    
    output$datatable <- DT::renderDataTable({
        DT::datatable(df)
    })
}

global.r:

library(shiny)
library(DBI)
library(odbc)
library(dplyr)
library(DT)

Upvotes: 3

Views: 3704

Answers (2)

dora
dora

Reputation: 21

If it throws the error like "ODBC Driver 17 for SQL Server" cannot be found, perhaps you haven't installed the ODBC driver in your Dockerfile.

I had the same issue, but it was solved for me when putting this in my Dockerfile: https://community.rstudio.com/t/odbc-drivers-docker/32714.

Upvotes: 2

mikey
mikey

Reputation: 1313

I still think we don't have enough information (it isn't ideal that we can't see the specific error message confirming that it is a connectivity issue between the Docker container and the SQL Server), but I'll state my assumptions and give this a shot.

You mentioned that you have this stack running locally without issue, and it's only when you move the R application into a container that it isn't working. This leads me to think that your SQL Server instance is running locally.

Once again, it's hard to be certain as in server.r. You've censored out the database endpoint (Server = 'xxxxxx',). If it is an external endpoint it makes sense to censor it, but if it is an internal/localhost address this could be useful.

I'm not familiar with this stack, but from what I've read you've done the right thing in regards to ODBC - you installed the system packages, installed the R libraries, and included the R libraries. Looking at this article it seems like it should be correct: https://nhsrcommunity.com/blog/sql-server-database-connections-in-r/

So, my theory is that your server configuration is still pointing at localhost or 127.0.0.1 despite being in a Docker container (they no longer share the same loopback/localhost!).

So please try updating your server.r database endpoint to Server = 'host.docker.internal',

I think there's a decent chance this is the issue, please refer to this thread for a lot more information about connecting a Docker container to a host service: https://stackoverflow.com/a/24326540/3080207, host.docker.internal should work, but I think it may depend on the OS you are running and the version of Docker you are running.

If I'm way off the mark, please provide more information :)

Upvotes: 0

Related Questions