New array data type in version 0.10.0 can't be returned to R

[BorgeJorge]

con <- dbConnect(duckdb())
dbGetQuery(con, "select array_value(1,2,3)")
#> Error: rapi_prepare: Unknown column type for prepare: INTEGER[3]

[krlmlr]

Indeed. I had to remove those values from tests_all_types() in 19c9533.

[DavZim]

Hi all,
is there something that I can do about this?
We oftentimes use DuckDB as a vector store for Embeddings, and as the name suggests, this involves vectors/arrays.
At the moment we construct the query and insert the values into the query, which is not optimal.
Thanks

[t-kalinowski]

I also just ran into this. Support for ARRAY types would be very helpful.

[christophsax]

We also run into this. It works for lists but not for arrays. Thanks a lot! cc @schochastics

library(duckdb)
#> Loading required package: DBI
con <- dbConnect(duckdb())

# Does still not work
dbSendQuery(con, "CREATE OR REPLACE TABLE docs_array (text TEXT, embedding FLOAT[3]);")
#> <duckdb_result cbb90 connection=b54e0 statement='CREATE OR REPLACE TABLE docs_array (text TEXT, embedding FLOAT[3]);'>
dplyr::tbl(con, "docs_array")
#> Error in `db_query_fields.DBIConnection()`:
#> ! Can't query fields.
#> ℹ Using SQL: SELECT * FROM (FROM docs_array) q01 WHERE (0 = 1)
#> Caused by error in `dbSendQuery()`:
#> ! rapi_prepare: Unknown column type for prepare: FLOAT[3]

# It works with a list column, but they cannot be used in similarity search
dbSendQuery(con, "CREATE OR REPLACE TABLE docs_list (text TEXT, embedding FLOAT[]);")
#> <duckdb_result a1540 connection=b54e0 statement='CREATE OR REPLACE TABLE docs_list (text TEXT, embedding FLOAT[]);'>
dplyr::tbl(con, "docs_list")
#> # Source:   table<docs_list> [?? x 2]
#> # Database: DuckDB v1.2.1 [root@Darwin 24.3.0:R 4.4.3/:memory:]
#> # ℹ 2 variables: text <chr>, embedding <list>

^{Created on 2025-03-26 with reprex v2.1.1}

[krlmlr]

The data can be stored (if it comes from an external source such as a Parquet file or inserted manually), passed through, and converted to something that the R client can parse today. In the future, I agree that we should return and also consume these columns as a matrix.

library(duckdb)
#> Loading required package: DBI
con <- dbConnect(duckdb())

# Does still not work
dbExecute(con, "CREATE OR REPLACE TABLE docs_array (text TEXT, embedding FLOAT[3]);")
#> [1] 0
dbExecute(con, "INSERT INTO docs_array VALUES ('hello', ARRAY[1.0, 2.0, 3.0]), ('world', ARRAY[4.0, 5.0, 6.0])")
#> [1] 2

dplyr::tbl(con, dplyr::sql("SELECT text, embedding[1] AS embedding_1, embedding[2] AS embedding_2, embedding[3] AS embedding_3 FROM docs_array"))
#> # Source:   SQL [?? x 4]
#> # Database: DuckDB v1.2.1 [kirill@Darwin 24.3.0:R 4.4.2/:memory:]
#>   text  embedding_1 embedding_2 embedding_3
#>   <chr>       <dbl>       <dbl>       <dbl>
#> 1 hello           1           2           3
#> 2 world           4           5           6

^{Created on 2025-03-26 with reprex v2.1.1}

[DavZim]

