Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
edgararuiz opened this issue Sep 20, 2018 · 1 comment

Comments

@edgararuiz
Copy link
Contributor

commented Sep 20, 2018

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

This comment has been minimized.

Copy link
Contributor Author

commented Sep 20, 2018

The change would have to be made here:

# Coercion

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.