将数据从R数据集中写入Redshift [英] Writing data from an R data-set into Redshift

问题描述

我正在使用"RPostgreSQL"包将R连接到Amazon Redshift，我可以连接，创建和删除表，查询数据库，将数据保存到R数据框中并手动插入值.

I am connecting R to amazon Redshift using the package "RPostgreSQL" I can connect, create and delete tables, query the database, save the data into an R data-frame and manually insert values.

我无法做的是直接从R数据帧中将数据插入Redshift读取，并且由于redshift中缺少Posgress组件而无法使用dbWriteTable函数.

What I cannot do is insert data into Redshift reading directly from an R data-frame and I can not use the dbWriteTable function due to missing Posgress component in redshift.

我在做什么错了?

提前感谢您的帮助！

推荐答案

只需使用RODBC包自己解决.

Just solve it myself 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 = "******")

将我的数据框加载到R中并运行:

loaded my dataframe in R and runned:

sqlSave(ch, mydataframename, addPK = TRUE, verbose = TRUE)

然后R在redshift中创建一个名为"mydataframename"的新表.速度不是很快，如果我找到了优化的方法，我会及时通知你.

And R creates a new table in redshift called "mydataframename". It's not really fast, I'll keep you 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

半小时记录1500条记录.

half an hour for 1500 records.

这篇关于将数据从R数据集中写入Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！