export from R to MySQL
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 at stat.math.ethz.ch [mailto:r-help-bounces at 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 at 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 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