Shiny: Filter/Selection of variables without downloading all data from BigQuery table

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 filter for the target strings without success because 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 that I try 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)
#

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.

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.

Please, any help for solving it?

Problem solved, no more errors!! The choose dataset must to be reactive({}) too and elements of choice in a first selection a query paste("SELECT",var1,"as year, 'REG' as seasonType,'N' as dayNight,",var2,"as homeFinalRuns FROM games_wide LIMIT 30").

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

#[1] "games_post_wide" "games_wide"      "schedules"    
#

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

# 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 <- 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.
}
shinyApp(ui, server)
#