Think like a Deephaven ninja
This guide collects habits that experienced Deephaven users rely on. It shows you how to:
- Choose the right quotes in query strings.
- Replace loops with table operations.
- Write fast, correct filters.
- Use the ternary operator.
- Call Java from query strings.
- Format chained queries.
Each section stands on its own, so you can read them in any order.
Choose the right quotes
A query string uses quotes at two levels. The outer quotes are Python's own string delimiters, and the inner quotes are part of the Deephaven Query Language (DQL):
| Quote | Where it goes | What it marks |
|---|---|---|
" (double quote) | Around the whole query string | A Python string. Single quotes also work here, but double quotes don't clash with the single quotes inside. |
` (backtick) | Inside a query string | A string value, such as `A` |
' (single quote) | Inside a query string | A date-time, date, time, duration, time period, time zone, or character, such as 'PT10S' or 'ET' |
Choosing the right quote can be confusing. Let's explore where to use each one.
DQL query strings are strings. In Python, you can define strings with either single or double quotes, so "Y = X * X" and 'Y = X * X' are the same query string. This example uses double quotes, as do the other query strings on this page.
Backticks surround strings within a query string. This is easier to understand with an example.
In table result1, the query creates column X and sets it to the string "A". It sets column Y to the string "P" in even rows and the string "Q" in odd rows, using the ternary operator. In table result2, the filter keeps only the rows where the Instrument column equals "BTC/USD" or "DOGE/USD".
Single quotes are the most complex quoting case. Just as backticks surround strings within a query string, single quotes surround date-times, dates, times, durations, time periods, time zones such as 'ET', and characters.
In this example, the query sets column X to a date-time. Column Y holds the date-time 10 seconds after X, and column Z holds the date-time one day after X. Column A holds a character. Single quotes surround the date-time, the duration, the time period, and the character.
There is one catch. A query string that contains single quotes, as these date-time and character literals do, is easiest to write in double quotes. In a single-quoted string, the first unescaped single quote inside it ends the string:
There is no downside to using double quotes for query strings. When in doubt, use double quotes. You can use single quotes with escapes, but they are more difficult to read.
Tip
If a date column is stored as a string rather than a date-time, compare it with a backtick string, such as Date = `2021-09-22`, not a single-quoted date.
Replace loops with table operations
Common programming languages, such as Python, Java, C, C++, and Go, have instructions that operate upon a single piece of data at a time. To operate upon multiple pieces of data, users must write loops. Like NumPy, TensorFlow, and PyTorch, Deephaven uses vectorized operations, where a single command operates upon a whole column of data.
Because looping is so common in standard programming languages, new Deephaven users rely on loops to perform calculations. Their code often looks like:
This code is fairly complex. It has multiple loops, multiple queries, and multiple table merges, and it is slow.
The loop version also breaks on live data. A dynamic query runs on ticking tables, whose rows change while the query runs, and it updates its results as those rows change. The loops read the Date and coin values only once. When a new value appears in either column, the loops never see it, and the result goes stale. Oops.
A more experienced Deephaven user would write the same query as:
This version is easier to read, runs faster, and updates when the source table ticks.
Every time you write a loop or pull values out of a table, as the iter_tuple calls above do, ask yourself how you can accomplish the same goal without the loop and without directly accessing the contents of the Deephaven table. Most changes involve using key columns in aggregations and join columns in joins. At first, these changes seem foreign, but they soon become natural.
As with most rules, there are a few exceptions to the "don't use loops" rule. Most commonly, users break the rule to get around RAM limitations. If you are applying complex calculations to a huge dataset, your calculation may exhaust the RAM available to the Deephaven server. To avoid RAM limitations, you can use a loop to break the massive dataset into smaller datasets that fit into RAM one at a time. Such exceptions to the rule are rare.
Write fast, correct filters
The secret to computational efficiency is being lazy — doing as little work as possible. For Deephaven, this means operating on the minimum possible amount of data. Filter as early in a query as you can, so that later operations such as update and joins run on fewer rows.
where is the most common way to filter a table, but you can express the same logic in different ways.
Caution
This query allocates a billion-row table in memory. If you run it to compare how long t1, t2, and t3 take, run each filter separately.
In this query, t1, t2, and t3 produce the same result, but t2 is the best way to write it:
- In
t1andt2, each simple comparison, such asX > 2, becomes a range filter, which the engine evaluates without compiling a formula. Int3, the engine compiles the combined expressionX > 2 && X < 6into a single formula filter. t2passes both filters to onewhere, so it produces one result table, whilet1builds an intermediate table and filters it again. On tables that support predicate pushdown, onewherealso lets the engine reorder the filters.
You can write many filters as either a formula filter (e.g., where("X == 1 || X == 2 || X == 3")) or a match filter (e.g., where("X in 1,2,3")). When a filter can be expressed either way, use the match filter. Deephaven evaluates a match filter by comparing values directly instead of compiling a formula, so match filters typically perform better.
Filter performance also depends on how a table is structured. Some columns let a filter skip data without reading every row. Two common kinds are:
- A partitioning column is a column whose values split the table's data into separate files or directories on disk. Each partition holds one value, so the engine checks that value once and skips partitions that don't match.
- An indexed column is a column with a data index, which maps each key value to the rows that hold it. The engine looks up matching rows in the index instead of checking every row.
Within one where, the engine can change the order of the filters when the data supports it:
- It applies filters on partitioning columns first.
- It can move filters that support predicate pushdown ahead of others by estimated cost, and the estimate takes data indexes into account.
When the engine can't reorder filters, it applies them in the order you give them, so put the filter that removes the most rows first. Predicate pushdown lists the order in which the engine prioritizes the filters it can reorder.
To keep every filter in the order you give, even when the engine could reorder them, use Barriers or serial filters.
Sometimes the values you filter on live in another table that changes while the query runs. For example, an input table can hold the values a user cares about, and the user can add or remove values during the day. A where filter's values are fixed when you call it, so it never sees those changes. Use where_in or where_not_in instead.
In this example, a time table stands in for the input table, so the filter values change without anyone editing them:
As t2 ticks, its one row alternates between X = 0 and X = 1, so t3 switches between the rows of t1 where X is 0 and the rows where X is 1.
Important
To write fast, correct filters:
- Filter first, so later operations compute on less data.
- Combine filters in a single
wherecall so the engine can reorder them when predicate pushdown applies, and put the most selective filter first for when it can't. - Prefer simple comparisons and match filters to formula filters.
- When the filter values come from a table that changes, use
where_inorwhere_not_ininstead ofwhere.
Use the ternary operator
The ternary operator ? : lets you express an if-else block in a single line of code.
The query string "Z = (X % 2 == 0) ? Y : 42" sets Z to Y when X is even and to 42 otherwise. Using the ternary operator results in concise queries and saves you from writing functions.
Call Java from query strings
Most users work with Deephaven through a programming language such as Python or Groovy, but the Deephaven query engine is written in Java. Occasionally, it is beneficial to use that "Java inside" directly.
This query uses Java twice in its query strings. First, the toBinaryString method from the java.lang.Integer class computes column A, the binary form of X.
Second, DQL strings are java.lang.String objects. Here, the split method splits Y on /, and the [1] array access retrieves the second token. Calling Java String methods inline means you don't need to write a custom string-processing function.
If you find a Java library that implements functionality you need, place it on the classpath, and query strings can call it by its fully qualified class name. For more details, see Install and use Java packages.
Because Java is deeply integrated with Deephaven, you can create Java objects in a Python query with jpy. This technique isn't common, but it helps when you use a Java package.
This example uses jpy.get_type to get the Java ArrayList type and builds a list of strings. The filter keeps the rows whose Letter is in the list, so t1 holds the rows where Letter is "B" or "D". The query string can refer to al because it can use variables from your script.
The example adds strings, not numbers, on purpose. jpy converts each Python string to a java.lang.String, so the values match the String column Letter. A small Python integer doesn't work the same way. jpy converts it to a java.lang.Byte, and a Byte never equals the int or long values in a numeric column.
You now have the power of Python, Java, and Deephaven available within a single query!
Format chained queries
You can chain query operations together to express complex ideas.
While powerful, these chained queries can quickly become difficult to read. The example above already starts each operation on its own line with its leading dot. To clean things up further, put each query string on its own line inside the operation's list.
Because the chain is wrapped in parentheses, you can put comments between operations without backslash line continuations.
With some practice, you develop a feel for how to format chained query operations for maximum readability.
Related documentation
- Table types
- Table operations overview
- Work with time
- Parallelization
- Vectorization and the recipe paradigm
- Create an empty table
- Choose a join method
- Single aggregation
- Create a time table
- Create and use input tables
- Data indexes
- Install and use Java packages
- Iterate over table data
- Predicate pushdown
- Supported Parquet formats
- Query strings
- Work with arrays
- Work with strings
- Use filters
- Filters
- Use NumPy in Deephaven queries
- Use PyTorch in Deephaven
- Use TensorFlow in Deephaven
- Use the ternary conditional operator in query strings
- Use variables in query strings
- Use jpy
- Strings
- Date-times
- Durations
- Periods
- Barrier
empty_tableint_coliter_tuplelast_bymax_bymergenew_tableread_csvselect_distinctstring_coltime_tableupdatewherewhere_inwhere_not_injpyPydoctoLocalDateJavadoc