Как я могу добавить данные в таблицу PostgreSQL с помощью `dplyr` без `collect()`?

dplyr не включает команды для вставки или обновления записей в базе данных, поэтому для этого нет полного собственного dplyr решения. Но вы можете комбинировать dplyr с обычными операторами SQL, чтобы не передавать данные в R.

Давайте начнем с воспроизведения ваших шагов перед оператором collect()

library(dplyr)

pg <- src_postgres()

reg_data <- tbl(pg, "reg_data")

reg_results <-
    reg_data %>%
    summarize(r_squared=regr_r2(y, x),
              num_obs=regr_count(y, x),
              constant=regr_intercept(y, x),
              slope=regr_slope(y, x),
              mean_analyst_fog=regr_avgx(y, x),
              mean_manager_fog=regr_avgy(y, x))

Теперь вы можете использовать compute() вместо collect() для создания временной таблицы в базе данных.

temp.table.name <- paste0(sample(letters, 10, replace = TRUE), collapse = "")

reg_results <- reg_results %>% compute(name=random.table.name)

Где temp.table.name — случайное имя таблицы. Используя опцию name= temp.table.name в вычислении, мы назначаем это случайное имя созданной временной таблице.

Теперь мы воспользуемся библиотекой RPostgreSQL для создания запроса на вставку, использующего результаты, хранящиеся во временной таблице. Поскольку временная таблица существует только в соединении, созданном src_postgresql(), нам необходимо повторно использовать ее.

library(RPostgreSQL)
copyconn <- pg$con
class(copyconn) <- "PostgreSQLConnection" # I get an error if I don't fix the class

Наконец, запрос на вставку

sql <- paste0("INSERT INTO destination_table SELECT * FROM ", temp.tbl.name,";")

dbSendQuery(copyconn, sql)

Итак, все происходит в базе данных, а данные не заносятся в R.

ИЗМЕНИТЬ

Предыдущие версии этого поста нарушали инкапсуляцию, когда мы получали temp.tbl.name из reg_results. Этого можно избежать, используя параметр name=in вычисления.

другим вариантом может быть использование команды с именем sql_render() для создания каждого оператора SQL, а затем другой команды с именем db_save_query() для создания таблицы с использованием оператора SQL, а затем ручного оператора для добавления к таблице. Для перебора каждого запроса используются команды purrr: map и walk. Желательно, чтобы это делала такая команда, как команда compute(), но вместо этого ниже приведен полностью воспроизводимый пример:

library(dplyr)
library(dbplyr)
library(purrr)

# Setting up a SQLite db with 3 tables
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, filter(mtcars, cyl == 4), "mtcars1")
copy_to(con, filter(mtcars, cyl == 6), "mtcars2")
copy_to(con, filter(mtcars, cyl == 8), "mtcars3")



# Pre-process the SQL statements
tables <- c("mtcars1","mtcars2","mtcars3")
all_results <- tables %>%
  map(~{
    tbl(con, .x) %>%
      summarise(avg_mpg = mean(mpg),
                records = n()) %>%
      sql_render() 
  })

# Execute the SQL statements, 1st one creates the table
# subsquent queries are insterted to the table
first_query <- TRUE
all_results %>%
  walk(~{
    if(first_query == TRUE){
      first_query <<- FALSE
      db_save_query(con, ., "results")
    } else {
      dbExecute(con, build_sql("INSERT INTO results ", .))
    }
  })


tbl(con, "results")

dbDisconnect(con)