0

I'm trying to create an SQL database from a set of data.frames in R. These data.frames have the same row names but different columns. In the SQL database I would therefore like them all to be in the same table, where data.frame1 with n1 columns will populate 1:n1 columns in this SQL table, data.frame2 with n2 columns will populate (n1+1):n2 columns in this SQL table, and so on.

For writing the first data.frame (df1 below) to the SQL database table, my code is:

library(sqldf)
setwd(db_path)
db <- dbConnect(SQLite(), dbname=db_name)
dbWriteTable(db, table_id, as.data.frame(df1))

Where db_path is the path to the SQL database, db_name is the name, table_id is the table's name, and df1 is the first data.frame.

Obviously, I can cbind these data.frames and then just dbWriteTable that combined data.frame. Or, for every data.frame I want to add use dbGetQuery to fetch the table_id, cbind the new data.frame to that and then write that back to db. My data.frames, however, are quite large, and therefore I was wondering if there's any operation that avoids holding all of them in memory before writing them to db. In other words, is there an SQL operation that would append new data.frames directly to table_id?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
dan
  • 6,048
  • 10
  • 57
  • 125
  • I'd suggest taking your data from wide to long format first... So let's say your data has id, col1, col2, col3. If you change it such that it's like id, feature, value... so that the first row is like id1, col1, value1. The features column is now your separate columns. Then the db schema is the same even if your dataframes have different numbers of columns. – cory Apr 20 '16 at 15:29
  • @cory this will result in very bad performance and force conversion of different data types to string or some variant type. The key-value schema is considered a bad table design. Actually, the OP should *not* try to stuff different frames in the same table. Different tables should be used and in fact, it would be far better to design the tables in advance and add the proper indexes. – Panagiotis Kanavos Apr 20 '16 at 15:40

0 Answers0