How to retrieve more than 100000 rows from Redshift using R and dplyr

Question

I'm analyzing data from a Redshift database, working in R using a connection per dplyr - which works:

my_db<-src_postgres(host='my-cluster-blahblah.redshift.amazonaws.com', port='5439', dbname='dev',user='me', password='mypw')
mytable <- tbl(my_db, "mytable")

viewstation<-mytable %>%
    filter(stationname=="something") 

When I try to turn that output into a data frame, so:

thisdata<-data.frame(viewstation)

I get the error message, Warning message:

Only first 100,000 results retrieved. Use n = -1 to retrieve all. 

Where am I supposed to set n?

Answer 1

Instead of using

thisdata<-data.frame(viewstation)

use

thisdata <- collect(viewstation)

collect() will pull all the data from the database back into R. As mentioned in the DPLYR::databases vignette:

When working with databases, dplyr tries to be as lazy as possible. It’s lazy in two ways:

It never pulls data back to R unless you explicitly ask for it.

It delays doing any work until the last possible minute, collecting together everything you want to do then sending that to the database in one step.