r语言 - 在 BQ 中使用带有 WHERE 语句的表属性进行选择

Question

我想使用查询在CF_2021表中提取所有列，如果ID_UNIQUE = "FIGUEIRAS040C" 没有成功。就我而言：

# First read the table inside the Big Query:
library(tidyverse)
library(bigrquery)
library(DBI)
require(googleAuthR)
bq_auth(path = path)
projectid<-'fc-vm-v1'
datasetid2<-'stands_ROI_2021'
bq_conn_stands <-  dbConnect(bigquery(), 
project = projectid,
dataset = datasetid2, 
use_legacy_sql = FALSE
)
stands_bq <- dplyr::tbl(bq_conn_stands, 
"CF_2021") 

glimpse(stands_bq)
Rows: ??
Columns: 18
Database: BigQueryConnection
$ CD_USO_SOL <dbl> 223279, 224259, 224432, 239080, 233853, 218146, 219544, 236743, 223827, 219563, 183728, 220320, 219~
$ ID_REGIAO  <dbl> 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11,~
$ ID_PROJETO <chr> "001", "001", "001", "001", "001", "002", "002", "002", "002", "002", "002", "002", "002", "002", "~
$ PROJETO    <chr> "FIGUEIRAS", "FIGUEIRAS", "FIGUEIRAS", "FIGUEIRAS", "FIGUEIRAS", "BARBA NEGRA", "BARBA NEGRA", "BAR~
$ CD_TALHAO  <chr> "003B", "005A", "009A", "040C", "010D", "276B", "132A", "115I", "422F", "325C", "282E", "311H", "28~
$ CARACTERIS <chr> "Plantio Comercial", "Plantio Comercial", "Plantio Comercial", "Plantio Comercial", "Plantio Comerc~
$ CARACTER_1 <chr> "Produtivo", "Produtivo", "Produtivo", "Produtivo", "Produtivo", "Produtivo", "Produtivo", "Produti~
$ CICLO      <int> 3, 2, 2, 3, 3, 3, 2, 4, 2, 2, 2, 3, 3, 3, 3, 4, 3, 3, 3, 3, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 2, ~
$ ROTACAO    <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ~
$ DATA_PLANT <chr> "2019/08/10", "2019/08/12", "2019/08/27", "2019/09/15", "2019/12/02", "2019/04/22", "2019/05/16", "~
$ LOCALIDADE <chr> "PANTANO GRANDE", "PANTANO GRANDE", "PANTANO GRANDE", "PANTANO GRANDE", "PANTANO GRANDE", "BARRA DO~
$ ESPACAMENT <chr> "3.5x2.14", "3.5x2.14", "3.5x2.14", "3.5x2.14", "3.00x2.50", "6x1.25", "6x1.5", "4x1.85", "3.5x2", ~
$ ESPECIE    <chr> "GLOBULUS X UROPHYLLA", "DUNNI", "UROPHYLLA", "UROPHYLLA", "SALIGNA", "SALIGNA", "SALIGNA", "SALIGN~
$ SISTEMA_PR <chr> "SEMENTE - EUCALIPTO", "SEMENTE - EUCALIPTO", "SEMENTE - EUCALIPTO", "MACRO ESTACA - EUCALIPTO", "M~
$ VLR_AREA   <dbl> 4.78, 35.68, 19.31, 2.32, 0.74, 33.44, 0.58, 2.34, 5.63, 1.04, 0.05, 3.81, 11.66, 6.52, 4.42, 3.56,~
$ ID_UNIQUE  <chr> "FIGUEIRAS003B", "FIGUEIRAS005A", "FIGUEIRAS009A", "FIGUEIRAS040C", "FIGUEIRAS010D", "BARBANEGRA276~
$ IDADE      <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, ~
$ geom       <chr> "MULTIPOLYGON (((-52.2267 -30.36209, -52.22656 -30.36222, -52.2264 -30.36237, -52.22636 -30.36248, ~

现在，在stands_bq中，我想选择所有列，但仅在ID_UNIQUE中使用"FIGUEIRAS040C"WHERE：

sqlInput <- paste("SELECT * FROM CF_2021 WHERE ID_UNIQUE = ' FIGUEIRAS040C '")
sqlInput
#[1] "SELECT * FROM CF_2021 WHERE ID_UNIQUE = ' FIGUEIRAS040C '"
stands_sel <-
dbGetQuery(bq_conn_stands, as.character(sqlInput), stringsAsFactors = T)
#Complete
#Billed: 0 B
stands_sel
# A tibble: 0 x 18
# ... with 18 variables: CD_USO_SOL <dbl>, ID_REGIAO <dbl>, ID_PROJETO <chr>, PROJETO <chr>, CD_TALHAO <chr>,
#   CARACTERIS <chr>, CARACTER_1 <chr>, CICLO <int64>, ROTACAO <int64>, DATA_PLANT <chr>, LOCALIDADE <chr>,
#   ESPACAMENT <chr>, ESPECIE <chr>, SISTEMA_PR <chr>, VLR_AREA <dbl>, ID_UNIQUE <chr>, IDADE <dbl>, geom <chr> 

但stands_sel对象为空。请，有什么帮助来解决它吗？

Answer 1

您可以使用REGEXP_REPLACE[1] 删除 BigQuery 中的空格，或者如果您想在 r 中删除它，您可以尝试 trimws()[2]。所以这样你就没有空间了FIGUEIRAS040C.

REGEXP_REPLACE在下面的查询中的工作方式是删除值FIGUEIRAS040C中的空格，以便输出看起来像FIGUEIRAS040C。

SELECT REGEXP_REPLACE(' FIGUEIRAS040C ', ' ', '')

下面是在 where 子句中选择它的一些参考。

SELECT DATA.TEXT
FROM(
SELECT 'FIGUEIRAS040C' AS TEXT   
) AS DATA
WHERE DATA.TEXT=(SELECT REGEXP_REPLACE((' FIGUEIRAS040C '), ' ', ''))

[1]https://cloud.google.com/bigquery/docs/reference/legacy-sql#正则表达式函数 [2]https://study.com/academy/lesson/removing-space-from-string-in-r-programming.html