Arrays

This guide shows you how to work with arrays in query strings.

Arrays are an invaluable tool for grouping related data. They provide an easy way to access previous and future values in time-series data, and support for them is built into the Deephaven Query Language (DQL).

Array column types

On this page, "array column" means a column of Java arrays, Deephaven vectors, or Python lists wrapped as PyListWrapper. The following sections describe each. "Java array" always means the first kind.

Java array columns

Java array columns hold Java arrays, most often of primitive types such as int[] or double[]. For example, the following query creates a table with a single row containing an array of primitive integers.

You can also create Java primitive array columns with Python functions. When a function has a sequence or NumPy array return type hint, as list_func and np_array_func do, Deephaven stores the result as a Java array:

ArrayFromPython is a long[] column, because Deephaven maps Python int to Java long. ArrayFromNumpy is an int[] column, because of the np.int32 hint.

Built-in query language functions such as sum and len accept Java array columns directly. For example, len(X) returns 3 for the X column in the first example. The same functions work on vector columns, as Get array length and Functions with array arguments show.

Vector columns

Vector columns hold Deephaven vectors, such as LongVector or DoubleVector, rather than Java arrays. Operations such as grouping produce them. The following example creates a vector column by calling group_by:

For other operations that produce vector columns, see Create vector columns. To compare vectors with Java arrays, see Arrays vs vectors.

PyListWrapper columns

An org.jpy.PyListWrapper is jpy's Java List wrapper around a Python list. Its elements are Python objects, so Deephaven can't infer their type.

This column type appears when you use a Python list in a query string without explicit type information, as in the following example:

source_meta shows that the List column has the type org.jpy.PyListWrapper. Only Python lists become PyListWrapper columns. Other Python values reach the query string as follows:

  • Tuples, NumPy arrays, and other sequences that aren't strings become PyObjects.
  • Strings become Java String values.
  • Java arrays, such as those that the jpy and deephaven.dtypes.array examples below create, stay Java arrays.

You can access individual elements from Python lists in query strings using standard indexing syntax. However, Deephaven can't infer the data type of these elements, so cast each one to the type you need:

PyListWrapper is the array equivalent of org.jpy.PyObject. Query strings process it slowly, and fewer operations accept it than the other array column types. To avoid it, convert the list to a Java array with jpy or deephaven.dtypes.array before you use it in a query string.

Using jpy:

Using deephaven.dtypes.array:

Create vector columns

Grouping with group_by, shown in Vector columns, is one way to create vector columns. This section shows two others. Several operations collect values into vectors, and the underscore operator gives a vector view of any column.

Using aggregations, update_by, and joins

Several operations produce vector columns:

The following example calls rolling_group_tick to create a vector column:

Each TickGroup cell holds the current row's Value and up to two earlier Values with the same Key, because the rev_ticks=3 window includes the current row.

Using the underscore operator

Every column in a table has an associated column array variable, which holds the column's values as a vector. You refer to it by adding the underscore (_) operator to the column name. This syntax is specific to Deephaven. For example, X_ is the column array variable for column X:

Each row of A holds the entire X column as a vector.

Note

By default, the engine rejects formulas that use column array variables such as X_ on refreshing tables, because it can't safely update their results as the table changes. The engine allows two kinds of these formulas anyway:

  • Formulas on blink tables.
  • Formulas on any table that read a constant offset from the current row, such as X_[ii - 1]. Here, ii is the current row index.

See Refreshing table restrictions for the full rules and Alternatives for refreshing tables for other approaches.

Convert between Java arrays and vectors

Since Deephaven tables commonly use both Java primitive arrays and Deephaven vectors, it's useful to convert between the two. Use the built-in array function to convert a vector to a Java array, and vec to convert a Java array to a vector. The following example converts a vector column to an array column, then converts that array column back to a vector column:

To convert a Python list to a Java array instead, see PyListWrapper columns.

Get array length

The len function returns the length of its argument. Use it in a query string to get the size of a Vector or a Java array.

Vectors also have a size method, so X.size() returns the same value as len(X) in this example.

Access array elements

Use the square bracket operators [] to access elements in array columns. The following example indexes X_, the column array variable for column X described in Using the underscore operator. It uses the row index ii to read the elements before and after the current row:

Note

The first row of column A is null because X_[ii - 1] reads index -1, which is out of bounds. The last row of column B is null because X_[ii + 1] reads index 10, one past the last element.

On a vector, any out-of-range index returns null, even a negative one. On a Java array, an out-of-range index throws an ArrayIndexOutOfBoundsException, so check the index against len first.

To read the same position from every row's array, use a constant index. The following example reads the elements at indexes 2 and 3 of each group in the vector column Y:

Slice arrays

You can slice a vector into a smaller vector with subVector. The first argument is the start index, which is inclusive, and the second is the end index, which is exclusive.

To slice a Java array, first convert it to a vector with vec, as shown in Convert between Java arrays and vectors.

The following example slices the vector column Y, then grabs a specific element from the slice.

Functions with array arguments

Built-in query language functions

Many built-in query language functions take arrays as input. The following example uses the sum and avg functions on a vector column created by group_by. The same functions also accept Java array columns.

Caution

On refreshing tables, which update as new data arrives, avoid group_by followed by calculations like the one above. Use dedicated aggregations, combined aggregations, or rolling aggregations with update_by instead. Their built-in statistics, such as sums and averages, update incrementally. A formula over a grouped column recomputes the whole group each time the group changes.

Python functions

Query strings can also call Python functions that take arrays as input. The following example calls a Python function that takes a NumPy array as input: