Using reactive() just for read/selection target variables and not downloading all table from BigQuery

Leprechault · October 7, 2021, 3:04pm

I'd like to make the selection of variables (year,seasonType,dayNight and homeFinalRuns) inside the baseball public data from BigQuery without downloading all data. I try to use the reactive() variables as a filter for the target levels without success because the complete baseball is always complete downloading and not just read when my Shiny starts I have the output:

    Listening on http://127.0.0.1:7377
    Downloading first chunk of data.
    Received 4,179 rows in the first chunk.
    Downloading the remaining 757,439 rows in 242 chunks of (up to) 3,134 rows.

The code that I try is:

    library(shinythemes)
    library(dplyr)
    library(ggplot2)
    library(bigrquery)
    
    # Open a public BigQuery dataset eg. "baseball"
    bq_con <- dbConnect(
      bigrquery::bigquery(),
      project = "bigquery-public-data",
      dataset = "baseball",
      billing = "my_project_id"
    )
    bigrquery::dbListTables(bq_con) # List all the tables in BigQuery data set
    # My email google option
    1
    #[1] "games_post_wide" "games_wide"      "schedules"    
    #
    
    # Selection of target data set
    dataset <- dplyr::tbl(bq_con, 
                                   "games_wide") # connects to a table
    
    
    # Create the shiny dash
    ui <- fluidPage(
        theme = shinytheme("cosmo"),
        titlePanel(title="My Baseball Dashboard"),  
        sidebarLayout(
            sidebarPanel(
                selectInput(inputId = "selectedvariable0",
                            label = "Year", 
                            choices = c(unique(dataset$year)),selected = TRUE ), 
                selectInput(inputId = "selectedvariable1",
                            label = "Season", 
                            choices = c(unique(dataset$seasonType)),selected = TRUE ), 
                selectInput(inputId = "selectedvariable2",
                            label = "Period", 
                            choices = c(unique(dataset$dayNight)),selected = TRUE ) ,      
                selectInput(inputId = "selectedvariable3",
                            label = "Final Runs", 
                            choices = c(unique(dataset$homeFinalRuns)),selected = TRUE )      
            ),
            mainPanel(
                textOutput("idSaida"),
                fluidRow(
                    splitLayout(plotOutput("myplot")))
            )
        )
    )
    server <- function(input, output){
        
        currentvariable0 <- reactive({input$selectedvariable0})
        currentvariable1 <- reactive({input$selectedvariable1})
        currentvariable2 <- reactive({input$selectedvariable2})
        currentvariable3 <- reactive({input$selectedvariable3})
    
    observe({ 
        if(currentvariable2()=="D"){
            output$myplot <- renderPlot({
                
                #Create the plot
                ggplot(data=dataset, aes(x=currentvariable0(), y=currentvariable3())) +
                geom_bar(stat="identity")
            })
        } else {
                #Create the plot
                ggplot(data=dataset, aes(x=currentvariable0(), y=currentvariable3())) +
                geom_bar(stat="identity")
            
        }
         }) #end of observe function.
    }
    shinyApp(ui, server)
    #

Please, any help for solving it?

nirgrahamuk · October 7, 2021, 3:16pm

looks like you pass the entire table without first selecting from it to your ggplot

