Load large data to R data.table from Postgresql

Question

I store my data in Postgresql server. I want to load a table which has 15mil rows to data.frame or data.table

I use RPostgreSQL to load data.

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, ...)

# Select data from a table
system.time(
df <- dbGetQuery(con, "SELECT * FROM 15mil_rows_table")
)

It took 20 minutes to load data from DB to df. I use google cloud server which have 60GB ram and 16 Core CPU

What should I do to reduce load time?

Do you need to load all the data into R? Might there be operations you could do directly in PostgreSQL - filter or aggregate rows, for instance? — Dominic Comtois
– Dominic Comtois, Commented Mar 29, 2015 at 11:37
@DominicComtois: I need to load all data into R because I want to do many aggregations which is not easy to do in PostgreSQL. I have completed my R code. Now I want to improve the load data part — Minh Ha Pham
– Minh Ha Pham, Commented Mar 29, 2015 at 11:39
if you use src_postgres from dplyr you can then use dplyr functions for the aggregation and it will push many if not all of those operations back onto the database itself and you won't need to read all the records into R. ref: cran.rstudio.com/web/packages/dplyr/vignettes/databases.html — hrbrmstr
– hrbrmstr, Commented Mar 29, 2015 at 12:07
@hrbrmstr: Thank you for your advice. I will try dplyr later. I am still want to find the answer for my question because I want to load all data to aggression and plot data. — Minh Ha Pham
– Minh Ha Pham, Commented Mar 30, 2015 at 2:36
you should be able to use dplyr to load the data into R. failing that, you should be able to write your data to csv and use fread in data.table to read it very quickly. limiting step is probably not RAM or cores -- it will be the disk read. — Mark Danese
– Mark Danese, Commented Apr 9, 2015 at 5:37

jangorecki · Accepted Answer · 2015-04-16 20:41:06Z

4

Not sure if this will reduce load time, for sure it may reduce load time as both processes are quite performance efficient. You can leave a comment about the timming.

using bash run psql as dump table to csv:

COPY 15mil_rows_table TO '/path/15mil_rows_table.csv' DELIMITER ',' CSV HEADER;

in R just fread it:

library(data.table)
DT <- fread("/path/15mil_rows_table.csv")

answered Apr 16, 2015 at 20:41

jangorecki

16.8k5 gold badges86 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Minh Ha Pham Over a year ago

I use this method to reduce load time also but I zip data to save memory storage. I add my answer below.

Minh Ha Pham · Accepted Answer · 2015-04-17 03:45:53Z

3

I use the method as @Jan Gorecki with zip data to save memory.

1- Dump table to csv

psql -h localhost -U user -d 'database' -c "COPY 15mil_rows_table TO stdout DELIMITER ',' CSV HEADER" | gzip > 15mil_rows_table.csv.gz &

2- Load data in R

DT <- fread('zcat 15mil_rows_table.csv.gz')

edited Apr 17, 2015 at 3:45

answered Apr 17, 2015 at 3:38

Minh Ha Pham

2,6063 gold badges31 silver badges43 bronze badges

2 Comments

jangorecki Over a year ago

Can you share a total timing using this way? May it could be further improved by stdout-in instead csv file, but not sure.

Minh Ha Pham Over a year ago

@JanGorecki: My table has 40mil rows and 11 columns. The load time is about 5 minutes.

Collectives™ on Stack Overflow

Load large data to R data.table from Postgresql

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related