as.character(column_name) translates to CAST(column_name as TEXT) for BigQuery

[edgararuiz-zz]

Posted by: @mfalcioni1

Casting to string in BigQuery uses CAST(column_name as STRING). When R translates as.character(column_name) it writes it as CAST(column_name as TEXT) which fails.

> library(dbplyr)
> sessionInfo()
R version 3.5.1 (2018-07-02)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.4 LTS

Matrix products: default
BLAS: /usr/lib/libblas/libblas.so.3.6.0
LAPACK: /usr/lib/lapack/liblapack.so.3.6.0

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] dbplyr_1.2.2

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.18     dplyr_0.7.6      assertthat_0.2.0 R6_2.2.2        
 [5] DBI_1.0.0        magrittr_1.5     pillar_1.2.3     rlang_0.2.2     
 [9] bindrcpp_0.2.2   tools_3.4.4      glue_1.3.0       purrr_0.2.5     
[13] yaml_2.1.19      compiler_3.4.4   pkgconfig_2.0.2  bindr_0.1.1     
[17] tidyselect_0.2.4 tibble_1.4.2

Example:

project <- "project-name"
billing <- "billing-name"
con <- dbConnect(
  bigrquery::bigquery(),
  project = project,
  billing = billing
)
# a date dimensional table
date_dim <- tbl(con, "project.schema.table")
## illustrative of date_dim
#date_dim <- data.frame(date_key = c(20180101:20180131), fiscal_month_name = rep("JAN", 31))

date_dim %>%
  select(DATE_KEY, FISCAL_MONTH_NAME) %>%
  mutate(date_key_str = as.character(DATE_KEY)) %>%
  show_query()

<SQL>
SELECT `DATE_KEY`, `FISCAL_MONTH_NAME`, CAST(`DATE_KEY` AS TEXT) AS `date_key_str`
FROM (SELECT `DATE_KEY`, `FISCAL_MONTH_NAME`
FROM `project.schema.table`) `vnzsigtmhn`

date_dim %>%
  select(DATE_KEY, FISCAL_MONTH_NAME) %>%
  mutate(date_key_str = as.character(DATE_KEY)) %>%
  collect()

Attempting to collect then produces this error:

Error: Type not found: TEXT at [2:66] [invalidQuery]

Unfortunately hard-coding the CAST into the mutate does not work either.

> date_dim %>%
+   select(DATE_KEY, FISCAL_MONTH_NAME) %>%
+   mutate(date_key_str = CAST(DATE_KEY as STRING))
Error: unexpected symbol in:
"  select(DATE_KEY, FISCAL_MONTH_NAME) %>%
  mutate(date_key_str = CAST(DATE_KEY as"

EDIT: Original script was running on an old version of R, I updated and the issue still persists.

[edgararuiz-zz]

The change would have to be made here:

bigrquery/R/dplyr.R

Line 154 in a3ef603

# Coercion