unionAll

unionAll(tableA, tableB, [byColName=false])

or

unionAll(tables, [partition=true], [byColName=false])

or

unionAll(tables, tableB)

• For the first scenario, tableA and tableB are 2 tables with the same number of columns.

• For the second scenario,

  • tables is a list of tables with the same number of columns;

  • partition (optional) is a Boolean parameter with the default value of true.

  • byColName (optional) is a Boolean value indicating whether the table combination is conducted along columns with the same name. If byColName =false, the table combination is conducted based on the order of columns regardless of column names.

• For the third scenario, tables is a tuple where the elements represent in-memory tables with the same number of columns. tableB is also an in-memory table having the the same number of columns. This form is often used to specify the finalFunc parameter of the mr function.

For the first scenario, combine 2 tables into a single table. The result is an unpartitioned in-memory table.

For the second scenario, combine multiple tables into a single table. If partitioned is set to "false", the result is an unpartitioned in-memory table; if partitioned is set to "true", the result is a partitioned in-memory table with sequential domain. The default value is "true".

If byColName =false, all tables to be combined must have identical number of columns.

If byColName =true, the tables to be combined can have different number of columns. If a column does not exist in a table, it is filled with NULL values in the final result.

Scenario 1. Combine two in-memory tables.

t1=table(1 2 3 as id, 11 12 13 as x)
t2=table(4 5 6 as id, 14 15 16 as x)
re=unionAll(t1,t2)
re;

typestr(re);
// output
IN-MEMORY TABLE

Scenario 2. Combine multiple in-memory tables.

t1=table(1 2 3 as id, 11 12 13 as x)
t2=table(4 5 6 as id, 14 15 16 as x)
t3=table(7 8 as id, 17 18 as x)
re=unionAll([t1,t2,t3])
select * from re;

typestr(re);
// output
SEGMENTED IN-MEMORY TABLE

Specifies byColName

t1=table(1 2 3 as id, 11 12 13 as x)
t2=table(14 15 16 as x, 4 5 6 as id)
unionAll(t1,t2,true);

t1=table(1 2 3 as id, 11 12 13 as x)
t2=table(14 15 16 as x, 4 5 6 as id)
unionAll(t1,t2);

From the examples above, please make sure column names and their order are identical in all tables to be combined if byColName is not specified (i.e., byColName =false).

t1=table(1 2 3 as id, 11 12 13 as x, 21 22 23 as y)
t2=table(4 5 6 as id, 14 15 16 as x)
unionAll(t1,t2,true);

t1=table(1 2 3 as id, 11 12 13 as x, 21 22 23 as y)
t2=table(4 5 6 as id, 14 15 16 as x)
unionAll(t1, t2) => The number of columns of the table to insert must be the same as that of the original table.

From the examples above, if the tables to be combined have different number of columns, we must set byColName =true.

Scenario 3: Combine multiple in-memory tables and a partitioned in-memory table. Update the partitioned in-memory table with the combination result.

def testFunc(data, off){
    return select *, price * (1-off) as `discountPrice from data
}
n = 100
dates = 2021.01.01..2021.12.31
t = table(take(dates, 365 * n).sort() as `date, `sym + take(1..n, 365 * n).sort()$STRING as `sym, round(10 + norm(0, 2, 365 * n), 2) as `price)

db = database("", VALUE, 2021.01.01..2021.12.31)
trade = db.createPartitionedTable(table=t, tableName="trade", partitionColumns=`date).append!(t)
db = database("", RANGE, date(month(dates.first()) .. (month(dates.last()) + 1)))
outputT=table(1:0, `date`sym`price`discountPrice, [DATE,SYMBOL,DOUBLE,DOUBLE])
ports = db.createPartitionedTable(outputT, "ports", `date)
//map reduce
mr(sqlDS(<select * from trade>), testFunc{,0.3},,unionAll{,ports})

select * from ports