This is often used with embeddings (from models such as openai's ada-002, which returns 1536 dimensions per entry).

When we convert the values to text first to use a text-based insert statement, we either run into length limits of the query, reduced accuracy because we only use 4-6 digits, or long insert times.

Native support for arrays would be great 😃

[krlmlr]

If insertion is the bottleneck, can you create a Parquet file with the data in the correct format?

[DavZim]

For users that run into the same issue, this is how you can solve this

library(duckdb)
library(DBI)

sim_data <- function(n, k = 1536) {
  res <- data.frame(
    txt = replicate(n, paste(sample(letters, 10, replace = TRUE), collapse = ""))
  )
  res$embeddings <- lapply(seq(n), function(i) rnorm(k))
  res
}

set.seed(3123)
data <- sim_data(3)

con <- dbConnect(duckdb())

dbExecute(con, "CREATE TABLE sim_data (txt STRING, embeddings FLOAT[1536])")

# this wont work, therefore the workaround below
dbAppendTable(con, "sim_data", data)
#> Error: rapi_prepare: Unknown column type for prepare: FLOAT[1536]

tmppq <- tempfile(fileext = ".parquet")
arrow::write_parquet(data, tmppq)
insert_query <- sprintf("INSERT INTO sim_data SELECT * FROM read_parquet('%s')", tmppq)
insert_query
#> [1] "INSERT INTO sim_data SELECT * FROM read_parquet('/tmp/RtmpXzWofM/file3d59e42fa39d86.parquet')"
dbExecute(con, insert_query)
#> [1] 3


dbGetQuery(con, "DESCRIBE sim_data")
#>     column_name column_type null  key default extra
#>   1         txt     VARCHAR  YES <NA>    <NA>  <NA>
#>   2  embeddings FLOAT[1536]  YES <NA>    <NA>  <NA>
  
dbGetQuery(con, "SELECT COUNT(*) AS ct FROM sim_data")
#>   ct
#> 1  3



# similarly, this wont work either, hence the read-via-parquet workaround below
dbGetQuery(con, "SELECT * FROM sim_data")
#> Error: rapi_prepare: Unknown column type for prepare: FLOAT[1536]

outdata <- tempfile(fileext = ".parquet")
write_query <- sprintf("COPY sim_data TO '%s' (FORMAT parquet)", outdata)
write_query
#> [1] "COPY sim_data TO '/tmp/RtmpXzWofM/file3d59e48077184.parquet' (FORMAT parquet)"

dbExecute(con, write_query)
#> [1] 3

res <- arrow::read_parquet(outdata) |> as.data.frame()
res$embeddings <- lapply(res$embeddings, \(x) as.numeric(x[[1]]))

all.equal(data, res, tolerance = 1e-7)
#> [1] TRUE

I also did some benchmarks to get a feeling of how much this extra step would cost.
As expected, writing to the database is a lot more expensive than reading from it

bench_res <- bench::press(
  n = 10^(seq(5)),
  {
    set.seed(124315)
    data <- sim_data(n)
    # use this to read the data
    con_read <- dbConnect(duckdb())
    dbExecute(con_read, "CREATE TABLE sim_data (txt STRING, embeddings FLOAT[1536])")
    
    outdata <- tempfile(fileext = ".parquet")
    write_query <- sprintf("COPY sim_data TO '%s' (FORMAT parquet)", outdata)
    dbExecute(con_read, write_query)
    unlink(outdata)
    
    bench::mark(
      write = {
        # new memory db for each write
        con <- dbConnect(duckdb())
        dbExecute(con, "CREATE TABLE sim_data (txt STRING, embeddings FLOAT[1536])")
        
        outdata <- tempfile(fileext = ".parquet")
        arrow::write_parquet(data, outdata)
        write_query <- sprintf("INSERT INTO sim_data SELECT * FROM read_parquet('%s')", outdata)
        dbExecute(con, write_query)
        unlink(outdata)
      },
      read = {
        outdata <- tempfile(fileext = ".parquet")
        write_query <- sprintf("COPY sim_data TO '%s' (FORMAT parquet)", outdata)
        dbExecute(con_read, write_query)
        res <- arrow::read_parquet(outdata) |> as.data.frame()
        res$embeddings <- lapply(res$embeddings, \(x) as.numeric(x[[1]]))
        unlink(outdata)
      },
      check = FALSE,
      min_iterations = 3
    )
  }
)

bench_res
#> # A tibble: 10 × 14
#>    expression      n      min   median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc
#>    <bch:expr>  <dbl> <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl> <int> <dbl>
#>  1 write          10  68.07ms  69.19ms   14.4      20.89MB     0        8     0
#>  2 read           10  16.33ms  17.48ms   55.1       3.78MB     2.20    25     1
#>  3 write         100 109.26ms 110.19ms    9.08    177.28KB     0        5     0
#>  4 read          100  16.45ms  18.31ms   54.5       8.55KB     2.18    25     1
#>  5 write        1000 279.79ms 282.55ms    3.54    177.28KB     1.77     2     1
#>  6 read         1000   9.57ms  10.76ms   74.5       8.55KB     2.07    36     1
#>  7 write       10000    2.29s    2.33s    0.432   177.48KB     0        3     0
#>  8 read        10000   9.73ms  11.19ms   75.4       8.55KB     2.69    28     1
#>  9 write      100000   21.88s   22.11s    0.0453  177.48KB     0        3     0
#> 10 read       100000   9.79ms  11.15ms   75.7       8.55KB     0       38     0
#> # ℹ 5 more variables: total_time <bch:tm>, result <list>, memory <list>,
#> #   time <list>, gc <list>

ggplot2::autoplot(bench_res)

[krlmlr]

Thanks for looking into this.

I see two ways to represent a FLOAT[x] column in R. What's the better choice for the application?

CC @hannes.

Matrix column with dimensions [? x x]

• space-efficient
• column-major (efficient for comparing the same column across rows)
• a bit unusual

List with a float vector [x] in each element

• row-major (efficient for comparing values within the same row)
• require about 40 bytes more per row, and many more allocations (one SEXP per row)