[R] export from R to MySQL

[Meinhard Ploner]

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