Read a PostgreSQL local file into R in chunks and export to .csv

Question

I have a local file in PostgreSQL format that I would like to read into R into chunks and export it as .csv.

I know this might be a simple question but I'm not at all familiar with PostgreSQL or SQL. I've tried different things using R libraries like RPostgreSQL, RSQLite and sqldf but I couldn't get my head around this.

What is "`PostgreSQL` format"? I've never heard of that, and I've used PostgreSQL a lot. — bgoldst, Apr 21 '16 at 16:25
@DanBracuk, the file size is 18GB so I would like to do this task in chunks to go around the memory limitations in R. — rafa.pereira, Apr 21 '16 at 16:40
@bgoldst I guess this gives you an idea of how unfamiliar I am with PostgreSQL. The file I have is zipped with name "gps_PostgreSQL.rar". The file name is "gps_p.backup" — rafa.pereira, Apr 21 '16 at 16:43
Are you updating an existing file or creating a very large one from scratch? — Dan Bracuk, Apr 21 '16 at 16:50
You don't need to do it in chunks. You can just use the PostgreSQL `copy` command to write it directly to a csv file. See http://stackoverflow.com/questions/1120109/export-postgres-table-to-csv-file-with-headings — G. Grothendieck, Apr 22 '16 at 15:56

score 1 · Answer 1 · answered Jan 25 '17 at 02:51

1

If your final goal is to create a csv file, you can do it directly using PostgreSQL.

You can run something similar to this: COPY my_table TO 'C:\my_table.csv' DELIMITER ',' CSV HEADER;

Sorry if I misunderstood your requirement.

answered Jan 25 '17 at 02:51

Gayan

11
2

score 0 · Answer 2 · answered Apr 21 '16 at 17:25

The requirement is to programmatically create a very large .csv file from scratch and populate it from data in a database? I would use this approach.

Step 1 - isolate the database data into a single table with an auto incrementing primary key field. Whether you always use the same table or create and drop one each time depends on the possibility of concurrent use of the program.

Step 2 - create the .csv file with your programming code. It can either be empty, or have column headers, depending on whether or not you need column headers.

Step 3 - get the minimum and maximum primary key values from your table.

Step 4 - set up a loop in your programming code using the values from Step 3. Inside the loop:

query the table to get x rows
append those rows to your file
increment the variables that control your loop

Step 5 - Do whatever you have to do with the file. Don't try to read it with your programming code.

thank you for your answer, where you have laid out the logic of the process. I would be grateful if you or someone could develop this in `R` code. regarding step 1: I've seen some tutorial on how to write a `data.frame` into a SQL database, but I couldn't find how to do write the my local file into a SQL database. — rafa.pereira, Apr 21 '16 at 17:39
It won't be me. I'm a ColdFusion programmer who read the question because of the sql tag. The approach I suggest should work with any language and database engine. Another approach I recommend is to do one thing at a time. Finally, during development, work with small amounts of data. — Dan Bracuk, Apr 21 '16 at 19:21

Read a PostgreSQL local file into R in chunks and export to .csv

2 Answers2