MyNixOS website logo
Description

'DBI' Compliant Database Access Using 'ADBC'.

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.

adbi

rcc Codecov testcoverage CRANstatus

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)
Metadata

Version

0.1.1

License

Unknown

Platforms (75)

    Darwin
    FreeBSD
    Genode
    GHCJS
    Linux
    MMIXware
    NetBSD
    none
    OpenBSD
    Redox
    Solaris
    WASI
    Windows
Show all
  • aarch64-darwin
  • aarch64-genode
  • aarch64-linux
  • aarch64-netbsd
  • aarch64-none
  • aarch64_be-none
  • arm-none
  • armv5tel-linux
  • armv6l-linux
  • armv6l-netbsd
  • armv6l-none
  • armv7a-darwin
  • armv7a-linux
  • armv7a-netbsd
  • armv7l-linux
  • armv7l-netbsd
  • avr-none
  • i686-cygwin
  • i686-darwin
  • i686-freebsd
  • i686-genode
  • i686-linux
  • i686-netbsd
  • i686-none
  • i686-openbsd
  • i686-windows
  • javascript-ghcjs
  • loongarch64-linux
  • m68k-linux
  • m68k-netbsd
  • m68k-none
  • microblaze-linux
  • microblaze-none
  • microblazeel-linux
  • microblazeel-none
  • mips-linux
  • mips-none
  • mips64-linux
  • mips64-none
  • mips64el-linux
  • mipsel-linux
  • mipsel-netbsd
  • mmix-mmixware
  • msp430-none
  • or1k-none
  • powerpc-netbsd
  • powerpc-none
  • powerpc64-linux
  • powerpc64le-linux
  • powerpcle-none
  • riscv32-linux
  • riscv32-netbsd
  • riscv32-none
  • riscv64-linux
  • riscv64-netbsd
  • riscv64-none
  • rx-none
  • s390-linux
  • s390-none
  • s390x-linux
  • s390x-none
  • vc4-none
  • wasm32-wasi
  • wasm64-wasi
  • x86_64-cygwin
  • x86_64-darwin
  • x86_64-freebsd
  • x86_64-genode
  • x86_64-linux
  • x86_64-netbsd
  • x86_64-none
  • x86_64-openbsd
  • x86_64-redox
  • x86_64-solaris
  • x86_64-windows