Cannot write MAP type to DB

[DavZim]

I cannot write a MAP type to a duckdb using DBI::dbAppendTable().

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

DBI::dbExecute(con, "CREATE TABLE tbl (mp MAP(VARCHAR, VARCHAR));")
#> [1] 0
DBI::dbExecute(con, "INSERT INTO tbl VALUES (MAP {'a': 'b'})")
#> [1] 1

DBI::dbGetQuery(con, "DESCRIBE tbl")
#>   column_name           column_type null  key default extra
#> 1          mp MAP(VARCHAR, VARCHAR)  YES <NA>    <NA>  <NA>


DBI::dbReadTable(con, "tbl") |> str()
#> 'data.frame':	1 obs. of  1 variable:
#> $ mp:List of 1
#>  ..$ :'data.frame':	1 obs. of  2 variables:
#>  .. ..$ key  : chr "a"
#>  .. ..$ value: chr "b"

df <- data.frame(
  mp = I(list(data.frame(key = "page", value = "1")))
)
str(df) # same structure as the returned tbl data
#> 'data.frame':	1 obs. of  1 variable:
#>  $ mp:List of 1
#>   ..$ :'data.frame':	1 obs. of  2 variables:
#>   .. ..$ key  : chr "page"
#>   .. ..$ value: chr "1"
#>   ..- attr(*, "class")= chr "AsIs"

DBI::dbAppendTable(con, "tbl", df)
#> Error: rapi_execute: Failed to run query
#> Error: Conversion Error: Unimplemented type for cast (STRUCT("key" VARCHAR, "value" VARCHAR)[] -> MAP(VARCHAR, VARCHAR))

I am using

sapply(c("DBI", "duckdb"), packageVersion)
#> $DBI
#> [1] 1 2 3
#> 
#> $duckdb
#> [1] 1 0 0 1

(1.0.0-2 is being installed at the moment but I didnt see anything related to MAPs in the changelog from 1.0.0-1).
Update, I updated to 1.0.0-2 and I see the same error.

This is closely related to #165 and #61

[krlmlr]

Thanks, confirmed.

I'm currently at a loss what needs to happen here. You can work around with map_from_entries(), see below.

@hannes: do you think duckdb could implement the cast (STRUCT("key" *, "value" *)[] -> MAP(*, *)) directly?

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

DBI::dbExecute(con, "CREATE TABLE tbl (mp MAP(VARCHAR, VARCHAR));")
#> [1] 0
DBI::dbExecute(con, "INSERT INTO tbl VALUES (MAP {'a': 'b'})")
#> [1] 1

DBI::dbGetQuery(con, "DESCRIBE tbl")
#>   column_name           column_type null  key default extra
#> 1          mp MAP(VARCHAR, VARCHAR)  YES <NA>    <NA>  <NA>

df <- DBI::dbReadTable(con, "tbl")
tibble::as_tibble(df)
#> # A tibble: 1 × 1
#>   mp          
#>   <list>      
#> 1 <df [1 × 2]>
df$mp
#> [[1]]
#>   key value
#> 1   a     b

duckdb::duckdb_register(con, "df", df)
DBI::dbExecute(con, "INSERT INTO tbl SELECT map_from_entries(mp) FROM df")
#> [1] 1

df <- DBI::dbReadTable(con, "tbl")
tibble::as_tibble(df)
#> # A tibble: 2 × 1
#>   mp          
#>   <list>      
#> 1 <df [1 × 2]>
#> 2 <df [1 × 2]>
df$mp
#> [[1]]
#>   key value
#> 1   a     b
#> 
#> [[2]]
#>   key value
#> 1   a     b

^{Created on 2024-08-16 with reprex v2.1.0}

[mmyrte]

My intuition on this would have been to use an R list of named vectors to represent maps. This would also be clearly distinguishable from struct columns being data.frame-like objects.

One would have to make sure the named vector is actually coercible to the given map type. It could look like this:

v1 <- c(what = 1L, where = 2L)
v2 <- 8:9
names(v2) <- c("anything", "anyhow")
df <- data.frame(a = c("one", "two"), mp = list(v1, v2))

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

DBI::dbExecute(con, "CREATE TABLE tbl (a VARCHAR, mp MAP(VARCHAR, INTEGER));")

DBI::dbAppendTable(con, "tbl", df)

[krlmlr]

Thanks. Can the key have a type different from "string"?

[mmyrte]

No, that'd have to be cast to the declared map type, erroring out if it's not coercible. What's not possible to reproduce in this structure is a nested type key or value; if you want that, I would probably go for a list(list(key = c(1,2), value = "a")) representation, but that doesn't feel very smooth.

I guess the fantastic amount of data models possible within DuckDB will make it hard to fully match with idiomatic R structures, for example from R's lack of explicit scalars. I'm not sure casting on the database side of things alleviates these problems without causing other unexpected behaviour, but it would help with language-database data model mismatches in other languages besides R.

[mmyrte]

Just as a quick note - I hadn't seen the map_from_entries(STRUCT(k, v)[]) way of building maps. I can live for now with the following construct (sorry, don't have time for a proper reprex, but the crucial lapply is unchanged from what I use).

df <- data.frame(
  normal_col = 1:3,
  to_be_maps = list(
    list(key1 = "value", key5 = "samevalue"),
    NULL,
    list(key = "othervalue")
  )
)

df$to_be_maps <- lapply(df$to_be_maps, function(param_list) {
  if (is.null(param_list)) {
    return(NULL)
  }
  data.frame(
    k = names(param_list),
    v = as.character(unlist(param_list))
  )
})

duckdb::duckdb_register(
  conn = connection,
  "tmp_table",
  df = df
)

# Use INSERT OR REPLACE with MAP conversion for to_be_maps field
DBI::dbExecute(
  connection,
  "
CREATE TABLE target_table (
  normal_col INTEGER, 
  to_be_maps MAP(VARCHAR, VARCHAR)
);
INSERT OR REPLACE INTO target_table
SELECT
  normal_col,
  map_from_entries(to_be_maps) as to_be_maps
FROM tmp_table;
"
)

edit: And obviously I need to convert the key/value data frames back to a list afterwards; if these were named k/v we could get symmetry while making use of map_from_entries.

duckdb-r/src/transform.cpp

Lines 623 to 624 in dd5bb38

	dest_list.push_back(cpp11::named_arg("key") = std::move(key_sexp));
	dest_list.push_back(cpp11::named_arg("value") = std::move(value_sexp));