我实在是忍不住要抱怨了,哪有这样的半成品就给我们用的?RJDBC连TD是没有问题,可以读数据,但是dbWriteTable()写不回去!
dbWriteTable(jdbc.conn, name = "testdf", value =test_df) Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ", : Unable to execute JDBC prepared statement INSERT INTO testdf VALUES(?,?) ([Teradata Database] [TeraJDBC 14.10.00.26] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.)
[2016.1.12 更新]
亲爱的同事写了一个很方便用的r函数来解决这个问题,大家猛戳 -> github
以前的一些...
无奈之下,开始各种搜...除了大家都在抱怨的,有用的大概是两篇。抄过来。
第一篇讲dbWriteTable()的机制。
I have been able to reproduce the problem and look at the sourcecode of
RJDBC. The problem is that in dbWriteTable RJDBC disables autocommit
(and enables it again at the end), creates the table and then tries to
insert into it in the same transaction. Firebird does not allow inserts
into a table that is created in the same transaction.As I see it, there are are two potential solutions:
1) Manually create the table and then use:
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)2) First execute dbWriteTable as normal to create the database (+ get
the error) and then append the data
dbWriteTable(conn, "Tab_0", Tab)
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)Other than that, I suggest you file a bug with the creators of RJDBC
that they should commit after the create table.
兴致冲冲的去试了一下,还是不行。没见过配合这么扭的,我建好了表去append吧,说我的表不存在;去建表吧,又说已经在了。坑爹啊!
dbWriteTable(jdbc.conn, name = "testdf", value =test_df, overwrite =F, row.names=F,append=T) Error in .local(conn, name, value, ...) : Cannot append to a non-existing table `testdf' dbWriteTable(jdbc.conn, name = "testdf", value =test_df, row.names=F,append=T) Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 14.10.00.26] [Error 3803] [SQLState 42S01] Table 'testdf' already exists.)
然后又一阵苦搜,看遍了Teradata JDBC driver的文档之类的,最后觉得唯一靠谱的是这篇:
library(RJDBC) ################ #def functions ################ myinsert <- function(arg1,arg2){ .jcall(ps,"V","setInt",as.integer(1),as.integer(arg1)) .jcall(ps,"V","setString",as.integer(2),arg2) .jcall(ps,"V","addBatch") } MHmakeRandomString <- function(n=1, lenght=12) { randomString <- c(1:n) # initialize vector for (i in 1:n) { randomString[i] <- paste(sample(c(0:9, letters, LETTERS), lenght, replace=TRUE), collapse="") } return(randomString) } ################ #DB Connect ################ .jaddClassPath("/MyPath/terajdbc4.jar") .jaddClassPath("/MyPath/tdgssconfig.jar") drv = JDBC("com.teradata.jdbc.TeraDriver","/MyPath/tdgssconfig.jar","/MyPath/terajdbc4.jar") conn = dbConnect(drv,"jdbc:teradata://MyServer/CHARSET=UTF8,LOG=ERROR,DBS_PORT=1025,TYPE=FASTLOAD,TMODE=TERA,SESSIONS=1","user","password") ################ #main ################ ##gen test data dim = 1000000 i = 1:dim s = MHmakeRandomString(dim,12) ## set up table dbSendUpdate(conn,"drop table foo;") dbSendUpdate(conn,"create table foo (a int, b varchar(100));") #set autocommit false .jcall(conn@jc,"V","setAutoCommit",FALSE) ##prepare ps = .jcall(conn@jc,"Ljava/sql/PreparedStatement;","prepareStatement","insert into foo values(?,?)") #start time ptm <- proc.time() ## batch insert for(n in 1:dim){ myinsert(i[[n]],s[[n]]) } #run time proc.time() - ptm #apply & commit .jcall(ps,"[I","executeBatch") dbCommit(conn) .jcall(ps,"V","close") .jcall(conn@jc,"V","setAutoCommit",TRUE) #get some sample results dbGetQuery(conn,"select top 100 * from foo") dbGetQuery(conn,"select count(*) from foo") #disconnect dbDisconnect(conn)
等心情好的时候我去试试....大坑的感觉 T_T ODBC用起来怎么就没有那么多事儿呢?
update Jan 13 2015
居然teradataR可以用...
http://enoriver.net/index.php/2014/12/15/i-wrote-an-r-data-frame-to-a-teradata-table-on-a-mac/
Here's how I did it:
- On a new Mac running Mavericks and R 3.1.2 with
devtools
, I installed Java for Mac.- I installed the RJDBC package from CRAN (which depends on the DBI package also from CRAN) and the teradataR package from GitHub.
- I downloaded the Teradata JDBC driver, unpacked it, and moved
tdgssconfig.jar
andterajdbc4.jar
to/System/Library/Java/Extensions
.After that, writing the data frame
foo
to the tableDATABASE.BAR
was as simple as:
conn <- teradataR::tdConnect(dsn='datamart.mycompany.com',
uid='user',pwd='pass',
dType='jdbc')
teradataR::tdWriteTable(databasename='DATABASE',
tablename='BAR', df=foo)
teradataR::tdClose(conn)
I had to do this because
DBI::dbWriteTable()
now fails on Teradata as explained here.
One reply on “坑爹的RJDBC和Teradata”
我也被卡在这儿了~dbwritetable()向存在的表追加数据时总提示表已存在的错误。