Deephaven's Query Strings

Deephaven query strings are the primary way of expressing commands directly to the Deephaven engine. They tell the engine what to compute, and the engine turns them into filters and formulas, compiling code where needed. Query strings use Java-like syntax and can use Python variables, functions, and objects. They are the entry point to a universe of powerful built-in tools and Python-Java interoperability: Deephaven's built-in function library, and the ability to mix Python and Java objects in one expression.

Syntax

Query strings are Python strings that you pass to table operations. Deephaven highly recommends using double quotes for query strings, because query strings use single quotes for their own date-time and char literals.

Here, the query string NewColumn = 1 defines a formula for the engine to execute, and the update table operation understands that formula as a recipe for creating a new column called NewColumn that contains the value of 1 in each row.

Literals

Query strings often use literals. How the engine interprets a literal depends on how it's written in the query string. The engine reads:

• Unquoted literals as booleans, numbers, or null. Other unquoted names refer to columns or variables.
• Literals in backticks (`) as strings.
• Literals in single quotes (') as date-time values, such as dates, times, durations, periods, and time zones. A single character in single quotes is a char.

The meta_table attribute is useful for assessing a table's schema. You can use it to confirm that the resulting columns are of the correct type.

Special variables and constants

Deephaven provides several special variables and constants. The most commonly used of these are i and ii. They represent the row position as 32-bit and 64-bit integers, int and long, respectively.

Deephaven also provides a range of common constants that you can use in query strings. Their names are in uppercase snake case, such as NULL_INT. They include minimum and maximum values for various data types, conversion factors for time types, and more. Of particular interest are the null constants for primitive types.

These are useful for representing and handling null values of a specific type. Built-in numeric functions generally handle null values. For example, sqrt(NULL_DOUBLE) returns NULL_DOUBLE. Custom functions need to handle null values appropriately.

Common operations

Numeric types support mathematical operations such as +, -, *, /, and %.

The + operator also concatenates strings.

The + and - operators are defined for date-time types, making arithmetic on timestamp data easy.

Query strings support logical operators such as && and ||, and comparison operators such as == and >.

Deephaven provides an inline conditional operator (ternary-if) that makes writing conditional expressions compact and efficient.

In Deephaven, typecasting is easy: query strings support Java-style typecasts.

Deephaven supports many more operators. See the guide on operators to learn more.

Built-in functions

Aside from the common operations, Deephaven hosts a large library of functions known as built-in or auto-imported functions that you can use in query strings.

The numeric subset of this library is full of functions that perform common mathematical operations on numeric types. These include exponentials, trigonometric functions, and more. Random number generators are also available.

Combine these functions with the literals and operators covered earlier to build complex expressions.

The previous chapter, Time and Calendars, used several of the built-in date-time functions, such as now, upperBin, and lowerBin. The library contains many more functions for working with date-time values.

Date-time functions that begin with parse, such as parseInstant and parseDuration, convert strings to date-time types.

The time user guide provides a comprehensive overview of working with date-time data in Deephaven.

These functions provide only a glimpse of what the built-in library offers. There are modules for sorting, searching, string parsing, null handling, and much more. See the document on auto-imported functions for a comprehensive list of what's available or the module summary page for a high-level overview of what's offered.

Java methods

The data structures that underlie Deephaven tables are Java data structures. Many of the literals, operators, and functions covered so far are Java under the hood. Query strings can call methods on Java objects, unlocking new levels of functionality and efficiency.

To find out which methods a column's values have, first check the column's Java type with meta_table, as in the Literals section.

This column is a Java Instant. Its Javadoc lists every method you can call. Here are a few.

A basic understanding of how to read Javadocs helps you make the most of these Java methods.

You can also create Java objects for use in query strings. The following example uses Java's URI class in query strings in three ways:

• t1 uses the new keyword inside the query string.
• t2 uses the Python jpy package to create the object, then passes it into the query string as a Python variable.
• t3 uses jpy to create the object and call its methods in Python, then passes the results into the query string.

For more information, see the jpy guide.

Arrays

Deephaven tables can have array columns. Array columns often come from a group_by operation.

Many operators and built-in functions support array arguments. Operators apply element by element, while functions such as sum and avg reduce the array to a single value.

ungroup expands the array columns back into separate rows.

Deephaven provides array indexing and slicing operations.

Lastly, query strings can treat the columns of a Deephaven table as arrays.

Simple constant-offset access such as X_[ii - 2] works on all tables, even though it uses ii. The engine recognizes this pattern as an exception to the i and ii restriction described in Special variables and constants. Other uses of column arrays, such as sum(X_) or X_.subVector(i, i + 3), work only on static and blink tables. On any other ticking table, including an append-only table, the engine throws an error. See working with arrays and the special variables reference for more information.

Python in query strings

Query strings can use Python objects. Variables are the simplest case.

Query strings can also call Python functions.

So can classes.

Without any type casts or type hints, the Deephaven query engine cannot infer what data type results from a Python function. It stores the result as a Java PyObject or Object. Plain Python variables are different: the engine converts them to Java types, so the Sum column in add_vars is an int.

This isn't ideal, as PyObject and Object columns don't support many of the query language features we've covered. To fix this, add type hints to Python functions so the engine can infer the correct column types. The engine ignores type hints on class methods, so cast class method results in the query string instead.

The following example redefines my_sum with a return type hint and casts the results of the MyMathClass methods defined above.

To learn more about using Python in query strings, see Python in query strings.

Scoping

Functions that return tables or otherwise use query strings should pay careful attention to scoping details. Query strings resolve Python names from the calling function's local scope first, then from the global (module) scope.

Inside compute, the query string's a resolves to the function's local a argument (10, then 3), so result1 and result2 differ.

Python's LEGB rules resolve names from the local, enclosing, global, and built-in scopes. Query strings follow similar rules, with two differences:

• Python's built-in names aren't in scope.
• A query string inside a nested function can't see the enclosing function's variables unless the nested function's own code references them, for example by declaring them nonlocal.

For those cases and more examples, see scoping rules.

Stateless and stateful functions

Know whether each Python function you call in a query string is stateless or stateful. A stateless function has no side effects: it doesn't modify any object outside its own scope. Its result also doesn't depend on execution order, so the engine can evaluate the calls in any order. The following stateless function extracts elements from a list.

get_element_stateless is stateless because it reads my_list but never changes it. It could be evaluated in any order and give the same result.

A stateful function modifies objects outside its local scope: it doesn't leave the world as it found it. Its result can also depend on execution order. The engine assumes functions in query strings are stateless, so it may evaluate rows in any order, including in parallel.

To make the engine evaluate a query string in row order, wrap it with Selectable.parse and call with_serial on the result. In the following example, the update call that uses a stateful function produces the same table as t_stateless because the query string is marked serial.

Print idx to verify that get_element_stateful changed it.

Queries should use stateless functions whenever possible because:

• They minimize side effects when called.
• Their result does not depend on the order of evaluation, so you don't need with_serial.
• The engine is free to choose the execution order, including evaluating them in parallel where it supports it.

This chapter covered the building blocks of query strings: literals, special variables and constants, operators, built-in functions, Java methods, arrays, and Python objects. It also showed how query strings resolve variables and why you should prefer stateless functions. Next, Recipes, not loops! explains why Deephaven queries describe calculations as column formulas rather than loops over rows.