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

4

I have tried to achieve the same using both with Arrow ODBC and JDBC drivers but ending up with same error message which is “Illegal use of dynamic parameter”.

Is this feature still not supported or is in the roadmap?

5

@guray.uzun Will check on the status of the feature request and get back to you

1 Like