unionSQLDataFrame: Union of 'SQLDataFrame' objects
In SQLDataFrame: Representation of SQL database in DataFrame metaphor

Description Usage Arguments Details Value Examples

Performs union operations on SQLDataFrame objects.

1 2	## S4 method for signature 'SQLDataFrame,SQLDataFrame' union(x, y, localConn, ...)

`x`	A `SQLDataFrame` object.
`y`	A `SQLDataFrame` object.
`localConn`	A MySQL connection with write permission. Will be used only when `union`-ing two SQLDataFrame objects from different MySQL connections (to different MySQL databases), and neither has write permission. The situation is rare and operation is expensive. See Details for suggestions.
`...`	Other arguments passed on to methods.

The union function supports aggregation of SQLDataFrame objects from same or different connection (e.g., cross databases), either with or without write permission.

SQLite database tables are supported by SQLDataFrame package, in the same/cross-database aggregation, and saving.

For MySQL databases, There are different situations:

When the input SQLDataFrame objects connects to same remote MySQL database without write permission (e.g., ensembl), the functions work like dbplyr with the lazy operations and a DataFrame interface. Note that the unioned SQLDataFrame can not be saved using saveSQLDataFrame.

When the input SQLDataFrame objects connects to different MySQL databases, and neither has write permission, the union function is supported but will be quite expensive. To avoid this situation, a more efficient way is to save the database table in local MySQL server (with write permission) using saveSQLDataFrame, and then call the union function again.

More frequent situation will be the union operation on two SQLDataFrame objects, of which at least one has write permission. Then the cross-database aggregation through SQLDataFrame package will be supported by generating federated table from the non-writable connection in the writable connection. Look for MySQL database manual for more details.

NOTE also, that the union operation on SQLDataFrame objects will perform differently on database table from SQLite or MySQL. For SQLite tables, the union will sort the data using the key columns, and write the sorted data into a provided SQLite dbname when saveSQLDataFrame was called. For MySQL tables, union preserves the orders, but will be sorted with key columns and saved into a user provided MySQL database (with write permission) when saveSQLdataFrame was called.

A SQLDataFrame object.

test.db1 <- system.file("extdata/test.db", package = "SQLDataFrame")
test.db2 <- system.file("extdata/test1.db", package = "SQLDataFrame")
con1 <- DBI::dbConnect(DBI::dbDriver("SQLite"), dbname = test.db1)
con2 <- DBI::dbConnect(DBI::dbDriver("SQLite"), dbname = test.db2)
obj1 <- SQLDataFrame(conn = con1,
                     dbtable = "state",
                     dbkey = c("region", "population"))
obj2 <- SQLDataFrame(conn = con2,
                     dbtable = "state1",
                     dbkey = c("region", "population"))
obj1_sub <- obj1[1:10, 2:3]
obj2_sub <- obj2[8:15,2:3]

## union
res_union <- union(obj1_sub, obj2_sub)  ## sorted
dim(res_union)