Re: [R] Processing large datasets

From: Hugo Mildenberger <Hugo.Mildenberger_at_web.de>
Date: Wed, 25 May 2011 17:55:56 +0200

With PostgreSQL at least, R can also be used as implementation language for stored procedures. Hence data transfers between processes can be avoided alltogether.

   http://www.joeconway.com/plr/

Implemention of such a procedure in R appears to be straighforward:  

   CREATE OR REPLACE FUNCTION overpaid (emp) RETURNS bool AS '

      if (200000 < arg1$salary) {
          return(TRUE)
      }
      if (arg1$age < 30 && 100000 < arg1$salary) {
          return(TRUE)
      }
      return(FALSE)

    ' LANGUAGE 'plr';

  CREATE TABLE emp (name text, age int, salary numeric(10,2));

    INSERT INTO emp VALUES ('Joe', 41, 250000.00);
    INSERT INTO emp VALUES ('Jim', 25, 120000.00);
    INSERT INTO emp VALUES ('Jon', 35, 50000.00);
 

  SELECT name, overpaid(emp) FROM emp;
   name | overpaid
    ------+----------

    Joe  | t
    Jim  | t
    Jon  | f

   (3 rows)

Best

On Wednesday 25 May 2011 14:12:23 Jonathan Daily wrote:
> In cases where I have to parse through large datasets that will not
> fit into R's memory, I will grab relevant data using SQL and then
> analyze said data using R. There are several packages designed to do
> this, like [1] and [2] below, that allow you to query a database using
> SQL and end up with that data in an R data.frame.
>
> [1] http://cran.cnr.berkeley.edu/web/packages/RMySQL/index.html
> [2] http://cran.cnr.berkeley.edu/web/packages/RSQLite/index.html
>
> On Wed, May 25, 2011 at 12:29 AM, Roman Naumenko <roman_at_bestroman.com> wrote:
> > Hi R list,
> >
> > I'm new to R software, so I'd like to ask about it is capabilities.
> > What I'm looking to do is to run some statistical tests on quite big
> > tables which are aggregated quotes from a market feed.
> >
> > This is a typical set of data.
> > Each day contains millions of records (up to 10 non filtered).
> >
> > 2011-05-24 750 Bid DELL 14130770 400
> > 15.4800 BATS 35482391 Y 1 1 0 0
> > 2011-05-24 904 Bid DELL 14130772 300
> > 15.4800 BATS 35482391 Y 1 0 0 0
> > 2011-05-24 904 Bid DELL 14130773 135
> > 15.4800 BATS 35482391 Y 1 0 0 0
> >
> > I'll need to filter it out first based on some criteria.
> > Since I keep it mysql database, it can be done through by query. Not
> > super efficient, checked it already.
> >
> > Then I need to aggregate dataset into different time frames (time is
> > represented in ms from midnight, like 35482391).
> > Again, can be done through a databases query, not sure what gonna be faster.
> > Aggregated tables going to be much smaller, like thousands rows per
> > observation day.
> >
> > Then calculate basic statistic: mean, standard deviation, sums etc.
> > After stats are calculated, I need to perform some statistical
> > hypothesis tests.
> >
> > So, my question is: what tool faster for data aggregation and filtration
> > on big datasets: mysql or R?
> >
> > Thanks,
> > --Roman N.
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help_at_r-project.org mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
> >
>
>
>
>



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Wed 25 May 2011 - 15:57:27 GMT

This quarter's messages: by month, or sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

All messages

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 25 May 2011 - 16:20:10 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive