Converting Reactive SQL Output to Data Frame R Shiny for DataQualityR package

shiny
1

I am trying to run checkDataQuality function on a data frame created from a reactive SQL Query in R shiny.

sqlOutput1 <- eventReactive(input$pick_tablenames,({

      sqlInput1 <- paste("select *
                         from ",input$pick_tablenames," a left join ",outputDataMart()," b on a.trade_oid=b.trade_oid where b.FEED_SRC=","'",input$pick_feedsrc,"'",
                         "and b.SRC_FILE=" ,"'",input$pick_srcfile,"'"," and b.DT= ","'",input$pick_dt,"'","limit 50",
                         sep="")



      dbGetQuery(con, sqlInput1)
    }))

    sample1 <- reactive({
      sqlOutput1=sqlOutput1()
      sample1 = data.frame(sqlOutput1)
    })    
num.file <- paste(tempdir(), "/dqr_num.csv", sep= "")
cat.file <- paste(tempdir(), "/dqr_cat.csv", sep= "")
checkDataQuality(data= sample1, out.file.num= num.file, out.file.cat= cat.file)

I am facing issues trying to create the dataframe sample1. Could you please help me with this?

2

Hi, not sure if you've read this article yet: http://db.rstudio.com/best-practices/dashboards/, it covers how to create Shiny apps that have a database back end. This section covers your specific question: http://db.rstudio.com/best-practices/dashboards/#create-a-base-query-using-dplyr.

3

Hi,
Thanks for your response.
The issue I am facing is with large dataset the query hangs.
Another alternative is to use ETLUtils and ff package.

readDataForFreq<- reactive(
{
if(input$reload)
{
query<- paste("select *
from ",input$pick_tablenames," a left join ",outputMart()," b on a.oid=b.oid where b.FEED=","'",input$pick_feed,"'",
"and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'","limit 50",
sep="")
y<- read.jdbc.ffdf(query,dbConnect.args = list(drv = oc_drv, url = "jdbc:netezza:/" ,
dbname='', user ='', password ='',first.rows = 1000,next.rows = 1000))

  return (y)
}

})

The error I am getting with this is:-

Warning: Error in ff: vmode 'character' not implemented