Increase BigQuery speed in Shiny

[Leprechault]

Hi Everyone!

I don't like to access a complete public BigQuery dataset in my case geo_us_boundaries. But I'd like to choose the data by state using the query glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con). I try to do:

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

# 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(sqlInput)

  observe({
    
    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) }) 
  }) #end of observe function.
}
shinyApp(ui, server)
#

But this type of data access by state selection is very slow and in my real-world problem I have big geometries and I spend several minutes downloading from BQ and for plots finished. There is some approach for increasing the speed of this operation?

Please any help with it?

Thank in advance!

[nirgrahamuk]

@Leprechault

observe({
    
    output$myplot <- renderPlot({

I dont know bigquery but i know shiny.
The code I highlighted is problematic.
I recommend you remove the surrounding observe.
The renderPlot is inherently reactive to its content, the renderplot itself doesnt need redefining each time its dependencies invalidate

[Leprechault]

I removed the observe function and I try to use the fst package with the promise of speeds of multiple GB/s, but when I make:

  # # 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.'

[nirgrahamuk]

I think you need to redefine stands_sel as a reactive rather than a plain function
I don't understand your intentions regarding the use of fst, so I won't comment on that yet.