Materialize persistent tables in database

[nwstephens]

(1) I'm looking for a way to materialize persistent tables in the database using the dplyr backend. This is essentially a "create table as select ...;" statement for databases. Perhaps using "compute()".

(2) I'm also interested in using dplyr to chose multiple databases from a single host. Currently, the dplyr backend requires a separate connection for each database on a host. It would be useful to have a function that is the equivalent of "use ;"

(3) Finally, I would like to be able to query tables across databases and do cross database joins.

These notes are from our conversation earlier today. Thanks!

[krlmlr]

Does compute(., name = "new_table_name", temporary = FALSE) work for you for the first item?

I've been hacking around the second item, too. A crucial point here seems to be the support for schemas in DBI (r-dbi/DBI#24). You might be able to manually create temporary views in your dplyr source's connection:

DBI::dbExecute(src$con, "CREATE TEMPORARY VIEW imported_view AS SELECT * FROM schema.source_table")

(Join performance may degrade with RSQLite, but other backends might work better.)

[hadley]

1. my sense has always been to steer dplyr away from modifying the database, which is why everything uses temporary tables by default. Adding database mutation code worries me, especially due to the long update cycle of dplyr. Maybe we should put stuff in another help package?

2. This will get fixed once we add the generic DBI backend. Will be particularly nice in conjunction with tool

3. You can already do that if you set (IIRC) auto_copy = TRUE