shinyapps.io - problem using snowflake database

Swapna · June 29, 2020, 1:18pm

Hi,
I have installed shiny server on ubuntu 18.04 on EC2 instance. I am trying to deploy a shiny app using shinyapps.io. So basically the data used for my app is been pulled from snowflake. The app works perfectly fine with Run App. But when we publish using shinyapps.io it says error has occured. Below is the code
library(shiny)
library(forecast)

Define UI for application

ui <- fluidPage(
# Application title
titlePanel("Forecasting Model"),

# Sidebar with a slider input for number of bins 
sidebarLayout(
    sidebarPanel(
        sliderInput("bins",
                    "Number of weeks:",
                    min = 1,
                    max = 50,
                    value = 30)
    ),      
    # Show a plot of the generated distribution
    mainPanel(
        plotOutput("distPlot"),
        tableOutput("dataTable")
    )
)

)

Define server logic

server <- function(input, output) {

output$distPlot <- renderPlot({
    library(forecast)
    #setwd("D:/Edgematrics-official/Forcaste")
    forecast<-DBI::dbGetQuery(con,"SELECT * FROM FORECAST_WEEKLY")
    y=ts(forecast[,2],start=c(2010,2),frequency=52)
    fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
    fst<-forecast(fit_arima,h=input$bins)
    plot(fst)
    #print(summary(fst))
})

output$dataTable<-renderTable({
    #setwd("D:/Edgematrics-official/Forcaste")
    forecast<-DBI::dbGetQuery(con,"SELECT * FROM FORECAST_WEEKLY")
    y=ts(forecast[,2],start=c(2010,2),frequency=52)
    fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
    fst<-forecast(fit_arima,h=input$bins)
    head(print(summary(fst)))
})

}

Run the application

shinyApp(ui = ui, server = server)

Please help me with this.

nirgrahamuk · June 29, 2020, 1:40pm

check the logs on the shinyapps.io managment console.
Or on your local machine run rsconnect::showLogs(appName="nameofyourhostedapp",streaming=TRUE) (sendit the correct params for your app)

Swapna · June 29, 2020, 2:07pm

Hi,
Thanks for replying. I Checked the log. I think the error is from 'dbGetQuery'. Should I write the code of pulling the data from snowflake ie the below code.
library(DBI)
con <- DBI::dbConnect(odbc::odbc(),

snowflake="SnowflakeDSIIDriver",
Driver = "/usr/lib/snowflake/odbc/lib/libSnowflake.so",
server = ".eu-west-1.snowflakecomputing.com.",
role = "SYSADMIN",
database = "TAL_SF",
Schema = "TS",
warehouse = "TAL_SF_WH",
UID=" ",
pwd=' ',
timeout = 10)

sending the log. Please help me to understand it.

Thanks,
Swapna

Swapna · June 30, 2020, 6:50am

Hi,
I have included the code of extracting the data from snowflake database as shown above but still gives the same error.
Warning: Error in h: error in evaluating the argument 'conn' in selecting a method for function 'dbGetQuery': object 'conn' not found

Can you please help me how can i resolve this issue.
Thanks,
Swapna

nirgrahamuk · June 30, 2020, 8:05am

I dont understand why your database code that you show some of now was absent from your first posting of the code. Is it in there, or absent? what method had you used to include it in your app ?

Swapna · June 30, 2020, 8:17am

Hi,
So in the first post I did not add database code, later I added the extraction code in the server part but still shows the same error. How can I run the shiny app with data extracting from database.
Thanks for your help.

Swapna

nirgrahamuk · June 30, 2020, 8:33am

Can you show the code together?

Swapna · June 30, 2020, 9:14am

Hi,
Below is the code. I am not mentioning the UID and Password for security purpose.
library(shiny)
library(forecast)
library(rsconnect)
library(DBI)

Define UI

ui <- fluidPage(

# Application title
titlePanel("Forecasting Model"),

# Sidebar with a slider input for number of bins 
sidebarLayout(
    sidebarPanel(
        sliderInput("bins",
                    "Number of weeks:",
                    min = 1,
                    max = 50,
                    value = 30)
    ),
    
    # Show a plot of the generated distribution
    mainPanel(
        plotOutput("distPlot"),
        tableOutput("dataTable")
    )
)

)

Define server

server <- function(input, output) {

output$distPlot <- renderPlot({
    library(forecast)
    conn<- DBI::dbConnect(odbc::odbc(),  
                          snowflake="SnowflakeDSIIDriver",
                          Driver      = "/usr/lib/snowflake/odbc/lib/libSnowflake.so",
                          server      = ".eu-west-1.snowflakecomputing.com.",
                          role        = "SYSADMIN",
                          database    = "TAL_SF",
                          Schema      = "TS",
                          warehouse   = "TAL_SF_WH", 
                          UID="    ", 
                          pwd='   ',
                          timeout = 10)
    forecast<-DBI::dbGetQuery(conn,"SELECT * FROM FORECAST_WEEKLY")
    y=ts(forecast[,2],start=c(2010,2),frequency=52)
    fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
    fst<-forecast(fit_arima,h=input$bins)
    plot(fst)
    #print(summary(fst))
})

output$dataTable<-renderTable({
    conn<- DBI::dbConnect(odbc::odbc(),  
                          snowflake="SnowflakeDSIIDriver",
                          Driver      = "/usr/lib/snowflake/odbc/lib/libSnowflake.so",
                          server      = ".eu-west-1.snowflakecomputing.com.",
                          role        = "SYSADMIN",
                          database    = "TAL_SF",
                          Schema      = "TS",
                          warehouse   = "TAL_SF_WH", 
                          UID="   ", 
                          pwd='   ',
                          timeout = 10)
    forecast<-DBI::dbGetQuery(conn,"SELECT * FROM FORECAST_WEEKLY")
    y=ts(forecast[,2],start=c(2010,2),frequency=52)
    fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
    fst<-forecast(fit_arima,h=input$bins)
    head(print(summary(fst)))
})

}

