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
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)