Create and use partitioned tables

This guide shows you how to create partitioned tables, work with their constituent tables, and use them to improve query performance.

What is a partitioned table?

A partitioned table is a special Deephaven table that holds other tables, called constituent tables or subtables. The partitioned table stores its constituent tables in one column, called the constituent column. A partitioned table usually also has key columns that index its constituent tables, so you can access a particular one by key. partitionBy creates these key columns from the columns you partition on. All constituent tables in a partitioned table must have the same schema.

This guide also calls each constituent table a partition.

You can think of a partitioned table in two ways:

1. A list of tables stacked vertically. You can combine this list into a single table with merge.
2. A map of tables. You can retrieve a constituent table by key with constituentFor.

Partitioned tables are typically used to:

• Quickly retrieve subtables, in a user interface or in place of filters called repeatedly in a loop.
• Parallelize queries across multiple threads.
• Avoid tick amplification from grouping and ungrouping.

Create a partitioned table

Common ways to create a partitioned table include:

• From a table
• From a Kafka stream

You can also create one with partitionedAggBy, or from existing tables with the PartitionedTableFactory methods of and ofTables.

From a table

The simplest way to create a partitioned table is from another table. To show this, let's first create a new table.

Creating a partitioned table from the source table requires only one table operation: partitionBy. In this simple case, partitionBy splits source into subtables on the single key column Letter. The resulting partitioned table has three constituent tables, one for each unique value in the Letter column.

The key columns and the constituent column described in What is a partitioned table? live in an ordinary table that has one row per constituent. You access that table with the partitioned table's table method. The example below selects the distinct key values from that table to show the partition keys.

You can partition a table by more than one key column. The following source table has two columns to use as keys: Exchange and Coin.

To partition by multiple keys, pass every key column to partitionBy.

From a Kafka stream

Deephaven can consume data from Kafka streams. Use consumeToTable to ingest streaming data into a table, or consumeToPartitionedTable to ingest it directly into a partitioned table.

A partitioned table consumed from Kafka has one constituent table for each partition of the Kafka topic. A Kafka topic partition is Kafka's own unit of a topic, not a partitioned-table partition. The Kafka partition number is the key, stored in the Partition column.

The following example ingests data from the Kafka topic orders directly into a partitioned table.

For more information and examples on ingesting Kafka streams directly into partitioned tables, see consumeToPartitionedTable.

Work with partitioned tables

Partitioned tables have their own methods, which differ from those of standard tables. This section covers the most common ones. The Related documentation section links to the reference page for each method.

The following code uses newTable to create two tables, quotes and trades, with hypothetical stock quote and trade data. It then partitions each one on its Ticker column into ptQuotes and ptTrades, which the rest of this section uses.

Grab a constituent with constituentFor

When you create a partitioned table with partitionBy, each constituent has a unique key, so you can retrieve it with constituentFor. The code block below grabs a constituent from each of the ptQuotes and ptTrades tables based on a key value.

Merge

Just as you can merge a list of tables with identical schemas into a single table, a partitioned table's merge method stacks its constituents vertically into a single standard table. This is often the best and easiest way to get a standard table from a partitioned table.

The following example merges the ptQuotes and ptTrades tables to return tables similar to the original quotes and trades tables they were created from.

Modify a partitioned table

There are two ways to apply table operations to every constituent of a partitioned table: transform and proxy. A proxy supports most common table operations, and a transform can apply any function that returns a table. To choose between them, see Should I use transform or proxy?.

Transform

A transform applies a user-defined transformation function to every constituent of a partitioned table. The function receives each constituent as a standard table, so it can call any standard table operation. When the transformation function uses operations that compile formulas or use the query scope, such as update and where, it must run them inside an execution context. In a console session, open the context inside the function, as shown below.

The engine can call the transformation function on several constituents at once, and on an empty constituent. Write it so that it keeps no state between calls, is safe to run concurrently, and returns a valid table for an empty input.

The following code block uses transform to apply an update to every constituent of the ptTrades table.

Proxy

A PartitionedTable.Proxy is an alternative to transform. You call standard table operations on the proxy, and the proxy applies each one to every constituent of the partitioned table.

The following code block applies an update to every constituent of the ptTrades table by creating a proxy rather than applying a transform.

Should I use transform or proxy?

A transform and a proxy produce the same results for the operations a proxy supports. Choose based on what matters more for your query:

• Use transform for greater control and, for some queries, better performance. A transform can apply any operation, including ones a proxy doesn't provide, such as renameColumns.
• Use proxy to call standard table operations directly on a partitioned table.

Combine two partitioned tables with partitionedTransform

You combine standard tables with joins. To join two partitioned tables constituent by constituent, use partitionedTransform.

A partitioned transform is similar to a transform, except the transformation function takes two tables as input and returns a single table. The function can apply any table operations to either input table. It has the same requirements as a transform function: it keeps no state between calls, is safe to run concurrently, and returns a valid table for empty inputs.

Most uses of partitionedTransform join the two partitioned tables together, as in the following example.

You can get the same result with a partitioned table proxy.

Why use partitioned tables?

So far this guide has shown how you can use partitioned tables in your queries. The following sections explain why you may want to use them, covering the uses listed in What is a partitioned table?.

Quickly retrieve subtables

If a partitioned table has key columns, you can use it like a map and retrieve constituent tables with constituentFor. This can be useful in user interface components that need to display a subset of a table quickly.

For instance, the following animation shows the ptTrades partitioned table from the earlier examples opened in the UI. The UI displays the constituent for the key value IBM. Menu options atop the table let you view the key values, merge the table, or switch between key values to see data in each constituent.

The same lookup can replace filters called repeatedly in a loop. Instead of calling where on the full table once per key, partition the table once and retrieve each key's constituent with constituentFor.

Parallelization

Partitioned tables can improve query performance by running an operation on each constituent in parallel. Take, for example, an as-of join between two tables. An as-of join matches some key columns exactly and matches the last column, usually a timestamp, to the closest value at or before it. If you partition both tables by the exactly matched columns and join them with partitionedTransform or a proxy, the engine can join each pair of constituents in parallel.

The following example shows this in action. It creates two new, larger tables, quotes and trades, which replace the smaller tables of the same names used earlier on this page. Each has 5 million rows of data, split across 4 unique exchanges and 7 unique symbols, for a total of 28 partitions. It then performs the join operation on both the standard and partitioned tables and times how long each takes.

On a machine with multiple cores, the partitioned version is usually faster because the engine runs the join on each pair of constituents in parallel.

Partitioned tables don't improve performance in every case. Parallelization helps most when:

• Shared resources between concurrent tasks are minimal.
• Each partition holds a substantial amount of data, rather than the data being spread across many nearly empty partitions.

If you are unsure whether partitioned tables could improve your query's performance, ask on Slack for guidance.

Avoid tick amplification

Partitioned tables also help avoid tick amplification. Tick amplification occurs when an operation produces downstream updates that touch many more cells than the upstream change. A common example is groupBy followed by ungroup. If you use partitionBy and merge instead, the engine processes each constituent independently and does not check entire groups for changes.

Related documentation

• partitionBy
• constituentChangesPermitted
• constituentColumnName
• constituentDefinition
• constituentFor
• constituents
• consumeToPartitionedTable
• filter
• keyColumnNames
• merge
• partitionedAggBy
• partitionedTransform
• proxy
• sort
• table
• transform
• uniqueKeys
• Javadoc