Description
'DBI' Compliant Database Access Using 'ADBC'.
Description
In order to make Arrow Database Connectivity ('ADBC' <https://arrow.apache.org/adbc/>) accessible from R, an interface compliant with the 'DBI' package is provided, using driver back-ends that are implemented in the 'adbcdrivermanager' framework. This enables interacting with database systems using the Arrow data format, thereby offering an efficient alternative to 'ODBC' for analytical applications.
README.md
adbi
Bringing arrow-adbc to R via DBI, adbi aims to provide DBI-compliant database access.
Installation
You can install the development version of adbi from GitHub with:
# install.packages("devtools")
devtools::install_github("r-dbi/adbi")
Example
The data.frame API of DBI is supported.
library(DBI)
# Create an SQLite connection using the adbcsqlite backend
con <- dbConnect(adbi::adbi("adbcsqlite"), uri = ":memory:")
# Write a table
dbWriteTable(con, "swiss", datasets::swiss)
# Query it
dbGetQuery(con, "SELECT * from swiss WHERE Agriculture < 40")
#> Fertility Agriculture Examination Education Catholic Infant.Mortality
#> 1 80.2 17.0 15 12 9.96 22.2
#> 2 92.5 39.7 5 5 93.40 20.2
#> 3 85.8 36.5 12 7 33.77 20.3
#> 4 76.1 35.3 9 7 90.57 26.6
#> 5 71.7 34.0 17 8 3.30 20.0
#> 6 55.7 19.4 26 28 12.11 20.2
#> 7 54.3 15.2 31 20 2.15 10.8
#> 8 58.3 26.8 25 19 18.46 20.9
#> 9 70.4 38.4 26 12 5.62 20.3
#> 10 65.7 7.7 29 11 13.79 20.5
#> 11 72.7 16.7 22 13 11.22 18.9
#> 12 64.4 17.6 35 32 16.92 23.0
#> 13 77.6 37.6 15 7 4.97 20.0
#> 14 67.6 18.7 25 7 8.65 19.5
#> 15 35.0 1.2 37 53 42.34 18.0
#> 16 42.8 27.7 22 29 58.33 19.3
# Prepared statements
res <- dbSendQuery(con, "SELECT * from swiss WHERE Agriculture < ?")
dbBind(res, 30)
dbFetch(res)
#> Fertility Agriculture Examination Education Catholic Infant.Mortality
#> 1 80.2 17.0 15 12 9.96 22.2
#> 2 55.7 19.4 26 28 12.11 20.2
#> 3 54.3 15.2 31 20 2.15 10.8
#> 4 58.3 26.8 25 19 18.46 20.9
#> 5 65.7 7.7 29 11 13.79 20.5
#> 6 72.7 16.7 22 13 11.22 18.9
#> 7 64.4 17.6 35 32 16.92 23.0
#> 8 67.6 18.7 25 7 8.65 19.5
#> 9 35.0 1.2 37 53 42.34 18.0
#> 10 42.8 27.7 22 29 58.33 19.3
dbBind(res, 20)
dbFetch(res)
#> Fertility Agriculture Examination Education Catholic Infant.Mortality
#> 1 80.2 17.0 15 12 9.96 22.2
#> 2 55.7 19.4 26 28 12.11 20.2
#> 3 54.3 15.2 31 20 2.15 10.8
#> 4 65.7 7.7 29 11 13.79 20.5
#> 5 72.7 16.7 22 13 11.22 18.9
#> 6 64.4 17.6 35 32 16.92 23.0
#> 7 67.6 18.7 25 7 8.65 19.5
#> 8 35.0 1.2 37 53 42.34 18.0
# Cleanup
dbClearResult(res)
More interestingly, the recent arrow-extension API of DBI is supported as well.
# Queries
dbGetQueryArrow(con, "SELECT * from swiss WHERE Agriculture < 40")
#> <nanoarrow_array_stream struct<Fertility: double, Agriculture: double, Examination: int64, Education: int64, Catholic: double, Infant.Mortality: double>>
#> $ get_schema:function ()
#> $ get_next :function (schema = x$get_schema(), validate = TRUE)
#> $ release :function ()
# Prepared statements
res <- dbSendQueryArrow(con, "SELECT * from swiss WHERE Agriculture < ?")
dbBind(res, 30)
ret <- dbFetchArrow(res)
ret$length
#> NULL
dbBind(res, 20)
# Chunked fetches
while (!dbHasCompleted(res)) {
ret <- dbFetchArrowChunk(res)
message("fetched ", ret$length, " rows")
}
#> fetched 8 rows
#> fetched 0 rows
# Cleanup
dbClearResult(res)
dbDisconnect(con)