Writing data to Dremio via R

Dremio University
1

Hello,

after I manged to connect to Dremio via R I came to the conclusion that i dislike the RODBC packe and read here and there (https:/ /cojamalo.github.io/macOS_Dremio_R/guide.html) to get it working with the odbc package, which is nicer.

So fa so good, i managed to read (select * from . . .) and write (INSERT INTO . . . VALUES (21);), but i cant write my own data.frame in a prepared statement.
Here is an MWE with changed DB name:

dremio_host <- "<Host>"
dremio_port <- "32010"
dremio_uid <- "<User>"
dremio_pwd <- "<Password>"

data <- data.frame(
  a = "AA",
  b = as.Date("01.02.2020", format = "%d.%m.%Y"),
  c = 500.1,
)

db.connection <- DBI::dbConnect(odbc::odbc(),
                                driver = "Arrow Flight SQL ODBC Driver",
                                uid = dremio_uid,
                                pwd = dremio_pwd,
                                host = dremio_host,
                                port = dremio_port,
                                AuthenticationType = "Basic Authentication",
                                ConnectionType = "Direct"
)

# CREATE TABLE "name" (
#   a varchar(20),
#   b DATE,
#   c DOUBLE,
# );

db.query <- 'INSERT INTO "name"
  (a, b, c, d) VALUES
  (?, TO_DATE(?, \'YYYY-MM-DD\'), ?);'

result <- odbc::dbGetQuery(
  conn = db.connection,
  statement = db.query,
  params = data
)

result <- odbc::dbDisconnect(conn = db.connection)

which leads to an Error:
Error: nanodbc/nanodbc.cpp:1509: HY000: [Apache Arrow][Dremio Server] (100) Flight returned invalid argument error, with message: Illegal use of dynamic parameter

Can you provide an example as to how i can write my R data.frame via the odbc-package in Dremio?

2

I get a similar error message using the JDBC driver:

  Unable to create JDBC prepared statement
  JDBC ERROR: while preparing SQL: INSERT INTO dremio.mtcars VALUES(?,?,?,?,?,?,?,?,?,?,?)
3

@Someone894 @nachti Looks like this needs support for parameterized prepare statement which is currently not yet supported, currently in the roadmap but no ETA