An overview of query strings

Query strings are the main way you tell Deephaven's query engine what to compute. Almost every query uses them, so a conceptual understanding of the Deephaven Query Language (DQL) and query strings is essential for writing correct, performant, and effective queries. The guides in this section teach you what you need to know about query strings and DQL. This page gives a high-level overview of the most common query string features, with links to the pages that cover each one in depth.

What is a query string?

A query string is a string that you pass as input to a table operation such as select, update, or where.

Query strings are written in DQL, which is simple and natural to use. Its syntax is based on Java expressions, with a few Deephaven additions such as backtick string literals and the underscore operator (_). Don't worry: you don't need to know Java to write query strings. Query strings can use Groovy variables, closures, and objects as well as Java classes and objects.

The same query string works on static tables and on refreshing (real-time) tables, with the few restrictions this page notes.

The following code block uses a simple query string to create a new column called NewColumn in a table called source. The string passed into update is the query string. The query string NewColumn = 1 is a formula that creates NewColumn and sets each of its values to 1.

Formulas and filters

Query strings that you pass to selection operations such as select and update are formulas, which create columns. Query strings that you pass to filtering operations such as where are filters, which keep or drop rows.

Formulas

Formulas contain assignments, usually in the form LHS = RHS. The left-hand side, or LHS, names the new column. The right-hand side, or RHS, defines the values that go in that column.

The following example uses five formulas to create a table with five columns:

The Z formula uses the ternary conditional operator (condition ? a : b) to choose between two values. Query strings support many other operators as well.

All of the formulas above use query language built-ins, which the Query language built-ins section explains.

Filters

Filters evaluate to true or false and determine which rows the result table includes. Consider the following example, which uses the table created in the previous section:

Passing several filters to one where call keeps only rows that match all of them, as in resultConjunctive. Use || inside a single filter to keep rows that match either condition, as in resultDisjunctive.

For more on filtering table data, see Filter table data.

What a query string can contain

Both formulas and filters can use literals, built-in constants, variables, and functions, Groovy variables, closures, and objects, and array access. They can also call Java classes and objects directly.

Literals

A literal is a fixed value written directly in a query string, rather than defined outside it and passed in. The earlier formula and filter examples already use several, such as 0.2 and `Even`.

The following example creates a table with one column for each of several common literal types. The literalsMeta table, created with meta, shows each column's data type.

For more on literals, see:

Query language built-ins

The query language offers many constants, variables, and functions that you can use in a query string without importing anything. These built-ins are generally the most performant way to accomplish tasks in a query string. There are too many to list here.

The formulas example already uses five of them. The following query adds two built-in constants to that example's source table:

The formulas example and the query above use the following built-ins:

  • ii: A built-in variable representing the current row number as a 64-bit integer.
  • sin: A function that returns the sine of its argument.
  • randomDouble: A function that returns a random double value between its two arguments.
  • now: A function that returns the current date-time as an Instant.
  • SECOND: A constant that equals one second in nanoseconds, used here to space the timestamps one second apart.
  • MAX_LONG: A constant that equals the maximum value of a Java primitive long.
  • NULL_DOUBLE: A constant that Deephaven uses to represent null for a Java primitive double.

Caution

A refreshing table is one whose rows change as new data arrives. By default, a formula or filter that uses ii on a refreshing table throws an error, unless the table is append-only or blink. The exception is ii used as a constant offset inside a column array, such as X_[ii - 1], which works on every table, as Arrays explains. For the rules for i, k, and column arrays such as X_, see refreshing table restrictions.

For more on query language built-in constants, variables, and functions, see:

Groovy

Deephaven lets you use Groovy code in query strings. Query strings can reference Groovy variables, closures, and objects from the query scope. To use the name of a class your script defines, such as MyClass, first import the class into the query library. Groovy classes and objects in query strings shows how.

The following example uses a Groovy variable, a closure (randName), and an object (myObj) of a Groovy class in a query string.

A query string can read a public field directly, as in myObj.b, or call a method on the object, as in myObj.getA(). Calling a static method through the object, as in myObj.scaledValue(5), works without importing MyClass. Each of these columns takes its type from the Groovy declaration, so X, Y, and Z are int columns.

A query string calls a closure with the same syntax as a built-in function: randName() looks like sin(X). Unlike a built-in, a closure returns Object, so Name is an Object column. Cast the call, as in (String)randName(), when you need a typed column. See Groovy closures in queries.

For more on using Groovy in query strings, see:

Arrays

Columns can hold arrays. The groupBy table operation is the most common way to make an array column. It stores each group as a vector, Deephaven's own array type, which is distinct from a Java array. Deephaven provides a suite of built-in methods for working with vectors, including methods that access their elements.

The following example creates a vector column, then uses some of these methods to access elements and subsets of elements in the vector.

In the result:

  • SubsetX holds elements 3 through 7, because the end index of subVector is exclusive.
  • StringX shows up to the first 10 elements as a string.
  • FifthElement is 4, because vector indexes start at 0.

Additionally, the underscore operator (_) lets a query string access any column as an array. For example, the following code grabs previous and next elements from a column:

The first row's PreviousElement and the last row's NextElement are null, because those offsets fall outside the table.

On a refreshing table, column arrays follow these rules by default:

  • An index of i or ii works on every table type, alone or with an integer literal added or subtracted, such as X_[ii] or X_[ii - 1].
  • Any other use throws an error unless the table is a blink table. An append-only table is not exempt. This includes the whole array (X_), a fixed index (X_[0]), and other index expressions (X_[ii * 2]).

For the full rules, see refreshing table restrictions.

For full coverage of arrays in tables, see the Arrays guide.