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屋！