unionAll
Syntax
unionAll(tableA, tableB, [byColName=false])
or
unionAll(tables, [partition=true], [byColName=false])
or
unionAll(tables, tableB)
Arguments
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 is an optional Boolean parameter with the default value of true.
byColName 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.
Note: The following table types are supported: table, keyedTable, indexedTable, latestKeyedTable, latestIndexedTable. tableB of the third scenario can also be a partitioned in-memory table.
Details
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.
Examples
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;
id |
x |
|---|---|
1 |
11 |
2 |
12 |
3 |
13 |
4 |
14 |
5 |
15 |
6 |
16 |
$ typestr(re);
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;
id |
x |
|---|---|
1 |
11 |
2 |
12 |
3 |
13 |
4 |
14 |
5 |
15 |
6 |
16 |
7 |
17 |
8 |
18 |
$ typestr(re);
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);
id |
x |
|---|---|
1 |
11 |
2 |
12 |
3 |
13 |
4 |
14 |
5 |
15 |
6 |
16 |
$ 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);
id |
x |
|---|---|
1 |
11 |
2 |
12 |
3 |
13 |
14 |
4 |
15 |
5 |
16 |
6 |
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);
id |
x |
y |
|---|---|---|
1 |
11 |
21 |
2 |
12 |
22 |
3 |
13 |
23 |
4 |
14 |
|
5 |
15 |
|
6 |
16 |
|
$ 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