Merge tables
Deephaven combines tables in two ways. Merge operations stack tables vertically, one on top of the other. Join operations place columns from two tables side by side.
This guide shows how to merge tables. To join tables horizontally, see Exact and relational joins and Inexact, time-series, and range joins.
This guide covers two methods for merging tables. merge stacks whole tables in the order you pass them and works with static and ticking tables. mergeSorted interleaves the rows of static tables that are each already sorted on a key column, so the result is sorted by that column. To merge the tables that make up a partitioned table, use PartitionedTable.merge.
The following code block creates three tables, each with two columns. The examples in the merge and mergeSorted sections use these tables. Each table is sorted by its Number column.
merge
The merge method stacks one or more tables on top of one another.
Pass the source tables either as separate arguments or as a collection, such as a List<Table>.
Note
Every table must have columns with the same names and types, or merge fails with an error. merge skips null inputs, but at least one table must be non-null.
The following example merges two of the tables:
result contains the rows of source1 followed by the rows of source2. Rows from each source table stay together, in the order you pass the tables to merge.
This order also holds when the source tables are ticking. Deephaven inserts each new row with the other rows from its source table, not at the end of the result. For example, if source1 gains a new last row, that row appears in result after all other source1 rows and before all source2 rows.
mergeSorted
The mergeSorted method merges tables that are each already sorted on a key column into one table sorted by that column.
keyColumn is the name of the key column, and tables are the source tables.
Note
Each input table must already be sorted by the key column in ascending order, or the results are undefined. mergeSorted does not support ticking tables. Unlike merge, mergeSorted does not skip null inputs. Every input table must have the same column names, in any order, and each column must have the same type in every table.
When the key column contains no null values, mergeSorted produces the same table as merge followed by sort. It interleaves rows that are already in order instead of sorting the whole merged table, so it typically does less work. The following example merges all three tables by Number both ways:
Both tables contain all rows from the three source tables, interleaved in Number order. If any source table is ticking or isn't sorted on the key column, use merge followed by sort.
Perform efficient merges
Merge all tables in one call
When you have several tables to merge, pass them all to a single merge call instead of merging them one at a time.
The following example is inefficient. It merges each new table into result inside the loop, so every iteration after the first creates a new intermediate merged table:
The following example produces the same table more efficiently. It collects the new tables in a list and calls merge once on that list:
Order ticking tables by how fast they grow
When you merge ticking tables, pass static tables first, then tables that grow slowly, and tables that grow without bound last. To keep each source table's rows together, the result reserves a range of row keys for each source table, in the order you pass them. When a source table outgrows its range, the engine shifts the rows of every table after it. Some downstream operations do extra work for every shifted row.