I have a 11Gb size dataframe named data which i somehow have loaded to Rstudio. Now I have converted that to Sql database by using following code but my question is how can I save the sqlite database to load it later even if I close rstudio. I want to save it in some kind of output format like a Rds file.
library(RSQLite)
library(DBI)
library(dplyr)
library(dbplyr)
con <- dbConnect(RSQLite::SQLite(),dbame="ShearTest3.sqlite")
# Writing the data frame to the database
dbWriteTable(con, name = "sh3", value = data ,rownames=FALSE,overwrite=T)
# this is just makes a connection to the database.
my_db <- tbl(con, "sh3")
There seem to be two different questions here: "How do I save an .rds file" and "How do I save my data as a SQLite database". Taking those in turn:
How do I save my data as an rds file?
saveRDS(data, 'myfilename.rds')
How do I save my data as a SQLite database?
What is wrong with the code you have? Provided your call to dbWriteTable works successfully, you could then close your R session and use the following code to to get your data dataframe back:
library(RSQLite)
con <- dbConnect(SQLite(), dbname = 'ShearTest3.sqlite')
data <- dbGetQuery(con, 'select * from sh3;')
I do not want the dataframe because 11Gb data is already taking a long time to load and manipulate rather I want a sql database to load on each session of rstudio.
Regarding Rds this was just an example to show that I want a file like file.db to load whenever I launch the rstudio.
If you load the entire data set from the SQLite file into memory then you would end up with an 11GB object again each time. R works with "on-memory" data so anything you retrieve into your working environment is loaded into memory regardless of the source. An alternative approach is to manipulate the data using the sql engine "on-disk" using sql commands or if you prefer, with dplyr like commands trough dbplyr
What package you suggest for that if it's possible to read data in chunks from 11gb sql database? I will read it's documentation? dbplyr ?
and yes I want to use dplyr for read data
I don't understand your question. The code you have showed must have already wrote your data frame into a table called sh3 inside the file called 'ShearTest3.sqlite' and unless you explicitly retrieve some data with the collect() function anything is loaded into memory, all processing made trough dbplyr uses lazy evaluation, which means it is only executed on the sql engine when you collect the data.