Re: [R] export from R to MySQL

From: bogdan romocea <br44114_at_gmail.com>
Date: Tue 13 Dec 2005 - 01:21:25 EST


> 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); load data infile '",infile,"'
into table ",table,"
fields terminated by ','
lines terminated by '\\r\\n'
ignore 0 lines;
show warnings;
",sep="")
query
}
sink("mysql_script.sql")
cat(q("db.table","infile"),"\n")
sink()

then run the script from R with system(). The mysql command looks like mysql -u user --password=pswd -v < mysql_script.sql >> log.txt 2>&1

> -----Original Message-----
> From: r-help-bounces@stat.math.ethz.ch
> [mailto:r-help-bounces@stat.math.ethz.ch] On Behalf Of Sean Davis
> Sent: Monday, December 12, 2005 8:51 AM
> To: Meinhard Ploner; r-help
> Subject: Re: [R] export from R to MySQL
>
>
>
>
>
> On 12/12/05 8:33 AM, "Meinhard Ploner" <meinhardploner@gmx.net> wrote:
>
> > Hi R user!
> >
> > What is the fastest way to export a large matrix or vector
> to a MySQL
> > database? The use of data.frame() and dbWriteTable() makes
> the process
> > slow, so is there any <direct> alternative?
>
> Probably dumping to a text file and then using mysqlimport
> will be fastest,
> in terms of computation time, but you will have to create the
> table by hand
> (using SQL CREATE TABLE), so it might take just as much user time.
>
> Sean
>
> ______________________________________________
> 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
>



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 01:26:41 2005

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