Selection using table attribute with WHERE statment in BQ-CodePudding

I'd like to extract inside my CF_2021 table all the columns if ID_UNIQUE = "FIGUEIRAS040C" using a query without success. In my case:

# 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, ~

Now, In stands_bq, I'd like to select all the columns but just only in"FIGUEIRAS040C" in ID_UNIQUE using 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>

But the stands_selobject is empty. Please, any help to fix it?

CodePudding user response：

You can use REGEXP_REPLACE[1] to remove the spaces in BigQuery, or if you want to remove it in r, you can try trimws()[2]. So in this way you wouldn't have the spaces around FIGUEIRAS040C.

How REGEXP_REPLACE works in the query below is by removing the spaces that are in the value FIGUEIRAS040C so the output looks like FIGUEIRAS040C.

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

Here is some reference to select this in a where clause.

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#regularexpressionfunctions [2]https://study.com/academy/lesson/removing-space-from-string-in-r-programming.html