Aaron Mackey writes:
> I guess I'd do it something like this:
>
> dbGetQuery(con, "CREATE TEMPORARY TABLE foo ( etc etc)")
> sapply(@userids, function (x) { dbGetQuery(con, paste("INSERT INTO foo
> (userid) VALUES (", x, ")")) })
>
> then later:
>
> dbGetQuery(con, "DROP TABLE foo");
>
Actually, based on my reading of the DBI reference, you should be able
to do the following to create a table (although possibly not temporary):
dbWriteTable(connection, "r_user_ids", r)
Then you can use the following to drop the table:
dbRemoveTable(connection, "r_user_ids")
Of course, I don't know whether the ODBC driver implements these
functions or not. (Is 'RODBC' built on DBI? Looks like Aaron and I
have been assuming that.)
Coey
> -Aaron
>
> On Thu, Sep 11, 2008 at 3:21 PM, Avram Aelony <[EMAIL PROTECTED]> wrote:
> >
> > Perhaps I will need to create a temp table, but I am asking if there is a
> > way to avoid it. It would be great if there were a way to tie the R data
> > frame temporarily to the query in a transparent fashion. If not, I will
> > see if I can create/drop the temp table directly from sqlQuery.
> > -Avram
> >
> >
> >
> > On Thursday, September 11, 2008, at 12:07PM, "Aaron Mackey" <[EMAIL
> > PROTECTED]> wrote:
> >>Sorry, I see now you want to avoid this, but you did ask what was the
> >>"best way to efficiently ...", and the temp. table solution certainly
> >>matches your description. What's wrong with using a temporary table?
> >>
> >>-Aaron
> >>
> >>On Thu, Sep 11, 2008 at 3:05 PM, Aaron Mackey <[EMAIL PROTECTED]> wrote:
> >>> I would load your set of userid's into a temporary table in oracle,
> >>> then join that table with the rest of your SQL query to get only the
> >>> matching rows out.
> >>>
> >>> -Aaron
> >>>
> >>> On Thu, Sep 11, 2008 at 2:33 PM, Avram Aelony <[EMAIL PROTECTED]> wrote:
> >>>>
> >>>> Dear R list,
> >>>>
> >>>> What is the best way to efficiently marry an R dataset with a very
> >>>> large (Oracle) database table?
> >>>>
> >>>> The goal is to only return Oracle table rows that match IDs present in
> >>>> the R dataset.
> >>>> I have an R data frame with 2000 user IDs analogous to: r =
> >>>> data.frame(userid=round(runif(2000)*100000,0))
> >>>>
> >>>> ...and I need to pull data from an Oracle table only for these 2000
> >>>> IDs. The Oracle table is quite large. Additionally, the sql query may
> >>>> need to join to other tables to bring in ancillary fields.
> >>>>
> >>>> I currently connect to Oracle via odbc:
> >>>>
> >>>> library(RODBC)
> >>>> connection <- odbcConnect("****", uid="****", pwd="****")
> >>>> d = sqlQuery(connection, "select userid, x, y, z from largetable where
> >>>> timestamp > sysdate -7")
> >>>>
> >>>> ...allowing me to pull data from the database table into the R object
> >>>> "d" and then use the R merge function. The problem however is that if
> >>>> "d" is too large it may fail due to memory limitations or be
> >>>> inefficient. I would like to push the merge portion to the database
> >>>> and it would be very convenient if it were possible to request that the
> >>>> query look to the R object for the ID's to which it should restrict the
> >>>> output.
> >>>>
> >>>> Is there a way to do this?
> >>>> Something like the following fictional code:
> >>>> d = sqlQuery(connection, "select t.userid, x, y, z from largetable t
> >>>> where r$userid=t.userid")
> >>>>
> >>>> Would sqldf (http://code.google.com/p/sqldf/) help me out here? If so,
> >>>> how? This would be convenient and help me avoid needing to create a
> >>>> temporary table to store the R data, join via sql, then return the data
> >>>> back to R.
> >>>>
> >>>> I am using R version 2.7.2 (2008-08-25) / i386-pc-mingw32 .
> >>>> Thanks for your comments, ideas, recommendations.
> >>>>
> >>>>
> >>>> -Avram
> >>>>
> >>>> ______________________________________________
> >>>> R-help@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@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@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.
