Re: [R] export from R to MySQL

From: Meinhard Ploner <meinhardploner_at_gmx.net>
Date: Tue 13 Dec 2005 - 20:34:03 EST

On Dec 12, 2005, at 6:50 PM, David James wrote:

> 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.

Thanks at all!

As write.table and read.table itself are to some degree slow, for matrizes which are only numeric cat() and scan() could be faster. however it's a special case.

>> 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 Wed Dec 14 10:07:41 2005

This archive was generated by hypermail 2.1.8 : Wed 14 Dec 2005 - 14:38:33 EST