r - 如何在后端使用 Impala 连接修复 dbWriteTable“错误：无法取消引用 dbo.test_cars”？

Question

我正在尝试使用 dbWriteTable 在我的数据库中编写一个表，但遇到了各种问题。这可能是由于架构或与 Impala 的后端连接，我不完全确定。

我尝试了 dbWriteTable 命令的几种变体，试图从 dbo 数据库中的 mtcars 数据集创建一个名为 test cars 的新表，但没有一个有效。令人惊讶的是，尽管我能够成功使用 dbCreateTable 从我的数据集中创建一个包含列名的空表。看看我在下面尝试了什么：

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

理想情况下，我想使用 dbWriteTable 命令将我的数据集从 R 写入我的数据库，以将表名为 test_car 的表写入数据库 dbo。

标签： rdplyrodbcrodbcr-dbi