Ultimate cheat sheet

Necessary data

The named tables in these lines are used throughout this page and should be run first.

Create tables

Empty tables

• emptyTable

New tables

• newTable

Columns are created using the following methods:

• booleanCol
• byteCol
• charCol
• doubleCol
• floatCol
• instantCol
• intCol
• longCol
• shortCol
• stringCol

Time tables

• timeTable

The following code makes a timeTable that updates every second.

Input tables

Use an InputTable when you would like to add or modify data in table cells directly.

Ring tables

A Ring Table is a table that contains the n most recent rows from a source table. If the table is non-static, rows outside of n will disappear from the ring table as the source table updates.

Tree tables

A tree table has collapsible subsections that can be be expanded in the UI for more detail.

Rollup tables

A rollup table "rolls" several child specifications into one parent specification:

Merge tables

To merge tables, the schema must be identical: same column names, same column data types. This applies to both static and updating tables.

• merge
• mergeSorted

View table metadata

meta shows the column names, data types, partitions, and groups for the table. It is useful to make sure the schema matches before merging.

Filter

Most queries benefit by starting with filters. Less data generally means better performance.

For SQL developers: In Deephaven, joins are not the primary filtering operation. Use where, whereIn, and whereNotIn.

Date & Time examples

String examples

Number examples

Multiple filters

where

whereIn/whereNotIn

Nulls and NaNs

Head and Tail

Used to reduce the number of rows:

• tail
• tailPct
• head
• headPct

Sort

Single direction sorting:

• sort
• sortDescending

Sort on multiple columns or directions:

• sort(sortColumns)

Reverse the order of rows in a table:

• reverse

Select and create new columns

Option 1: Choose and add new columns - Calculate and write to memory

Use select and update when data is expensive to calculate or accessed frequently. Results are saved in RAM for faster access, but they take more memory.

Option 2: Choose and add new columns - Reference a formula and calculate on the fly

Use view and updateView when formula is quick or only a portion of the data is used at a time. Minimizes RAM used.

Option 3: Add new columns - Reference a formula and calculate on the fly

Use lazyUpdate when there are a relatively small number of unique values. On-demand formula results are stored in cache and re-used.

Getting the row number

Do math

Handle arrays

Create a slice or sub-vector

Manipulate time and calendars

Bin data

Binning is a great pre-step for aggregating to support the down-sampling or other profiling of data.

Manipulate strings

You can use any of the standard Java String operators in your queries, as the following examples show:

Use Ternaries (If-Thens)

Create and use custom variables

Create and use a custom function

See our guides:

• How to write a Groovy closure

Type casting

(type) casting is used to cast from one numeric primitive type handled by Java to another.

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

This is useful when working with operations that require more or less precision than the pre-cast data type. See casting.

cast numeric types

Casting strings

Manipulate columns

Group and aggregate

See How to group and ungroup data for more details.

Simple grouping

Ungrouping

Expands each row so that each value in any array inside that row becomes itself a new row.

Aggregations

• How to perform dedicated aggregations for groups
• How to perform multiple aggregations for groups

Join data

See Choose the right join method for more details.

Joins that get used a lot

Natural Join

naturalJoin

• 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 will appear for that row in the columns from the right table.
• If there are multiple matches in the right table for a given row, the query will fail.

leftTable.naturalJoin(rightTable, columnsToMatch, columnsToAdd)

Multiple Keys

AJ (As-Of Join)

As-of joins are time series joins. They can also be used with other ordered numerics as the join key(s). It is often wise to make sure the Right-table is sorted (based on the key). aj is designed to find "the exact match" of the key or "the record just before". For timestamp aj-keys, this means "that time or the record just before".

leftTable = rightTable.aj(columnsToMatch, columnsToAdd)

Reverse As-of joins raj find "the exact match" of the key or "the record just after". For timestamp reverse aj-keys, this means "that time or the record just after".

result = leftTable.raj(rightTable, columnsToMatch, columnsToAdd)

Less common joins

join returns all rows that match between the left and right tables, potentially with duplicates. Similar to SQL inner join.

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

exactJoin

• Returns all rows of leftTable.
• If there are no matching keys, the result will fail.
• Multiple matches will fail.

Use columns as arrays and cells as variables

Read and write files

It is very easy to import files and to export any table to a CSV via the UI.

CSVs and Parquet files imported from a server-side directory should be done via the script below - these are NOT working examples. The code below provides syntax, but the filepaths are unknown.

Do Cum-Sum and Rolling Average

Another example of creating a rolling sum

Set up an EMA

Format tables

Datetime formatting

Number formatting

Color formatting

You will find an itemization of stock colors in the docs.

Plot programmatically

Substantial documentation about plotting exists. The following example intends to show the basics, and everything about styling and labeling is omitted.

Time series plots

Bar chart

Plot-by-some key

Histogram

Area graph

Stacked Area

Scatter

Use layout hints

The setLayoutHints method creates a new table with the layout specified by the parameters.

Other useful methods

Wait for table updates

Use awaitUpdate to instruct Deephaven to wait for updates to a specified table before continuing.

Convert dynamic tables to static tables

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

Reduce ticking frequency

Uses snapshotWhen to reduce the ticking frequency.

Capture the history of ticking tables

Uses snapshotWhen to capture the history of ticking tables.

Use DynamicTableWriter

See our guide How to write data to an in-memory, real-time table.