Error when writing a table to databricks with dbWriteTable()

[emilianomm]

Hi

Trying to automatize the upload of excels files to databricks I encountered an error with dbWriteTable(). I'm using a code very much like this one

library(readxl)
library(DBI)
library(odbc)
library(dplyr)
library(janitor)

# Define the path to the Excel file
file1 <- read_excel("path/to/your/excel1.xlsx")
file2 <- read_excel("path/to/your/excel2.xlsx")

# Read the Excel file
df_final <- bind_rows(
  file1,
  file2
) %>%
  clean_names()

# Define the connection details
access_token <- "your_access_token_here"

# Create a connection string using JDBC
con <- dbConnect(
  odbc::databricks(),
  workspace = "your_workspace_url",
  httpPath = "your_http_path",
  uid = "token",
  pwd = access_token
)

# Error part
dbWriteTable(
  con,
  name = Id(schema = "your_schema", table = "your_table", catalog = "your_catalog"),
  value = df_final,
  append = TRUE
)

But the last R statement returns the following error

[UNBOUND_SQL_PARAMETER] Found the unbound parameter: _1980. Please, fix argsand provide a mapping of the parameter to either a SQL literal or collection constructor functions such asmap(), array(), struct(). SQLSTATE: 42P02; line 2 pos 8

Upon reviewing the monitoring tab on the sql warehouse (within databricks), it show the same error on the job history.

What may be causing this error?

Thanks

[viv-analytics]

Hi @emilianomm,

In the past, setting the option useNativeQuery = FALSE has been effective for me.
You may want to try the following code snippet to establish a connection:

con_db_odbc <- DBI::dbConnect(drv = odbc::databricks(), HTTPPath = Sys.getenv("HTTP_PATH"), useNativeQuery = FALSE)

I hope this information is helpful

[detule]

Thanks @emilianomm !

Closing this issue since this behavior is documented.

[emilianomm]

Appreciate