redshift data.frame没有被写入 [英] redshift data.frame not getting written
问题描述
我能够使用RPostgreSQL软件包连接到AWS redshift集群;读取表,甚至使用SQL-dbGetQuery创建表.但是,如果我尝试按如下方式编写一个data.frame(con是将dbConnect对象转换为redshift,而newdf是一个简单的data.frame
I am able to connect to AWS redshift cluster using RPostgreSQL package; read tables, even create tables using SQL - dbGetQuery. However, if I try to write a data.frame as below (wher con is a dbConnect object into redshift, and newdf if a simple data.frame
dbWriteTable(con,"newtb",newdf)
我收到以下错误:
postgresqlgetResult(new.con)中的错误:RS-DBI驱动程序:(无法 检索结果:错误:不支持LOAD源. (暗示: 仅允许基于S3或DynamoDB的负载)
Error in postgresqlgetResult(new.con) : RS-DBI driver: (could not Retrieve the result : ERROR: LOAD source is not supported. (Hint: only S3 or DynamoDB based load is allowed)
我尝试了多个简单的data.frames,而读取data.frame却无法写入.提示告诉我使用s3进行加载,这意味着我可以在R中处理数据,但不能将data.frame写回redshift,除非我使用显式插入.有什么想法我做错了吗?或者这是redshift禁止或强迫您使用s3的固有方式
I have tried multiple simple data.frames, while reading data.frame works writing does not. Hint tells me to use s3 for loading, which means I can process data in R but cannot write a data.frame back to redshift, unless i use explicit inserts. Any ideas what I am doing wrong? or if this is an inherent way redshift prohibits or forces you to use s3
推荐答案
我使用RODBC软件包解决了.
I solved using RODBC package.
您需要设置ODBC连接,我们将其称为redshift_con.在R中,您需要使用以下命令创建连接(我称其为ch):
You need to set up an ODBC connection let's call it i.e. redshift_con. From R than you need to create a connection (i called it ch) using:
ch<-odbcConnect("Annalect DB",uid ="username",pwd ="**")
ch <- odbcConnect("Annalect DB", uid = "username", pwd = "**")
将我的数据框加载到R中并运行:
loaded my dataframe in R and runned:
sqlSave(ch,mydataframename,addPK = TRUE,verbose = TRUE)
sqlSave(ch, mydataframename, addPK = TRUE, verbose = TRUE)
然后R在redshift中创建一个名为"mydataframename"的新表.速度不是很快,如果找到并优化了方法,我会不断更新.
And R creates a new table in redshift called "mydataframename". Is not really fast, I'll keep updated if I find and optimized method.
如果您需要更多信息,请参见 http://cran.r- project.org/web/packages/RODBC/index.html
If you need more information see http://cran.r-project.org/web/packages/RODBC/index.html
################ SAMPLE CODE FOR TESTING ##############
install.packages("RODBC")
library('RODBC')
ch <- odbcConnect("redshift_con", uid = "admin", pwd = "********")
sqlColumns(ch, "public.r_test")
USArrest<-data(USArrests)
sqlSave(redshift_con, USArrests, rownames = "State", addPK = TRUE, verbose = TRUE)
I'll post a comment on the process speed.
希望它可以提供帮助.
更新
仅适用于非常少量的数据
Good only for VERY small amount of data
这篇关于redshift data.frame没有被写入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!