I connect to Redshift using R remotely from my workstation.
install.packages("RPostgreSQL")
library (RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con1 <- dbConnect(drv, host="url", port="xxxx",
dbname="db_name", user="id", password="password")
dbGetInfo(con1)
then I create a table:
dbSendQuery(con1, "create table schema.table_name as select * from schema.table_name;")
now I want to export this table to a .csv file on my workstation, how to do this ? Again, I don't have PostGres database installed on my workstation, only using R to get to it.
Also, this table is LARGE, 4 columns, 14 million rows.
Thanks!
You'll need to pull down the results of a query into a local object, then dump the object to a CSV. Something along the lines of res <- dbSendQuery(con1, "select * from schema.table_name"); dat <-dbFetch(res); readr::write_csv(dat, "~/output.csv") should get you started.
I figured this out after posting - sharing..
system.time( fwrite(dbReadTable(con1, c("schema","table")), file="file.csv", sep=",", na="", row.names=FALSE, col.names=TRUE ))
I hear feather is even faster ?
this was for 43 million rows with 4 columns, took 15 minutes.