search

duckdb / duckdb-r

The duckdb R package
https://r.duckdb.org/
Other
104 stars 21 forks source link

dplyr::tbl(conn, I("schema.table")) is not working #189

Open ablack3 opened 1 week ago

ablack3 commented 1 week ago

dbplyr is recommending the use of I("schema.table") to refer to tables in schemas but I think duckdb is missing some methods to make this work.

https://dbplyr.tidyverse.org/news/index.html#dbplyr-250

image 
conn <- DBI::dbConnect(duckdb::duckdb())

DBI::dbWriteTable(conn, DBI::Id(schema = "main", table = "cars"), cars)

dplyr::tbl(conn, I("main.cars"))
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbExistsTable' for signature '"duckdb_connection", "AsIs"'

DBI::dbDisconnect(conn, shutdown = TRUE)

sessionInfo()
#> R version 4.3.1 (2023-06-16)
#> Platform: aarch64-apple-darwin20 (64-bit)
#> Running under: macOS Sonoma 14.0
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRblas.0.dylib 
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> time zone: Europe/Amsterdam
#> tzcode source: internal
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] vctrs_0.6.5       cli_3.6.3         knitr_1.47        rlang_1.1.4      
#>  [5] xfun_0.45         DBI_1.2.3         purrr_1.0.2       styler_1.10.3    
#>  [9] generics_0.1.3    glue_1.7.0        htmltools_0.5.8.1 fansi_1.0.6      
#> [13] rmarkdown_2.27    R.cache_0.16.0    tibble_3.2.1      evaluate_0.24.0  
#> [17] fastmap_1.2.0     yaml_2.3.8        lifecycle_1.0.4   duckdb_1.0.0.9000
#> [21] compiler_4.3.1    dplyr_1.1.4       fs_1.6.4          pkgconfig_2.0.3  
#> [25] rstudioapi_0.16.0 R.oo_1.26.0       R.utils_2.12.3    digest_0.6.35    
#> [29] R6_2.5.1          tidyselect_1.2.1  utf8_1.2.4        reprex_2.1.0     
#> [33] pillar_1.9.0      magrittr_2.0.3    R.methodsS3_1.8.2 tools_4.3.1      
#> [37] withr_3.0.0

Created on 2024-06-28 with reprex v2.1.0

ablack3 commented 1 week ago

removing I() works fine. But the use case here is to have cross platform DBI code that runs the same way on different databases including duckdb.

conn <- DBI::dbConnect(duckdb::duckdb())

DBI::dbExecute(conn, "create schema new;")
#> [1] 0
DBI::dbWriteTable(conn, DBI::Id(schema = "new", table = "iris"), iris, overwrite = T)
dplyr::tbl(conn, I("new.iris"))
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbExistsTable' for signature '"duckdb_connection", "AsIs"'
dplyr::tbl(conn, "new.iris")
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB v1.0.0 [root@Darwin 23.0.0:R 4.3.1/:memory:]
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>           <dbl>       <dbl>        <dbl>       <dbl> <fct>  
#>  1          5.1         3.5          1.4         0.2 setosa 
#>  2          4.9         3            1.4         0.2 setosa 
#>  3          4.7         3.2          1.3         0.2 setosa 
#>  4          4.6         3.1          1.5         0.2 setosa 
#>  5          5           3.6          1.4         0.2 setosa 
#>  6          5.4         3.9          1.7         0.4 setosa 
#>  7          4.6         3.4          1.4         0.3 setosa 
#>  8          5           3.4          1.5         0.2 setosa 
#>  9          4.4         2.9          1.4         0.2 setosa 
#> 10          4.9         3.1          1.5         0.1 setosa 
#> # ℹ more rows

DBI::dbDisconnect(conn, shutdown = TRUE)

sessionInfo()
#> R version 4.3.1 (2023-06-16)
#> Platform: aarch64-apple-darwin20 (64-bit)
#> Running under: macOS Sonoma 14.0
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRblas.0.dylib 
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> time zone: Europe/Amsterdam
#> tzcode source: internal
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] vctrs_0.6.5       cli_3.6.3         knitr_1.47        rlang_1.1.4      
#>  [5] xfun_0.45         DBI_1.2.3         purrr_1.0.2       styler_1.10.3    
#>  [9] generics_0.1.3    glue_1.7.0        dbplyr_2.5.0      htmltools_0.5.8.1
#> [13] fansi_1.0.6       rmarkdown_2.27    R.cache_0.16.0    tibble_3.2.1     
#> [17] evaluate_0.24.0   fastmap_1.2.0     yaml_2.3.8        lifecycle_1.0.4  
#> [21] duckdb_1.0.0      compiler_4.3.1    dplyr_1.1.4       fs_1.6.4         
#> [25] pkgconfig_2.0.3   rstudioapi_0.16.0 R.oo_1.26.0       R.utils_2.12.3   
#> [29] digest_0.6.35     R6_2.5.1          tidyselect_1.2.1  utf8_1.2.4       
#> [33] reprex_2.1.0      pillar_1.9.0      magrittr_2.0.3    R.methodsS3_1.8.2
#> [37] tools_4.3.1       withr_3.0.0

Created on 2024-06-28 with reprex v2.1.0