shinyapps.io - problem using snowflake database

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

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 ?

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

Can you show the code together?

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

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)

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

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.

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

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)

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

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?)

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

you located it. you know the path...

Thanks for all the help.

Swapna

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 !

what is your code for this now ?

Thanks for replying.
Below is the code.
library(DBI)
conn<- DBI::dbConnect(odbc::odbc(),
snowflake="SnowflakeDSIIDriver",
Driver = "libSnowflake.so",
server = ".eu-west-1.snowflakecomputing.com.",
role = "SYSADMIN",
database = "TAL_SF",
Schema = "TS",
warehouse = "TAL_SF_WH",
UID=" ",
pwd=' ',
timeout = 10)
Should I add or change some thing.Please let me know.
Thanks

does that work locally, i.e. can you run exactly that code in the console and get a working conn?
(sidenote : also it seems to me you should add library(odbc) if you are using it)

I Have included
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
conn<- DBI::dbConnect(odbc::odbc(),
snowflake="SnowflakeDSIIDriver",
Driver = "libSnowflake.so" packages in snowflake.R and library(odbc) in app.R
But same issue continues says Dissconnected from server.
The code work perfectly fine with Run App(console). That means it might not be the problem with snowflake database. But when I double click the libSnowflake.so file it says its a large file to open in source editor, attaching the screen shot of it.
Is the issue related to shinyapp.io. Should I subscribe shinyapp.io with monthly payment. Please guide me with this.
Thanks