r-dbi
Unable to get dplyr to work with bigrquery
I am unable to get dplyr to work with bigrquery. The DBI interface works well, but when I try to use dplyr::tbl, I get an error. Running show_query() on the tbl suggests that the dataset name is not prepended to the table name. Wondering if that might be the issue.
# Load packages
library(bigrquery)
library(dplyr)
# Connect to bigquery
bq_auth(path = Sys.getenv("SERVICE_TOKEN_JSON"))
con <- dbConnect(
bigquery(),
project = "publicdata",
dataset = "samples",
billing = Sys.getenv("BILLING_PROJECT_ID")
)
DBI::dbReadTable(con, 'natality', n_max = 5)
#> # A tibble: 5 × 31
#> source_year year month day wday state is_male child_race weight_pounds
#> <int> <int> <int> <int> <int> <chr> <lgl> <int> <dbl>
#> 1 1969 1969 11 29 NA KS TRUE 1 6.00
#> 2 1969 1969 2 6 NA MN FALSE 1 8.94
#> 3 1970 1970 9 4 NA LA TRUE 1 7.13
#> 4 1970 1970 1 24 NA NC FALSE 1 7.63
#> 5 1970 1970 6 6 NA SC TRUE 2 9.00
#> # … with 22 more variables: plurality <int>, apgar_1min <int>,
#> # apgar_5min <int>, mother_residence_state <chr>, mother_race <int>,
#> # mother_age <int>, gestation_weeks <int>, lmp <chr>, mother_married <lgl>,
#> # mother_birth_state <chr>, cigarette_use <lgl>, cigarettes_per_day <int>,
#> # alcohol_use <lgl>, drinks_per_week <int>, weight_gain_pounds <int>,
#> # born_alive_alive <int>, born_alive_dead <int>, born_dead <int>,
#> # ever_born <int>, father_race <int>, father_age <int>, record_weight <int>
tbl(con, 'natality') %>%
head()
#> Warning: <BigQueryConnection> uses an old dbplyr interface
#> ℹ Please install a newer version of the package or contact the maintainer
#> This warning is displayed once every 8 hours.
#> Error in UseMethod("op_can_download"): no applicable method for 'op_can_download' applied to an object of class "NULL"
Are you using the dev version of dbplyr? An new dbplyr release may come around soon, not sure if new dbplyr can be made compatible with bigrquery or if bigrquery really needs an update.
@krlmlr I thought I was running the CRAN version of dbplyr, but I was wrong. Switching to the CRAN version 2.1.1 resolved the issue. It would be great if the new version of dbplyr can work with bigrquery. I am happy to contribute PRs if you can provide me with any pointers on what dbplyr changes might be breaking bigrquery.
@ramnathv came here to report that bigrquery does not work with newest version of dbplyr 2.2.0. latest version I can confirm it working with is dbplyr V 2.1.1
How do I revert to dbplyr v2.11?
My session_info() shows that v2.2.0 is still loaded with a message next to it that says "(on disk v.2.1.1)" after installing v2.1.1 with install_version().
I had some luck getting an older version of dbplyr in R.
I uninstalled the new version, then downloaded from the archive the 2.1.1 version (HERE).
Then in R, you can run: install.packages("~/Downloads/dbplyr_2.1.1.tar", repos = NULL, type = "source").
Same problem (warnings below). Reverting to dbplyr 2.1.1 did the trick. Hoping for a fix soon and thanks for all the work on these amazing packages 🖖
Warnings:
#> Warning: <BigQueryConnection> uses an old dbplyr interface
#> ℹ Please install a newer version of the package or contact the maintainer
#> This warning is displayed once every 8 hours.
#> Error in UseMethod("op_can_download"): no applicable method for 'op_can_download' applied to an object of class "NULL"
Session Info:
> sessionInfo()
R version 4.2.0 (2022-04-22)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.04.4 LTS
Matrix products: default
BLAS: /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.9.0
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.9.0
locale:
[1] LC_CTYPE=C.UTF-8 LC_NUMERIC=C LC_TIME=C.UTF-8 LC_COLLATE=C.UTF-8
[5] LC_MONETARY=C.UTF-8 LC_MESSAGES=C.UTF-8 LC_PAPER=C.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] stats graphics grDevices datasets utils methods base
other attached packages:
[1] plotly_4.10.0 DT_0.23
[3] thematic_0.1.2.1 fresh_0.2.0
[5] shinyWidgets_0.7.0 waiter_0.2.5
[7] bs4Dash_2.1.0 htmltools_0.5.2
[9] brConnectR_0.5.4.0.0.0.1653514198 glue_1.6.2
[11] rlang_1.0.2 forcats_0.5.1
[13] stringr_1.4.0 dplyr_1.0.9
[15] purrr_0.3.4 readr_2.1.2
[17] tidyr_1.2.0 tibble_3.1.7
[19] ggplot2_3.3.6 tidyverse_1.3.1
[21] pins_1.0.1 rsconnect_0.8.26
[23] shiny_1.7.1
loaded via a namespace (and not attached):
[1] fs_1.5.2 usethis_2.1.6 fontawesome_0.2.2 lubridate_1.8.0 bigrquery_1.4.0
[6] bit64_4.0.5 progress_1.2.2 httr_1.4.3 tools_4.2.0 backports_1.4.1
[11] bslib_0.3.1 utf8_1.2.2 R6_2.5.1 DBI_1.1.2 lazyeval_0.2.2
[16] colorspace_2.0-3 withr_2.5.0 prettyunits_1.1.1 tidyselect_1.1.2 sodium_1.2.0
[21] bit_4.0.4 curl_4.3.2 compiler_4.2.0 cli_3.3.0 rvest_1.0.2
[26] xml2_1.3.3 sass_0.4.1 scales_1.2.0 askpass_1.1 rappdirs_0.3.3
[31] digest_0.6.29 pkgconfig_2.0.3 dbplyr_2.2.0 fastmap_1.1.0 htmlwidgets_1.5.4
[36] readxl_1.4.0 rstudioapi_0.13 jquerylib_0.1.4 generics_0.1.2 jsonlite_1.8.0
[41] crosstalk_1.2.0 magrittr_2.0.3 Rcpp_1.0.8.3 munsell_0.5.0 fansi_1.0.3
[46] lifecycle_1.0.1 stringi_1.7.6 yaml_2.3.5 blob_1.2.3 grid_4.2.0
[51] promises_1.2.0.1 crayon_1.5.1 haven_2.5.0 hms_1.1.1 knitr_1.39
[56] pillar_1.7.0 reprex_2.0.1 packrat_0.8.0 data.table_1.14.2 renv_0.15.5
[61] modelr_0.1.8 vctrs_0.4.1 tzdb_0.3.0 httpuv_1.6.5 cellranger_1.1.0
[66] gtable_0.3.0 openssl_2.0.2 assertthat_0.2.1 cachem_1.0.6 xfun_0.31
[71] mime_0.12 xtable_1.8-4 broom_0.8.0 later_1.3.0 viridisLite_0.4.0
[76] gargle_1.2.0 ellipsis_0.3.2
I am going to take a shot at making bigrquery compatible with the latest dbplyr release, since it comes with some really cool features (e.g. support for cut). I don't have a clear timeline on this yet, but will update once I figure that out.