Python functions in query strings

The ability to use your own custom Python variables, functions, and classes in query strings is one of Deephaven's most powerful features. This guide covers the basic rules for using custom Python functions in query strings.

Caution

Python functions in query strings have performance and memory implications. Python objects live outside the Java heap, so the process's resident memory can grow well beyond the heap size. For performance-critical or high-throughput queries, consider using built-in query language functions instead.

Call a Python function in a query string

You call a Python function in a query string by name, passing arguments by position, just as you would in Python code. Deephaven finds the function through query scope rules, which are similar to Python's LEGB scoping, with some differences. For a function that takes a column value, see Parameter type hints. The following example calls a user-defined function with no arguments to create a new column in a table:

The random_symbol function has no return type hint. meta_table lists each column's name and type, and source_meta shows that Symbol is a PyObject column. A PyObject is a Java wrapper around a Python object. To get a typed column instead, add a type hint to the function, or cast its result in the query string.

Type hints

Type hints in Python specify the expected data types of a function's parameters and return value. Deephaven uses them in two ways:

  • Parameter hints tell Deephaven how to convert the arguments passed to the function.
  • The return hint sets the type of the result column.

For example, a return hint of int produces a Java long column, float produces a double column, and str produces a String column. For the full mapping, see Data types.

Type hints in Deephaven queries:

  • Ensure appropriate data types in columns.
  • Eliminate the need for explicit type casting.
  • Avoid PyObject columns, which are slower and use more memory than typed columns.
  • Make code more maintainable.

The following example adds the -> str return type hint to the random_symbol function from the previous example, so source_meta shows that Symbol is a String column:

Optional return values

Python's Optional type hint declares that a function can return None. When you call such a function in a query string, Deephaven stores each None return as a null value. In the following example, source_meta shows that X is a long column, and every row where ii is even holds a null value:

Array return values

In Python, a sequence is any ordered, indexable collection, such as a list or a tuple. Deephaven stores a function's result in a Java array column when the function's return type hint is one of the following:

  • A sequence with an element type, such as Sequence[str] or list[int].
  • A NumPy array with a dtype, such as numpy.typing.NDArray[np.int64].

The element type sets the array type:

Element typeColumn type
int, np.int64long[]
np.int32int[]
np.int16short[]
np.int8byte[]
float, np.float64double[]
np.float32float[]
bool, np.bool_Boolean[]
str, np.str_String []
datetime, pd.Timestamp, np.datetime64Instant []
np.uint16char[]
Any other typePyObject

Three other kinds of return hint aren't in the table:

  • A Deephaven type from deephaven.dtypes, such as dtypes.int32, maps the same way as its NumPy type.
  • A bytes or bytearray return hint produces a byte[] column.
  • A sequence hint with no element type, such as plain list, produces a PyObject column.

The following example calls random_symbols, which returns a sequence of strings, in a query string. source_meta shows that Symbols is a String [] column:

Multiple return values

Python functions can return multiple values as a tuple. Java has no tuple type, so the return type hint decides how Deephaven stores the result:

  • Without a hint, the column holds PyObject values, as in the first example.
  • With a hint such as tuple[int, int, int], Deephaven treats the tuple as a sequence and stores it in an array column, as described in Array return values.

The first type argument sets the array's element type, so tuple[int, int, int] produces a long[] column. Every element must have that type, so a float element in this hint, even 2.0, makes the query fail.

The following example calls two functions that each return three values in a query string. func_tuple_typehint has a tuple[int, int, int] return hint, so X1 is a long[] column. func_tuple_no_typehint has no hint, so X2 is a PyObject column. func_sum accepts either column, so Y1 and Y2 both hold 6:

Parameter type hints

A parameter type hint tells Deephaven which argument types the function accepts. When Deephaven compiles the query string, it checks each argument against the hint:

  • Deephaven accepts an argument whose Java type converts to the hinted type without loss. An int hint is a Java long, so it accepts long values such as ii and int values such as i.
  • Any other argument type raises an error. For example, you can't pass a double column to an int hint.

Deephaven converts each accepted argument to the hinted Python type before it calls the function. In the following example, both ii and i arrive in times_two as a Python int, and the -> int return hint makes X and Y long columns:

A parameter hint also decides how a null value reaches the function:

  • Without Optional, Deephaven passes the null value of a primitive column as is, so a null long arrives in Python as the integer -9223372036854775808.
  • With an Optional parameter hint, such as value: Optional[int], Deephaven passes each null value as None.

In the following example, X uses the built-in constant NULL_LONG to hold a null value in every row where ii is even. is_none never receives None, so IsNone is false in every row. is_none_optional receives None for each null, so IsNoneOptional is true in the rows where X is null:

Combine with built-in functions

You can use Python functions alongside Deephaven's built-in query language functions. The following example calls the Python function my_func and the built-in cos function in the same query string. Because my_func returns a float, source_meta shows that Y is a double column: