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

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

Sean
That was just an example -- it's not difficult to write an R function
to generate the mysql create table syntax for a data frame with 60 or
600 columns. (BTW, I would never type 67 columns.)

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

Sean

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.

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

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