Steve,

I had a similar challenge this week.  I ended up writing a function that
runs a query using odbcQuery() and then fetches small chunks of the result
using odbcFetchRows().  For the first chunk, ff() is used to create the ff
file.  After each additional chunk is fetched, the ff file is expanded with
the length() function.  E.g. length(ff) <- length(ff) + length(next.chunk).
The values from the next chunk are then inserted in the appropriate location
using the usual [ ] notation.

SQL queries can be constructed either by specifying the appropriate
arguments ('variable', 'dbtable', 'orderby') or by specifying a custom query
with the 'query' argument.  Additional arguments are explained in comments
within the function.

This function just returns a single ff file from a single database column.
I then loop it over all the required variables of my table and use ffdf()
to combine the ff files into an ffdf.  This approach seemed to make sense
for what I was doing.  I imagine it might be be more optimal for performance
to read in multiple columns at once.  I have limited experience with RODBC
and ff, so please let me know if I've made any glaring errors.  The function
is no substitution for an ODBC version of read.table.ffdf(), but it appears
to be working for me on Ubuntu 10.10 with R 2.11.1 x86_64, ff 2.1-4, and
RODBC 1.3-2.  My dataset includes about 77 million records with about 100
columns of hospital discharge data from the 1998-2007 HCUP NIS database.
The R code is below, or you can source('
http://jthetzel.homeip.net/public/read.odbc.ff.r')

An example of arguments that I used:
keynum <- read.odbc.ff(dsn='nis', variable='keyid', dbtable='core',
orderby='keynum', sql_extension='sql_no_cache', overwrite=T, verbose=T,
rows_at_time=1000, limit=1000)

Cheers,
Jeremy

Jeremy T. Hetzel
Department of Surgery
Boston Medical Center


#---------------------------begin R code---------------------------------#
## function to create a ff file, piecemeal from an ODBC connection
read.odbc.ff <- function(dsn, variable=NULL, dbtable=NULL, orderby=NULL,
sql_extension='', location=getwd(), overwrite=F, rows_at_time=1000,
vmode=NULL, limit=NULL, verbose=F, query=NULL)
#/ 'dsn' is a character string of the registered data source name passed to
the odbcConnect function
#/ 'variable' is the name of the database column to be fetched
#/ 'dbtable' is the name of the database table containing the column to be
fetched
#/ 'orderby' is the name of the database column used to sort the result.
When using the 'read.odbc.ff' function multiple times to read multiple
columns into multiple ff files to create a ffdf, it is important that
'orderby' be the same.  Otherwise, the resulting ffdf might be jumbled.
#/ 'sql_extension' is a character string that may optionally contain any SQL
arguments to appear immediately after 'select' in the SQL query. I sometimes
use 'sql_no_cache' for MySQL databases, but I don't know if it actually
affects performance. Default is an empty string.
#/ 'location' is the directory in which the ff files will be saved. Default
is the current working directory. The ff filenames take the form
'location'/'variable'.ff .
#/ 'overwrite' is paassed to the ff function to specify whether existing ff
files may be overwritten.
#/ 'rows_at_time' is the number of rows to fetch from the ODBC query result
at a time. I am not sure which values are optimal, so one might want to
experiment.
#/ 'vmode' is passed to the ff function. Specification is optional. If null,
ff function will attempt specify an appropriate vmode automatically.
#/ 'limit' adds 'limit #' to the query, where # is an integer. Alternately,
# could be a string in the format '#,#' to specify an offset and row count.
Specification is optional, but useful for testing purposes.
#/ 'verbose' is a logical. When true, function will report its progress.
#/ 'query' is an optional character string. This allows for a custom SQL
query to be used, instead of automatic construction from the 'variable',
'dbtable', 'orderby', 'sql_extension', and 'limit' arguments. When 'query'
is specified, 'variable', 'dbtable', 'orderby', and 'limit' arguments are
ignored.
{
  ## require libraries
  require(ff)
  require(RODBC)

  # create filename for ff
  filename <- paste(location,'/',variable,'.ff', sep='')

  # create RODBC connection
  channel <- odbcConnect(dsn=dsn)

  # if no custom query is supplied, construct query
  if(is.null(query))
  {
    query <- ifelse(is.null(limit),
      paste('select ',sql_extension,' ',variable,' from ',dbtable,' order by
',orderby,sep=''),
      paste('select ',sql_extension,' ',variable,' from ',dbtable,' order by
',orderby,' limit ',limit,sep=''))
  }

  # perform query
  if(verbose==T) {cat(paste('\n Query:',query,'\n FF
filename:',filename,'\n'))}
  odbcQuery(channel, query, rows_at_time=rows_at_time)

  # read in first chunk of result and create ff file
  fetched <- c()
  fetched <- odbcFetchRows(channel, max=rows_at_time)[['data']][[1]]
  fetched <- type.convert(fetched)
  if(verbose==T) {cat(paste('\n Head of first chunk
fetched:',paste(head(fetched),collapse=' '),'\n Class of
fetched:',class(fetched),'\n'))}
  if(is.null(vmode))
      {
        result <- ff(fetched, filename=filename, finalizer='close',
overwrite=overwrite)
      } else
      {
        result <- ff(fetched, filename=filename, finalizer='close',
overwrite=overwrite, vmode=vmode)
      }

  # read in subsequent chunks
  current_row <- rows_at_time + 1
  status <- T
  while(status)
  {
    fetched <- odbcFetchRows(channel, max=rows_at_time)
    if(fetched$stat==1)
    {
      gc(verbose=F)
      fetched <- fetched[['data']][[1]]
      fetched <- type.convert(fetched)
      length_fetched <- length(fetched)
      if(verbose==T) {cat(paste('Current row:',current_row,' | Length of
chunk fetched:',length_fetched,' | Head of chunk
fetched:',paste(head(fetched),collapse=' '),'  :::  '))}
      # expand existing ff to accomodate next chunk
      if(length_fetched>0)
      {
        length(result) <- length(result) + length_fetched
        result[current_row:(current_row+length_fetched-1)] <- fetched
        current_row <- current_row + rows_at_time
      } else {
        status <- F
      }
    } else {
      status <- F
   }
  }
  if(verbose==T) {cat(paste('Complete.\n'))}
  close(channel)
  return(result)
}

#--------------------end R code------------------------------#






2010/9/21 "Jens Oehlschlägel" <jens.oehlschlaegel@truecluster.com>

> Hi Steve,
>
> >I have a fairly large (~4M rows, 27 double columns) data set I am
> attempting to
> >read in in R on Windows using RODBC and find I am running out of memory.
>
> Fairly small under Rwin64 with enough RAM, and reasonably small for ff
> under RWin32.
>
> >I thought I could easily workaround the problem by reading in smaller
> number of
> >rows and then rbind the results together as below:-
>
> Fair approach, will work. So far we don't have an rbind for ffdf (but all
> pieces are there, see below). If we had rbind.ffdf it ideally would cover
> the following situations
>
> 1) combing two (or more) ffdf into a third one
> 2) growing an existing ffdf by another (or more) ffdf
> 3) growing an existing ffdf by another (or more) data.frame
>
> Technicaly all the pieces are there. You can
> first.ffdf <- as.ffdf(first.data.frame)
> third.ffdf <- clone(first.ffdf)
> and grow an ffdf by assigning nrow like in
> nrow(third.ffdf) <- nrow(third.ffdf) + nrow(second.ffdf)
>
> Then the devil is in some details like maintaining appropriate factor
> levels. Example for how this works is in read.table.ffdf. Should be easy to
> abstract a rbind.ffdf from this, think I will do this soon - but first have
> to prepare the next release of ff which will support RWin64, sorting,
> ordering and using positive ff integers as subscripts. If you abstract
> rbind.fdf from read.table.ffdf before I do, please let me know, then I am
> happy to include in ff and give credit.
>
> I recently had a similar request from Xiaobo Gu who uses a different
> data.base driver and who wrote code which covers some of the functionality
> in read.table.ffdf for his database connection. Maybe he is willing to share
> the code with you, I cc him.
>
> [r-sig-hpc@r-project.org] is the appropriate list for questions on ff, I
> cc.
>
> Cheers
> Jens
>
> _______________________________________________
> R-sig-hpc mailing list
> R-sig-hpc@r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-hpc
>

	[[alternative HTML version deleted]]

