Saving a flat file as an SQL database in R without loading it 100% into RAM

user_2952838 0
Viewed 714

I hope that what I am about to write makes some sense. If you look at

How to deal with a 50GB large csv file in r language?

it is explained how to query à la SQL, a csv file from R. In my case, I have vast amount of data stored as large (or larger than my RAM) flat files.

I would like to storer for instance one of these as an SQLite database without loading it in memory entirely. Imagine if you could automatically read a limited chunk of that file which is suitable for your RAM, store it into a SQL, then free up some memory, process the next chunk and so on and so forth until all the file is in the database. Is this doable in R? If the table could be stored as tibble, it would be even better, but it is not crucial. Any suggestion is appreciated. Thanks!

dbplyr sqldf r sqlite
2 Answers

1) dbWriteTable dbWriteTable can read the file into a database without going through R. The database is created if it does not already exist.

library(RSQLite)

cat("a,b\n1,2\n", file = "myfile.csv")  # create test file

con <- dbConnect(SQLite(), "mydb") 

dbWriteTable(con, "mytable", "myfile.csv")
dbGetQuery(con, "select count(*) from mytable")  # ensure it is there

dbDisconnect(con)

2) SQLite CLI We could alternately do it using the sqlite cli which can be downloaded from the sqlite download site.

https://www.sqlite.org/download.html

This does not involve R at all in creating the database. mydb will be created if it does not exist. This first line is entered at the shell or cmd prompt and that will provide its own prompt at which the remaining lines cana be entered.

sqlite3 mydb
.mode csv
.import myfile.csv mytable
.quit

3) Other database Another option is to use a database that has the ability to read csv files directly into it. H2 has csvread, MySQL has load data infile and PostgreSQL has copy.

user_5165480
user_29528380
Related