Ultimate cheat sheet

This cheat sheet shows common Deephaven table operations in one place, with a short example of each. Most of them work the same way on static tables and on ticking tables that update in real time; the few exceptions are noted where they apply. Run the code in Necessary data first, because most examples on this page use the tables it creates.

Necessary data

This code creates the following tables, helper functions, and values:

  • static_source_1 and static_source_2: static tables.
  • ticking_source: a ticking table, which updates while the query runs. time_table adds a row to it every second. See Deephaven's table types.
  • rand_choice and get_random_string: helper functions that generate random strings.
  • letters_upp: a string of the uppercase letters A to Z, passed to rand_choice.

The code casts the helper functions' return values with (String), which turns the Python return values into Java strings. See Cast strings.

Note

In query strings, backticks (`) delimit a string, and single quotes (') delimit a literal that the engine parses, such as a date-time, duration, or time zone.

Create tables

Empty tables

New tables

Create columns with the following methods:

Time tables

The following code makes a time_table that updates every second.

Input tables

Use an input_table to add or modify data in table cells directly.

Ring tables

A ring_table keeps the n most recent rows of a source table. When the source table ticks, rows older than the newest n drop out of the ring table.

Tree tables

A tree table has collapsible subsections that you can expand in the UI for more detail.

Rollup tables

A rollup table aggregates the source table at each level of a list of grouping columns. You can expand each level in the UI to show the rows of the level below it.

Tables written from code

A DynamicTableWriter creates an append-only table and writes rows to it from your own code. It is the simpler choice when you add one row at a time. For most other cases, use table_publisher, which publishes rows to a blink table. Write data to an in-memory, real-time table compares the two.

View table metadata

meta_table shows each column's name, data type, column type, and whether it is a partitioning column. Use it to confirm that schemas match before a merge.

Merge tables

Tables passed to merge must have identical schemas, with the same column names and the same column data types. This applies to both static and ticking tables.

Filter

Most queries benefit from filtering early. Less data generally means better performance.

Tip

For SQL developers: in Deephaven, joins are not a primary operation for filtering. Filter your data with where, where_in, and where_not_in before joining. Filtering first reduces the rows each join has to match.

Date and time examples

String examples

Number examples

The mod_example_1 filter condition uses the row-position variable i. See Get the row number for when i and ii are allowed in formulas. The engine doesn't check where filters, so a filter that uses i on a ticking table runs, but its results can go stale as rows change.

Multiple filters

OR filters

A list of filters passed to where keeps rows that match all of them. To keep rows that match at least one condition, either combine the conditions with || in one filter string, as in Multiple filters, or pass the filters to where_one_of.

where_in and where_not_in

Nulls and NaNs

Head and tail

These methods reduce the number of rows:

Sort

Sort on one or more columns in one direction:

Sort in mixed directions, with a direction for each column:

Reverse the order of rows:

Tip

reverse is faster than sort. UIs often use it to show the most recently appended rows at the top of a table.

Select and create new columns

Deephaven has three ways to choose and add columns. They differ in when formulas are calculated and whether the results are stored in memory. For a full comparison, see Select and create columns. The last subsection, Get the row number, shows the built-in row-position variables that any of these methods can use.

Option 1: Calculate and store new columns in memory

Use select and update when data is expensive to calculate or accessed frequently. These methods store results in memory, which speeds up access but uses more memory.

Option 2: Calculate new columns on demand

Use view and update_view when the formula is quick to compute or when queries read only a portion of the data at a time. These methods minimize memory use.

Option 3: Calculate on demand and cache results

Use lazy_update when the formula's input columns have a relatively small number of unique values. It computes each result on demand, caches it, and reuses it for repeated inputs.

Get the row number

i and ii are built-in variables that hold a row's position in the table. i is an int and ii is a long.

Caution

A row's position can change as rows are added or removed. By default, the engine allows i and ii in a formula on a ticking table only if the table is append-only or a blink table. An add-only table whose row keys match its row positions also qualifies. The engine doesn't check where filters, so a filter that uses i or ii on a ticking table runs, but its results can go stale as rows change.

Manipulate columns

These methods list distinct values and rename, drop, and reorder columns.

Do math

Query strings can call Java Math methods and Deephaven's built-in numeric functions, such as abs, exp, log, round, and floor.

Handle arrays

Append an underscore to a column name to use the whole column as an array. For example, Double2_ is the Double2 column as an array. Index the array with i to reach values in other rows. subVector(start, end) returns a slice of an array, from start up to but not including end.

Caution

In a formula, a simple constant offset, such as Double2_[i-1], works on any table. Other column-array expressions, such as Double2_.subVector(i-2, i+1), work in formulas only on static tables and blink tables. On other ticking tables, including append-only tables, the engine rejects them with an error. For rolling calculations on ticking tables, use update_by. See Special variables.

Calculate rolling sums and averages

update_by provides dedicated cumulative and rolling operations, such as cum_sum and rolling_sum_tick.

Create a rolling sum with update_by

update_by with rolling_sum_tick computes a sum over a 20-row window that ends at the current row. It computes a separate sum for each String1 group.

Set up an exponential moving average (EMA)

An exponential moving average (EMA) is an average that weights recent values more heavily than older ones. ema_time computes an EMA with a decay time of 30 seconds, across the whole table and for each String5 group. last_by then keeps the latest row overall and the latest row for each group.

Calculate a rolling window with column arrays

For a static table, you can also compute statistics over a window of rows with column arrays (see Handle arrays) instead of update_by. This example sorts by String3. For each row, it then sums Double2 and averages Int2 over the 10 rows that end at that row. The result is null unless all 10 rows have the same String3 value.

Manipulate time and calendars

Query strings include built-in functions for date-times and business calendars. See Work with time and Work with calendars.

Manipulate strings

Query strings can call Java String methods, as the following examples show:

Use ternaries (if-then)

For details, see Use the ternary conditional operator in query strings.

Create and use custom variables

A query string can use Python variables that are in scope when the query runs. For details, see Python variables in query strings.

Create and use a custom function

For details, see Python functions in query strings.

Cast types

A (type) cast converts a value from one type to another and follows Java casting rules, except that a Deephaven null converts to the null value of the target type. You can cast between the following numeric primitive types:

  • byte
  • short
  • int
  • long
  • float
  • double

You can also cast an object, such as the return value of a Python function, to String or to a numeric type. Casting is useful when an operation needs more or less precision than the original data type. For details, see Casting.

Cast numeric types

This example casts a column of doubles near each type's largest and smallest values to each numeric type. The two metadata tables show the resulting column types.

Each row holds a value near one type's limit. When that value is outside a column's type, the cast wraps, saturates, or overflows to infinity, as in Java, and a result equal to the type's null value, such as Integer.MIN_VALUE for int, displays as null. See Casting.

Cast strings

A Python function without type hints returns a Python object. Cast its return value with (String) to get a Java String column. Java methods such as String.valueOf and Integer.parseInt convert between numbers and strings. Manipulate strings shows more conversions between numbers and strings.

Bin data

Binning groups values into buckets, usually as the first step before an aggregation that downsamples or profiles the data.

Group and aggregate

See Group and ungroup data for more details.

Simple grouping

Ungrouping

ungroup expands array columns so that each array element becomes its own row.

Aggregations

Formula aggregations

agg.formula applies a formula to each group. This example averages three columns for each Letter-Color pair.

Join data

See Choose the right join method for more details.

Common joins

Natural join

natural_join adds columns from the right table to every row of the left table, using at most one matching right row per left row.

  • Returns all the rows of the left table, along with up to one matching row from the right table.
  • If there is no match in the right table for a given row, nulls appear for that row in the columns from the right table.
  • By default, the query fails if there are multiple matches in the right table for a given row. Set type to NaturalJoinType.FIRST_MATCH or NaturalJoinType.LAST_MATCH (from deephaven.table) to keep the first or last match instead.

Natural join on multiple keys

As-of joins (aj and raj)

As-of joins are time-series joins. For each left-table row, aj returns the right-table row whose key matches exactly or, if none does, the closest earlier row. With a timestamp key, that is the row at the same time or the most recent row before it. As-of joins also work on other ordered keys, such as numbers or strings.

A reverse as-of join, raj, returns the exact match or, if there is none, the closest row after it. For a timestamp key, that is the row at the same time or the next later row.

Less common joins

Exact join

exact_join adds columns from the right table to every row of the left table, and each left row must match exactly one right row.

  • Returns all rows of the left table.
  • If a row has no matching key, the query fails.
  • If a row has multiple matching keys in the right table, the query fails.

Inner join

join returns every combination of matching rows from the left and right tables, similar to a SQL inner join.

  • Returns only matching rows.
  • Multiple matches produce duplicate rows, which can result in a long table.

Import and export data

Deephaven reads and writes files, such as CSV and Parquet files, and converts tables to and from NumPy arrays and pandas DataFrames.

Read and write files

The following script reads CSV files from URLs and from the /data directory of a Deephaven Docker container, and writes and reads CSV and Parquet files there. For every parameter, see the read_csv, write_csv, Parquet read, and Parquet write reference pages.

You can also import files and export any table to a CSV file from the UI.

Copy column values into a NumPy array

to_numpy copies column values into a NumPy array, so you can read a cell by position.

Use pandas

to_pandas copies a table into a pandas DataFrame, and to_table converts a DataFrame back into a table.

Format tables

Date-time formatting

Date(...) in format_columns sets how the UI displays a date-time column, using Java date-time pattern letters. The column's data does not change.

Number formatting

Color formatting

For the full list of stock colors, see Named colors.

Use layout hints

layout_hints returns a new table that tells the UI how to arrange its columns. This example freezes Even, moves Odd to the front and B to the back, hides C, and groups A and E under Vowels.

Plot programmatically

Deephaven Express is the recommended choice for most plotting tasks, and the first example below uses it. The remaining examples use the built-in plotting API and show only the basics. For styling, labeling, and more, see the plotting guide.

Deephaven Express

Deephaven Express is a real-time version of Plotly Express.

Time series plots

Bar chart

Plot by key

Passing grouping columns plots one series for each key.

Histogram

Area graph

Stacked area

Scatter

Work with ticking tables

Wait for table updates

Use await_update to wait until a table updates before continuing.

Convert ticking tables to static tables

Use snapshot to create a static, in-memory copy of a source table.

Reduce ticking frequency

snapshot_when takes a snapshot of the source table each time a trigger table ticks, so the result updates only as often as the trigger. The result stays empty until the trigger table first ticks, unless you pass initial=True.

Capture the history of ticking tables

Pass history=True to snapshot_when to append a full snapshot of the source table's rows each time the trigger table ticks, instead of replacing the previous snapshot. The result keeps the history of the ticking table.