Re: [R] export from R to MySQL

From: David James <dj_at_research.bell-labs.com>
Date: Tue 13 Dec 2005 - 04:50:38 EST

Prof Brian Ripley wrote:
> On Mon, 12 Dec 2005, Sean Davis wrote:
>
> >
> >
> >
> > On 12/12/05 9:21 AM, "bogdan romocea" <br44114@gmail.com> wrote:
> >
> >>> Sean Davis wrote:
> >>> but you will have to create the table by hand
> >>
> >> There's no need for manual steps. To take advantage of MySQL's
> >> extremely fast 'load data infile' you could dump the data in CSV
> >> format, write a script for mysql (the command line tool), for example
> >>
> >> q <- function(table,infile)
> >> {
> >> query <- paste("
> >> create table ",table," (col1 float, col2 float);
> >
> > This is creating the table by hand, as opposed to using dbWriteTable. If
> > your data.frame contains 67 columns, using dbWriteTable saves quite a bit of
> > typing....
>
> The RODBC equivalent creates the table for you, then fast imports the
> file. Might be worthwhile contribution to RMySQL for someone.
>

That's what RMySQL's dbWriteTable() does. The original posting mentioned problems associated with speed of data.frame and dbWriteTable, which seems plausible (but I haven't quantified it myself) given the fact that dbWriteTable outputs a data.frame to an intermediate file via write.table and then uses the LOAD DATA for fast loading that intermediate file.

> Just be careful with client-server systems to have the file in the right
> place (if indeed you are allowed to have files on the server).
>
> --
> Brian D. Ripley, ripley@stats.ox.ac.uk
> Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
> University of Oxford, Tel: +44 1865 272861 (self)
> 1 South Parks Road, +44 1865 272866 (PA)
> Oxford OX1 3TG, UK Fax: +44 1865 272595
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html

--
David

______________________________________________
R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Received on Tue Dec 13 07:24:09 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:41:35 EST