R using RJDBC write table to Hive

Question

I have successfully connected local R3.1.2( win7 64bit rstudio) and remote hive server using rjdbc,

library(RJDBC)
.jinit()
dir = "E:/xxx/jars/hive/" 
for(l in list.files(dir)) {
  .jaddClassPath(paste( dir ,l,sep="")) }
options( java.parameters = "-Xmx8g" ) 
drv <- JDBC("org.apache.hadoop.hive.jdbc.HiveDriver",
        "E:/xxx/jars/hive/hive-jdbc-0.11.0.jar")

conn <- dbConnect(drv,  "jdbc:hive://10.127.130.162:10002/default", "", "" ) 
dbGetQuery(conn, "select * from test.test limit 10 ")

successfully read data from hive ,but I cannot write R data frame using
dbWriteTable :

data(iris)
dbWriteTable(conn, iris , "test.dc_test")

Error return:

Error in .jcall(md, "Ljava/sql/ResultSet;", "getTables", .jnull("java/lang/String"), : method getTables with signature (Ljava/lang/String;Ljava/lang/String;[Ljava/lang/String;)Ljava/sql/ResultSet; not found

Either my misuse or other methods needed?

Answer 1

I have a partial answer. Your arguments to dbWriteTable are reversed. The pattern is dbWriteTable(connection, tableName, data), the docs read dbWriteTable(conn, name, value, ...). That being said, I don't find that the 'correct' form works either, instead yielding the following error message:

Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate ([Simba][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: ParseException line 1:41 mismatched input 'PRECISION' expecting ) near 'DOUBLE' in create table statement), Query: CREATE TABLE iris (`Sepal.Length` DOUBLE PRECISION,`Sepal.Width` DOUBLE PRECISION,`Petal.Length` DOUBLE PRECISION,`Petal.Width` DOUBLE PRECISION,Species VARCHAR(255)).)

(at least when using Amazon's JDBC driver for Hive). That error at least seems self apparent, the query generated to make the table for data insertion didn't parse correctly in HiveQL. The fix, other than doing it manually, I'm not sure about.