I'm trying to write a table in one my databases using dbWriteTable but have been running into a variety of problems. It may be due to the schema or the backend connection to Impala I'm not entirely sure.
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)
impala_con <- dbConnect(odbc::odbc(),
Driver = "impala",
Host = "host",
Port = "21050")
#impala_con
#<OdbcConnection> User@Impala
# Database: IMPALA
# Impala Version: 2.11.0-cdh5.14.0
> table_id = DBI::Id(schema = "dbo", name = "test_cars")
> dbWriteTable( impala_con, name=table_id, as.data.frame(data(mtcars)))
Error: Can't unquote `dbo`.`test_cars`
> dbWriteTable( impala_con, name=SQL("dbo.test_cars"), as.data.frame(data(mtcars)))
Error: Can't unquote dbo.test_cars
> dbWriteTable( impala_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( impala_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(impala_con, paste("use dbo"))
<OdbcResult>
SQL use dbo
ROWS Fetched: 0 [complete]
Changed: 0
> dbWriteTable(impala_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
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.
