Dbplyr: объединить две таблицы и добавить результат в базу данных, не загружая их в память
Пожалуйста, взгляните на простой скрипт в конце поста. У меня есть база данных, содержащая две таблицы, которые я объединяю с помощью union_all. Есть ли способ добавить результат в базу данных без сбора данных, т.е. загрузки их в память? Большое спасибо!
library(tidyverse)
library(DBI) # main DB interface
library(dbplyr) # dplyr back-end for DBs
#>
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#>
#> ident, sql
library(RSQLite)
##create the databases
df1 <- tibble(x=1:20,y=rep(c("a", "b"), 10))
df2 <- tibble(x=101:120,y=rep(c("d", "e"), 10))
con <- dbConnect(drv=RSQLite::SQLite(), dbname = "db.sqlite")
dbWriteTable(con,"mydata1",df1, overwrite=T)
dbWriteTable(con,"mydata2",df2, overwrite=T)
dbDisconnect(con) # closes our DB connection
con <- dbConnect(drv=RSQLite::SQLite(), dbname = "db.sqlite")
mydb1 <- tbl(con, "mydata1")
mydb2 <- tbl(con, "mydata2")
mydb12 <- union_all(mydb1,mydb2)
#is there a way to add the union of mydb1 and mydb2 to the database without explicitly collecting the data?
Created on 2020-12-24 by the reprex package (v0.3.0)
Ответы 1
Поскольку вы имеете дело с SQL, просто используйте SQL.
collect(mydb1) %>%
nrow()
# [1] 20
DBI::dbExecute(con, "insert into mydata1 select * from mydata2")
# [1] 20
collect(mydb1) %>%
nrow()
# [1] 40
collect(mydb1) %>%
tail()
# # A tibble: 6 x 2
# x y
# <int> <chr>
# 1 115 d
# 2 116 e
# 3 117 d
# 4 118 e
# 5 119 d
# 6 120 e
Если вы хотите объединить данные в новую таблицу, то вот альтернатива.
DBI::dbExecute(con, "
create table mydata12 as
select * from mydata2 union all select * from mydata1")
Спасибо. О вашем предложении создать новую таблицу с объединенными данными (это то, что мне действительно нужно): требуется ли загрузка данных в память? Я буду работать с большими таблицами, и мне абсолютно необходимо этого избегать. Я считаю, что должен быть в безопасности, поскольку мы говорим о sql, но можете ли вы это подтвердить?
Он не загружает данные в R; единственные данные, возвращаемые R по первой команде dbExecute, — это 20, целое число, количество строк, затронутых этой командой; во втором вызове (создать) он снова возвращает целое число (но не количество возвращаемых строк). Ни возвращает кадр, ни загружает объединенный набор данных в R.
Я не могу не задаться вопросом, действительно ли вы имеете дело с «большими таблицами» в SQLite? Хотя я знаю, что он может работать с большими данными, может быть момент, когда вам имеет смысл работать с большим количеством кластеризованных СУБД.
Ну, может быть, гигабайты, если данные не являются большими данными в наши дни, но это для моей опытной рабочей станции 😃
Обсуждение СУБД/размера в стороне, работает ли это для вас?
Спасибо! Я надеялся на чистое решение dbplyr, но ваше работает и напоминает мне, что пришло время изучить sql по-настоящему.
Да, я это понял, но, к сожалению, не знаю, как это сделать в dbplyr-родных глаголах.
Просто запустите
UNION ALLв базе данных.