Run the application

shinyApp(ui = ui, server = server)

This code works fine with Run App. But when publishing it shows publish content issues
Line 36 Path should be a file within the project directory
line 56 Path should be a file within the project directory

Here line 36 and 56 are
Driver = "/usr/lib/snowflake/odbc/lib/libSnowflake.so",
This line
Thanks,
Swapna

nirgrahamuk · June 30, 2020, 9:20am

yes, ok, so if you are publishing your code onto a foreign computer (shinyapps.io)
how could you guarantee that the file you want is in that long path you gave ?
You should copy that file locally so its in the same folder as your shiny app project. and access it
just by its name (assuming its pass is the working directory)

Swapna · June 30, 2020, 10:10am

Hi Nir,

Sorry to disturb you, but this is my first deploying app.

As you mentioned in the previous email I created a R script code of extracting data and saved it in same folder where my app is saved.

Below is the code

library(shiny)
library(forecast)
library(rsconnect)
library(DBI)

Define UI

ui <- fluidPage(

Application title

titlePanel("Forecasting Model"),

Sidebar with a slider input for number of bins

sidebarLayout(

sidebarPanel(
sliderInput("bins",
"Number of weeks:",
min = 1,
max = 50,
value = 30)
),

Show a plot of the generated distribution

mainPanel(
plotOutput("distPlot"),
tableOutput("dataTable")
)
)
)

Define server

server <- function(input, output) {
source('~/sample/Forecast/Snowflakes.R', local = TRUE)
output$distPlot <- renderPlot({
library(forecast)
forecast<-DBI::dbGetQuery(conn,"SELECT * FROM FORECAST_WEEKLY")
y=ts(forecast[,2],start=c(2010,2),frequency=52)
fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
fst<-forecast(fit_arima,h=input$bins)
plot(fst)
#print(summary(fst))
})

output$dataTable<-renderTable({
forecast<-DBI::dbGetQuery(conn,"SELECT * FROM FORECAST_WEEKLY")
y=ts(forecast[,2],start=c(2010,2),frequency=52)
fit_arima<-auto.arima(y,d=1,D=1,stepwise = FALSE,approximation = FALSE,trace = TRUE)
fst<-forecast(fit_arima,h=input$bins)
head(print(summary(fst)))
})
}

Run the application

shinyApp(ui = ui, server = server)

The error know it shows is

app.R

line 31 Path should be to file within the project directory .

Snowflakes.R

Line 4 Path should be to file within the project directory

Line 31 is: source('~/sample/Forecast/Snowflakes.R', local = TRUE)

Line 4: Driver = "/usr/lib/snowflake/odbc/lib/libSnowflake.so"

Can you please help me to understand this.

Thanks,

Swapna

nirgrahamuk · June 30, 2020, 10:13am

when you getwd() in your console when working on this project where are you ?

and the driver location is wrong, you can access a usr folder thats not yours on anothers computer...
/usr/lib/snowflake/odbc/lib/libSnowflake.so <- bad
libSnowflake.so <- good, so put this in your project along with the scripts.

Swapna · June 30, 2020, 11:06am

Hi,
With getwd() I got the working directory and mentioned the same in source() and changed the path /usr/lib/snowflake/odbc/lib/libSnowflake.so to libSnowflake.so

But again for the source('/home/rstudio-user/sample/Forecast/Snowflakes.R', local = TRUE') it gives error as

How can I resolve this.
Thanks,
Swapna

nirgrahamuk · June 30, 2020, 11:12am

snowflakes.R is in the wd then (/home/sample/forecast on your local computer)
so your app should find it at its current wd without need of being told a path, the name alone is sufficient.
however I dont see a libSnowflake.so file in your screenshot so I dont believe you moved it to the same location as app.R and snowflakes.R are.

source('Snowflakes.R', local = TRUE)

Swapna · June 30, 2020, 11:40am

Everything worked fine but as seen in the snap shot it says Disconnected from the server.
As you mentioned it might be due to libSnowflake.so not in the location. But libSnowflake.so is the driver. How can i save libSnowflake.so file in same folder. I do not know which is this file.I have just the path of it "/usr/lib/snowflake/odbc/lib/libSnowflake.so". Can you just explain me on this.

Thanks for all your support,
Swapna

nirgrahamuk · June 30, 2020, 11:42am

you can switch to terminal.
use cp command to copy from source path to destination path
I'm guessing you are on a unix system ? (or mac?)

Swapna · June 30, 2020, 11:53am

The error in the log is:
Error : nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Can't open lib 'libSnowflake.so' : file not found .
How can i locate this driver file and place in the working directory

nirgrahamuk · June 30, 2020, 11:55am

you located it. you know the path...

Swapna · June 30, 2020, 12:01pm

Thanks for all the help.

Swapna

Swapna · July 1, 2020, 8:37am

Hi,
As you suggested using cp command I copies libSnowflake.so file to the working directory but still I getting same issue. It says disconnected from the server. What might be the issue? Is it something related to snowflake or shiny server. As log still says Can't open lib 'libSnowflake.so' : file not found
Attaching the screen shots

!
Please help me !

nirgrahamuk · July 1, 2020, 8:39am

what is your code for this now ?