title: store matrix data in SQLite for fast retrieval in R
body: <p>I have 48 matrices of dimensions 1,000 rows and 300,000 columns where each column has a respective ID, and each row is a measurement at one time point. Each of the 48 matrices is of the same dimension and their column IDs are all the same. </p>
  
  <p>The way I have the matrices stored now is as RData objects and also as text files.  I guess for SQL I'd have to transpose and store by ID, and in such case now the matrix would be of dimensions 300,000 rows and 1,000 columns.</p>
  
  <p>I guess if I transpose it a small version of the data would look like this:</p>
  
  <pre><code>id1 1.5 3.4 10 8.6 .... 10 (with 1,000 columns, and 30,0000 rows now)
  </code></pre>
  
  <p>I want to store them in a way such that I can use R to retrieve a few of the rows (~ 5 to 100 each time).  </p>
  
  <p>The general strategy I have in mind is as follows:</p>
  
  <p>(1) Create a database in sqlite3 using R that I will use to store the matrices (in different tables)</p>
  
  <p>For file 1 to 48 (each file is of dim 1,000 rows and 300,000 columns):</p>
  
  <p>(2) Read in file into R</p>
  
  <p>(3) Store the file as a matrix in R</p>
  
  <p>(4) Transpose the matrix (now its of dimensions 300,000 rows and 1,000 columns).  Each row now is the unique id in the table in sqlite.</p>
  
  <p>(5) Dump/write the matrix into the sqlite3 database created in (1) (dump it into a new table probably?)</p>
  
  <p>Steps 1-5 are to create the DB.</p>
  
  <p>Next, I need step 6 to read-in the database:</p>
  
  <p>(6) Read some rows (at most 100 or so at a time) into R as a (sub)matrix.</p>
  
  <p>A simple example code doing steps 1-6 would be best.</p>
  
  <p>Some Thoughts:</p>
  
  <p>I have used SQL before but it was mostly to store tabular data where each column had a name, in this case each column is just one point of the data matrix, I guess I could just name it col1 ... to col1000? or there are better tricks?</p>
  
  <p>If I look at: <a href="http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html" rel="nofollow">http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html</a> they show this example:</p>
  
  <pre><code> dbSendQuery(conn = db,
         "CREATE TABLE School
         (SchID INTEGER,
          Location TEXT,
          Authority TEXT,
          SchSize TEXT)")
  </code></pre>
  
  <p>But in my case this would look like:</p>
  
  <pre><code> dbSendQuery(conn = db,
         "CREATE TABLE mymatrixdata
         (myid TEXT,
          col1 float,
          col2 float,
  .... etc.....
          col1000 float)")
  </code></pre>
  
  <p>I.e., I have to type in col1 to ... col1000 manually, that doesn't sound very smart.  This is where I am mostly stuck.  Some code snippet would help me.</p>
  
  <p>Then, I need to dump the text files into the SQLite database?  Again, unsure how to do this from R.</p>
  
  <p>Seems I could do something like this:</p>
  
  <pre><code>setwd(&lt;directory where to save the database&gt;)
  db &lt;- dbConnect(SQLite(), dbname="myDBname")
  mymatrix.df = read.table(&lt;full name to my text file containing one of the matrices&gt;)
  mymatrix = as.matrix(mymatrix.df)
  </code></pre>
  
  <p>Here I need to now the coe on how to dump this into the database...</p>
  
  <p>Finally, </p>
  
  <p>How to fast retrieve the values (without having to read the entire matrices each time) for some of the rows (by ID) using R?</p>
  
  <p>From the tutorial it'd look like this:</p>
  
  <pre><code>sqldf("SELECT id1,id2,id30 FROM mymatrixdata", dbname = "Test2.sqlite")
  </code></pre>
  
  <p>But it the id1,id2,id30 are hardcoded in the code and I need to dynamically obtain them.  I.e., sometimes i may want id1, id2, id10, id100; and another time i may want id80, id90, id250000, etc.</p>
  
  <p>Something like this would be more approp for my needs:</p>
  
  <pre><code>cols.i.want = c("id1","id2","id30")
  sqldf("SELECT cols.i.want FROM mymatrixdata", dbname = "Test2.sqlite")
  </code></pre>
  
  <p>Again, unsure how to proceed here.  Code snippets would also help.</p>
  
  <p>A simple example would help me a lot here, no need to code the whole 48 files, etc. just a simple example would be great!</p>
  
  <p>Note: I am using Linux server, SQlite 3 and R 2.13 (I could update it as well).</p>