将数据从R数据集中写入Redshift

本文介绍了将数据从R数据集中写入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.

如果您需要更多信息，请参见 cran.r -project/web/packages/RODBC/index.html

If you need more information see cran.r-project/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.