R w adbi package reading from FlightSQL server - could duckdb type UBIGINT and BIGINT be treated as bit64 in R?

[mskyttner]

After successfully connecting to a FlightSQL sever (gizmo-data/gizmo) - exposing a duckdb v 1.4 database - listing the tables works as does retrieving tables but would it be possible to infer those fields of type UBIGINT or BIGINT as bit64 integers? Currently those become double and it looks like RPostgres and some others have an option to use bit64 integers there.

library(adbi)
library(adbcdrivermanager)
library(adbcflightsql)

con <- 
  adbi::dbConnect(adbi::adbi("adbcflightsql"),
    uri = "grpc://<redacted>:31337/",
    username = "<redacted>",
    password = "<redacted>"
  )

con
#<AdbiConnection>
#  Vendor name: gizmosql
#  Vendor version: duckdb v1.4.0
#  Vendor arrow version: 21.0.0
#  Driver name: ADBC Flight SQL Driver - Go
#  Driver version: (unknown or development build)
#  Driver arrow version: v18.4.1
#  NA: NA

# this works
adbi::dbListTables(con)

# this works
at <- DBI::dbReadTableArrow(con, "publishers")

# at$get_schema()
# nanoarrow::infer_nanoarrow_schema(at)

adbi::dbReadTableArrow(con, "institutions") |> 
  as_tibble()

adbi::dbGetQueryArrow(con, "from institutions limit 5") |> 
  as_tibble()

# this works
dplyr::tbl(con, "topics")

# doesn't work probably because of 64-bit integers (?)
dplyr::tbl(con, "publishers")
#Error in nanoarrow::convert_array_stream(x, to) : 
#  21 value(s) outside integer range set to NA

[paleolimbot]

Thanks for opening!

nanoarrow has an option to explicitly choose the destination ptype when converting to R, including using a function to handle arbitrary input!

library(nanoarrow)

df <- data.frame(x = bit64::as.integer64(2^60) + 1L)
stream <- as_nanoarrow_array_stream(df)
stream$get_schema()
#> <nanoarrow_schema struct>
#>  $ format    : chr "+s"
#>  $ name      : chr ""
#>  $ metadata  : list()
#>  $ flags     : int 0
#>  $ children  :List of 1
#>   ..$ x:<nanoarrow_schema int64>
#>   .. ..$ format    : chr "l"
#>   .. ..$ name      : chr "x"
#>   .. ..$ metadata  : list()
#>   .. ..$ flags     : int 2
#>   .. ..$ children  : list()
#>   .. ..$ dictionary: NULL
#>  $ dictionary: NULL

convert_array_stream(stream)
#> Warning in convert_array_stream(stream): 1 value(s) may have incurred loss of
#> precision in conversion to double()
#>              x
#> 1 1.152922e+18

stream <- as_nanoarrow_array_stream(df)
convert_array_stream(stream, to = data.frame(x = bit64::integer64()))
#>                     x
#> 1 1152921504606846977

stream <- as_nanoarrow_array_stream(df)
convert_array_stream(stream, to = data.frame(x = character()))
#>                     x
#> 1 1152921504606846977

custom <- function(schema, inferred_ptype) {
  for (i in seq_along(schema$children)) {
    if (schema$children[[i]]$format == "l") {
      inferred_ptype[[i]] <- bit64::integer64()
    }
  }
  
  inferred_ptype
}

stream <- as_nanoarrow_array_stream(df)
convert_array_stream(stream, to = custom)
#>                     x
#> 1 1152921504606846977

I forget how or if this was integrated into adbi (I seem to recall there was an option at some point!)