Convert `sql` query from BigQuery to `fst` format

[Leprechault]

Hi Everyone!!

I'd like to convert a sql query from BigQuery to fst format for more speed in the creation of the plots in Shiny. But when I try to pass the BigQuery data frame to fst format:

  # # Selection of variables for plots constructions
  
  sqlInput <- reactive({
    glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con)
  })
  stands_sel <- function() dbGetQuery(bq_con, as.character(sqlInput()), stringsAsFactors = T)
  
    print(stands_sel)
    stands_sel <- write.fst(stands_sel(), "dataset.fst")
    stands_sel <- read.fst("dataset.fst")

I always received as output:

function() dbGetQuery(bq_con, as.character(sqlInput()), stringsAsFactors = T)
<environment: 0x0000018fc5fe2c70>
Warning: Error in <Anonymous>: erro na avaliação do argumento 'statement' na seleção do método para a função 'dbGetQuery': '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 in (function (cond)  : 
  erro na avaliação do argumento 'statement' na seleção do método para a função 'dbGetQuery': 'Operation not allowed without an active reactive context.
* You tried to do something that can only be done from inside a reactive consumer.'

My complete example is:

library(dplyr)
library(ggplot2)
library(bigrquery)
library(DBI)
library(sf)
library(glue)
library(fst)

# Open a public BigQuery dataset eg. "geo_us_boundaries"
bq_con <- dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "geo_us_boundaries",
  billing = "my-project"
)
bigrquery::dbListTables(bq_con) # List all the tables in BigQuery data set


# Take the table
dataset <- dplyr::tbl(bq_con, 
                      "states") # connects to a table


# Enumerate the states
dataset_vars <- dataset %>% dplyr::distinct(geo_id, state, state_name)%>% 
  collect() 
str(dataset_vars)


# Create the shiny dash
ui <- fluidPage(
  titlePanel(title="States Dashboard"),  
  sidebarLayout(
    sidebarPanel(
      selectInput(inputId = "selectedvariable0",
                  label = "Geo ID", 
                  choices = c(unique(dataset_vars$geo_id)),selected = TRUE ), 
      selectInput(inputId = "selectedvariable1",
                  label = "State", 
                  choices = c(unique(dataset_vars$state)),selected = TRUE ), 
      selectInput(inputId = "selectedvariable2",
                  label = "State name", 
                  choices = c(unique(dataset_vars$state_name)),selected = TRUE )
    ),
    mainPanel(
      fluidRow(
        splitLayout(plotOutput("myplot")))
      
    )
  )
)
server <- function(input, output){
  
  # # Selection of variables for plots constructions
  
  sqlInput <- reactive({
    glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con)
  })
  stands_sel <- function() dbGetQuery(bq_con, as.character(sqlInput()), stringsAsFactors = T)
  
    print(stands_sel)
    stands_sel <- write.fst(stands_sel(), "dataset.fst")
    stands_sel <- read.fst("dataset.fst")

    
    output$myplot <- renderPlot({
      
      #Create the plot 
      stands_sel_sf <- st_as_sf(stands_sel(), wkt = "state_geom", crs = 4326) 
      ggplot() + geom_sf(data=stands_sel_sf) }) 
}
shinyApp(ui, server)
#

Please any help with it?

[cwdjankoski]

you are passing a function and not a dataframe to write.fst, you can maybe try instead of

 stands_sel <- function() dbGetQuery(bq_con, as.character(sqlInput()), stringsAsFactors = T)

to just remove the function call

 stands_sel <- dbGetQuery(bq_con, as.character(sqlInput()))