Append two tables from SQLite database before query using R

Question

I have an SQLite database with several large tables containing the same variables. Usually I establish a connection and make a query (using dbplyr) like this:

database <- dbConnect(RSQLite::SQLite(), "/database.sqlite")

table_1 <- tbl(database, "datatable_1")
table_2 <- tbl(database, "datatable_2")

table_1_final <- table_1 %>% filter(id < 1000) %<% collect()
table_2_final <- table_2 %>% filter(id < 1000) %<% collect()

Instead of doing

table_final <- bind_rows(table_1_final, table_2_final)

I would like to append the two tables in a first step so that I can make a query like this

table_final <- table %>% filter(id < 1000) %>% collect()

where table already refers to an the appended tables datatable_1 and datatable_2 from the SQLite database.

However I do not want to create the appended table permanently inside the database and I do not want to load them into memory (too big).

(How) can I achieve this?

Answer 1

you can use union_all for this:

Example Code:

library(dplyr, warn.conflicts = FALSE)
data("mtcars")

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars[1,], "mtcars1")
copy_to(con, mtcars[2,], "mtcars2")

mtcars1 <- tbl(con, "mtcars1")
mtcars2 <- tbl(con, "mtcars2")

mt_combined <- union_all(mtcars1,mtcars2)

mt_combined <- collect(mt_combined)

This performs the UNION SQL Statement, if both tables are from the same Databes connection.