Select and create columns

You often need to select, create, or modify columns in a table. Deephaven has five table operations for this:

• select
• view
• update
• update_view
• lazy_update

All five operations work on real-time (updating), static, and snapshotted tables.

All five operations take the same kind of formula arguments, but select and view must also list the source columns to keep. The five queries below produce the same table. They differ in when the engine computes the results and whether it stores them. To pick one, see Choose the right column selection method.

Use query strings in column formulas

In all five operations, each formula is a query string. You can use query strings to:

• Transform data and do math.
• Use common operators, literals, objects, and special variables.
• Cast data types.
• Parse and manipulate strings.
• Handle arrays.
• Use built-in functions.
• Operate on time.
• Bin data.
• Introduce ternaries.

You can also call Java methods, Python functions, and third-party library functions within these table operations.

The following script uses query strings in an update operation. The same formulas work in the other four operations.

Each column name says which feature its formula shows. Dividing two int values returns a double, so ColOperator is a double column, and ColCast casts it back to int. These formulas are just a taste of what you can do with query strings.

Choose the right column selection method

Which of the five operations you use depends on two choices:

• Whether the new table keeps all of the source columns or only the ones you list.
• How the engine produces the new column's values. There are three column types:
  • An in-memory column computes its values in advance and stores them.
  • A formula column stores only the formula. It computes a cell each time a consumer, such as a client or a downstream table, reads it.
  • A memoized column computes a cell the first time a consumer reads it, then caches the result.

The following table shows each operation's behavior. The subsections below explain each choice.

	Source columns in new table		New column type
Operation	Subset	All	In-memory	Formula	Memoized
select
view
update
update_view
lazy_update

Keep all source columns or only the ones you list

• select and view return only the columns you list.
• update, update_view, and lazy_update return all of the original columns of the table, as well as the columns you list.

Notice below how t_select and t_view return only Column2, whereas the others return the original Column1, as well as the new Column2.

To modify an existing column, give the formula that column's name. For example, source.update(formulas=["Column1 = Column1 * 2"]) replaces Column1 with the doubled values.

Store new columns in memory or calculate them on demand

Each operation creates one of the three column types defined above. A column's consumers include clients and downstream tables in the DAG, the graph of dependent table operations.

• view and update_view create formula columns, which store only their formula and compute values on demand, recalculating a cell each time a consumer reads it.
• select and update create in-memory columns. They calculate the values and write them to memory, then, on each update cycle, recalculate only the rows that were added or whose input columns changed.
• lazy_update creates memoized columns. It also computes cells on demand, but caches each result by its input values, so it does not recompute a repeated input. See Cache results with lazy_update.

Use update_view or view when one or more of the following is true:

1. The formula is fast to compute.
2. Consumers read only a small fraction of the rows.
3. Consumers read each cell only a few times.
4. You need to minimize memory use.

These conditions aren't always obvious. Prefer update_view or view in situations like these, which usually meet condition 2 or 3:

• The table is the last node in the DAG and it isn't obvious how consumers use the calculated column.
• The table updates in real time, so the new column's values change often, but clients and other consumers read it only occasionally.
• The table is big, and the column is an intermediate result used only once in downstream calculations.
• The only consumers of the new column are people using a GUI. A GUI reads only the rows visible in its viewport, which is a small number of rows.

Otherwise, use select or update. If the formula has few distinct input values relative to the number of rows, consider lazy_update instead. See Cache results with lazy_update.

To turn formula columns into in-memory columns later, call select with no arguments. It evaluates every formula column and stores the results. Columns that are already in memory are usually kept as they are, without a copy.

Cache results with lazy_update

lazy_update computes column formulas on demand, like update_view, and caches each result in a map keyed by the formula's input values.

• It computes nothing for a cell until a consumer reads it.
• It computes the formula only once for each distinct input.
• Like update and update_view, it references the source table's existing columns instead of copying them.

Use lazy_update when a formula has few distinct input values relative to the number of rows. It then uses less memory than update and less computation than update_view. On a ticking table, the cache keeps every distinct input it has seen and never evicts any, so this advantage holds only while the number of distinct inputs stays small over the table's lifetime. With many distinct input values, update is more efficient, because it stores results in an array, which is more compact than a map.

Here is an example of lazy_update. Column C contains only two distinct values (2 and 5), so the engine computes sqrt(2) and sqrt(5) once each, the first time a consumer reads a cell that needs them. The other two cells where C is 5 reuse the cached result instead of recomputing it.

Related documentation

• select
• view
• update
• update_view
• lazy_update
• Query strings
• Formulas
• Python functions in query strings
• Directed acyclic graph (DAG)
• Incremental update model
• Blog: Compute data immediately or as needed