更快的BigQuery数据在闪亮

Question

我不喜欢访问完整的公共BigQuery数据集(在我的例子中是geo_us_boundaries )。但是我想通过state使用查询glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con)来选择数据。我试着做：

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

但是state选择的这种类型的数据访问非常缓慢，在我的现实世界中，我有很大的几何图形，我花了几分钟从by下载和完成了地块。有什么办法来提高这一行动的速度吗？

能帮个忙吗？

提前感谢！

Answer 1

你确定花在下载数据，而不是渲染数据的时间吗？查询需要几秒钟，即使是几何文本大小(德州)最大的州也只有1.3MB。这不应该花那么多时间下载。

无论如何，一个典型的解决方案是简化状态几何：

SELECT * EXCEPT(state_geom), ST_Simplify(state_geom, 1000) AS state_geom 
FROM states WHERE state = {x}

这使得最重的州(现在的阿拉斯加)小于40 to的大小，应该更快地下载和绘制。

请注意，它每次在查询中执行ST_Simplify计算，这有点昂贵。您可能只想做一次--使用简化的状态几何创建表并从中读取--这也使得查询本身更便宜、更快，因为查询读取的数据较少，而且不必计算简化的几何图形。

CREATE TABLE tmp.states AS
SELECT * EXCEPT(state_geom), ST_Simplify(state_geom, 1000) AS state_geom
FROM  `bigquery-public-data`.geo_us_boundaries.states;