dbWriteTable with Impala backend unable to write to table in database

[dhaycraft]

Issue Description

dbWriteTable() returns error when attempting to write out data using Impala backend. An interesting error occurs as it appears the parameter placeholder, ?, is not recognized.

Ideally I would like write my dataset from R into my database using the dbWriteTable command to write a table into the database dbo with the table name test_car.

Database

con
<OdbcConnection> User@Impala
 Database: IMPALA
 Impala Version: 2.11.0-cdh5.14.0

Example

I've tried several variations of the dbWriteTable command trying to create a new table called test_cars from the mtcars dataset in the dbo database but none have worked. Surprisingly though I am able to use dbCreateTable successfully to create an empty table with column names from my dataset. See what I've tried below:

library(odbc)
library(implyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc::odbc(),
                        Driver = "impala", 
                        Host = "host",
                        Port = "21050")

#con
#<OdbcConnection> User@Impala
# Database: IMPALA
# Impala Version: 2.11.0-cdh5.14.0


> table_id = DBI::Id(schema = "dbo", name = "test_cars")

> dbWriteTable(  con, name=table_id, as.data.frame(data(mtcars)))
Error: Can't unquote `dbo`.`test_cars`

> dbWriteTable(  con, name=SQL("dbo.test_cars"), as.data.frame(data(mtcars)))
Error: Can't unquote dbo.test_cars


> dbWriteTable(  con, name="dbo.test_cars", mtcars)
Error: <SQL> 'CREATE TABLE `dbo.test_cars` (
  `row_names` STRING,
  `mpg` DOUBLE,
  `cyl` DOUBLE,
  `disp` DOUBLE,
  `hp` DOUBLE,
  `drat` DOUBLE,
  `wt` DOUBLE,
  `qsec` DOUBLE,
  `vs` DOUBLE,
  `am` DOUBLE,
  `gear` DOUBLE,
  `carb` DOUBLE
)
'
  nanodbc/nanodbc.cpp:1587: 42000: [Cloudera][ImpalaODBC] (360) Syntax error occurred during query execution: [HY000] : AnalysisException: Invalid table/view name: dbo.test_cars

#The following command creates an empty table with the appropriate columns.

> dbCreateTable(  con, SQL("dbo.test_cars"), mtcars)

#There are multiple databases in the system
#Was able to switch to appropriate database and now getting a different error

#As suggested switch database first and then try dbWriteTable

> dbSendQuery(con, paste("use dbo"))
<OdbcResult>
  SQL  use dbo
  ROWS Fetched: 0 [complete]
       Changed: 0

> dbWriteTable(con, name="test_cars", mtcars, overwrite=TRUE)
Error: <SQL> 'INSERT INTO `test_cars` (`row_names`, `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
  nanodbc/nanodbc.cpp:1587: 42000: [Cloudera][ImpalaODBC] (360) Syntax error occurred during query execution: [HY000] : AnalysisException: Syntax error in line 2:
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ^
Encountered: Unexpected character
Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER

CAUSED BY: Exception: Syntax error

In addition: Warning message:
In new_result(connection@ptr, statement) : Cancelling previous query

> head(mtcars)
                   mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

[Leo-Lee15]

I've also met with the same problem. I would really appreciate it if this bug could be solved ASAP. Thanks very much.