Programmatically generate query strings with Python

The Deephaven Query Language lets you write powerful query strings that filter and modify tables of data. Consider the following query, which uses a formula to add a new column to a table and a filter to filter the resulting table.

Table operations take query strings as ordinary Python strings, so all of Python's power is available to build them. That helps most when a query is long or repetitive. Let's work through a few examples that Python-generated query strings simplify.

Note

This guide assumes you are familiar with the use of strings, f-strings, loops, and list comprehension in Python. If not, please refer to the Python documentation for more information.

Many columns

In practice, queries may have a large number of inputs, making it inconvenient to type in each column name. Other times, user inputs determine the column names, so you don't know them when you write the query. In both situations, you can generate the query from a list of column names.

In the following example, an f-string and str.join build a query string that adds the values of all the columns in each row and takes the square root.

Add a column name to the list, and the generated query string includes it the next time the code runs:

Repeated logic

Some queries repeat the same logic with minor tweaks. For example, a query may add columns containing data from 1, 5, and 10 rows before the current row. Generated query strings simplify these queries too.

In the following example, an f-string creates columns of data from 1, 5, and 10 rows before. Each formula uses the underscore operator to access column X as an array, and the built-in variable ii to index that array by row position.

X1 is null in the first row, X5 in the first 5 rows, and X10 in the first 10 rows, because those rows have no row that far back.

A list comprehension simplifies this further.

Generated strings are ordinary query strings, so the same code works on static and ticking tables. The constant offsets these examples generate, such as X_[ii - 1], are a form the engine supports on refreshing tables. See Refreshing table restrictions for details.

Time bins

Data analysis, particularly in finance, often groups data into time bins, and a single query may use several bin sizes. Here is a query that uses lowerBin to bin the timestamps at nine bin sizes, written out by hand:

That query is tedious to write, long, and repetitive. It doesn't take advantage of Python's power.

The f-string and list comprehension pattern from Repeated logic does the same thing in a few lines:

This query is shorter, faster to write, and easier to read for a Python programmer. It also makes future updates easier: to change the bins, edit the bin_sizes list and re-run the query.

Other table operations

Programmatically generated strings work as arguments to any table operation that takes strings, not just update. For example, the following query generates the arguments to each natural_join.

Each pass of the loop runs one natural_join. It matches every row to the row offset rows earlier, the one whose X is smaller by offset. It copies that row's Y and Z into new columns named for the offset, such as Y1 and Z1. The first offset rows have no earlier row to match, so their new columns are null.