使用RJDBC将数据帧写入Teradata表 [英] Writing a data frame to a Teradata table using RJDBC
问题描述
按照此处所述建立连接后，我试图将非常简单的数据帧(trythis，如下所示)写入到名为p_cia_t的数据库中名为gh_test_20141105的表中.首先，我尝试过
After establishing a connection as explained here, I tried to write a very simple data frame (trythis, shown below) to a table called gh_test_20141105 in a database called p_cia_t. First, I tried
> conn <- getTdConnection(vdm='vivaldi')
> dbWriteTable(conn=conn,name=tbl,value=trythis)
Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ", :
Unable to execute JDBC prepared statement INSERT INTO p_cia_t.gh_test_20141005 VALUES(?,?,?,?) ([Teradata Database] [TeraJDBC 14.10.00.17] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.)
接下来，我在Teradata中创建了一个空表:
Next, I created an empty table in Teradata:
create table p_cia_t.gh_test_20141105 (eenie integer, meenie integer, minie integer, moe integer);
接下来的两次尝试写入此数据帧的尝试均以相反的方式失败了:
My next two attempts to write this data frame failed in contradictory ways:
> dbWriteTable(conn=conn,name=tbl,value=trythis,append=T)
Error in .local(conn, statement, ...) :
execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 14.10.00.17] [Error 3803] [SQLState 42S01] Table 'gh_test_20141105' already exists.)
> dbWriteTable(conn=conn,name=tbl,value=trythis,append=T,overwrite=F)
Error in .local(conn, name, value, ...) :
Cannot append to a non-existing table `p_cia_t.gh_test_20141105'
> trythis
eenie meenie minie moe
1 1 4 7 10
2 2 5 8 11
3 3 6 9 12
> conn
An object of class "JDBCConnection"
Slot "jc":
[1] "Java-Object{com.teradata.jdbc.jdk6.JDK6_SQL_Connection@2f242b11}"
Slot "identifier.quote":
[1] NA
> tbl
[1] "p_cia_t.gh_test_20141105"
为什么append=T反对表已经存在，并且添加overwrite=F如何将反对变为不存在的表"?两者都没有任何意义.
Why would append=T object that a table already exists, and how can adding overwrite=F change the objection to "non-existing table?" Neither makes any sense.
对于将数据帧成功写入Teradata表(无论是否存在)(添加或不添加)的任何可行示例，我将不胜感激.
I would greatly appreciate any working example of successful writing of a data frame to a Teradata table, whether pre-existing or not, with or without appending.
> sessionInfo()
R version 3.1.2 (2014-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RJDBC_0.2-4 rJava_0.9-6 DBI_0.3.1 dplyr_0.3.0.2 scales_0.2.4 ggplot2_1.0.0 reshape2_1.4
[8] RODBC_1.3-10 data.table_1.9.4
loaded via a namespace (and not attached):
[1] assertthat_0.1 chron_2.3-45 colorspace_1.2-4 digest_0.6.4 grid_3.1.2 gtable_0.1.2 magrittr_1.0.1
[8] MASS_7.3-35 munsell_0.4.2 parallel_3.1.2 plyr_1.8.1 proto_0.3-10 Rcpp_0.11.3 stringr_0.6.2
[15] tools_3.1.2
推荐答案
我认为这是由于Teradata的JDBC驱动程序中的错误引起的.如此处所述，原因是
I think this is due to a bug in Teradata's JDBC driver. As explained here, the reason is that
问题在于，在dbWriteTable中，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.
此页似乎提供了一种解决方案，但我认为从根本上讲，我们可能必须等到TD修复错误为止.
This page seems to offer a solution but I think fundamentally we may have to wait until TD fixes the bug...
我的临时解决方案是转向ODBC(小表)或通过system(fastload < your_fastload_script)(大表)直接调用fastload…
My temporary solution is to turn to ODBC (small table) or call fastload directly by system("fastload < your_fastload_script") (big table)…
这篇关于使用RJDBC将数据帧写入Teradata表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
