[R] export from R to MySQL
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 alternative? Regards Meinhard Ploner __ 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
Re: [R] export from R to MySQL
On 12/12/05 8:33 AM, "Meinhard Ploner" <[EMAIL PROTECTED]> 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 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
Re: [R] 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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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 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
Re: [R] export from R to MySQL
On 12/12/05 9:21 AM, "bogdan romocea" <[EMAIL PROTECTED]> 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
__
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
Re: [R] export from R to MySQL
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, Sean Davis <[EMAIL PROTECTED]> wrote:
>
>
>
> On 12/12/05 9:21 AM, "bogdan romocea" <[EMAIL PROTECTED]> 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
>
>
>
__
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
Re: [R] export from R to MySQL
On Mon, 12 Dec 2005, Sean Davis wrote:
>
>
>
> On 12/12/05 9:21 AM, "bogdan romocea" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED]
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, UKFax: +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
Re: [R] export from R to MySQL
Prof Brian Ripley wrote:
> On Mon, 12 Dec 2005, Sean Davis wrote:
>
> >
> >
> >
> > On 12/12/05 9:21 AM, "bogdan romocea" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED]
> 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, UKFax: +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
Re: [R] export from R to MySQL
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" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED]
>> 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, UKFax: +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
