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 Groovy variables, closures, and classes defined in your session. They are the entry point to a universe of powerful built-in tools, starting with Deephaven's built-in function library.

Syntax

Query strings are Groovy 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 method 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, as in the Literals section.

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

Additionally, you can create Java objects for use in query strings with the new keyword. The following example creates new instances of Java's URI class.

Arrays

Deephaven tables can have array columns. Array columns often come from a groupBy 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.

Groovy in query strings

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

Query strings can also call Groovy closures.

So can classes.

Closures don't expose a return type, and the class methods above don't declare one, so the engine can't infer what data type they return. It stores each result as a Java Object. Plain Groovy variables are different: the engine knows their types, so the Sum column in addVars is an int.

This isn't ideal, as Object columns don't support many of the query language features we've covered. To fix this, cast the results of Groovy closures and methods in the query string. The following example casts the results of mySum and the MyMathClass methods defined above.

To learn more about using Groovy in query strings, see the user guides on functions and classes.

Scoping

Query strings resolve names from the query scope, the set of variables the engine can see when it compiles a query string. Variables you assign at the top level of a Groovy script without def or a type, such as int1 = 10, are in the query scope automatically. Variables declared with def or a type are not in the query scope, and neither are closure parameters. To use one of those in a query string, add it to the query scope with QueryScope.addParam.

compute adds anInteger to the query scope as intInScope, so result1 uses 10 and result2 uses 3.

For more information, see the scoping rules.

Stateless and stateful functions

Know whether each Groovy closure 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.

getElementStateless is stateless because it reads myList 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 withSerial on the result. In the following example, the update call that uses a stateful closure produces the same table as tStateless because the query string is marked serial.

Print idx to verify that getElementStateful 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 withSerial.
• 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 Groovy 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.