Search code examples
sqlrdplyrtidyversedbplyr

Is there a way to create a table database-side with the result of a query with dbplyr?

Suppose I have a connection to a database and a link to a large table created like that:

library(tidyverse)
conn <- ROracle::dbConnect(drv, dbname = "IPIAMPR2.WORLD")
patients <- tbl(conn, "patients")

I can do the following query:

young <- patients %>% filter(age < 18)

and then copy the result to the database:

copy_to(conn, young, "young")

Question

But is there a way to do something equivalent to the following SQL:

create table YOUNG
select *
from PATIENTS
where AGE < 18

and have all the work done database-server-side?

Solution

  • Yes. Use compute().

    young <-
  patients %>%
  filter(age < 18) %>%
  compute(name = "young", temporary = FALSE)

    There is (I believe) no overwrite = TRUE option with compute(), so precede with dbExecute(conn, "DROP TABLE IF EXISTS young") if necessary.