ggplot(data=dataset

you should probably do something along roughly these lines.

for_ggplot_df <- select(dataset, var_i_get = var_i_need) %>% collect()
and then ggplot(data=for_ggplot_df,aes(x=var_i_get)

Leprechault · October 7, 2021, 11:18pm

And if I use !! in:

  # Selection of variables for plots constructions 
  dataset_sel <- dataset %>% filter(year=!!currentvariable0(),homeFinalRuns==!!currentvariable0())%>% 
    collect()

A new error happened:

Complete
Billed: 0 B
Downloading first chunk of data.
First chunk includes all requested rows.

Listening on http://127.0.0.1:4784
Warning: Error in : Operation not allowed without an active reactive context.
* You tried to do something that can only be done from inside a reactive consumer.
  60: <Anonymous>
Error : Operation not allowed without an active reactive context.
* You tried to do something that can only be done from inside a reactive consumer.

Leprechault · October 7, 2021, 11:18pm

Thank you very much @nirgrahamuk , but when I try your solution I have the error:

Complete
Billed: 0 B
Downloading first chunk of data.
First chunk includes all requested rows.

Listening on http://127.0.0.1:4784
Warning: Error in : Cannot translate a shiny reactive to SQL.
* Force evaluation in R with (e.g.) `!!foo()` or `local(foo())`
70: <Anonymous>
  Error : Cannot translate a shiny reactive to SQL.
* Force evaluation in R with (e.g.) `!!foo()` or `local(foo())

The code suggested that I used is:

library(shinythemes)
library(dplyr)
library(ggplot2)
library(bigrquery)
library(DBI)

# Open a public BigQuery dataset eg. "baseball"
bq_con <- dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "baseball",
  billing = "my_project_id"
)
bigrquery::dbListTables(bq_con) # List all the tables in BigQuery data set
# My email google option
1
#[1] "games_post_wide" "games_wide"      "schedules"    
#

# Selection of target data set
dataset <- dplyr::tbl(bq_con, 
                      "games_wide") # connects to a table

# Selection of reactive strings 
dataset_vars <- dataset %>% distinct(year,seasonType,dayNight,homeFinalRuns)%>% 
  collect() 
dataset_vars
# Create the shiny dash
ui <- fluidPage(
  theme = shinytheme("cosmo"),
  titlePanel(title="My Baseball Dashboard"),  
  sidebarLayout(
    sidebarPanel(
      selectInput(inputId = "selectedvariable0",
                  label = "Year", 
                  choices = c(unique(dataset_vars$year)),selected = TRUE ), 
      selectInput(inputId = "selectedvariable1",
                  label = "Season", 
                  choices = c(unique(dataset_vars$seasonType)),selected = TRUE ), 
      selectInput(inputId = "selectedvariable2",
                  label = "Period", 
                  choices = c(unique(dataset_vars$dayNight)),selected = TRUE ),
      
      selectInput(inputId = "selectedvariable3",
                  label = "Final Runs", 
                  choices = c(unique(dataset_vars$homeFinalRuns)),selected = TRUE )      
    ),
    mainPanel(
      textOutput("idSaida"),
      fluidRow(
        splitLayout(plotOutput("myplot")))
    )
  )
)
server <- function(input, output){
  
  currentvariable0 <- reactive({input$selectedvariable0})
  currentvariable1 <- reactive({input$selectedvariable1})
  currentvariable2 <- reactive({input$selectedvariable2})
  currentvariable3 <- reactive({input$selectedvariable3})
  
  
  # Selection of variables for plots constructions 
  dataset_sel <- dataset %>% filter(year=currentvariable0(),homeFinalRuns==currentvariable0())%>% 
    collect() 
  
  
  observe({ 
    if(currentvariable2()=="D"){
      output$myplot <- renderPlot({
        
        #Create the plot
        ggplot(data=dataset_sel, aes(x=currentvariable0(), y=currentvariable3())) +
          geom_bar(stat="identity")
      })
    } else {
      #Create the plot
      ggplot(data=dataset_sel, aes(x=currentvariable0(), y=currentvariable3())) +
        geom_bar(stat="identity")
      
    }
  }) #end of observe function.
}
shinyApp(ui, server)
#

Leprechault · October 8, 2021, 2:01am

And if I change by query approach like:

    var1 <- currentvariable0()
    var2 <- currentvariable3()
    sqlInput <- paste("SELECT",var1,"as year, 'REG' as seasonType,'N' as dayNight,",var2,"as homeFinalRuns FROM games_wide LIMIT 30")
    dataset_sel <- dbGetQuery(bq_con, sqlInput, stringsAsFactors = T)

Doesn't work too:

    Warning: Error in : Operation not allowed without an active reactive context.
    * You tried to do something that can only be done from inside a reactive consumer.
      55: <Anonymous>
    Error : Operation not allowed without an active reactive context.
    * You tried to do something that can only be done from inside a reactive consumer.

nirgrahamuk · October 8, 2021, 8:20am

Apologies for not figuring it all out to the last detail, the issue preventing me doing that is trying it for myself as I don't have access to the database type you require.

That said, In your shoes I would flow the advice given by the error message itself.

Force evaluation in R with (e.g.) `!!foo()` or `local(foo())`

Leprechault · October 8, 2021, 3:43pm

Thanks @nirgrahamuk ,

But not work yet:

 # Selection of variables for plots constructions 
  dataset_sel <- dataset %>% filter(year=!!currentvariable0(),homeFinalRuns==!!currentvariable0())%>% 
    collect() 

Complete
Billed: 0 B
Downloading first chunk of data.
First chunk includes all requested rows.

Listening on http://127.0.0.1:4784
Warning: Error in : Operation not allowed without an active reactive context.
* You tried to do something that can only be done from inside a reactive consumer.
  60: <Anonymous>
Error : Operation not allowed without an active reactive context.
* You tried to do something that can only be done from inside a reactive consumer.

nirgrahamuk · October 8, 2021, 3:47pm

a reactive context means one of either observeEvent,observe,reactive,or reactiveEvent or a render* function. so datatset_sel should be a reactive({}) with the code happening within the braces. and when you refer to dataset_sel elsewhere in the app you need to evaluate it by calling it as a function like dataset_sel()

Leprechault · October 8, 2021, 5:50pm

Thanks very much @nirgrahamuk , problem solved!! The solution is:

dataset_sel <- reactive({ 
    var1 <- currentvariable0()
    var2 <- currentvariable3()
    sqlInput <- paste("SELECT",var1,"as year, 'REG' as seasonType,'N' as dayNight,",var2,"as homeFinalRuns FROM games_wide LIMIT 30")
    dbGetQuery(bq_con, sqlInput, stringsAsFactors = T)})


  observe({
    if(currentvariable2()=="D"){
      output$myplot <- renderPlot({

        #Create the plot
        ggplot(data=dataset_sel(), aes(x=year, y=homeFinalRuns)) +
          geom_bar(stat="identity")
      })
    } else {
      #Create the plot
      ggplot(data=dataset_sel(), aes(x=year, y=homeFinalRuns)) +
        geom_bar(stat="identity")

    }
  }) #end of observe function.

system · October 15, 2021, 5:51pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.