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_1andstatic_source_2: static tables.ticking_source: a ticking table, which updates while the query runs.time_tableadds a row to it every second. See Deephaven's table types.rand_choiceandget_random_string: helper functions that generate random strings.letters_upp: a string of the uppercase letters A to Z, passed torand_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:
bool_colbyte_colchar_coldatetime_coldouble_colfloat_colint_coljobj_collong_colpyobj_colshort_colstring_col
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:
sortwithorder
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:
byteshortintlongfloatdouble
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
typetoNaturalJoinType.FIRST_MATCHorNaturalJoinType.LAST_MATCH(fromdeephaven.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.
Related documentation
- Work with time
- Deephaven's table types
- Filter table data
- Select and create columns
- Strings
- Use the ternary conditional operator in query strings
- Group and ungroup data
- Single aggregation
- Multi-aggregation
- Rolling aggregations
- Exact and relational joins
- Inexact, time-series, and range joins
- Work with calendars
- Python variables in query strings
- Python functions in query strings
- Convert between Deephaven tables and pandas DataFrames
- Apply color formatting to columns
- Deephaven Express
- Built-in plotting API
- Write data to an in-memory, real-time table
- Read CSV or other delimited files
- Read Parquet files into Deephaven tables
- CSV cheat sheet
- Parquet cheat sheet
- Simple table constructors
- Time operations cheat sheet
agg_byagg.avgagg.formulaagg.max_aj- Arrays
avg_byawait_update- Casting
count_bycum_sumdrop_columnsdouble_colDynamicTableWriterema_timeempty_tableexact_joinfirst_byformat_column_whereformat_columnsformat_row_wheregroup_byheadhead_byhead_pctinput_tableint_coljoinlast_bylayout_hintslazy_updatemax_bymedian_bymergemerge_sortedmeta_tablemin_bymove_columnsmove_columns_up- Named colors
natural_joinnew_table- Parquet
read - Parquet
write plot_xyplot_xy_histplot_stylerajread_csvrename_columnsreversering_tablerolling_sum_tickrollupselectselect_distinctset_calendarsnapshotsnapshot_whensortsort_descending- Special variables
std_bystring_colsum_bytailtail_bytail_pcttable_publishertime_tableto_j_instantto_numpyto_pandasto_tabletreeungroupupdateupdate_byupdate_viewvar_byviewwherewhere_inwhere_not_inwhere_one_ofwrite_csv- Pydoc