Save dbplyr query to postgree

Question

sorry for the very simple question, but...

How to save a query result build with dbplyr without load data on memory.

This is what I tried.

library(DBI)
library(odbc)
library(RPostgreSQL)
library(tidyverse)
library(magrittr)
#> 
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#> 
#>     set_names
#> The following object is masked from 'package:tidyr':
#> 
#>     extract
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

pgdrv <- dbDriver(drvName = "PostgreSQL")
con <-dbConnect(pgdrv,
               dbname="genomes",
               host="127.0.0.1", port=5432,
               user = 'rotifer')

mtcars %<>% 
  rownames_to_column()

dbWriteTable(con, "cars", mtcars)
#> [1] TRUE

dbmtcars <- tbl(con, "cars") 

dbmtcars %>% 
  mutate(ts = cyl * carb) -> newtb

newtb %>% show_query()
#> <SQL>
#> SELECT "row.names", "rowname", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb", "cyl" * "carb" AS "ts"
#> FROM "cars"

newtb %>% db_write_table(con, "newtb")
#> Error in UseMethod("db_write_table"): no applicable method for 'db_write_table' applied to an object of class "c('tbl_PostgreSQLConnection', 'tbl_dbi', 'tbl_sql', 'tbl_lazy', 'tbl')"

^{Created on 2020-10-09 by the reprex package (v0.3.0)}

Thanks in advance

Answer 1

I found the answer by tentative which I don't know how explain but it works.

I just used db_compute and db_sql_render to address the query to perform and save in a new table. db_compute(con=con, sql=db_sql_render(con, newtb), table="newtb")

Complete code below:

library(DBI)
library(odbc)
library(RPostgreSQL)
library(tidyverse)
library(magrittr)
#> 
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#> 
#>     set_names
#> The following object is masked from 'package:tidyr':
#> 
#>     extract
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

pgdrv <- dbDriver(drvName = "PostgreSQL")
con <-dbConnect(pgdrv,
               dbname="genomes",
               host="127.0.0.1", port=5432,
               user = 'rotifer')

mtcars %<>% 
  rownames_to_column()
#> [1] TRUE

dbmtcars <- tbl(con, "cars") 

dbmtcars %>% 
  mutate(ts = cyl * carb) -> newtb

newtb %>% show_query()
#> <SQL>
#> SELECT "row.names", "rowname", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb", "cyl" * "carb" AS "ts"
#> FROM "cars"

db_compute(con=con, sql=db_sql_render(con, newtb), table="newtb")
#> [1] "newtb"
dbCommit(con)
#> [1] TRUE

sbpsigt <- tbl(con, "newtb")
sbpsigt
#> # Source:   table<newtb> [?? x 14]
#> # Database: postgres 10.0.14 [[email protected]:5432/genomes]
#>    row.names rowname   mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear
#>    <chr>     <chr>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1 1         Mazda …  21       6  160    110  3.9   2.62  16.5     0     1     4
#>  2 2         Mazda …  21       6  160    110  3.9   2.88  17.0     0     1     4
#>  3 3         Datsun…  22.8     4  108     93  3.85  2.32  18.6     1     1     4
#>  4 4         Hornet…  21.4     6  258    110  3.08  3.22  19.4     1     0     3
#>  5 5         Hornet…  18.7     8  360    175  3.15  3.44  17.0     0     0     3
#>  6 6         Valiant  18.1     6  225    105  2.76  3.46  20.2     1     0     3
#>  7 7         Duster…  14.3     8  360    245  3.21  3.57  15.8     0     0     3
#>  8 8         Merc 2…  24.4     4  147.    62  3.69  3.19  20       1     0     4
#>  9 9         Merc 2…  22.8     4  141.    95  3.92  3.15  22.9     1     0     4
#> 10 10        Merc 2…  19.2     6  168.   123  3.92  3.44  18.3     1     0     4
#> # … with more rows, and 2 more variables: carb <dbl>, ts <dbl>

dbDisconnect(con)
#> [1] TRUE

^{Created on 2020-10-09 by the reprex package (v0.3.0)}

Answer 2

I use the following pattern for saving constructed SQL tables:

con <-dbConnect('you connection details here')
dbmtcars <- tbl(con, "cars") 

newtb = dbmtcars %>% mutate(ts = cyl * carb)

sql_query <- "SELECT *\n",
             "INTO db_name.schema_name.new_tbl_name\n",
             "FROM (\n",
             dbplyr::sql_render(newtb ),
             "\n) AS alias")

dbExecute(db_connection, as.character(sql_query))

For more details see the write_to_datebase function here.

Answer 3

Use db_save_query could help. In your case, the codes would be:

db_save_query(con = con, sql = sql_render(newtb),temporary = F,table = "newtb")

However, I could not find a way to join two tables from different connections. If anybody knows how, please let me know.